Compound select statements ending in a VALUES-clause do not work properly.

[cubinator]

Executing a compound select statement of the form

[select-core] [set-operator] [values-clause]
-- e.g. select * from table intersect values (...), ..., (...)
-    or values (...), ..., (...) intersect values (...), ..., (...)

results in a table that has the same shape as the actual result, but every cell is shaded red and contains loading... in italic. Currently, I can work around this by swapping the operands of the [set-operator] (unless it is except or both operands are values clauses), or by wrapping the values clause or the entire compound select statement in a with clause and selecting from it, i.e.

[values-clause] [set-operator] [select-core]

-- OR

with tmp as ( [values-clause] )
[select-core] [set-operator] select * from tmp

-- OR

with tmp as ( [select-core] [set-operator] [values-clause] )
select * from tmp

---

DB4S v3.11.2 [built for x86_64-little_endian-llp64] on Windows 10 (10.0) (winnt/10.0.18362) [x86_64]
using SQLite Version 3.27.2
and Qt 5.11.3

[chrisjlocke]

One for @MKleusberg ...

[MKleusberg]

Thank you for reporting this issue! The problem is that we add a LIMIT x,y clause to the statement for lazy population. So SELECT * FROM tbl actually becomes SELECT * FROM tbl LIMIT 0,9999 or similar. For queries with compound/set operators this however results in a syntax error: SELECT * FROM tbl UNION VALUES(1,2,3) LIMIT 0,9999 is invalid.

Currently it's very hard for us to figure out when adding a LIMIT clause is permitted and when it's not. That's why I have disabled lazy population for all queries containing UNION, INTERSECT, or EXCEPT. It's not the perfect solution but for now it seems like the best compromise.

Can you try tomorrow's nightly build (https://nightlies.sqlitebrowser.org/latest/) and see if it's working for you?

[justinclift]

Sounds like a candidate for 3.12.1 too. 😄

[cubinator]

WOW! I didn't actually except a response this fast. The nightly build you linked produces the correct output for the problematic queries, and it seems to run just fine on all the databases I have lying around locally :)

[justinclift]

Awesome. 😄

@MKleusberg Is this suitable for 3.12.1?

[MKleusberg]

@justinclift Yeah, why not 😄