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

Property alias not working under certain cases

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 2.7.0
    • 2.8.0
    • LiteCore
    • Security Level: Public
    • 8

    Description

      Unit test in iOS

      func test() throws {
              Database.log.console.level = .verbose
              let q = QueryBuilder
                  .select([SelectResult.property("owner.username").as("owner")])
                  .from(DataSource.database(db))
              let rs = try q.execute()
              let res = rs.allResults()
              print(res.count)
          }
      

      Logs

      2019-11-25 22:18:45.399135-0800 xctest[76786:4880045] CouchbaseLite Query Info: {Query#1} Compiling JSON query: {"WHAT":[["AS",[".owner.username"],"owner"]]}
      2019-11-25 22:18:45.399766-0800 xctest[76786:4880045] CouchbaseLite Query Info: {Query#1} Compiled as SELECT fl_result(fl_nested_value("owner", 'username')) AS "owner" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0)
      2019-11-25 22:18:45.399961-0800 xctest[76786:4880045] CouchbaseLite Database ERROR: SQLite error (code 28): double-quoted string literal: "owner"
      

      Result
      When created a SelectResult with alias "owner"(assuming same as the column name, not happening with random string like "something", "any"), it is returning SQLite error.

      CouchbaseLite Database ERROR: SQLite error (code 28): double-quoted string literal: "owner"
      

      For completeness, here are some additional details of the issue and cases under which it fails :

      +Sample Document +

      {
        "created": "2018-05-06T02:34:18",
        "id": "ff88b2b7-5afb-4b27-8aca-e7ad50e4ac7d",
        "modified": "2018-09-11T10:39:33",
        "name": "Playlist # 1 for Nellie",
        "owner": {
          "firstName": "Nellie",
          "lastName": "Henry",
          "picture": {
            "large": "https://randomuser.me/api/portraits/women/69.jpg",
            "medium": "https://randomuser.me/api/portraits/med/women/69.jpg",
            "thumbnail": "https://randomuser.me/api/portraits/thumb/women/69.jpg"
          },
          "title": "Ms",
          "username": "abacusesthronging42801"
        },
        "tracks": [
          "3342EDAB4B2F688FDF8D1E00DC9DB60FBEDD1334",
          "26ECEDD91645E9D8F856FD52FEB20CF70F1056A6",
          "17272A29F039A615A3BF874FD215831F21F9B5A6",
          "5EE3E30773C4DDAEEE7689B77DB1F4201BF8567F",
          "1CD8A504C681EC2465CD1040D227AC6606472378",
          "77D9D84146AB3C5618458FE8990B94A17F1A06D4",
          "4E8F8C84068492B78EA5F2812CC9F31EA0610DCA",
          "99A6A67ECFC090F574C071C60C6B16B4E02A9119",
          "1298D078AE07D841F0F34F088AB61BFFD3703D78",
          "B50D40495911737113C09EA15DD6E520C1D74699"
        ],
        "type": "playlist",
        "ver": "2.0",
        "visibility": "PUBLIC"
      }
      

      +Query #1 (Same as what is indicated above in unit test) +

      let selectResults = [
                  SelectResult.expression(Meta.id),
                  SelectResult.property("type"),
                  SelectResult.property("name"),
                  SelectResult.property("owner.username").as("owner")
              ]
              let datasource: DataSourceAs = DataSource.database(database)
              let whereExp: ExpressionProtocol = Expression.property("type").equalTo(Expression.string("playlist"))
              let orderBy: OrderingProtocol = Ordering.property("modified").descending()
              let limit: ExpressionProtocol = Expression.int(100)
              let searchQuery: Query = QueryBuilder
                  .select(selectResults)
                  .from(datasource)
                  .where(whereExp)
                  .orderBy(orderBy)
                  .limit(limit)
      

      CouchbaseLite Database ERROR: SQLite error (code 28): double-quoted string literal: "owner"
      

      +Query #2 (The following Select Expressions result in the same error below ) +

      let selectResults = [
                  SelectResult.expression(Meta.id),
                  SelectResult.property("type").as("type"),
                  SelectResult.property("name"),
                  SelectResult.property("owner.username")
              ]
      
      

      CouchbaseLite Database ERROR: SQLite error (code 1): no such column: type in "SELECT fl_result(_doc.key), fl_result(type) AS "type", fl_result(fl_value(_doc.body, 'name')), fl_result(fl_value(_doc.body, 'owner.username')) FROM kv_default AS _doc WHERE (type = 'playlist') AND (_doc.flags & 1 = 0) ORDER BY fl_value(_doc.body, 'modified') DESC LIMIT MAX(0, 100)"
      

      +Query #3 (The following Select Expressions work ) +

      let selectResults = [
                  SelectResult.expression(Meta.id),
                  SelectResult.property("type"),
                  SelectResult.property("name"),
                  SelectResult.property("owner.username").as("username")
              ]
       
      let selectResults = [
                  SelectResult.expression(Meta.id),
                  SelectResult.property("type").as("typeVal"),
                  SelectResult.property("name"),
                  SelectResult.property("owner.username")
              ]
      

      works
      

      Attachments

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

        Activity

          priya.rajagopal Priya Rajagopal added a comment - - edited

          How did we miss this in our unit tests ? Also, This was working in 2.1 ..and probably 2.5 (haven't confirmed)

          priya.rajagopal Priya Rajagopal added a comment - - edited How did we miss this in our unit tests ? Also, This was working in 2.1 ..and probably 2.5 (haven't confirmed)
          jens Jens Alfke added a comment - - edited

          I can't get this to produce the SQL syntax error you reported, even by compiling the exact same JSON query {"WHAT":[["AS",[".owner.username"],"owner"]]}. Weird.

          But the SQL definitely seems bad, since the result alias "owner" is defined in terms of itself:

          SELECT fl_result(fl_nested_value("owner", 'username')) AS "owner" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0)

          This is due to commit 5deba45 from June 2019, "Account for result aliases when creating SQL", which fixes Github #761. Looks like the purpose of the change is to allow result aliases to be used by name in ORDER BY expressions. But here it's being used in the WHAT clause...

          jens Jens Alfke added a comment - - edited I can't get this to produce the SQL syntax error you reported, even by compiling the exact same JSON query {"WHAT":[["AS", [".owner.username"] ,"owner"]]}. Weird. But the SQL definitely seems bad, since the result alias "owner" is defined in terms of itself: SELECT fl_result(fl_nested_value("owner", 'username')) AS "owner" FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0) This is due to commit 5deba45 from June 2019, "Account for result aliases when creating SQL", which fixes Github #761 . Looks like the purpose of the change is to allow result aliases to be used by name in ORDER BY expressions. But here it's being used in the WHAT clause...
          jens Jens Alfke added a comment -

          A PR is out for review by Jim.

          jens Jens Alfke added a comment - A PR is out for review by Jim.
          jens Jens Alfke added a comment -

          Fixed in commit 83d1e5e9, now merged to master.

          jens Jens Alfke added a comment - Fixed in commit 83d1e5e9, now merged to master.

          People

            jens Jens Alfke
            priya.rajagopal Priya Rajagopal
            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