search

couchbase / couchbase-lite-net

A lightweight, document-oriented (NoSQL), syncable database engine for .NET
http://developer.couchbase.com/mobile/
Apache License 2.0
439 stars 126 forks source link

Problem with using existing field names as alias #1207

Closed john-dalsgaard closed 4 years ago

john-dalsgaard commented 4 years ago

Library Version

Couchbase.Lite 2.7.0 (Community Edition)

Visual Studio environment

=== Visual Studio Community 2019 for Mac ===

Version 8.4.8 (build 2) Installation UUID: 0e93c0c9-35a7-4964-a385-abee678b36e8 GTK+ 2.24.23 (Raleigh theme) Xamarin.Mac 5.16.1.25 (issue-7441-d16-3-vsmac / 881172e73)

Package version: 606000166

=== Mono Framework MDK ===

Runtime: Mono 6.6.0.166 (2019-08/d9001b5ae70) (64-bit) Package version: 606000166

=== Roslyn (Language Service) ===

3.4.0-beta4-19562-05+ff930dec4565e2bc424ad3bf3e22ecb20542c87d

=== NuGet ===

Version: 5.3.0.6192

=== .NET Core SDK ===

SDK: /usr/local/share/dotnet/sdk/3.1.102/Sdks SDK Versions: 3.1.102 3.1.101 3.1.100 3.0.101 3.0.100 2.2.401 2.1.701 2.1.700 2.1.505 2.1.504 2.1.503 2.1.401 2.1.302 2.1.301 2.1.4 MSBuild SDKs: /Library/Frameworks/Mono.framework/Versions/6.6.0/lib/mono/msbuild/Current/bin/Sdks

=== .NET Core Runtime ===

Runtime: /usr/local/share/dotnet/dotnet Runtime Versions: 3.1.2 3.1.1 3.1.0 3.0.1 3.0.0 3.0.0-rc1-19456-20 3.0.0-preview9-19423-09 2.2.6 2.1.15 2.1.14 2.1.13 2.1.12 2.1.11 2.1.9 2.1.8 2.1.7 2.1.3 2.1.2 2.1.1 2.0.5

=== Xamarin.Profiler ===

Version: 1.6.12.29 Location: /Applications/Xamarin Profiler.app/Contents/MacOS/Xamarin Profiler

=== Updater ===

Version: 11

=== Xamarin.Android ===

Version: 10.1.3.7 (Visual Studio Community) Commit: xamarin-android/d16-4/d66aed0 Android SDK: /Users/jda/Library/Android/sdk Supported Android versions: 5.1 (API level 22) 6.0 (API level 23) 7.1 (API level 25) 8.0 (API level 26) 8.1 (API level 27)

SDK Tools Version: 26.1.1 SDK Platform Tools Version: 29.0.6 SDK Build Tools Version: 29.0.3

Build Information: Mono: fd9f379 Java.Interop: xamarin/java.interop/d16-4@c4e569f ProGuard: xamarin/proguard/master@905836d SQLite: xamarin/sqlite/3.28.0@46204c4 Xamarin.Android Tools: xamarin/xamarin-android-tools/master@9f4ed4b

=== Microsoft Mobile OpenJDK ===

Java SDK: /Users/jda/Library/Developer/Xamarin/jdk/microsoft_dist_openjdk_8.0.25 1.8.0-25 Android Designer EPL code available here: https://github.com/xamarin/AndroidDesigner.EPL

=== Android SDK Manager ===

Version: 16.4.0.10 Hash: 2c49a7d Branch: remotes/origin/d16-4 Build date: 2020-02-20 19:25:52 UTC

=== Android Device Manager ===

Version: 16.4.0.32 Hash: 7a5cb8b Branch: remotes/origin/d16-4 Build date: 2020-02-20 19:26:14 UTC

=== Apple Developer Tools ===

Xcode 11.3.1 (15715) Build 11E146

=== Xamarin.Mac ===

Version: 6.10.0.21 (Visual Studio Community) Hash: 02c4b3bdc Branch: xcode11.3 Build date: 2020-02-18 14:13:31-0500

=== Xamarin.iOS ===

Version: 13.10.0.21 (Visual Studio Community) Hash: 02c4b3bdc Branch: xcode11.3 Build date: 2020-02-18 14:13:32-0500

=== Xamarin Designer ===

Version: 16.4.0.479 Hash: 074544417 Branch: remotes/origin/d16-4 Build date: 2020-01-22 22:50:22 UTC

=== Xamarin Inspector ===

Version: 1.4.3 Hash: db27525 Branch: 1.4-release Build date: Mon, 09 Jul 2018 21:20:18 GMT Client compatibility: 1

=== Build Information ===

Release ID: 804080002 Git revision: 4f35aa7e44fb398379e512d0bfd6f8df8d34b5ac Build date: 2020-02-27 16:16:52+00 Build branch: release-8.4 Xamarin extensions: 4f35aa7e44fb398379e512d0bfd6f8df8d34b5ac

=== Operating System ===

Mac OS X 10.15.3 Darwin 19.3.0 Darwin Kernel Version 19.3.0 Thu Jan 9 20:58:23 PST 2020 root:xnu-6153.81.5~1/RELEASE_X86_64 x86_64

Log Output

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

Expected behavior

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 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 the following SQLite:

var year = DateTime.Now.Year;
var _condition = Expression.Property(TYPE).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)));
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();
    :

When I run it I receive the error in "Logs" above.

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…

Sandychuang8 commented 4 years ago

I modified one of our unit tests and the result I got was correct. 

var sum = SelectResult.Expression(Function.Sum(Expression.Property("number1"))).As("number1");
....
row.GetInt("number1").Should().Be(5050); // correct result

if you want automation, you can maybe do something like As(nameof(Catch.Count).ToLower()+"alias") and see what kind of result you are getting.

john-dalsgaard commented 4 years ago

Hmmmm.... perhaps it is related to the specific name "count" (which is the name behind the reference to the field)? It's really a matter of avoiding to have to type all field names when handling the result set - and thus avoid typos :-)

Sandychuang8 commented 4 years ago

Not able to repro the issue base on description. Close the issue.