Confirmed. SQLite 3.22.0 2018-01-12 23:38:10 dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a
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 * FROM x JOIN y ON y.a = x.a WHERE (x.a,2) IN (VALUES (1,2)); Error: sub-select returns 2 columns - expected 1 SELECT * FROM z JOIN y ON y.a = z.a WHERE (z.a,2) IN (VALUES (1,2)); a,b,a 1,1,1 1,2,1 On Tue, Jan 23, 2018 at 4:55 AM, Mark Brand <mabr...@mabrand.nl> 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 > > 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
