SQLite Forum

sql parser error
Login

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 [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? 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.

(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);

(4) By Keith Medcalf (kmedcalf) on 2021-05-23 17:16:14 in reply to 2 [source]

IN is a scalar operator and does not (presently) operate on row-values in SQLite3.