Subquery returns no rows on SQLite about swift-kuery HOT 6 CLOSED

Similarly:

Select(from: t).group(by: t.a, t.b).having(Parameter().in(Parameter(), Parameter()))

is generating:

SELECT * FROM tableSubquery GROUP BY tableSubquery.a, tableSubquery.b HAVING ?1 IN (?2, ?3)

which is returning no rows (instead of 6, as the test expects).

from swift-kuery.

SELECT * FROM tableSubquery GROUP BY tableSubquery.a, tableSubquery.b HAVING ?1 IN (?2, ?3)

is actually

SELECT * FROM tableSubquery GROUP BY tableSubquery.a, tableSubquery.b HAVING true IN (true, false)

and it looks like SQLite doesn't have boolean values. I'll add this to QueryBuilder.

from swift-kuery.

SELECT * FROM tableSubquery WHERE -7 IN (SELECT tableSubquery.b FROM tableSubquery WHERE tableSubquery.b = -1)

should return an empty result. Does SQLite always return nil instead of empty result? If so, we just need to change the tests.

I'm confused though, because as far as I can see the subquery should return no rows because t.b is never -1?

Yes, the subquery should return no rows, as well as the query, and the expected number of rows is 0. Posgres returns an empty result as expected.

from swift-kuery.

@ianpartridge Please change the tests in TestSubquery from:

s = Select(from: t).group(by: t.a, t.b).having(Parameter().in(Parameter(), Parameter()))   
executeQueryWithParameters(query: s, connection: connection, parameters: true, true, false) { 
...

s = Select(from: t).where(false.notIn(Parameter(), Parameter())) 
executeQueryWithParameters(query: s, connection: connection, parameters: true, true) {       

to

s = Select(from: t).group(by: t.a, t.b).having(Parameter().in(Parameter(), Parameter())) 
executeQueryWithParameters(query: s, connection: connection, parameters: 1, 1, 0) { 
...

s = Select(from: t).where(false.notIn(Parameter(), Parameter())) 
executeQueryWithParameters(query: s, connection: connection, parameters: 1, 1) {       

Here we pass booleans as parameters, and SQLite tests should use 1/0 instead of true/false.

from swift-kuery.

Ah, the SQLite driver doesn't support parameters at the moment. I haven't worked out whether that's a limitation of SQLite or the driver.

from swift-kuery.

Fixed

from swift-kuery.