|18:00||• Fixed ticket [7310e2fb]: Valid row-value syntax is rejected plus 6 other changes (artifact: e1e0573a user: drh)|
|17:59||• New ticket [7310e2fb]. (artifact: f3e9ec99 user: drh)|
|16:38||Fix a bug causing spurious "sub-select returns N columns expected 1" errors in join queries with a term like "(a, b) IN (SELECT ...)" in the WHERE clause. Ticket [7310e2fb3d046a5f5]. (check-in: 14dfd96f user: dan tags: trunk)|
|Title:||Valid row-value syntax is rejected|
|Last Modified:||2018-01-23 18:00:58|
|Version Found In:||3.22.0|
drh added on 2018-01-23 17:59:29:
The SELECT statement below if valid syntax and ought to work, but SQLite rejects it and refuses to evaluate it:
CREATE TABLE x(a, b, PRIMARY KEY (a,b)); CREATE TABLE y(a); CREATE TABLE z(a, b); SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN (SELECT a, b FROM z);
This problem has been in the code since support for row-values was added with version 3.15.0 (2016-10-14). SQLite does not generate an incorrect answer because of this bug. Rather, SQLite merely refuses to process what ought to be valid SQL syntax.
Note that this problem was fixed prior to the ticket being created. The ticket was added after-the-fact for documentation purposes.
drh added on 2018-01-23 18:00:58:
Forgot to say: The problem was reported on the sqlite-users mailing list by Mark Brand.