diff options
Diffstat (limited to 'tests/fixtures/optimizer')
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns.sql | 33 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_columns_ddl.sql | 14 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/qualify_tables.sql | 4 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/simplify.sql | 32 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/tpc-ds/tpc-ds.sql | 30 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/unnest_subqueries.sql | 312 |
6 files changed, 150 insertions, 275 deletions
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql index b020a27..ea96fe5 100644 --- a/tests/fixtures/optimizer/qualify_columns.sql +++ b/tests/fixtures/optimizer/qualify_columns.sql @@ -131,6 +131,10 @@ SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE( SELECT (SELECT MIN(a) FROM UNNEST([1, 2])) AS f FROM x GROUP BY 1; SELECT (SELECT MIN(_q_0.a) AS _col_0 FROM UNNEST(ARRAY(1, 2)) AS _q_0) AS f FROM x AS x GROUP BY 1; +# dialect: bigquery +WITH x AS (select 'a' as a, 1 as b) SELECT x.a AS c, y.a as d, SUM(x.b) AS y, FROM x join x as y on x.a = y.a group by 1, 2; +WITH x AS (SELECT 'a' AS a, 1 AS b) SELECT x.a AS c, y.a AS d, SUM(x.b) AS y FROM x AS x JOIN x AS y ON x.a = y.a GROUP BY x.a, 2; + SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c; SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c; @@ -210,6 +214,9 @@ SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) A SELECT x.a FROM x AS x JOIN (SELECT * FROM x) AS y ON x.a = y.a; SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS y ON x.a = y.a; +SELECT a FROM x as t1 /* there is comment */; +SELECT t1.a AS a FROM x AS t1 /* there is comment */; + -------------------------------------- -- Joins -------------------------------------- @@ -314,6 +321,28 @@ SELECT s.a AS a, s.b AS b FROM (SELECT t.a AS a, t.b AS b FROM t AS t) AS s; SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) AS s(b); SELECT s.b AS b FROM (SELECT t1.b AS b FROM t1 AS t1 UNION ALL SELECT t2.b AS b FROM t2 AS t2) AS s; +# dialect: bigquery +# execute: false +WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct("test" AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col), tbl2 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct("test" AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col) SELECT tbl1.col.*, tbl2.col.* FROM tbl1, tbl2; +WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct('test' AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col), tbl2 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct('test' AS col1, Struct(3 AS col2) AS lvl2) AS lvl1) AS col) SELECT tbl1.col.col1 AS col1, tbl1.col.col2 AS col2, tbl1.col.lvl1 AS lvl1, tbl2.col.col1 AS col1, tbl2.col.col2 AS col2, tbl2.col.lvl1 AS lvl1 FROM tbl1 AS tbl1, tbl2 AS tbl2; + +# dialect: bigquery +# execute: false +WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct("test" AS col1, Struct(3 AS col2) AS lvl2) AS lvl1, 3 AS col3) AS col) SELECT tbl1.col.lvl1.* FROM tbl1; +WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col2, Struct('test' AS col1, Struct(3 AS col2) AS lvl2) AS lvl1, 3 AS col3) AS col) SELECT tbl1.col.lvl1.col1 AS col1, tbl1.col.lvl1.lvl2 AS lvl2 FROM tbl1 AS tbl1; + +# dialect: bigquery +# execute: false +# title: Cannot expand struct star with unnamed fields +WITH tbl1 AS (SELECT STRUCT(1 AS col1, Struct(5 AS col1)) AS col) SELECT tbl1.col.* FROM tbl1; +WITH tbl1 AS (SELECT STRUCT(1 AS col1, Struct(5 AS col1)) AS col) SELECT tbl1.col.* FROM tbl1 AS tbl1; + +# dialect: bigquery +# execute: false +# title: Cannot expand struct star with ambiguous fields +WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col1) AS col) SELECT tbl1.col.* FROM tbl1; +WITH tbl1 AS (SELECT STRUCT(1 AS col1, 2 AS col1) AS col) SELECT tbl1.col.* FROM tbl1 AS tbl1; + -------------------------------------- -- CTEs -------------------------------------- @@ -523,6 +552,10 @@ SELECT t.c1 AS c1, t.c3 AS c3 FROM FOO(bar) AS t(c1, c2, c3); SELECT c.f::VARCHAR(MAX) AS f, e AS e FROM a.b AS c, c.d AS e; SELECT CAST(c.f AS VARCHAR(MAX)) AS f, e AS e FROM a.b AS c, c.d AS e; +# dialect: bigquery +WITH cte AS (SELECT 1 AS col) SELECT * FROM cte LEFT JOIN UNNEST((SELECT ARRAY_AGG(DISTINCT x) AS agg FROM UNNEST([1]) AS x WHERE col = 1)); +WITH cte AS (SELECT 1 AS col) SELECT * FROM cte AS cte LEFT JOIN UNNEST((SELECT ARRAY_AGG(DISTINCT x) AS agg FROM UNNEST([1]) AS x WHERE cte.col = 1)); + -------------------------------------- -- Window functions -------------------------------------- diff --git a/tests/fixtures/optimizer/qualify_columns_ddl.sql b/tests/fixtures/optimizer/qualify_columns_ddl.sql index 9b4bb34..75d84ca 100644 --- a/tests/fixtures/optimizer/qualify_columns_ddl.sql +++ b/tests/fixtures/optimizer/qualify_columns_ddl.sql @@ -1,26 +1,26 @@ # title: Create with CTE WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM cte; -CREATE TABLE s AS WITH cte AS (SELECT y.b AS b FROM y AS y) SELECT cte.b AS b FROM cte AS cte; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT cte.b AS b FROM cte AS cte; # title: Create with CTE, query also has CTE WITH cte1 AS (SELECT b FROM y) CREATE TABLE s AS WITH cte2 AS (SELECT b FROM cte1) SELECT * FROM cte2; -CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2; +WITH cte1 AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS WITH cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2; # title: Create without CTE CREATE TABLE foo AS SELECT a FROM tbl; CREATE TABLE foo AS SELECT tbl.a AS a FROM tbl AS tbl; # title: Create with complex CTE with derived table -WITH cte AS (SELECT a FROM (SELECT a from x)) CREATE TABLE s AS SELECT * FROM cte; -CREATE TABLE s AS WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) SELECT cte.a AS a FROM cte AS cte; +WITH cte AS (SELECT a FROM (SELECT a FROM x)) CREATE TABLE s AS SELECT * FROM cte; +WITH cte AS (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) CREATE TABLE s AS SELECT cte.a AS a FROM cte AS cte; # title: Create wtih multiple CTEs WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte2; -CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte2.b AS b FROM cte2 AS cte2; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte2.b AS b FROM cte2 AS cte2; # title: Create with multiple CTEs, selecting only from the first CTE (unnecessary code) WITH cte1 AS (SELECT b FROM y), cte2 AS (SELECT b FROM cte1) CREATE TABLE s AS SELECT * FROM cte1; -CREATE TABLE s AS WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) SELECT cte1.b AS b FROM cte1 AS cte1; +WITH cte1 AS (SELECT y.b AS b FROM y AS y), cte2 AS (SELECT cte1.b AS b FROM cte1 AS cte1) CREATE TABLE s AS SELECT cte1.b AS b FROM cte1 AS cte1; # title: Create with multiple derived tables CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM y)); @@ -28,7 +28,7 @@ CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT y.b A # title: Create with a CTE and a derived table WITH cte AS (SELECT b FROM y) CREATE TABLE s AS SELECT * FROM (SELECT b FROM (SELECT b FROM cte)); -CREATE TABLE s AS WITH cte AS (SELECT y.b AS b FROM y AS y) SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1; +WITH cte AS (SELECT y.b AS b FROM y AS y) CREATE TABLE s AS SELECT _q_1.b AS b FROM (SELECT _q_0.b AS b FROM (SELECT cte.b AS b FROM cte AS cte) AS _q_0) AS _q_1; # title: Insert with CTE # dialect: spark diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql index 104400e..30bf834 100644 --- a/tests/fixtures/optimizer/qualify_tables.sql +++ b/tests/fixtures/optimizer/qualify_tables.sql @@ -158,6 +158,10 @@ ALTER TABLE c.db.t ADD PRIMARY KEY (id) NOT ENFORCED; CREATE TABLE t1 AS (WITH cte AS (SELECT x FROM t2) SELECT * FROM cte); CREATE TABLE c.db.t1 AS (WITH cte AS (SELECT x FROM c.db.t2 AS t2) SELECT * FROM cte AS cte); +# title: delete statement +DELETE FROM t1 WHERE NOT c IN (SELECT c FROM t2); +DELETE FROM c.db.t1 WHERE NOT c IN (SELECT c FROM c.db.t2 AS t2); + # title: insert statement with cte # dialect: spark WITH cte AS (SELECT b FROM y) INSERT INTO s SELECT * FROM cte; diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index 6af51bf..6035ee6 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -109,6 +109,13 @@ a AND b; (x is not null) != (y is null); (NOT x IS NULL) <> (y IS NULL); +# dialect: mysql +A XOR A; +FALSE; + +TRUE AND TRUE OR TRUE AND FALSE; +TRUE; + -------------------------------------- -- Absorption -------------------------------------- @@ -154,6 +161,12 @@ A OR C; A AND (B AND C) AND (D AND E); A AND B AND C AND D AND E; +A AND (A OR B) AND (A OR B OR C); +A; + +(A OR B) AND (A OR C) AND (A OR B OR C); +(A OR B) AND (A OR C); + -------------------------------------- -- Elimination -------------------------------------- @@ -190,6 +203,15 @@ NOT A; E OR (A AND B) OR C OR D OR (A AND NOT B); A OR C OR D OR E; +(A AND B) OR (A AND NOT B) OR (A AND NOT B); +A; + +(A AND B) OR (A AND B) OR (A AND NOT B); +A; + +(A AND B) OR (A AND NOT B) OR (A AND B) OR (A AND NOT B); +A; + -------------------------------------- -- Associativity -------------------------------------- @@ -232,6 +254,13 @@ x - 1; A AND D AND B AND E AND F AND G AND E AND A; A AND B AND D AND E AND F AND G; +A OR D OR B OR E OR F OR G OR E OR A; +A OR B OR D OR E OR F OR G; + +# dialect: mysql +A XOR D XOR B XOR E XOR F XOR G XOR C; +A XOR B XOR C XOR D XOR E XOR F XOR G; + A AND NOT B AND C AND B; FALSE; @@ -1018,6 +1047,9 @@ x < CAST('2021-01-02' AS DATE) AND x >= CAST('2021-01-01' AS DATE); TIMESTAMP_TRUNC(x, YEAR) = CAST(CAST('2021-01-01 01:02:03' AS DATE) AS DATETIME); x < CAST('2022-01-01 00:00:00' AS DATETIME) AND x >= CAST('2021-01-01 00:00:00' AS DATETIME); +DATE_TRUNC('day', CAST(x AS DATE)) <= CAST('2021-01-01 01:02:03' AS TIMESTAMP); +CAST(x AS DATE) < CAST('2021-01-02 01:02:03' AS TIMESTAMP); + -------------------------------------- -- EQUALITY -------------------------------------- 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); |