Details
-
Bug
-
Status: Closed
-
Critical
-
Resolution: Fixed
-
2.7.0
-
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
Activity
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...
How did we miss this in our unit tests ? Also, This was working in 2.1 ..and probably 2.5 (haven't confirmed)