diff options
Diffstat (limited to 'tests/fixtures')
| -rw-r--r-- | tests/fixtures/identity.sql | 3 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 30 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/unnest_subqueries.sql | 312 |
3 files changed, 76 insertions, 269 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 6b742c3..13a6153 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -869,4 +869,5 @@ TRUNCATE(a, b) SELECT enum SELECT unlogged SELECT name -SELECT copy
\ No newline at end of file +SELECT copy +SELECT rollup
\ No newline at end of file diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql index a357b07..5b004fa 100644 --- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql +++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql @@ -1409,31 +1409,11 @@ WITH "_u_0" AS ( "store_sales"."ss_quantity" <= 80 AND "store_sales"."ss_quantity" >= 61 ) SELECT - CASE - WHEN MAX("_u_0"."_col_0") > 3672 - THEN MAX("_u_1"."_col_0") - ELSE MAX("_u_2"."_col_0") - END AS "bucket1", - CASE - WHEN MAX("_u_3"."_col_0") > 3392 - THEN MAX("_u_4"."_col_0") - ELSE MAX("_u_5"."_col_0") - END AS "bucket2", - CASE - WHEN MAX("_u_6"."_col_0") > 32784 - THEN MAX("_u_7"."_col_0") - ELSE MAX("_u_8"."_col_0") - END AS "bucket3", - CASE - WHEN MAX("_u_9"."_col_0") > 26032 - THEN MAX("_u_10"."_col_0") - ELSE MAX("_u_11"."_col_0") - END AS "bucket4", - CASE - WHEN MAX("_u_12"."_col_0") > 23982 - THEN MAX("_u_13"."_col_0") - ELSE MAX("_u_14"."_col_0") - END AS "bucket5" + CASE WHEN "_u_0"."_col_0" > 3672 THEN "_u_1"."_col_0" ELSE "_u_2"."_col_0" END AS "bucket1", + CASE WHEN "_u_3"."_col_0" > 3392 THEN "_u_4"."_col_0" ELSE "_u_5"."_col_0" END AS "bucket2", + CASE WHEN "_u_6"."_col_0" > 32784 THEN "_u_7"."_col_0" ELSE "_u_8"."_col_0" END AS "bucket3", + CASE WHEN "_u_9"."_col_0" > 26032 THEN "_u_10"."_col_0" ELSE "_u_11"."_col_0" END AS "bucket4", + CASE WHEN "_u_12"."_col_0" > 23982 THEN "_u_13"."_col_0" ELSE "_u_14"."_col_0" END AS "bucket5" FROM "reason" AS "reason" CROSS JOIN "_u_0" AS "_u_0" CROSS JOIN "_u_1" AS "_u_1" diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql index 45e462b..a5a35b1 100644 --- a/tests/fixtures/optimizer/unnest_subqueries.sql +++ b/tests/fixtures/optimizer/unnest_subqueries.sql @@ -1,243 +1,69 @@ ---SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x; --------------------------------------- --- Unnest Subqueries --------------------------------------- -SELECT * -FROM x AS x -WHERE - x.a = (SELECT SUM(y.a) AS a FROM y) - AND x.a IN (SELECT y.a AS a FROM y) - AND x.a IN (SELECT y.b AS b FROM y) - AND x.a = ANY (SELECT y.a AS a FROM y) - AND x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a) - AND x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a) - AND x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a) - AND x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a) - AND x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a) - AND x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d) - AND EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a) - AND x.a IN (SELECT y.a AS a FROM y LIMIT 10) - AND x.a IN (SELECT y.a AS a FROM y OFFSET 10) - AND x.a IN (SELECT y.a AS a, y.b AS b FROM y) - AND x.a > ANY (SELECT y.a FROM y) - AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10) - AND x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10) - AND x.a > ALL (SELECT y.c FROM y WHERE y.a = x.a) - AND x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a) - AND x.a = SUM(SELECT 1) -- invalid statement left alone - AND x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a) -; -SELECT - * -FROM x AS x -CROSS JOIN ( - SELECT - SUM(y.a) AS a - FROM y -) AS _u_0 -LEFT JOIN ( - SELECT - y.a AS a - FROM y - GROUP BY - y.a -) AS _u_1 - ON x.a = _u_1.a -LEFT JOIN ( - SELECT - y.b AS b - FROM y - GROUP BY - y.b -) AS _u_2 - ON x.a = _u_2.b -LEFT JOIN ( - SELECT - y.a AS a - FROM y - GROUP BY - y.a -) AS _u_3 - ON x.a = _u_3.a -LEFT JOIN ( - SELECT - SUM(y.b) AS b, - y.a AS _u_5 - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_4 - ON x.a = _u_4._u_5 -LEFT JOIN ( - SELECT - SUM(y.b) AS b, - y.a AS _u_7 - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_6 - ON x.a = _u_6._u_7 -LEFT JOIN ( - SELECT - y.a AS a - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_8 - ON _u_8.a = x.a -LEFT JOIN ( - SELECT - y.a AS a - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_9 - ON _u_9.a = x.a -LEFT JOIN ( - SELECT - ARRAY_AGG(y.a) AS a, - y.b AS _u_11 - FROM y - WHERE - TRUE - GROUP BY - y.b -) AS _u_10 - ON _u_10._u_11 = x.a -LEFT JOIN ( - SELECT - SUM(y.a) AS a, - y.a AS _u_13, - ARRAY_AGG(y.b) AS _u_14 - FROM y - WHERE - TRUE AND TRUE AND TRUE - GROUP BY - y.a -) AS _u_12 - ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b -LEFT JOIN ( - SELECT - y.a AS a - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_15 - ON x.a = _u_15.a -LEFT JOIN ( - SELECT - ARRAY_AGG(c), - y.a AS _u_20 - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_19 - ON _u_19._u_20 = x.a -LEFT JOIN ( - SELECT - COUNT(*) AS d, - y.a AS _u_22 - FROM y - WHERE - TRUE - GROUP BY - y.a -) AS _u_21 - ON _u_21._u_22 = x.a -LEFT JOIN ( - SELECT - _q.b - FROM ( - SELECT - MAX(y.b) AS b - FROM y - GROUP BY - y.a - ) AS _q - GROUP BY - _q.b -) AS _u_24 - ON x.a = _u_24.b -WHERE - x.a = _u_0.a - AND NOT _u_1.a IS NULL - AND NOT _u_2.b IS NULL - AND NOT _u_3.a IS NULL - AND x.a = _u_4.b - AND x.a > _u_6.b - AND x.a = _u_8.a - AND NOT x.a = _u_9.a - AND ARRAY_ANY(_u_10.a, _x -> _x = x.a) - AND ( - x.a < _u_12.a AND ARRAY_ANY(_u_12._u_14, _x -> _x <> x.d) - ) - AND NOT _u_15.a IS NULL - AND x.a IN ( - SELECT - y.a AS a - FROM y - LIMIT 10 - ) - AND x.a IN ( - SELECT - y.a AS a - FROM y - OFFSET 10 - ) - AND x.a IN ( - SELECT - y.a AS a, - y.b AS b - FROM y - ) - AND x.a > ANY ( - SELECT - y.a - FROM y - ) - AND x.a = ( - SELECT - SUM(y.c) AS c - FROM y - WHERE - y.a = x.a - LIMIT 10 - ) - AND x.a = ( - SELECT - SUM(y.c) AS c - FROM y - WHERE - y.a = x.a - OFFSET 10 - ) - AND ARRAY_ALL(_u_19."", _x -> _x = x.a) - AND x.a > COALESCE(_u_21.d, 0) - AND x.a = SUM(SELECT - 1) /* invalid statement left alone */ - AND NOT _u_24.b IS NULL -; -SELECT - CAST(( - SELECT - x.a AS a - FROM x - ) AS TEXT) AS a; -SELECT - CAST(( - SELECT - x.a AS a - FROM x - ) AS TEXT) AS a; +SELECT * FROM x WHERE x.a = (SELECT SUM(y.a) AS a FROM y); +SELECT * FROM x CROSS JOIN (SELECT SUM(y.a) AS a FROM y) AS _u_0 WHERE x.a = _u_0.a; + +SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y); +SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL; + +SELECT * FROM x WHERE x.a IN (SELECT y.b AS b FROM y); +SELECT * FROM x LEFT JOIN (SELECT y.b AS b FROM y GROUP BY y.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL; + +SELECT * FROM x WHERE x.a = ANY (SELECT y.a AS a FROM y); +SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL; + +SELECT * FROM x WHERE x.a = (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a); +SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a = _u_0.b; + +SELECT * FROM x WHERE x.a > (SELECT SUM(y.b) AS b FROM y WHERE x.a = y.a); +SELECT * FROM x LEFT JOIN (SELECT SUM(y.b) AS b, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0._u_1 WHERE x.a > _u_0.b; + +SELECT * FROM x WHERE x.a <> ANY (SELECT y.a AS a FROM y WHERE y.a = x.a); +SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE x.a <> _u_0.a; + +SELECT * FROM x WHERE x.a NOT IN (SELECT y.a AS a FROM y WHERE y.a = x.a); +SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0.a = x.a WHERE NOT x.a = _u_0.a; + +SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y WHERE y.b = x.a); +SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.a) AS a, y.b AS _u_1 FROM y WHERE TRUE GROUP BY y.b) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ANY(_u_0.a, _x -> _x = x.a); + +SELECT * FROM x WHERE x.a < (SELECT SUM(y.a) AS a FROM y WHERE y.a = x.a and y.a = x.b and y.b <> x.d); +SELECT * FROM x LEFT JOIN (SELECT SUM(y.a) AS a, y.a AS _u_1, ARRAY_AGG(y.b) AS _u_2 FROM y WHERE TRUE AND TRUE AND TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a AND _u_0._u_1 = x.b WHERE (x.a < _u_0.a AND ARRAY_ANY(_u_0._u_2, _x -> _x <> x.d)); + +SELECT * FROM x WHERE EXISTS (SELECT y.a AS a, y.b AS b FROM y WHERE x.a = y.a); +SELECT * FROM x LEFT JOIN (SELECT y.a AS a FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON x.a = _u_0.a WHERE NOT _u_0.a IS NULL; + +SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10); +SELECT * FROM x WHERE x.a IN (SELECT y.a AS a FROM y LIMIT 10); + +SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10); +SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a FROM y OFFSET 10); + +SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y); +SELECT * FROM x.a WHERE x.a IN (SELECT y.a AS a, y.b AS b FROM y); + +SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y); +SELECT * FROM x.a WHERE x.a > ANY (SELECT y.a FROM y); + +SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10); +SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a LIMIT 10); + +SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10); +SELECT * FROM x WHERE x.a = (SELECT SUM(y.c) AS c FROM y WHERE y.a = x.a OFFSET 10); + +SELECT * FROM x WHERE x.a > ALL (SELECT y.c AS c FROM y WHERE y.a = x.a); +SELECT * FROM x LEFT JOIN (SELECT ARRAY_AGG(y.c) AS c, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE ARRAY_ALL(_u_0.c, _x -> x.a > _x); + +SELECT * FROM x WHERE x.a > (SELECT COUNT(*) as d FROM y WHERE y.a = x.a); +SELECT * FROM x LEFT JOIN (SELECT COUNT(*) AS d, y.a AS _u_1 FROM y WHERE TRUE GROUP BY y.a) AS _u_0 ON _u_0._u_1 = x.a WHERE x.a > COALESCE(_u_0.d, 0); + +# title: invalid statement left alone +SELECT * FROM x WHERE x.a = SUM(SELECT 1); +SELECT * FROM x WHERE x.a = SUM(SELECT 1); + +SELECT * FROM x WHERE x.a IN (SELECT max(y.b) AS b FROM y GROUP BY y.a); +SELECT * FROM x LEFT JOIN (SELECT _q.b AS b FROM (SELECT MAX(y.b) AS b FROM y GROUP BY y.a) AS _q GROUP BY _q.b) AS _u_0 ON x.a = _u_0.b WHERE NOT _u_0.b IS NULL; + +SELECT x.a > (SELECT SUM(y.a) AS b FROM y) FROM x; +SELECT x.a > _u_0.b FROM x CROSS JOIN (SELECT SUM(y.a) AS b FROM y) AS _u_0; + +SELECT (SELECT MAX(t2.c1) AS c1 FROM t2 WHERE t2.c2 = t1.c2 AND t2.c3 <= TRUNC(t1.c3)) AS c FROM t1; +SELECT _u_0.c1 AS c FROM t1 LEFT JOIN (SELECT MAX(t2.c1) AS c1, t2.c2 AS _u_1, MAX(t2.c3) AS _u_2 FROM t2 WHERE TRUE AND TRUE GROUP BY t2.c2) AS _u_0 ON _u_0._u_1 = t1.c2 WHERE _u_0._u_2 <= TRUNC(t1.c3); |