unexpected row value error

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

unexpected row value error

Mark Brand
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected row value error

curmudgeon
Probably won't help but the final one works with SELECT in double brackets

SELECT * FROM x
JOIN y ON y.a = x.a
WHERE (x.a, x.b) IN ( ( SELECT a, b FROM z ) );
.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected row value error

petern
In reply to this post by Mark Brand
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected row value error

petern
In reply to this post by curmudgeon
Confirmed that way too.

CREATE TABLE x ( a, b, PRIMARY KEY (a, b) );
CREATE TABLE y ( a );

INSERT INTO x VALUES (1, 1), (1, 2);
INSERT INTO y VALUES (1);

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN (VALUES (1,2));
--Error: sub-select returns 2 columns - expected 1

SELECT * FROM x JOIN y ON y.a = x.a WHERE (x.a, x.b) IN ((VALUES (1,2)));
--a,b,a
--1,2,1

.version
--SQLite 3.22.0 2018-01-12 23:38:10
dec3ea4e4e6c4b1761ddc883a29eaa50dcd663ce6199667cc0ff82f7849d4f2a


On Tue, Jan 23, 2018 at 7:12 AM, curmudgeon <[hidden email]> wrote:

> Probably won't help but the final one works with SELECT in double brackets
>
> SELECT * FROM x
> JOIN y ON y.a = x.a
> WHERE (x.a, x.b) IN ( ( SELECT a, b FROM z ) );
> .
>
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: unexpected row value error

Dan Kennedy-4
In reply to this post by Mark Brand
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users