-- IN + correlated subquery tests
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (a int, b int) engine=columnstore;
CREATE TABLE t2 (c int, d int) engine=columnstore;
PREPARE stmt1 FROM "SELECT * FROM t1 WHERE b IN (SELECT c FROM t2 WHERE t2.d=t1.a or t1.b=2)";
EXECUTE stmt1;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (a int, b int) engine=columnstore;
CREATE TABLE t2 (c int, d int) engine=columnstore;
PREPARE stmt3 FROM "SELECT * FROM t1 WHERE b NOT IN (SELECT c FROM t2 WHERE t2.c=t1.b)";
EXECUTE stmt3;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (a int, b int) engine=columnstore;
INSERT INTO t1 VALUES (1, 2), (2, 4), (3, 1);
CREATE TABLE t2 (c int, d int) engine=columnstore;
INSERT INTO t2 VALUES (1, 3), (2, 5);

PREPARE stmt1 FROM "SELECT * FROM t1 WHERE b IN (SELECT c FROM t2 WHERE t2.d=t1.a or t1.b=2) ORDER BY a,b";
PREPARE stmt2 FROM "SELECT * FROM t1 WHERE b NOT IN (SELECT c FROM t2 WHERE t2.d=t1.a or t1.b=2) ORDER BY a,b";
EXECUTE stmt1;
EXECUTE stmt2;
EXECUTE stmt2;
EXECUTE stmt1;
PREPARE stmt3 FROM "SELECT * FROM t1 WHERE b NOT IN (SELECT c FROM t2 WHERE t2.c=t1.b) ORDER BY a,b";
PREPARE stmt4 FROM "SELECT * FROM t1 WHERE b IN (SELECT c FROM t2 WHERE t2.c=t1.b) ORDER BY a,b";
EXECUTE stmt4;
EXECUTE stmt3;
EXECUTE stmt4;
EXECUTE stmt3;
