Repository navigation
Compound select statements ending in a VALUES-clause do not work properly. #2316
Description
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
Activity
chrisjlocke commentedon Jul 1, 2020
One for @MKleusberg ...
Disable lazy popululation for queries with compound operators
MKleusberg commentedon Jul 1, 2020
Thank you for reporting this issue! The problem is that we add a
LIMIT x,yclause to the statement for lazy population. SoSELECT * FROM tblactually becomesSELECT * FROM tbl LIMIT 0,9999or similar. For queries with compound/set operators this however results in a syntax error:SELECT * FROM tbl UNION VALUES(1,2,3) LIMIT 0,9999is invalid.Currently it's very hard for us to figure out when adding a
LIMITclause is permitted and when it's not. That's why I have disabled lazy population for all queries containingUNION,INTERSECT, orEXCEPT. 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 commentedon Jul 1, 2020
Sounds like a candidate for 3.12.1 too. 😄
cubinator commentedon Jul 10, 2020
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 commentedon Jul 10, 2020
Awesome. 😄
@MKleusberg Is this suitable for 3.12.1?
MKleusberg commentedon Jul 10, 2020
@justinclift Yeah, why not 😄
Disable lazy popululation for queries with compound operators