Uploaded image for project: 'Couchbase Lite'
  1. Couchbase Lite
  2. CBL-698

Wrong query evaluation using expression values instead of expression properties

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 2.8.0, 2.7.1
    • LiteCore
    • Security Level: Public
    • None

    Description

      Hi, trying to convert a query from N1QL to Swift-N1QL i received many errors and strange behavior when i start use constant values instead of field name.

      N1QL query

      SELECT 
      		key1 as key1,
      		10 as key2,
      		"" as key3,
      		true as key4,
      		'2020-02-12T15:18:24.573Z' as key5,
      		["A","B"] as key6,
      		1.1 as key7,
      		{"A":"B"} as key8
      	FROM `test-bucket`
      

      Query result

      [
        {
          "key1": "value1",
          "key2": 10,
          "key3": "A",
          "key4": true,
          "key5": "2020-02-12T15:18:24.573Z",
          "key6": [
            "A",
            "B"
          ],
          "key7": 1.1,
          "key8": {
            "A": "B"
          }
        }
      ]
      

      So, if i try to do the same query from Couchbase Lite SDK (in this case Swift version), in the query result i missed the values of keys 3 and 5.

      Query converted to Swift language

      let query = QueryBuilder
                  .select(
                      SelectResult.expression(Expression.property("key1")),
                      SelectResult.expression(Expression.value(10)).as("key2"),
                      SelectResult.expression(Expression.value("A")).as("key3"),
                      SelectResult.expression(Expression.value(true)).as("key4"),
                      SelectResult.expression(Expression.value(Date())).as("key5"),
                      SelectResult.expression(Expression.value(["A","B"])).as("key6"),
                      SelectResult.expression(Expression.value(1.1)).as("key7"),
                      SelectResult.expression(Expression.value(["A":"B"])).as("key8")
                  )
                  .from(DataSource.database(database))
      

      Result from CouchbaseLiteSwift

      po array.first!.toDictionary()
      ▿ 6 elements
        ▿ 0 : 2 elements
          - key : "key1"
          - value : value1
        ▿ 1 : 2 elements
          - key : "key2"
          - value : 10
        ▿ 2 : 2 elements
          - key : "key4"
          - value : 1
        ▿ 3 : 2 elements
          - key : "key6"
          ▿ value : 2 elements
            - 0 : A
            - 1 : B
        ▿ 4 : 2 elements
          - key : "key7"
          - value : 1.1
        ▿ 5 : 2 elements
          - key : "key8"
          ▿ value : 1 element
            ▿ 0 : 2 elements
              - key : A
              - value : B
      

      As you can see the keys 3 and 5 is missing.

      Trying to debug the problem i found that the expression SelectResult.expression(Expression.value("A")).as("key3") use the string "A" as a field path and not as a plain string.

      In fact running query.explain() i get this result

      SELECT fl_result(fl_value(_doc.body, 'key1')), fl_result(10) AS "key2", fl_result(fl_value(_doc.body, 'A')) AS "key3", fl_result(fl_bool(1)) AS "key4", fl_result(fl_value(_doc.body, '2020-02-12T16:38:45.738Z')) AS "key5", fl_result(array_of('A', 'B')) AS "key6", fl_result(1.1000000000000001) AS "key7", fl_result(dict_of('A', 'B')) AS "key8" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0)
       
      2|0|0| SCAN TABLE kv_default AS _doc
       
      {"WHAT":[[".key1"],["AS",10,"key2"],["AS","A","key3"],["AS",true,"key4"],["AS","2020-02-12T16:38:45.738Z","key5"],["AS",["[]","A","B"],"key6"],["AS",1.1000000000000001,"key7"],["AS",{"A":"B"},"key8"]]}
      

      Why key3 is evaluated in this way fl_result(fl_value(_doc.body, 'A')) AS "key3" and not in this way fl_result(fl_value('A')) AS "key3"?

      Furthermore if i try to use an alias for database then i receive a fatal error

      2020-02-12 18:13:29.386587+0100 CouchbaseQueryIssueTestCase[99001:1630323] CouchbaseLite Database WARNING: Invalid LiteCore query: property 'A.' does not begin with a declared 'AS' alias
      Error Domain=CouchbaseLite Code=23 "property 'A.' does not begin with a declared 'AS' alias" UserInfo={NSLocalizedDescription=property 'A.' does not begin with a declared 'AS' alias}
      

      Test case to reproduce the issue

      let database: Database
              do {
                  database = try Database(name: "testDB")
              } catch {
                  fatalError("Error opening database")
              }
       
              let mutableDoc = MutableDocument()
                  .setString("value1", forKey: "key1")
       
              do {
                  try database.saveDocument(mutableDoc)
              } catch {
                  fatalError("Error saving document")
              }
       
              let query = QueryBuilder
                  .select(
                      SelectResult.expression(Expression.property("key1")),
                      SelectResult.expression(Expression.value(10)).as("key2"),
                      SelectResult.expression(Expression.value("A")).as("key3"),
                      SelectResult.expression(Expression.value(true)).as("key4"),
                      SelectResult.expression(Expression.value(Date())).as("key5"),
                      SelectResult.expression(Expression.value(["A","B"])).as("key6"),
                      SelectResult.expression(Expression.value(1.1)).as("key7"),
                      SelectResult.expression(Expression.value(["A":"B"])).as("key8")
                  )
                  .from(DataSource.database(database))
       
              do {
                  print(try query.explain())
                  let array = try query.execute().allResults()
       
                  let keysCount = array.first!.keys.count
                  let valuesCount = array.first!.toDictionary().map { (_,value) in value }.count
                  assert(keysCount == valuesCount)
              } catch {
                  print(error)
              }
      

      This issue can be reproduced also with Couchbase Lite Java CE & EE so i think the problem is in the core layer.

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          Jayahari.Vavachan Jay Vavachan added a comment - - edited

          Attached the logs cbl-698.txt , seems like the lite core is returning `null` in case of String and Date.
          `

          {"WHAT":[["AS","AB","key1"],["AS","2020-02-19T19:42:56.451Z","key2"]]}

          `
          `SELECT fl_result(fl_value(_doc.body, 'AB')) AS "key1", fl_result(fl_value(_doc.body, '2020-02-19T19:42:56.451Z')) AS "key2" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0)`

          Result
          [null,null]

          Unit test

          let srs: [SelectResultProtocol] = [
                      SelectResult.expression(Expression.value("AB")).as("key1"),
                      SelectResult.expression(Expression.value(Date())).as("key2")]
                  let query = QueryBuilder.select(srs).from(DataSource.database(self.db))
                  
                  let results = try! query.execute().allResults()
          

          Logs

          2020-02-19 11:42:56.453451-0800 xctest[72508:3317090] CouchbaseLite Query Info: {Query#2}==> litecore::SQLiteQuery 0x610000033270 @0x610000033270
          2020-02-19 11:42:56.453555-0800 xctest[72508:3317090] CouchbaseLite Query Info: {Query#2} Compiling JSON query: {"WHAT":[["AS","AB","key1"],["AS","2020-02-19T19:42:56.451Z","key2"]]}
          2020-02-19 11:42:56.454105-0800 xctest[72508:3317090] CouchbaseLite Query Info: {Query#2} Compiled as SELECT fl_result(fl_value(_doc.body, 'AB')) AS "key1", fl_result(fl_value(_doc.body, '2020-02-19T19:42:56.451Z')) AS "key2" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0)
          2020-02-19 11:42:56.454796-0800 xctest[72508:3317090] CouchbaseLite Query Info: {QueryEnum#3}==> litecore::SQLiteQueryEnumerator 0x60e0000331d8 @0x60e0000331d8
          2020-02-19 11:42:56.454897-0800 xctest[72508:3317090] CouchbaseLite Query Info: {QueryEnum#3} Created on {Query#2} with 1 rows (14 bytes) in 0.167ms
          2020-02-19 11:42:56.455061-0800 xctest[72508:3317090] CouchbaseLite Query Info: Beginning query enumeration (0x60b00003dc40)
          2020-02-19 11:42:56.455187-0800 xctest[72508:3317090] CouchbaseLite Query Verbose: {QueryEnum#3} --> [null,null]
          2020-02-19 11:42:56.455339-0800 xctest[72508:3317090] CouchbaseLite Query Verbose: {QueryEnum#3} END
          2020-02-19 11:42:56.455434-0800 xctest[72508:3317090] CouchbaseLite Query Info: End of query enumeration (0x60b00003dc40)
          

          Also found missing unit tests in platform side, when using `Expression.value(<>).as(<>)`, for all possible types.

          Jayahari.Vavachan Jay Vavachan added a comment - - edited Attached the logs cbl-698.txt , seems like the lite core is returning `null` in case of String and Date. ` {"WHAT":[["AS","AB","key1"],["AS","2020-02-19T19:42:56.451Z","key2"]]} ` `SELECT fl_result(fl_value(_doc.body, 'AB')) AS "key1", fl_result(fl_value(_doc.body, '2020-02-19T19:42:56.451Z')) AS "key2" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0)` Result [null,null] Unit test let srs: [SelectResultProtocol] = [ SelectResult.expression(Expression.value("AB")).as("key1"), SelectResult.expression(Expression.value(Date())).as("key2")] let query = QueryBuilder.select(srs).from(DataSource.database(self.db)) let results = try! query.execute().allResults() Logs 2020-02-19 11:42:56.453451-0800 xctest[72508:3317090] CouchbaseLite Query Info: {Query#2}==> litecore::SQLiteQuery 0x610000033270 @0x610000033270 2020-02-19 11:42:56.453555-0800 xctest[72508:3317090] CouchbaseLite Query Info: {Query#2} Compiling JSON query: {"WHAT":[["AS","AB","key1"],["AS","2020-02-19T19:42:56.451Z","key2"]]} 2020-02-19 11:42:56.454105-0800 xctest[72508:3317090] CouchbaseLite Query Info: {Query#2} Compiled as SELECT fl_result(fl_value(_doc.body, 'AB')) AS "key1", fl_result(fl_value(_doc.body, '2020-02-19T19:42:56.451Z')) AS "key2" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0) 2020-02-19 11:42:56.454796-0800 xctest[72508:3317090] CouchbaseLite Query Info: {QueryEnum#3}==> litecore::SQLiteQueryEnumerator 0x60e0000331d8 @0x60e0000331d8 2020-02-19 11:42:56.454897-0800 xctest[72508:3317090] CouchbaseLite Query Info: {QueryEnum#3} Created on {Query#2} with 1 rows (14 bytes) in 0.167ms 2020-02-19 11:42:56.455061-0800 xctest[72508:3317090] CouchbaseLite Query Info: Beginning query enumeration (0x60b00003dc40) 2020-02-19 11:42:56.455187-0800 xctest[72508:3317090] CouchbaseLite Query Verbose: {QueryEnum#3} --> [null,null] 2020-02-19 11:42:56.455339-0800 xctest[72508:3317090] CouchbaseLite Query Verbose: {QueryEnum#3} END 2020-02-19 11:42:56.455434-0800 xctest[72508:3317090] CouchbaseLite Query Info: End of query enumeration (0x60b00003dc40) Also found missing unit tests in platform side, when using `Expression.value(<>).as(<>)`, for all possible types.

          This issue exists in Cobalt and Mercury branch, and iridium branch is throwing an exception. " Invalid property name 'AB'; must start with '.' "

          2020-02-19 12:05:17.480303-0800 xctest[76974:3342558] CouchbaseLite Query Info: {Query#1}==> litecore::SQLiteQuery 0x60f00001d7e8 @0x60f00001d7e8
          2020-02-19 12:05:17.480455-0800 xctest[76974:3342558] CouchbaseLite Query Info: {Query#1} Compiling JSON query: {"WHAT":[["AS","AB","key1"],["AS","2020-02-19T20:05:17.478Z","key2"]]}
          2020-02-19 12:05:17.480988-0800 xctest[76974:3342558] CouchbaseLite Database WARNING: Invalid LiteCore query: Invalid property name 'AB'; must start with '.'
          exception Error Domain=CouchbaseLite Code=23 "Invalid property name 'AB'; must start with '.'" UserInfo={NSLocalizedDescription=Invalid property name 'AB'; must start with '.'}
          

          Jayahari.Vavachan Jay Vavachan added a comment - This issue exists in Cobalt and Mercury branch, and iridium branch is throwing an exception. " Invalid property name 'AB'; must start with '.' " 2020-02-19 12:05:17.480303-0800 xctest[76974:3342558] CouchbaseLite Query Info: {Query#1}==> litecore::SQLiteQuery 0x60f00001d7e8 @0x60f00001d7e8 2020-02-19 12:05:17.480455-0800 xctest[76974:3342558] CouchbaseLite Query Info: {Query#1} Compiling JSON query: {"WHAT":[["AS","AB","key1"],["AS","2020-02-19T20:05:17.478Z","key2"]]} 2020-02-19 12:05:17.480988-0800 xctest[76974:3342558] CouchbaseLite Database WARNING: Invalid LiteCore query: Invalid property name 'AB'; must start with '.' exception Error Domain=CouchbaseLite Code=23 "Invalid property name 'AB'; must start with '.'" UserInfo={NSLocalizedDescription=Invalid property name 'AB'; must start with '.'}
          jimb Jim Borden added a comment -

          Understandable that it is returning null in those cases because it's looking for a property with the string as the key instead of just using the string itself for some reason.  Probably mistaken shorthand Jens Alfke ?

          jimb Jim Borden added a comment - Understandable that it is returning null in those cases because it's looking for a property with the string as the key instead of just using the string itself for some reason.  Probably mistaken shorthand Jens Alfke ?
          jens Jens Alfke added a comment -

          "WHAT":["AS","A","key3"] means to return the literal string "A" as column key3. But it looks like it gets interpreted as a property name instead. Weird...

          jens Jens Alfke added a comment - "WHAT": ["AS","A","key3"] means to return the literal string "A" as column key3. But it looks like it gets interpreted as a property name instead. Weird...
          jens Jens Alfke added a comment -

          Ah, it's because of an ugly hack where QueryParser::parseStringLiteral() interprets the string as a property not a literal, if it's in the context of a column-list or result-list.

          I have a fix out for review.

          jens Jens Alfke added a comment - Ah, it's because of an ugly hack where QueryParser::parseStringLiteral() interprets the string as a property not a literal, if it's in the context of a column-list or result-list. I have a fix out for review .
          Jayahari.Vavachan Jay Vavachan added a comment - - edited

          Also about the other question in the issue,

          if i try to use an alias for database then i receive a fatal error

          when using database alias, make sure to include property alias too. For example, `key1` is a property expression with alias.

          // if not mention the property alias, it will cause an error 
          SelectResult.expression(Expression.property("key1").from("db2"))
           
          // query with database alias
           let query = QueryBuilder.select(srs).from(DataSource.database(self.db).as("db2"))
          

          If we didn't include the property alias, it will cause a

          Fatal error: 'try!' expression unexpectedly raised an error: Error Domain=CouchbaseLite Code=23 "property 'key1.' does not begin with a declared 'AS' alias" UserInfo={NSLocalizedDescription=property 'key1.' does not begin with a declared 'AS' alias}: ..... 
          

           

          Jayahari.Vavachan Jay Vavachan added a comment - - edited Also about the other question in the issue, if i try to use an alias for database then i receive a fatal error when using database alias, make sure to include property alias too. For example, `key1` is a property expression with alias. // if not mention the property alias, it will cause an error SelectResult.expression(Expression.property("key1").from("db2"))   // query with database alias let query = QueryBuilder.select(srs).from(DataSource.database(self.db).as("db2")) If we didn't include the property alias, it will cause a Fatal error: 'try!' expression unexpectedly raised an error: Error Domain=CouchbaseLite Code=23 "property 'key1.' does not begin with a declared 'AS' alias" UserInfo={NSLocalizedDescription=property 'key1.' does not begin with a declared 'AS' alias}: .....  
          Jayahari.Vavachan Jay Vavachan added a comment - - edited

          Mercury commit -> 14476c

          Master(> Hydrogen) -> e30f0

          Jayahari.Vavachan Jay Vavachan added a comment - - edited Mercury commit -> 14476c Master(> Hydrogen) -> e30f0

          People

            Jayahari.Vavachan Jay Vavachan
            Jayahari.Vavachan Jay Vavachan
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty