On 01/23/2018 07:55 PM, Mark Brand wrote:
Hi,
The 6th SELECT example below throws an error. This seems unexpected,
especially given the contrast with example 3, which differs only in
lacking a seemingly unrelated JOIN. Am I overlooking something?
Removing the PRIMARY KEY from table x also avoids the error somehow.
Seen on version 3.22.0, and also on 3.19.3.
Mark
Hi Mark,
Thanks for looking into this one. Looks like it has been in since 3.15.0
(when row-value support was added). Now fixed here:
http://www.sqlite.org/src/info/14dfd96f9bca2df5
Dan.
CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );
CREATE TABLE z ( a, b );
INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);
INSERT INTO z VALUES (1, 1), (1, 2);
SELECT sqlite_version();
-- CASE 1: OK
SELECT * FROM x
WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );
-- CASE 2: OK
SELECT * FROM x
WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );
-- CASE 3: OK
SELECT * FROM x
WHERE (x.a, x.b) IN ( SELECT a, b FROM z );
-- CASE 4: OK
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (SELECT x.a, x.b) IN ( SELECT a, b FROM z );
-- CASE 5: OK
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (VALUES(x.a, x.b)) IN ( SELECT a, b FROM z );
-- CASE 6: ERROR
SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (x.a, x.b) IN ( SELECT a, b FROM z );
/*
sqlite_version()
3.22.0
a|b
1|1
1|2
a|b
1|1
1|2
a|b
1|1
1|2
a|b|a
1|1|1
1|2|1
a|b|a
1|1|1
1|2|1
Error: near line 34: sub-select returns 2 columns - expected 1
*/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
