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
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