sql parser error
(1) By eternal (yh2216) on 2021-05-23 07:14:39 [link] [source]
when I exec this sql: "SELECT * FROM tt WHERE (a, b) IN ( (1, 2), (3, 4) );",I received the error message:"row value misused".
The table tt 's DDL is:
CREATE TABLE IF NOT EXISTS tt ( a, b );
then the tt 's DML is:
INSERT INTO tt VALUES ( 1, 2 ); INSERT INTO tt VALUES ( 3,4 );
INSERT INTO tt VALUES ( 5, 6 );
and then exec the sql"SELECT * FROM tt WHERE (a, b) IN ( (1, 2), (3, 4) );",and an error returned!
(2) By eternal (yh2216) on 2021-05-23 12:21:14 in reply to 1 [source]
I test the sql with mysql ,and I received the right result! Why the sql can't run on sqlite,could some one help me? Thanks a lot.
(3) By Stephan Beal (stephan) on 2021-05-23 14:05:23 in reply to 2 [link] [source]
I test the sql with mysql ,and I received the right result! Why the sql can't run on sqlite,could some one help me?
The syntax of passing a parenthesized multi-column expression to WHERE is apparently a MySQL-ism, not supported by sqlite (at least in this context). Not all SQL runs on all SQL engines.
(4) By Keith Medcalf (kmedcalf) on 2021-05-23 17:16:14 in reply to 2 [link] [source]
IN is a scalar operator and does not (presently) operate on row-values in SQLite3.
(5) By eternal (yh2216) on 2021-05-24 01:39:09 in reply to 3 [link] [source]
Thanks a lot, using the CTE,I received the right result.
with my_table(a,b) as( values(1,2),(3,4) ) SELECT * FROM tt WHERE (a,b) IN (SELECT a,b FROM my_table);