PREPARE stmt FROM
"SELECT l_shipmode FROM (SELECT l_shipmode, l_comment FROM lineitem) t1 GROUP BY l_shipmode ORDER BY l_shipmode";
EXECUTE stmt;
EXECUTE stmt;
SELECT l_shipmode FROM (SELECT l_shipmode, l_comment FROM lineitem) t1 GROUP BY l_shipmode ORDER BY l_shipmode;
EXECUTE stmt;
EXECUTE stmt;

-- Inner Join
CREATE TABLE t1 (a int, b int) engine=columnstore;
INSERT INTO t1 VALUES (1, 2), (2, 4), (3, 6);
CREATE TABLE t2 (a int, b int) engine=columnstore;
INSERT INTO t2 VALUES (1, 3), (2, 5);
PREPARE stmt FROM "SELECT t1.a AS t1_a, t1.b AS t1_b, t2.b AS t2_b FROM (t1 JOIN t2 ON t1.a=t2.a) WHERE t1.a >= ? ORDER BY 1,2,3";
SET @parm=1;
EXECUTE stmt USING @parm;
EXECUTE stmt USING @parm;
SELECT t1.a AS t1_a, t1.b AS t1_b, t2.b AS t2_b FROM (t1 JOIN t2 ON t1.a=t2.a) ORDER BY 1,2,3;
EXECUTE stmt USING @parm;
EXECUTE stmt USING @parm;
SET @parm=2;
EXECUTE stmt USING @parm;
EXECUTE stmt USING @parm;

-- Left Join
PREPARE stmt FROM "SELECT t1.a AS t1_a, t1.b AS t1_b, t2.b AS t2_b FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) WHERE t1.a >= ? ORDER BY 1,2,3";
SET @parm=1;
EXECUTE stmt USING @parm;
EXECUTE stmt USING @parm;
SELECT t1.a AS t1_a, t1.b AS t1_b, t2.b AS t2_b FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) ORDER BY 1,2,3;
EXECUTE stmt USING @parm;
EXECUTE stmt USING @parm;
SET @parm=2;
EXECUTE stmt USING @parm;
EXECUTE stmt USING @parm;

-- Subquery tests
PREPARE notinstmt FROM "SELECT * FROM t1 WHERE a NOT IN (SELECT a FROM t2 WHERE a = ?)";
SET @parm=1;
EXECUTE notinstmt USING @parm;
PREPARE instmt FROM "SELECT * FROM t1 WHERE a IN (SELECT a FROM t2 WHERE a IN (?,?))";
EXECUTE instmt USING @parm, 2;
EXECUTE notinstmt USING @parm;
EXECUTE instmt USING @parm, 3;

PREPARE fromsubquery FROM "SELECT * FROM (SELECT * FROM t1 WHERE a > ?) tt";
SET @parm=2;
EXECUTE fromsubquery USING @parm;
EXECUTE fromsubquery USING 1;
SELECT * FROM (SELECT * FROM t1 WHERE a > 1) tt;
EXECUTE fromsubquery USING @parm;
EXECUTE fromsubquery USING 3;

-- Group by tests
INSERT INTO t1 VALUES (1, 10);
PREPARE stmt FROM "SELECT a, SUM(b) FROM t1 GROUP BY a ORDER BY a";
PREPARE stmt2 FROM "SELECT a, SUM(b) FROM t1 GROUP BY a HAVING SUM(b) > ? ORDER BY a";
EXECUTE stmt;
SET @parm=10;
EXECUTE stmt2 USING @parm;
EXECUTE stmt;
EXECUTE stmt2 USING @parm;
SELECT a, SUM(b) FROM t1 GROUP BY a ORDER BY a;
EXECUTE stmt;
EXECUTE stmt2 USING @parm;
EXECUTE stmt;
EXECUTE stmt2 USING @parm;

-- Basic DML tests
CREATE TABLE t1 (i int) engine=columnstore;
PREPARE instmt FROM "INSERT INTO t1 VALUES (?)";
EXECUTE instmt USING 45;
INSERT INTO t1 VALUES (-45);
EXECUTE instmt USING 10;
PREPARE selstmt FROM "SELECT * FROM t1";
EXECUTE selstmt;

PREPARE updstmt FROM "UPDATE t1 SET i=? WHERE i=?";
EXECUTE updstmt USING 46,45;
UPDATE t1 SET i=-46 WHERE i=-45;
EXECUTE selstmt;
PREPARE updstmt FROM "UPDATE t1 SET i=? WHERE i NOT IN (SELECT i FROM t1 WHERE i >= ?)";
EXECUTE updstmt USING 460,10;
EXECUTE selstmt;

PREPARE delstmt FROM "DELETE FROM t1 WHERE i = ?";
EXECUTE delstmt USING 460;
EXECUTE selstmt;
EXECUTE delstmt USING 46;
EXECUTE selstmt;
