summaryrefslogtreecommitdiffstats
path: root/tests/fixtures/optimizer
diff options
context:
space:
mode:
Diffstat (limited to 'tests/fixtures/optimizer')
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql33
-rw-r--r--tests/fixtures/optimizer/qualify_columns_ddl.sql14
-rw-r--r--tests/fixtures/optimizer/qualify_tables.sql4
-rw-r--r--tests/fixtures/optimizer/simplify.sql29
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql30
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql312
6 files changed, 147 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..87b42d1 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;
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);