Problem with SQLite query with sums

I have this N1QL query on the server that I also want to run in my app:

SELECT specieskey, year, sum(count) count, sum(IFMISSINGORNULL(takenhome,0)) takenhome,assocquotakey,assocquotaname,assocquotacount,assocsizerange
FROM data
WHERE type="Catch" 
	AND assockey="1"
	AND assocquotacount > 0
	AND year=2020
GROUP BY year,specieskey,assocquotakey,assocquotaname,assocquotacount,assocsizerange

I have tried to convert it to SQLite:

var year = DateTime.Now.Year;
var _condition = Expression.Property(TYPE).EqualTo(Expression.String(typeof(Catch).Name));
_condition.And(Expression.Property(nameof(Catch.AssocKey).ToLower()).EqualTo(Expression.String(assocKey)));
_condition.And(Expression.Property(nameof(Catch.AssocQuotaCount).ToLower()).GreaterThan(Expression.Int(0)));
_condition.And(Expression.Property(nameof(Catch.Year).ToLower()).EqualTo(Expression.Int(year)));
var _groupBy = new IExpression[] {
    Expression.Property(nameof(Catch.Year).ToLower()),
    Expression.Property(nameof(Catch.SpeciesKey).ToLower()),
    Expression.Property(nameof(Catch.AssocQuotaKey).ToLower()),
    Expression.Property(nameof(Catch.AssocQuotaName).ToLower()),
    Expression.Property(nameof(Catch.AssocQuotaCount).ToLower()),
    Expression.Property(nameof(Catch.AssocSizeRange).ToLower())
};
using (var query = QueryBuilder.Select(
            SelectResult.Property(nameof(Catch.SpeciesKey).ToLower()),
            SelectResult.Property(nameof(Catch.Year).ToLower()),
            SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.Count).ToLower())))
                .As(nameof(Catch.Count).ToLower()),
            SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.TakenHome).ToLower())))
                .As(nameof(Catch.TakenHome).ToLower()),
            SelectResult.Property(nameof(Catch.AssocQuotaKey).ToLower()),
            SelectResult.Property(nameof(Catch.AssocQuotaName).ToLower()),
            SelectResult.Property(nameof(Catch.AssocQuotaCount).ToLower()),
            SelectResult.Property(nameof(Catch.AssocSizeRange).ToLower())
        )
        .From(dbSource)
        .Where(_condition)
        .GroupBy(_groupBy)
    )
{
    var result = query?.Execute()?.AllResults();
    :

But when I run it I get the following error:

SQLite error (code 1): no such column: count in "SELECT fl_result(fl_value(_doc.body, 'specieskey')), fl_result(fl_value(_doc.body, 'year')), fl_result(sum(count)) AS "count", fl_result(sum(takenhome)) AS "takenhome", fl_result(fl_value(_doc.body, 'assocquotakey')), fl_result(fl_value(_doc.body, 'assocquotaname')), fl_result(fl_value(_doc.body, 'assocquotacount')), fl_result(fl_value(_doc.body, 'assocsizerange')) FROM kv_default AS _doc WHERE (fl_value(_doc.body, 'type') = 'Catch') AND (_doc.flags & 1 = 0) GROUP BY fl_value(_doc.body, 'year'), fl_value(_doc.body, 'specieskey'), fl_value(_doc.body, 'assocquotakey'),
2020-03-24 15:05:18.926772+0100 Angler.iOS[6931:1003747] [Thread Pool Worker]| ERROR)  [Database] no such column: count (1/1)
2020-03-24 15:05:18.927196+0100 Angler.iOS[6931:1003747] [Thread Pool Worker]| WARNING)  [Database] {DB#1}==> litecore::SQLiteDataFile /Users/jda/Library/Developer/CoreSimulator/Devices/AF4326D5-8F20-4103-BCDF-C0D5236F2640/data/Containers/Data/Application/EEEF9117-EC1C-405D-89BA-3AB8F09C4F9D/Library/Application Support/CouchbaseLite/data/angler.cblite2/db.sqlite3 @0x7f9f93e151b0
2020-03-24 15:05:18.927395+0100 Angler.iOS[6931:1003747] [Thread Pool Worker]| WARNING)  [Database] {DB#1} SQLite error compiling statement "SELECT fl_result(fl_value(_doc.body, 'specieskey')), fl_result(fl_value(_doc.body, 'year')), fl_result(sum(count)) AS "count", fl_result(sum(takenhome)) AS "takenhome", fl_result(fl_value(_doc.body, 'assocquotakey')), fl_result(fl_value(_doc.body, 'assocquotaname')), fl_result(fl_value(_doc.body, 'assocquotacount')), fl_result(fl_value(_doc.body, 'assocsizerange')) FROM kv_default AS _doc WHERE (fl_value(_doc.body, 'type') = 'Catch') AND (_doc.flags & 1 = 0) GROUP BY fl_value(_doc.body, 'year'), fl_value(_doc.body, 'specieskey'), fl_value(_doc.body, 'assocquotakey'), fl_value(_doc.body, 'assocquotaname'), fl_value(_doc.body, 'assocquotacount'), fl_value(_doc.body, 'assocsizerange')": no such column: count

I thought I had “count” covered by using an alias for the sum function?

Further tests show that if I use this instead:

    SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.Count).ToLower())))
        .As("s1"),
    SelectResult.Expression(Function.Sum(Expression.Property(nameof(Catch.TakenHome).ToLower())))
        .As("s2"),

and

    Count = record.GetInt("s1"),
    TakenHome = record.GetInt("s2"),

then it runs… However, it does not seem to group the data and returns a resultset that seems to be all records…???

Not sure whether this is a problem in the sum function or the GroupBy - or both? What should I do to get this working?

Thanks in advance!

At first glance this looks like a bug in the query generation that will need to be fixed. The final query should not be sum(count) but something like sum(fl_value(xxxx)). There should also be JSON logged before that, could you paste that in here too?

Regardless, this looks like a defect. If you could make an issue on the .NET repo with this query, and a small data set that shows the undesired result then it will greatly aid the process of debugging it.

Ok… after having reported the entire issue here I stumpled over an issue in my code!!!

So looking at the condition it should have been nested:

var _condition = typeProperty.EqualTo(Expression.String(typeof(Catch).Name))
                .And(Expression.Property(nameof(Catch.AssocKey).ToLower()).EqualTo(Expression.String(assocKey)))
                .And(Expression.Property(nameof(Catch.AssocQuotaCount).ToLower()).GreaterThan(Expression.Int(0)))
                .And(Expression.Property(nameof(Catch.Year).ToLower()).EqualTo(Expression.Int(year)));

So totally an issue on my end. First attempts using this condition seem to work as expected!

Sorry for such a dumb issue :flushed:

… well, the issue with .As(....) still exists. But using constants like “s1” or “s2” solves this

Which issue is that? I saw there was an issue solved by using As but not one created but it. I think there is an issue to be fixed lying around in here. The proper repo for mobile .NET is “couchbase-lite-net” by the way. The one in your first edit is the .NET Server SDK.

Ah, right. The issue with .As(...) is still there. However, during my tests where I used .As("s1") I found that I received all records - and that issue was a problem with my code. I probably lost focus in my comment after I had documented a rather long and detailed test where I ended up removing it again as I found the issue in my condition statements.

I’ve reported it as issue #1207