SQLite Forum

sql parser error
Login

sql parser error

(1) By eternal (yh2216) on 2021-05-23 07:14:39

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]

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]

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

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]

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