# Test cases for MCOL-4543
# The test cases demonstrate that non-referenced subquery columns
# (by non-reference we mean the subquery column is not
# referenced/used by the outer query) which are optimized out
# by the patch for MCOL-4543 do not impact the query results.

-- source ../include/have_columnstore.inc

--disable_warnings
DROP DATABASE IF EXISTS mcol4543;
--enable_warnings

CREATE DATABASE mcol4543;

USE mcol4543;

CREATE TABLE t1 (a int, b int) engine=columnstore;

INSERT INTO t1 values (1, 1), (2, 1), (3, 2), (4, 2), (5, 2);

# Test subquery columns referenced/not-referenced in simple projections
SELECT "123" FROM (SELECT * FROM t1) q;
SELECT "123" FROM (SELECT "234" FROM t1) q;
SELECT a FROM (SELECT * FROM t1) q;
SELECT b FROM (SELECT * FROM t1) q;
SELECT a,b FROM (SELECT * FROM t1) q;
SELECT b,a FROM (SELECT * FROM t1) q;
SELECT a FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1) q1) q2) q3;
SELECT b FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1) q1) q2) q3;
SELECT a FROM (SELECT b,a FROM (SELECT * FROM t1) q1) q2;
SELECT b FROM (SELECT b,a FROM (SELECT * FROM t1) q1) q2;

# Test subquery columns referenced/not-referenced in group by's and aggregates
SELECT "123" FROM (SELECT * FROM t1) q GROUP BY a ORDER BY a;
SELECT "123" FROM (SELECT * FROM t1) q GROUP BY b ORDER BY b;
SELECT "123" FROM (SELECT * FROM t1) q GROUP BY a,b ORDER BY a,b;
SELECT COUNT(a) FROM (SELECT * FROM t1) q GROUP BY a ORDER BY a;
SELECT COUNT(b) FROM (SELECT * FROM t1) q GROUP BY b ORDER BY b;
SELECT COUNT(a) FROM (SELECT * FROM t1) q GROUP BY b ORDER BY b;
SELECT COUNT(b) FROM (SELECT * FROM t1) q GROUP BY a ORDER BY a;
SELECT c1 FROM (SELECT a AS c1, COUNT(a) AS c2 FROM t1 GROUP BY c1) q ORDER BY c1;
SELECT c2 FROM (SELECT a AS c1, COUNT(a) AS c2 FROM t1 GROUP BY c1) q ORDER BY c2;
SELECT * FROM (SELECT a AS c1, COUNT(a) AS c2 FROM t1 GROUP BY c1) q ORDER BY c1,c2;

# Test subquery columns referenced/not-referenced in joins
SELECT tab1.a FROM t1 tab1 JOIN (SELECT * FROM t1) tab2 ON tab1.a=tab2.a ORDER BY tab1.a;
SELECT tab1.a FROM t1 tab1 JOIN (SELECT * FROM t1) tab2 ON tab1.b=tab2.b ORDER BY tab1.a;
SELECT tab1.a FROM t1 tab1 JOIN (SELECT * FROM t1) tab2 ON tab1.b=tab2.b and tab1.a=tab2.a ORDER BY tab1.a;
SELECT tab1.a, tab2.b FROM t1 tab1 JOIN (SELECT * FROM t1) tab2 ON tab1.a=tab2.a ORDER BY tab1.a,tab2.b;

# Test subquery columns referenced/not-referenced when subqueries contain unions
SELECT COUNT(a) FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q;
SELECT COUNT(b) FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q;
SELECT COUNT(b), COUNT(a) FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q;
SELECT COUNT(a) FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q GROUP BY b ORDER BY b;
SELECT q1.a FROM (SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q1_1) q1 JOIN
(SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q2_1) q2 ON q1.a=q2.a ORDER BY 1;
SELECT q1.a, q2.b FROM (SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q1_1) q1 JOIN
(SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t1) q2_1) q2 ON q1.a=q2.a ORDER BY 2 desc, 1 asc;

# Patch for MCOL-4543 also optimizes out an unnecessary BPS projection in PrimProc
# that was happening earlier. The following 2 queries trigger this optimization.
# To see the optimization, run "select calgettrace();" after the query execution.
SELECT "123" FROM (SELECT * FROM t1) q GROUP BY b ORDER BY b;
SELECT "123" FROM (SELECT * FROM t1) q GROUP BY b;

DROP DATABASE mcol4543;