Adding upstream version 6.0.4.upstream/6.0.4

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

23 files changed, 2993 insertions, 0 deletions

diff --git a/tests/fixtures/optimizer/eliminate_subqueries.sql b/tests/fixtures/optimizer/eliminate_subqueries.sql
new file mode 100644
index 0000000..aae5f2a
--- /dev/null
+++ b/tests/fixtures/optimizer/eliminate_subqueries.sql
@@ -0,0 +1,42 @@
+SELECT 1 AS x, 2 AS y
+UNION ALL
+SELECT 1 AS x, 2 AS y;
+WITH _e_0 AS (
+  SELECT
+    1 AS x,
+    2 AS y
+)
+SELECT
+  *
+FROM _e_0
+UNION ALL
+SELECT
+  *
+FROM _e_0;
+
+SELECT x.id
+FROM (
+    SELECT *
+    FROM x AS x
+    JOIN y AS y
+      ON x.id = y.id
+) AS x
+JOIN (
+    SELECT *
+    FROM x AS x
+    JOIN y AS y
+      ON x.id = y.id
+) AS y
+ON x.id = y.id;
+WITH _e_0 AS (
+  SELECT
+    *
+  FROM x AS x
+  JOIN y AS y
+    ON x.id = y.id
+)
+SELECT
+  x.id
+FROM "_e_0" AS x
+JOIN "_e_0" AS y
+  ON x.id = y.id;
diff --git a/tests/fixtures/optimizer/expand_multi_table_selects.sql b/tests/fixtures/optimizer/expand_multi_table_selects.sql
new file mode 100644
index 0000000..a5a4664
--- /dev/null
+++ b/tests/fixtures/optimizer/expand_multi_table_selects.sql
@@ -0,0 +1,11 @@
+--------------------------------------
+-- Multi Table Selects
+--------------------------------------
+SELECT * FROM x AS x, y AS y WHERE x.a = y.a;
+SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a;
+
+SELECT * FROM x AS x, y AS y WHERE x.a = y.a AND x.a = 1 and y.b = 1;
+SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a = y.a AND x.a = 1 AND y.b = 1;
+
+SELECT * FROM x AS x, y AS y WHERE x.a > y.a;
+SELECT * FROM x AS x CROSS JOIN y AS y WHERE x.a > y.a;
diff --git a/tests/fixtures/optimizer/isolate_table_selects.sql b/tests/fixtures/optimizer/isolate_table_selects.sql
new file mode 100644
index 0000000..3b9a938
--- /dev/null
+++ b/tests/fixtures/optimizer/isolate_table_selects.sql
@@ -0,0 +1,20 @@
+SELECT * FROM x AS x, y AS y2;
+SELECT * FROM (SELECT * FROM x AS x) AS x, (SELECT * FROM y AS y) AS y2;
+
+SELECT * FROM x AS x WHERE x = 1;
+SELECT * FROM x AS x WHERE x = 1;
+
+SELECT * FROM x AS x JOIN y AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y AS y) AS y;
+
+SELECT * FROM (SELECT 1) AS x JOIN y AS y;
+SELECT * FROM (SELECT 1) AS x JOIN (SELECT * FROM y AS y) AS y;
+
+SELECT * FROM x AS x JOIN (SELECT * FROM y) AS y;
+SELECT * FROM (SELECT * FROM x AS x) AS x JOIN (SELECT * FROM y) AS y;
+
+WITH y AS (SELECT *) SELECT * FROM x AS x;
+WITH y AS (SELECT *) SELECT * FROM x AS x;
+
+WITH y AS (SELECT * FROM y AS y2 JOIN x AS z2) SELECT * FROM x AS x JOIN y as y;
+WITH y AS (SELECT * FROM (SELECT * FROM y AS y) AS y2 JOIN (SELECT * FROM x AS x) AS z2) SELECT * FROM (SELECT * FROM x AS x) AS x JOIN y AS y;
diff --git a/tests/fixtures/optimizer/normalize.sql b/tests/fixtures/optimizer/normalize.sql
new file mode 100644
index 0000000..a84fadf
--- /dev/null
+++ b/tests/fixtures/optimizer/normalize.sql
@@ -0,0 +1,41 @@
+(A OR B) AND (B OR C) AND (E OR F);
+(A OR B) AND (B OR C) AND (E OR F);
+
+(A AND B) OR (B AND C AND D);
+(A OR C) AND (A OR D) AND B;
+
+(A OR B) AND (A OR C) AND (A OR D) AND (B OR C) AND (B OR D) AND B;
+(A OR C) AND (A OR D) AND B;
+
+(A AND E) OR (B AND C) OR (D AND (E OR F));
+(A OR B OR D) AND (A OR C OR D) AND (B OR D OR E) AND (B OR E OR F) AND (C OR D OR E) AND (C OR E OR F);
+
+(A AND B AND C AND D AND E AND F AND G) OR (H AND I AND J AND K AND L AND M AND N) OR (O AND P AND Q);
+(A AND B AND C AND D AND E AND F AND G) OR (H AND I AND J AND K AND L AND M AND N) OR (O AND P AND Q);
+
+NOT NOT NOT (A OR B);
+NOT A AND NOT B;
+
+A OR B;
+A OR B;
+
+A AND (B AND C);
+A AND B AND C;
+
+A OR (B AND C);
+(A OR B) AND (A OR C);
+
+(A AND B) OR C;
+(A OR C) AND (B OR C);
+
+A OR (B OR (C AND D));
+(A OR B OR C) AND (A OR B OR D);
+
+A OR ((((B OR C) AND (B OR D)) OR C) AND (((B OR C) AND (B OR D)) OR D));
+(A OR B OR C) AND (A OR B OR D);
+
+(A AND B) OR (C AND D);
+(A OR C) AND (A OR D) AND (B OR C) AND (B OR D);
+
+(A AND B) OR (C OR (D AND E));
+(A OR C OR D) AND (A OR C OR E) AND (B OR C OR D) AND (B OR C OR E);
diff --git a/tests/fixtures/optimizer/optimize_joins.sql b/tests/fixtures/optimizer/optimize_joins.sql
new file mode 100644
index 0000000..b64544e
--- /dev/null
+++ b/tests/fixtures/optimizer/optimize_joins.sql
@@ -0,0 +1,20 @@
+SELECT * FROM x JOIN y ON y.a = 1 JOIN z ON x.a = z.a AND y.a = z.a;
+SELECT * FROM x JOIN z ON x.a = z.a AND TRUE JOIN y ON y.a = 1 AND y.a = z.a;
+
+SELECT * FROM x JOIN y ON y.a = 1 JOIN z ON x.a = z.a;
+SELECT * FROM x JOIN y ON y.a = 1 JOIN z ON x.a = z.a;
+
+SELECT * FROM x CROSS JOIN y JOIN z ON x.a = z.a AND y.a = z.a;
+SELECT * FROM x JOIN z ON x.a = z.a AND TRUE JOIN y ON y.a = z.a;
+
+SELECT * FROM x LEFT JOIN y ON y.a = 1 JOIN z ON x.a = z.a AND y.a = z.a;
+SELECT * FROM x JOIN z ON x.a = z.a AND TRUE LEFT JOIN y ON y.a = 1 AND y.a = z.a;
+
+SELECT * FROM x INNER JOIN z;
+SELECT * FROM x JOIN z;
+
+SELECT * FROM x LEFT OUTER JOIN z;
+SELECT * FROM x LEFT JOIN z;
+
+SELECT * FROM x CROSS JOIN z;
+SELECT * FROM x CROSS JOIN z;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
new file mode 100644
index 0000000..f7bbdda
--- /dev/null
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -0,0 +1,148 @@
+SELECT a, m FROM z LATERAL VIEW EXPLODE([1, 2]) q AS m;
+SELECT
+  "z"."a" AS "a",
+  "q"."m" AS "m"
+FROM (
+  SELECT
+    "z"."a" AS "a"
+  FROM "z" AS "z"
+) AS "z"
+LATERAL VIEW
+EXPLODE(ARRAY(1, 2)) q AS "m";
+
+SELECT x FROM UNNEST([1, 2]) AS q(x, y);
+SELECT
+  "q"."x" AS "x"
+FROM UNNEST(ARRAY(1, 2)) AS "q"("x", "y");
+
+WITH cte AS (
+    (
+        SELECT
+            a
+            FROM
+            x
+    )
+    UNION ALL
+    (
+        SELECT
+            a
+        FROM
+            y
+    )
+)
+SELECT
+    *
+FROM
+    cte;
+WITH "cte" AS (
+  (
+    SELECT
+      "x"."a" AS "a"
+    FROM "x" AS "x"
+  )
+  UNION ALL
+  (
+    SELECT
+      "y"."a" AS "a"
+    FROM "y" AS "y"
+  )
+)
+SELECT
+  "cte"."a" AS "a"
+FROM "cte";
+
+WITH cte1 AS (
+    SELECT a
+    FROM x
+), cte2 AS (
+    SELECT a + 1 AS a
+    FROM cte1
+)
+SELECT
+    a
+FROM cte1
+UNION ALL
+SELECT
+    a
+FROM cte2;
+WITH "cte1" AS (
+  SELECT
+    "x"."a" AS "a"
+  FROM "x" AS "x"
+), "cte2" AS (
+  SELECT
+    "cte1"."a" + 1 AS "a"
+  FROM "cte1"
+)
+SELECT
+  "cte1"."a" AS "a"
+FROM "cte1"
+UNION ALL
+SELECT
+  "cte2"."a" AS "a"
+FROM "cte2";
+
+SELECT a, SUM(b)
+FROM (
+    SELECT x.a, y.b
+    FROM x, y
+    WHERE (SELECT max(b) FROM y WHERE x.a = y.a) >= 0 AND x.a = y.a
+) d
+WHERE (TRUE AND TRUE OR 'a' = 'b') AND a > 1
+GROUP BY a;
+SELECT
+  "d"."a" AS "a",
+  SUM("d"."b") AS "_col_1"
+FROM (
+  SELECT
+    "x"."a" AS "a",
+    "y"."b" AS "b"
+  FROM (
+    SELECT
+      "x"."a" AS "a"
+    FROM "x" AS "x"
+    WHERE
+      "x"."a" > 1
+  ) AS "x"
+  LEFT JOIN (
+    SELECT
+      MAX("y"."b") AS "_col_0",
+      "y"."a" AS "_u_1"
+    FROM "y" AS "y"
+    GROUP BY
+      "y"."a"
+  ) AS "_u_0"
+    ON "x"."a" = "_u_0"."_u_1"
+  JOIN (
+    SELECT
+      "y"."a" AS "a",
+      "y"."b" AS "b"
+    FROM "y" AS "y"
+  ) AS "y"
+    ON "x"."a" = "y"."a"
+  WHERE
+    "_u_0"."_col_0" >= 0
+    AND NOT "_u_0"."_u_1" IS NULL
+) AS "d"
+GROUP BY
+  "d"."a";
+
+(SELECT a FROM x) LIMIT 1;
+(
+  SELECT
+    "x"."a" AS "a"
+  FROM "x" AS "x"
+)
+LIMIT 1;
+
+(SELECT b FROM x UNION SELECT b FROM y) LIMIT 1;
+(
+  SELECT
+    "x"."b" AS "b"
+  FROM "x" AS "x"
+  UNION
+  SELECT
+    "y"."b" AS "b"
+  FROM "y" AS "y"
+)
+LIMIT 1;
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
new file mode 100644
index 0000000..676cb96
--- /dev/null
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -0,0 +1,32 @@
+SELECT x.a AS a FROM (SELECT x.a FROM x AS x) AS x JOIN y WHERE x.a = 1 AND x.b = 1 AND y.a = 1;
+SELECT x.a AS a FROM (SELECT x.a FROM x AS x WHERE x.a = 1 AND x.b = 1) AS x JOIN y ON y.a = 1 WHERE TRUE AND TRUE AND TRUE;
+
+WITH x AS (SELECT y.a FROM y) SELECT * FROM x WHERE x.a = 1;
+WITH x AS (SELECT y.a FROM y WHERE y.a = 1) SELECT * FROM x WHERE TRUE;
+
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE y.a = 1 OR (x.a = 1 AND x.b = 1);
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = 1 AND x.b = 1) OR y.a = 1;
+
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.a;
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a WHERE TRUE;
+
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.b;
+SELECT x.a FROM (SELECT * FROM x) AS x JOIN y ON x.a = y.a OR x.a = y.b WHERE (x.a = y.a AND x.a = 1 AND x.b = 1) OR x.a = y.b;
+
+SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x) AS x WHERE x.c = 1;
+SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x WHERE x.b * 1 = 1) AS x WHERE TRUE;
+
+SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x) AS x WHERE x.c = 1 or x.c = 2;
+SELECT x.a FROM (SELECT x.a AS a, x.b * 1 AS c FROM x WHERE x.b * 1 = 1 OR x.b * 1 = 2) AS x WHERE TRUE;
+
+SELECT x.a AS a FROM (SELECT x.a FROM x AS x) AS x JOIN y WHERE x.a = 1 AND x.b = 1 AND (x.c = 1 OR y.c = 1);
+SELECT x.a AS a FROM (SELECT x.a FROM x AS x WHERE x.a = 1 AND x.b = 1) AS x JOIN y ON x.c = 1 OR y.c = 1 WHERE TRUE AND TRUE AND (TRUE);
+
+SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y) AS y ON y.a = 1 AND x.a = y.a;
+SELECT x.a FROM x AS x JOIN (SELECT y.a FROM y AS y WHERE y.a = 1) AS y ON x.a = y.a AND TRUE;
+
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y) AS y ON y.a = 1 WHERE x.a = 1 AND x.b = 1 AND y.a = x;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE;
+
+SELECT x.a AS a FROM x AS x CROSS JOIN (SELECT * FROM y AS y) AS y WHERE x.a = 1 AND x.b = 1 AND y.a = x.a AND y.a = 1;
+SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.a = x.a AND TRUE WHERE x.a = 1 AND x.b = 1 AND TRUE AND TRUE;
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
new file mode 100644
index 0000000..9deceb6
--- /dev/null
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -0,0 +1,41 @@
+SELECT a FROM (SELECT * FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+SELECT 1 FROM (SELECT * FROM x) WHERE b = 2;
+SELECT 1 AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS "_q_0" WHERE "_q_0".b = 2;
+
+SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q;
+SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS q;
+
+SELECT a FROM x JOIN (SELECT b, c FROM y) AS z ON x.b = z.b;
+SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b;
+
+SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
+SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2;
+
+SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
+SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2;
+
+SELECT a FROM (SELECT DISTINCT a, b FROM x);
+SELECT "_q_0".a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+
+SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+
+WITH y AS (SELECT * FROM x) SELECT a FROM y;
+WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y;
+
+WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q;
+WITH z AS (SELECT x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q;
+
+WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z;
+
+SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a);
+SELECT "_q_0".b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS "_q_0";
+
+SELECT b FROM (SELECT a, SUM(b) AS b FROM x ORDER BY a);
+SELECT "_q_0".b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS "_q_0";
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
new file mode 100644
index 0000000..004c57c
--- /dev/null
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -0,0 +1,233 @@
+--------------------------------------
+-- Qualify columns
+--------------------------------------
+SELECT a FROM x;
+SELECT x.a AS a FROM x AS x;
+
+SELECT a FROM x AS z;
+SELECT z.a AS a FROM x AS z;
+
+SELECT a AS a FROM x;
+SELECT x.a AS a FROM x AS x;
+
+SELECT x.a FROM x;
+SELECT x.a AS a FROM x AS x;
+
+SELECT x.a AS a FROM x;
+SELECT x.a AS a FROM x AS x;
+
+SELECT a AS b FROM x;
+SELECT x.a AS b FROM x AS x;
+
+SELECT 1, 2 FROM x;
+SELECT 1 AS "_col_0", 2 AS "_col_1" FROM x AS x;
+
+SELECT a + b FROM x;
+SELECT x.a + x.b AS "_col_0" FROM x AS x;
+
+SELECT a + b FROM x;
+SELECT x.a + x.b AS "_col_0" FROM x AS x;
+
+SELECT a, SUM(b) FROM x WHERE a > 1 AND b > 1 GROUP BY a;
+SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a;
+
+SELECT a AS j, b FROM x ORDER BY j;
+SELECT x.a AS j, x.b AS b FROM x AS x ORDER BY j;
+
+SELECT a AS j, b FROM x GROUP BY j;
+SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a;
+
+SELECT a, b FROM x GROUP BY 1, 2;
+SELECT x.a AS a, x.b AS b FROM x AS x GROUP BY x.a, x.b;
+
+SELECT a, b FROM x ORDER BY 1, 2;
+SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a, b;
+
+SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2;
+SELECT DATE(x.a) AS "_col_0", DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b);
+
+SELECT x.a AS c FROM x JOIN y ON x.b = y.b GROUP BY c;
+SELECT x.a AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c;
+
+SELECT DATE(x.a) AS d FROM x JOIN y ON x.b = y.b GROUP BY d;
+SELECT DATE(x.a) AS d FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY DATE(x.a);
+
+SELECT a AS a, b FROM x ORDER BY a;
+SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a;
+
+SELECT a, b FROM x ORDER BY a;
+SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY a;
+
+SELECT a FROM x ORDER BY b;
+SELECT x.a AS a FROM x AS x ORDER BY x.b;
+
+# dialect: bigquery
+SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) AS row_num FROM x QUALIFY row_num = 1;
+SELECT ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.b) AS row_num FROM x AS x QUALIFY row_num = 1;
+
+# dialect: bigquery
+SELECT x.b, x.a FROM x LEFT JOIN y ON x.b = y.b QUALIFY ROW_NUMBER() OVER(PARTITION BY x.b ORDER BY x.a DESC) = 1;
+SELECT x.b AS b, x.a AS a FROM x AS x LEFT JOIN y AS y ON x.b = y.b QUALIFY ROW_NUMBER() OVER (PARTITION BY x.b ORDER BY x.a DESC) = 1;
+
+--------------------------------------
+-- Derived tables
+--------------------------------------
+SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y;
+SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y;
+
+SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y(a);
+SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y;
+
+SELECT y.c AS c FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS y(c);
+SELECT y.c AS c FROM (SELECT x.a AS c, x.b AS b FROM x AS x) AS y;
+
+SELECT a FROM (SELECT a FROM x AS x) y;
+SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y;
+
+SELECT a FROM (SELECT a AS a FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+SELECT a FROM (SELECT a FROM (SELECT a FROM x));
+SELECT "_q_1".a AS a FROM (SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0") AS "_q_1";
+
+SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
+SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+
+--------------------------------------
+-- Joins
+--------------------------------------
+SELECT a, c FROM x JOIN y ON x.b = y.b;
+SELECT x.a AS a, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+SELECT a, c FROM x, y;
+SELECT x.a AS a, y.c AS c FROM x AS x, y AS y;
+
+--------------------------------------
+-- Unions
+--------------------------------------
+SELECT a FROM x UNION SELECT a FROM x;
+SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x;
+
+SELECT a FROM x UNION SELECT a FROM x UNION SELECT a FROM x;
+SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x;
+
+SELECT a FROM (SELECT a FROM x UNION SELECT a FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+--------------------------------------
+-- Subqueries
+--------------------------------------
+SELECT a FROM x WHERE b IN (SELECT c FROM y);
+SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y);
+
+SELECT (SELECT c FROM y) FROM x;
+SELECT (SELECT y.c AS c FROM y AS y) AS "_col_0" FROM x AS x;
+
+SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y));
+SELECT "_q_1".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_1" WHERE "_q_1".a IN (SELECT "_q_0".b AS b FROM (SELECT y.b AS b FROM y AS y) AS "_q_0");
+
+--------------------------------------
+-- Correlated subqueries
+--------------------------------------
+SELECT a FROM x WHERE b IN (SELECT c FROM y WHERE y.b = x.a);
+SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y WHERE y.b = x.a);
+
+SELECT a FROM x WHERE b IN (SELECT c FROM y WHERE y.b = a);
+SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y WHERE y.b = x.a);
+
+SELECT a FROM x WHERE b IN (SELECT b FROM y AS x);
+SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT x.b AS b FROM y AS x);
+
+SELECT a FROM x AS i WHERE b IN (SELECT b FROM y AS j WHERE j.b IN (SELECT c FROM y AS k WHERE k.b = j.b));
+SELECT i.a AS a FROM x AS i WHERE i.b IN (SELECT j.b AS b FROM y AS j WHERE j.b IN (SELECT k.c AS c FROM y AS k WHERE k.b = j.b));
+
+# dialect: bigquery
+SELECT aa FROM x, UNNEST(a) AS aa;
+SELECT aa AS aa FROM x AS x, UNNEST(x.a) AS aa;
+
+SELECT aa FROM x, UNNEST(a) AS t(aa);
+SELECT t.aa AS aa FROM x AS x, UNNEST(x.a) AS t(aa);
+
+--------------------------------------
+-- Expand *
+--------------------------------------
+SELECT * FROM x;
+SELECT x.a AS a, x.b AS b FROM x AS x;
+
+SELECT x.* FROM x;
+SELECT x.a AS a, x.b AS b FROM x AS x;
+
+SELECT * FROM x JOIN y ON x.b = y.b;
+SELECT x.a AS a, x.b AS b, y.b AS b, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+SELECT x.* FROM x JOIN y ON x.b = y.b;
+SELECT x.a AS a, x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b;
+
+SELECT x.*, y.* FROM x JOIN y ON x.b = y.b;
+SELECT x.a AS a, x.b AS b, y.b AS b, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
+
+SELECT a FROM (SELECT * FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+
+SELECT * FROM (SELECT a FROM x);
+SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+
+--------------------------------------
+-- CTEs
+--------------------------------------
+WITH z AS (SELECT x.a AS a FROM x) SELECT z.a AS a FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z;
+
+WITH z(a) AS (SELECT a FROM x) SELECT * FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z;
+
+WITH z AS (SELECT a FROM x) SELECT * FROM z as q;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT q.a AS a FROM z AS q;
+
+WITH z AS (SELECT a FROM x) SELECT * FROM z;
+WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z;
+
+WITH z AS (SELECT a FROM x), q AS (SELECT * FROM z) SELECT * FROM q;
+WITH z AS (SELECT x.a AS a FROM x AS x), q AS (SELECT z.a AS a FROM z) SELECT q.a AS a FROM q;
+
+WITH z AS (SELECT * FROM x) SELECT * FROM z UNION SELECT * FROM z;
+WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x) SELECT z.a AS a, z.b AS b FROM z UNION SELECT z.a AS a, z.b AS b FROM z;
+
+WITH z AS (SELECT * FROM x), q AS (SELECT b FROM z) SELECT b FROM q;
+WITH z AS (SELECT x.a AS a, x.b AS b FROM x AS x), q AS (SELECT z.b AS b FROM z) SELECT q.b AS b FROM q;
+
+WITH z AS ((SELECT b FROM x UNION ALL SELECT b FROM y) ORDER BY b) SELECT * FROM z;
+WITH z AS ((SELECT x.b AS b FROM x AS x UNION ALL SELECT y.b AS b FROM y AS y) ORDER BY b) SELECT z.b AS b FROM z;
+
+--------------------------------------
+-- Except and Replace
+--------------------------------------
+SELECT * REPLACE(a AS d) FROM x;
+SELECT x.a AS d, x.b AS b FROM x AS x;
+
+SELECT * EXCEPT(b) REPLACE(a AS d) FROM x;
+SELECT x.a AS d FROM x AS x;
+
+SELECT x.* EXCEPT(a), y.* FROM x, y;
+SELECT x.b AS b, y.b AS b, y.c AS c FROM x AS x, y AS y;
+
+SELECT * EXCEPT(a) FROM x;
+SELECT x.b AS b FROM x AS x;
+
+--------------------------------------
+-- Using
+--------------------------------------
+SELECT x.b FROM x JOIN y USING (b);
+SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b;
+
+SELECT x.b FROM x JOIN y USING (b) JOIN z USING (b);
+SELECT x.b AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b;
+
+SELECT b FROM x AS x2 JOIN y AS y2 USING (b);
+SELECT COALESCE(x2.b, y2.b) AS b FROM x AS x2 JOIN y AS y2 ON x2.b = y2.b;
+
+SELECT b FROM x JOIN y USING (b) WHERE b = 1 and y.b = 2;
+SELECT COALESCE(x.b, y.b) AS b FROM x AS x JOIN y AS y ON x.b = y.b WHERE COALESCE(x.b, y.b) = 1 AND y.b = 2;
+
+SELECT b FROM x JOIN y USING (b) JOIN z USING (b);
+SELECT COALESCE(x.b, y.b, z.b) AS b FROM x AS x JOIN y AS y ON x.b = y.b JOIN z AS z ON x.b = z.b;
diff --git a/tests/fixtures/optimizer/qualify_columns__invalid.sql b/tests/fixtures/optimizer/qualify_columns__invalid.sql
new file mode 100644
index 0000000..056b0e9
--- /dev/null
+++ b/tests/fixtures/optimizer/qualify_columns__invalid.sql
@@ -0,0 +1,14 @@
+SELECT a FROM zz;
+SELECT * FROM zz;
+SELECT z.a FROM x;
+SELECT z.* FROM x;
+SELECT x FROM x;
+INSERT INTO x VALUES (1, 2);
+SELECT a FROM x AS z JOIN y AS z;
+WITH z AS (SELECT * FROM x) SELECT * FROM x AS z;
+SELECT a FROM x JOIN (SELECT b FROM y WHERE y.b = x.c);
+SELECT a FROM x AS y JOIN (SELECT a FROM y) AS q ON y.a = q.a;
+SELECT q.a FROM (SELECT x.b FROM x) AS z JOIN (SELECT a FROM z) AS q ON z.b = q.a;
+SELECT b FROM x AS a CROSS JOIN y AS b CROSS JOIN y AS c;
+SELECT x.a FROM x JOIN y USING (a);
+SELECT a, SUM(b) FROM x GROUP BY 3;
diff --git a/tests/fixtures/optimizer/qualify_tables.sql b/tests/fixtures/optimizer/qualify_tables.sql
new file mode 100644
index 0000000..2cea85d
--- /dev/null
+++ b/tests/fixtures/optimizer/qualify_tables.sql
@@ -0,0 +1,17 @@
+SELECT 1 FROM z;
+SELECT 1 FROM c.db.z AS z;
+
+SELECT 1 FROM y.z;
+SELECT 1 FROM c.y.z AS z;
+
+SELECT 1 FROM x.y.z;
+SELECT 1 FROM x.y.z AS z;
+
+SELECT 1 FROM x.y.z AS z;
+SELECT 1 FROM x.y.z AS z;
+
+WITH a AS (SELECT 1 FROM z) SELECT 1 FROM a;
+WITH a AS (SELECT 1 FROM c.db.z AS z) SELECT 1 FROM a;
+
+SELECT (SELECT y.c FROM y AS y) FROM x;
+SELECT (SELECT y.c FROM c.db.y AS y) FROM c.db.x AS x;
diff --git a/tests/fixtures/optimizer/quote_identities.sql b/tests/fixtures/optimizer/quote_identities.sql
new file mode 100644
index 0000000..407b7f6
--- /dev/null
+++ b/tests/fixtures/optimizer/quote_identities.sql
@@ -0,0 +1,8 @@
+SELECT a FROM x;
+SELECT "a" FROM "x";
+
+SELECT "a" FROM "x";
+SELECT "a" FROM "x";
+
+SELECT x.a AS a FROM db.x;
+SELECT "x"."a" AS "a" FROM "db"."x";
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
new file mode 100644
index 0000000..d7217cf
--- /dev/null
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -0,0 +1,350 @@
+--------------------------------------
+-- Conditions
+--------------------------------------
+x AND x;
+x;
+
+y OR y;
+y;
+
+x AND NOT x;
+FALSE;
+
+x OR NOT x;
+TRUE;
+
+1 AND TRUE;
+TRUE;
+
+TRUE AND TRUE;
+TRUE;
+
+1 AND TRUE AND 1 AND 1;
+TRUE;
+
+TRUE AND FALSE;
+FALSE;
+
+FALSE AND FALSE;
+FALSE;
+
+FALSE AND TRUE AND TRUE;
+FALSE;
+
+x > y OR FALSE;
+x > y;
+
+FALSE OR x = y;
+x = y;
+
+1 = 1;
+TRUE;
+
+1.0 = 1;
+TRUE;
+
+'x' = 'y';
+FALSE;
+
+'x' = 'x';
+TRUE;
+
+NULL AND TRUE;
+NULL;
+
+NULL AND NULL;
+NULL;
+
+NULL OR TRUE;
+TRUE;
+
+NULL OR NULL;
+NULL;
+
+FALSE OR NULL;
+NULL;
+
+NOT TRUE;
+FALSE;
+
+NOT FALSE;
+TRUE;
+
+NULL = NULL;
+NULL;
+
+NOT (NOT TRUE);
+TRUE;
+
+a AND (b OR b);
+a AND b;
+
+a AND (b AND b);
+a AND b;
+
+--------------------------------------
+-- Absorption
+--------------------------------------
+(A OR B) AND (C OR NOT A);
+(A OR B) AND (C OR NOT A);
+
+A AND (A OR B);
+A;
+
+A AND D AND E AND (B OR A);
+A AND D AND E;
+
+D AND A AND E AND (B OR A);
+A AND D AND E;
+
+(A OR B) AND A;
+A;
+
+C AND D AND (A OR B) AND E AND F AND A;
+A AND C AND D AND E AND F;
+
+A OR (A AND B);
+A;
+
+(A AND B) OR A;
+A;
+
+A AND (NOT A OR B);
+A AND B;
+
+(NOT A OR B) AND A;
+A AND B;
+
+A OR (NOT A AND B);
+A OR B;
+
+(A OR C) AND ((A OR C) OR B);
+A OR C;
+
+(A OR C) AND (A OR B OR C);
+A OR C;
+
+--------------------------------------
+-- Elimination
+--------------------------------------
+(A AND B) OR (A AND NOT B);
+A;
+
+(A AND B) OR (NOT A AND B);
+B;
+
+(A AND NOT B) OR (A AND B);
+A;
+
+(NOT A AND B) OR (A AND B);
+B;
+
+(A OR B) AND (A OR NOT B);
+A;
+
+(A OR B) AND (NOT A OR B);
+B;
+
+(A OR NOT B) AND (A OR B);
+A;
+
+(NOT A OR B) AND (A OR B);
+B;
+
+(NOT A OR NOT B) AND (NOT A OR B);
+NOT A;
+
+(NOT A OR NOT B) AND (NOT A OR NOT NOT B);
+NOT A;
+
+E OR (A AND B) OR C OR D OR (A AND NOT B);
+A OR C OR D OR E;
+
+--------------------------------------
+-- Associativity
+--------------------------------------
+(A AND B) AND C;
+A AND B AND C;
+
+A AND (B AND C);
+A AND B AND C;
+
+(A OR B) OR C;
+A OR B OR C;
+
+A OR (B OR C);
+A OR B OR C;
+
+((A AND B) AND C) AND D;
+A AND B AND C AND D;
+
+(((((A) AND B)) AND C)) AND D;
+A AND B AND C AND D;
+
+--------------------------------------
+-- Comparison and Pruning
+--------------------------------------
+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 AND NOT B AND C AND B;
+FALSE;
+
+(a AND b AND c AND d) AND (d AND c AND b AND a);
+a AND b AND c AND d;
+
+(c AND (a AND b)) AND ((b AND a) AND c);
+a AND b AND c;
+
+(A AND B AND C) OR (C AND B AND A);
+A AND B AND C;
+
+--------------------------------------
+-- Where removal
+--------------------------------------
+SELECT x WHERE TRUE;
+SELECT x;
+
+--------------------------------------
+-- Parenthesis removal
+--------------------------------------
+(TRUE);
+TRUE;
+
+(FALSE);
+FALSE;
+
+(FALSE OR TRUE);
+TRUE;
+
+TRUE OR (((FALSE) OR (TRUE)) OR FALSE);
+TRUE;
+
+(NOT FALSE) AND (NOT TRUE);
+FALSE;
+
+((NOT FALSE) AND (x = x)) AND (TRUE OR 1 <> 3);
+TRUE;
+
+((NOT FALSE) AND (x = x)) AND (FALSE OR 1 <> 2);
+TRUE;
+
+(('a' = 'a') AND TRUE and NOT FALSE);
+TRUE;
+
+--------------------------------------
+-- Literals
+--------------------------------------
+1 + 1;
+2;
+
+0.06 + 0.01;
+0.07;
+
+0.06 + 1;
+1.06;
+
+1.2E+1 + 15E-3;
+12.015;
+
+1.2E1 + 15E-3;
+12.015;
+
+1 - 2;
+-1;
+
+-1 + 3;
+2;
+
+-(-1);
+1;
+
+0.06 - 0.01;
+0.05;
+
+3 * 4;
+12;
+
+3.0 * 9;
+27.0;
+
+0.03 * 0.73;
+0.0219;
+
+1 / 3;
+0;
+
+20.0 / 6;
+3.333333333333333333333333333;
+
+10 / 5;
+2;
+
+(1.0 * 3) * 4 - 2 * (5 / 2);
+8.0;
+
+6 - 2 + 4 * 2 + a;
+12 + a;
+
+a + 1 + 1 + 2;
+a + 4;
+
+a + (1 + 1) + (10);
+a + 12;
+
+5 + 4 * 3;
+17;
+
+1 < 2;
+TRUE;
+
+2 <= 2;
+TRUE;
+
+2 >= 2;
+TRUE;
+
+2 > 1;
+TRUE;
+
+2 > 2.5;
+FALSE;
+
+3 > 2.5;
+TRUE;
+
+1 > NULL;
+NULL;
+
+1 <= NULL;
+NULL;
+
+1 IS NULL;
+FALSE;
+
+NULL IS NULL;
+TRUE;
+
+NULL IS NOT NULL;
+FALSE;
+
+1 IS NOT NULL;
+TRUE;
+
+date '1998-12-01' - interval '90' day;
+CAST('1998-09-02' AS DATE);
+
+date '1998-12-01' + interval '1' week;
+CAST('1998-12-08' AS DATE);
+
+interval '1' year + date '1998-01-01';
+CAST('1999-01-01' AS DATE);
+
+interval '1' year + date '1998-01-01' + 3 * 7 * 4;
+CAST('1999-01-01' AS DATE) + 84;
+
+date '1998-12-01' - interval '90' foo;
+CAST('1998-12-01' AS DATE) - INTERVAL '90' foo;
+
+date '1998-12-01' + interval '90' foo;
+CAST('1998-12-01' AS DATE) + INTERVAL '90' foo;
diff --git a/tests/fixtures/optimizer/tpc-h/customer.csv.gz b/tests/fixtures/optimizer/tpc-h/customer.csv.gz
new file mode 100644
index 0000000..e0d149c
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/customer.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/lineitem.csv.gz b/tests/fixtures/optimizer/tpc-h/lineitem.csv.gz
new file mode 100644
index 0000000..08e40d8
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/lineitem.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/nation.csv.gz b/tests/fixtures/optimizer/tpc-h/nation.csv.gz
new file mode 100644
index 0000000..d5bf6e3
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/nation.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/orders.csv.gz b/tests/fixtures/optimizer/tpc-h/orders.csv.gz
new file mode 100644
index 0000000..9b572bc
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/orders.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/part.csv.gz b/tests/fixtures/optimizer/tpc-h/part.csv.gz
new file mode 100644
index 0000000..2dfdaa5
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/part.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/partsupp.csv.gz b/tests/fixtures/optimizer/tpc-h/partsupp.csv.gz
new file mode 100644
index 0000000..de9a2ce
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/partsupp.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/region.csv.gz b/tests/fixtures/optimizer/tpc-h/region.csv.gz
new file mode 100644
index 0000000..3dbd31a
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/region.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/supplier.csv.gz b/tests/fixtures/optimizer/tpc-h/supplier.csv.gz
new file mode 100644
index 0000000..8dad82a
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/supplier.csv.gz
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
new file mode 100644
index 0000000..482e231
--- /dev/null
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -0,0 +1,1810 @@
+--------------------------------------
+-- TPC-H 1
+--------------------------------------
+select
+        l_returnflag,
+        l_linestatus,
+        sum(l_quantity) as sum_qty,
+        sum(l_extendedprice) as sum_base_price,
+        sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+        avg(l_quantity) as avg_qty,
+        avg(l_extendedprice) as avg_price,
+        avg(l_discount) as avg_disc,
+        count(*) as count_order
+from
+        lineitem
+where
+        CAST(l_shipdate AS DATE) <= date '1998-12-01' - interval '90' day
+group by
+        l_returnflag,
+        l_linestatus
+order by
+        l_returnflag,
+        l_linestatus;
+SELECT
+  "lineitem"."l_returnflag" AS "l_returnflag",
+  "lineitem"."l_linestatus" AS "l_linestatus",
+  SUM("lineitem"."l_quantity") AS "sum_qty",
+  SUM("lineitem"."l_extendedprice") AS "sum_base_price",
+  SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+  )) AS "sum_disc_price",
+  SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+    ) * (
+      1 + "lineitem"."l_tax"
+  )) AS "sum_charge",
+  AVG("lineitem"."l_quantity") AS "avg_qty",
+  AVG("lineitem"."l_extendedprice") AS "avg_price",
+  AVG("lineitem"."l_discount") AS "avg_disc",
+  COUNT(*) AS "count_order"
+FROM "lineitem" AS "lineitem"
+WHERE
+  CAST("lineitem"."l_shipdate" AS DATE) <= CAST('1998-09-02' AS DATE)
+GROUP BY
+  "lineitem"."l_returnflag",
+  "lineitem"."l_linestatus"
+ORDER BY
+  "l_returnflag",
+  "l_linestatus";
+
+--------------------------------------
+-- TPC-H 2
+--------------------------------------
+select
+   s_acctbal,
+   s_name,
+   n_name,
+   p_partkey,
+   p_mfgr,
+   s_address,
+   s_phone,
+   s_comment
+from
+   part,
+   supplier,
+   partsupp,
+   nation,
+   region
+where
+   p_partkey = ps_partkey
+   and s_suppkey = ps_suppkey
+   and p_size = 15
+   and p_type like '%BRASS'
+   and s_nationkey = n_nationkey
+   and n_regionkey = r_regionkey
+   and r_name = 'EUROPE'
+   and ps_supplycost = (
+           select
+                   min(ps_supplycost)
+           from
+                   partsupp,
+                   supplier,
+                   nation,
+                   region
+           where
+                   p_partkey = ps_partkey
+                   and s_suppkey = ps_suppkey
+                   and s_nationkey = n_nationkey
+                   and n_regionkey = r_regionkey
+                   and r_name = 'EUROPE'
+   )
+order by
+   s_acctbal desc,
+   n_name,
+   s_name,
+   p_partkey
+limit
+   100;
+WITH "_e_0" AS (
+  SELECT
+    "partsupp"."ps_partkey" AS "ps_partkey",
+    "partsupp"."ps_suppkey" AS "ps_suppkey",
+    "partsupp"."ps_supplycost" AS "ps_supplycost"
+  FROM "partsupp" AS "partsupp"
+), "_e_1" AS (
+  SELECT
+    "region"."r_regionkey" AS "r_regionkey",
+    "region"."r_name" AS "r_name"
+  FROM "region" AS "region"
+  WHERE
+    "region"."r_name" = 'EUROPE'
+)
+SELECT
+  "supplier"."s_acctbal" AS "s_acctbal",
+  "supplier"."s_name" AS "s_name",
+  "nation"."n_name" AS "n_name",
+  "part"."p_partkey" AS "p_partkey",
+  "part"."p_mfgr" AS "p_mfgr",
+  "supplier"."s_address" AS "s_address",
+  "supplier"."s_phone" AS "s_phone",
+  "supplier"."s_comment" AS "s_comment"
+FROM (
+  SELECT
+    "part"."p_partkey" AS "p_partkey",
+    "part"."p_mfgr" AS "p_mfgr",
+    "part"."p_type" AS "p_type",
+    "part"."p_size" AS "p_size"
+  FROM "part" AS "part"
+  WHERE
+    "part"."p_size" = 15
+    AND "part"."p_type" LIKE '%BRASS'
+) AS "part"
+LEFT JOIN (
+  SELECT
+    MIN("partsupp"."ps_supplycost") AS "_col_0",
+    "partsupp"."ps_partkey" AS "_u_1"
+  FROM "_e_0" AS "partsupp"
+  CROSS JOIN "_e_1" AS "region"
+  JOIN (
+    SELECT
+      "nation"."n_nationkey" AS "n_nationkey",
+      "nation"."n_regionkey" AS "n_regionkey"
+    FROM "nation" AS "nation"
+  ) AS "nation"
+    ON "nation"."n_regionkey" = "region"."r_regionkey"
+  JOIN (
+    SELECT
+      "supplier"."s_suppkey" AS "s_suppkey",
+      "supplier"."s_nationkey" AS "s_nationkey"
+    FROM "supplier" AS "supplier"
+  ) AS "supplier"
+    ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+    AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+  GROUP BY
+    "partsupp"."ps_partkey"
+) AS "_u_0"
+  ON "part"."p_partkey" = "_u_0"."_u_1"
+CROSS JOIN "_e_1" AS "region"
+JOIN (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name",
+    "nation"."n_regionkey" AS "n_regionkey"
+  FROM "nation" AS "nation"
+) AS "nation"
+  ON "nation"."n_regionkey" = "region"."r_regionkey"
+JOIN "_e_0" AS "partsupp"
+  ON "part"."p_partkey" = "partsupp"."ps_partkey"
+JOIN (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey",
+    "supplier"."s_name" AS "s_name",
+    "supplier"."s_address" AS "s_address",
+    "supplier"."s_nationkey" AS "s_nationkey",
+    "supplier"."s_phone" AS "s_phone",
+    "supplier"."s_acctbal" AS "s_acctbal",
+    "supplier"."s_comment" AS "s_comment"
+  FROM "supplier" AS "supplier"
+) AS "supplier"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+  AND "supplier"."s_suppkey" = "partsupp"."ps_suppkey"
+WHERE
+  "partsupp"."ps_supplycost" = "_u_0"."_col_0"
+  AND NOT "_u_0"."_u_1" IS NULL
+ORDER BY
+  "s_acctbal" DESC,
+  "n_name",
+  "s_name",
+  "p_partkey"
+LIMIT 100;
+
+--------------------------------------
+-- TPC-H 3
+--------------------------------------
+select
+        l_orderkey,
+        sum(l_extendedprice * (1 - l_discount)) as revenue,
+        CAST(o_orderdate AS STRING) AS o_orderdate,
+        o_shippriority
+from
+        customer,
+        orders,
+        lineitem
+where
+        c_mktsegment = 'BUILDING'
+        and c_custkey = o_custkey
+        and l_orderkey = o_orderkey
+        and o_orderdate < '1995-03-15'
+        and l_shipdate > '1995-03-15'
+group by
+        l_orderkey,
+        o_orderdate,
+        o_shippriority
+order by
+        revenue desc,
+        o_orderdate
+limit
+        10;
+SELECT
+  "lineitem"."l_orderkey" AS "l_orderkey",
+  SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+  )) AS "revenue",
+  CAST("orders"."o_orderdate" AS TEXT) AS "o_orderdate",
+  "orders"."o_shippriority" AS "o_shippriority"
+FROM (
+  SELECT
+    "customer"."c_custkey" AS "c_custkey",
+    "customer"."c_mktsegment" AS "c_mktsegment"
+  FROM "customer" AS "customer"
+  WHERE
+    "customer"."c_mktsegment" = 'BUILDING'
+) AS "customer"
+JOIN (
+  SELECT
+    "orders"."o_orderkey" AS "o_orderkey",
+    "orders"."o_custkey" AS "o_custkey",
+    "orders"."o_orderdate" AS "o_orderdate",
+    "orders"."o_shippriority" AS "o_shippriority"
+  FROM "orders" AS "orders"
+  WHERE
+    "orders"."o_orderdate" < '1995-03-15'
+) AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey",
+    "lineitem"."l_extendedprice" AS "l_extendedprice",
+    "lineitem"."l_discount" AS "l_discount",
+    "lineitem"."l_shipdate" AS "l_shipdate"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_shipdate" > '1995-03-15'
+) AS "lineitem"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+GROUP BY
+  "lineitem"."l_orderkey",
+  "orders"."o_orderdate",
+  "orders"."o_shippriority"
+ORDER BY
+  "revenue" DESC,
+  "o_orderdate"
+LIMIT 10;
+
+--------------------------------------
+-- TPC-H 4
+--------------------------------------
+select
+        o_orderpriority,
+        count(*) as order_count
+from
+        orders
+where
+        o_orderdate >= date '1993-07-01'
+        and o_orderdate < date '1993-07-01' + interval '3' month
+        and exists (
+                select
+                        *
+                from
+                        lineitem
+                where
+                        l_orderkey = o_orderkey
+                        and l_commitdate < l_receiptdate
+        )
+group by
+        o_orderpriority
+order by
+        o_orderpriority;
+SELECT
+  "orders"."o_orderpriority" AS "o_orderpriority",
+  COUNT(*) AS "order_count"
+FROM "orders" AS "orders"
+LEFT JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_commitdate" < "lineitem"."l_receiptdate"
+  GROUP BY
+    "lineitem"."l_orderkey"
+) AS "_u_0"
+  ON "_u_0"."l_orderkey" = "orders"."o_orderkey"
+WHERE
+  "orders"."o_orderdate" < CAST('1993-10-01' AS DATE)
+  AND "orders"."o_orderdate" >= CAST('1993-07-01' AS DATE)
+  AND NOT "_u_0"."l_orderkey" IS NULL
+GROUP BY
+  "orders"."o_orderpriority"
+ORDER BY
+  "o_orderpriority";
+
+--------------------------------------
+-- TPC-H 5
+--------------------------------------
+select
+        n_name,
+        sum(l_extendedprice * (1 - l_discount)) as revenue
+from
+        customer,
+        orders,
+        lineitem,
+        supplier,
+        nation,
+        region
+where
+        c_custkey = o_custkey
+        and l_orderkey = o_orderkey
+        and l_suppkey = s_suppkey
+        and c_nationkey = s_nationkey
+        and s_nationkey = n_nationkey
+        and n_regionkey = r_regionkey
+        and r_name = 'ASIA'
+        and o_orderdate >= date '1994-01-01'
+        and o_orderdate < date '1994-01-01' + interval '1' year
+group by
+        n_name
+order by
+        revenue desc;
+SELECT
+  "nation"."n_name" AS "n_name",
+  SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+  )) AS "revenue"
+FROM (
+  SELECT
+    "customer"."c_custkey" AS "c_custkey",
+    "customer"."c_nationkey" AS "c_nationkey"
+  FROM "customer" AS "customer"
+) AS "customer"
+JOIN (
+  SELECT
+    "orders"."o_orderkey" AS "o_orderkey",
+    "orders"."o_custkey" AS "o_custkey",
+    "orders"."o_orderdate" AS "o_orderdate"
+  FROM "orders" AS "orders"
+  WHERE
+    "orders"."o_orderdate" < CAST('1995-01-01' AS DATE)
+    AND "orders"."o_orderdate" >= CAST('1994-01-01' AS DATE)
+) AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+CROSS JOIN (
+  SELECT
+    "region"."r_regionkey" AS "r_regionkey",
+    "region"."r_name" AS "r_name"
+  FROM "region" AS "region"
+  WHERE
+    "region"."r_name" = 'ASIA'
+) AS "region"
+JOIN (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name",
+    "nation"."n_regionkey" AS "n_regionkey"
+  FROM "nation" AS "nation"
+) AS "nation"
+  ON "nation"."n_regionkey" = "region"."r_regionkey"
+JOIN (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey",
+    "supplier"."s_nationkey" AS "s_nationkey"
+  FROM "supplier" AS "supplier"
+) AS "supplier"
+  ON "customer"."c_nationkey" = "supplier"."s_nationkey"
+  AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey",
+    "lineitem"."l_suppkey" AS "l_suppkey",
+    "lineitem"."l_extendedprice" AS "l_extendedprice",
+    "lineitem"."l_discount" AS "l_discount"
+  FROM "lineitem" AS "lineitem"
+) AS "lineitem"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+  AND "lineitem"."l_suppkey" = "supplier"."s_suppkey"
+GROUP BY
+  "nation"."n_name"
+ORDER BY
+  "revenue" DESC;
+
+--------------------------------------
+-- TPC-H 6
+--------------------------------------
+select
+        sum(l_extendedprice * l_discount) as revenue
+from
+        lineitem
+where
+        l_shipdate >= date '1994-01-01'
+        and l_shipdate < date '1994-01-01' + interval '1' year
+        and l_discount between 0.06 - 0.01 and 0.06 + 0.01
+        and l_quantity < 24;
+SELECT
+  SUM("lineitem"."l_extendedprice" * "lineitem"."l_discount") AS "revenue"
+FROM "lineitem" AS "lineitem"
+WHERE
+  "lineitem"."l_discount" BETWEEN 0.05 AND 0.07
+  AND "lineitem"."l_quantity" < 24
+  AND "lineitem"."l_shipdate" < CAST('1995-01-01' AS DATE)
+  AND "lineitem"."l_shipdate" >= CAST('1994-01-01' AS DATE);
+
+--------------------------------------
+-- TPC-H 7
+--------------------------------------
+select
+        supp_nation,
+        cust_nation,
+        l_year,
+        sum(volume) as revenue
+from
+        (
+                select
+                        n1.n_name as supp_nation,
+                        n2.n_name as cust_nation,
+                        extract(year from l_shipdate) as l_year,
+                        l_extendedprice * (1 - l_discount) as volume
+                from
+                        supplier,
+                        lineitem,
+                        orders,
+                        customer,
+                        nation n1,
+                        nation n2
+                where
+                        s_suppkey = l_suppkey
+                        and o_orderkey = l_orderkey
+                        and c_custkey = o_custkey
+                        and s_nationkey = n1.n_nationkey
+                        and c_nationkey = n2.n_nationkey
+                        and (
+                                (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
+                                or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
+                        )
+                        and l_shipdate between date '1995-01-01' and date '1996-12-31'
+        ) as shipping
+group by
+        supp_nation,
+        cust_nation,
+        l_year
+order by
+        supp_nation,
+        cust_nation,
+        l_year;
+WITH "_e_0" AS (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name"
+  FROM "nation" AS "nation"
+  WHERE
+    "nation"."n_name" = 'FRANCE'
+    OR "nation"."n_name" = 'GERMANY'
+)
+SELECT
+  "shipping"."supp_nation" AS "supp_nation",
+  "shipping"."cust_nation" AS "cust_nation",
+  "shipping"."l_year" AS "l_year",
+  SUM("shipping"."volume") AS "revenue"
+FROM (
+  SELECT
+    "n1"."n_name" AS "supp_nation",
+    "n2"."n_name" AS "cust_nation",
+    EXTRACT(year FROM "lineitem"."l_shipdate") AS "l_year",
+    "lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+    ) AS "volume"
+  FROM (
+    SELECT
+      "supplier"."s_suppkey" AS "s_suppkey",
+      "supplier"."s_nationkey" AS "s_nationkey"
+    FROM "supplier" AS "supplier"
+  ) AS "supplier"
+  JOIN (
+    SELECT
+      "lineitem"."l_orderkey" AS "l_orderkey",
+      "lineitem"."l_suppkey" AS "l_suppkey",
+      "lineitem"."l_extendedprice" AS "l_extendedprice",
+      "lineitem"."l_discount" AS "l_discount",
+      "lineitem"."l_shipdate" AS "l_shipdate"
+    FROM "lineitem" AS "lineitem"
+    WHERE
+      "lineitem"."l_shipdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
+  ) AS "lineitem"
+    ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+  JOIN (
+    SELECT
+      "orders"."o_orderkey" AS "o_orderkey",
+      "orders"."o_custkey" AS "o_custkey"
+    FROM "orders" AS "orders"
+  ) AS "orders"
+    ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+  JOIN (
+    SELECT
+      "customer"."c_custkey" AS "c_custkey",
+      "customer"."c_nationkey" AS "c_nationkey"
+    FROM "customer" AS "customer"
+  ) AS "customer"
+    ON "customer"."c_custkey" = "orders"."o_custkey"
+  JOIN "_e_0" AS "n1"
+    ON "supplier"."s_nationkey" = "n1"."n_nationkey"
+  JOIN "_e_0" AS "n2"
+    ON "customer"."c_nationkey" = "n2"."n_nationkey"
+    AND (
+      "n1"."n_name" = 'FRANCE'
+      OR "n2"."n_name" = 'FRANCE'
+    )
+    AND (
+      "n1"."n_name" = 'GERMANY'
+      OR "n2"."n_name" = 'GERMANY'
+    )
+) AS "shipping"
+GROUP BY
+  "shipping"."supp_nation",
+  "shipping"."cust_nation",
+  "shipping"."l_year"
+ORDER BY
+  "supp_nation",
+  "cust_nation",
+  "l_year";
+
+--------------------------------------
+-- TPC-H 8
+--------------------------------------
+select
+        o_year,
+        sum(case
+                when nation = 'BRAZIL' then volume
+                else 0
+        end) / sum(volume) as mkt_share
+from
+        (
+                select
+                        extract(year from o_orderdate) as o_year,
+                        l_extendedprice * (1 - l_discount) as volume,
+                        n2.n_name as nation
+                from
+                        part,
+                        supplier,
+                        lineitem,
+                        orders,
+                        customer,
+                        nation n1,
+                        nation n2,
+                        region
+                where
+                        p_partkey = l_partkey
+                        and s_suppkey = l_suppkey
+                        and l_orderkey = o_orderkey
+                        and o_custkey = c_custkey
+                        and c_nationkey = n1.n_nationkey
+                        and n1.n_regionkey = r_regionkey
+                        and r_name = 'AMERICA'
+                        and s_nationkey = n2.n_nationkey
+                        and o_orderdate between date '1995-01-01' and date '1996-12-31'
+                        and p_type = 'ECONOMY ANODIZED STEEL'
+        ) as all_nations
+group by
+        o_year
+order by
+        o_year;
+SELECT
+  "all_nations"."o_year" AS "o_year",
+  SUM(CASE
+      WHEN "all_nations"."nation" = 'BRAZIL'
+      THEN "all_nations"."volume"
+      ELSE 0
+  END) / SUM("all_nations"."volume") AS "mkt_share"
+FROM (
+  SELECT
+    EXTRACT(year FROM "orders"."o_orderdate") AS "o_year",
+    "lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+    ) AS "volume",
+    "n2"."n_name" AS "nation"
+  FROM (
+    SELECT
+      "part"."p_partkey" AS "p_partkey",
+      "part"."p_type" AS "p_type"
+    FROM "part" AS "part"
+    WHERE
+      "part"."p_type" = 'ECONOMY ANODIZED STEEL'
+  ) AS "part"
+  CROSS JOIN (
+    SELECT
+      "region"."r_regionkey" AS "r_regionkey",
+      "region"."r_name" AS "r_name"
+    FROM "region" AS "region"
+    WHERE
+      "region"."r_name" = 'AMERICA'
+  ) AS "region"
+  JOIN (
+    SELECT
+      "nation"."n_nationkey" AS "n_nationkey",
+      "nation"."n_regionkey" AS "n_regionkey"
+    FROM "nation" AS "nation"
+  ) AS "n1"
+    ON "n1"."n_regionkey" = "region"."r_regionkey"
+  JOIN (
+    SELECT
+      "customer"."c_custkey" AS "c_custkey",
+      "customer"."c_nationkey" AS "c_nationkey"
+    FROM "customer" AS "customer"
+  ) AS "customer"
+    ON "customer"."c_nationkey" = "n1"."n_nationkey"
+  JOIN (
+    SELECT
+      "orders"."o_orderkey" AS "o_orderkey",
+      "orders"."o_custkey" AS "o_custkey",
+      "orders"."o_orderdate" AS "o_orderdate"
+    FROM "orders" AS "orders"
+    WHERE
+      "orders"."o_orderdate" BETWEEN CAST('1995-01-01' AS DATE) AND CAST('1996-12-31' AS DATE)
+  ) AS "orders"
+    ON "orders"."o_custkey" = "customer"."c_custkey"
+  JOIN (
+    SELECT
+      "lineitem"."l_orderkey" AS "l_orderkey",
+      "lineitem"."l_partkey" AS "l_partkey",
+      "lineitem"."l_suppkey" AS "l_suppkey",
+      "lineitem"."l_extendedprice" AS "l_extendedprice",
+      "lineitem"."l_discount" AS "l_discount"
+    FROM "lineitem" AS "lineitem"
+  ) AS "lineitem"
+    ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+  JOIN (
+    SELECT
+      "supplier"."s_suppkey" AS "s_suppkey",
+      "supplier"."s_nationkey" AS "s_nationkey"
+    FROM "supplier" AS "supplier"
+  ) AS "supplier"
+    ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+  JOIN (
+    SELECT
+      "nation"."n_nationkey" AS "n_nationkey",
+      "nation"."n_name" AS "n_name"
+    FROM "nation" AS "nation"
+  ) AS "n2"
+    ON "supplier"."s_nationkey" = "n2"."n_nationkey"
+) AS "all_nations"
+GROUP BY
+  "all_nations"."o_year"
+ORDER BY
+  "o_year";
+
+--------------------------------------
+-- TPC-H 9
+--------------------------------------
+select
+        nation,
+        o_year,
+        sum(amount) as sum_profit
+from
+        (
+                select
+                        n_name as nation,
+                        extract(year from o_orderdate) as o_year,
+                        l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
+                from
+                        part,
+                        supplier,
+                        lineitem,
+                        partsupp,
+                        orders,
+                        nation
+                where
+                        s_suppkey = l_suppkey
+                        and ps_suppkey = l_suppkey
+                        and ps_partkey = l_partkey
+                        and p_partkey = l_partkey
+                        and o_orderkey = l_orderkey
+                        and s_nationkey = n_nationkey
+                        and p_name like '%green%'
+        ) as profit
+group by
+        nation,
+        o_year
+order by
+        nation,
+        o_year desc;
+SELECT
+  "profit"."nation" AS "nation",
+  "profit"."o_year" AS "o_year",
+  SUM("profit"."amount") AS "sum_profit"
+FROM (
+  SELECT
+    "nation"."n_name" AS "nation",
+    EXTRACT(year FROM "orders"."o_orderdate") AS "o_year",
+    "lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+    ) - "partsupp"."ps_supplycost" * "lineitem"."l_quantity" AS "amount"
+  FROM (
+    SELECT
+      "part"."p_partkey" AS "p_partkey",
+      "part"."p_name" AS "p_name"
+    FROM "part" AS "part"
+    WHERE
+      "part"."p_name" LIKE '%green%'
+  ) AS "part"
+  JOIN (
+    SELECT
+      "lineitem"."l_orderkey" AS "l_orderkey",
+      "lineitem"."l_partkey" AS "l_partkey",
+      "lineitem"."l_suppkey" AS "l_suppkey",
+      "lineitem"."l_quantity" AS "l_quantity",
+      "lineitem"."l_extendedprice" AS "l_extendedprice",
+      "lineitem"."l_discount" AS "l_discount"
+    FROM "lineitem" AS "lineitem"
+  ) AS "lineitem"
+    ON "part"."p_partkey" = "lineitem"."l_partkey"
+  JOIN (
+    SELECT
+      "supplier"."s_suppkey" AS "s_suppkey",
+      "supplier"."s_nationkey" AS "s_nationkey"
+    FROM "supplier" AS "supplier"
+  ) AS "supplier"
+    ON "supplier"."s_suppkey" = "lineitem"."l_suppkey"
+  JOIN (
+    SELECT
+      "partsupp"."ps_partkey" AS "ps_partkey",
+      "partsupp"."ps_suppkey" AS "ps_suppkey",
+      "partsupp"."ps_supplycost" AS "ps_supplycost"
+    FROM "partsupp" AS "partsupp"
+  ) AS "partsupp"
+    ON "partsupp"."ps_partkey" = "lineitem"."l_partkey"
+    AND "partsupp"."ps_suppkey" = "lineitem"."l_suppkey"
+  JOIN (
+    SELECT
+      "orders"."o_orderkey" AS "o_orderkey",
+      "orders"."o_orderdate" AS "o_orderdate"
+    FROM "orders" AS "orders"
+  ) AS "orders"
+    ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+  JOIN (
+    SELECT
+      "nation"."n_nationkey" AS "n_nationkey",
+      "nation"."n_name" AS "n_name"
+    FROM "nation" AS "nation"
+  ) AS "nation"
+    ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+) AS "profit"
+GROUP BY
+  "profit"."nation",
+  "profit"."o_year"
+ORDER BY
+  "nation",
+  "o_year" DESC;
+
+--------------------------------------
+-- TPC-H 10
+--------------------------------------
+select
+        c_custkey,
+        c_name,
+        sum(l_extendedprice * (1 - l_discount)) as revenue,
+        c_acctbal,
+        n_name,
+        c_address,
+        c_phone,
+        c_comment
+from
+        customer,
+        orders,
+        lineitem,
+        nation
+where
+        c_custkey = o_custkey
+        and l_orderkey = o_orderkey
+        and o_orderdate >= date '1993-10-01'
+        and o_orderdate < date '1993-10-01' + interval '3' month
+        and l_returnflag = 'R'
+        and c_nationkey = n_nationkey
+group by
+        c_custkey,
+        c_name,
+        c_acctbal,
+        c_phone,
+        n_name,
+        c_address,
+        c_comment
+order by
+        revenue desc
+limit
+        20;
+SELECT
+  "customer"."c_custkey" AS "c_custkey",
+  "customer"."c_name" AS "c_name",
+  SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+  )) AS "revenue",
+  "customer"."c_acctbal" AS "c_acctbal",
+  "nation"."n_name" AS "n_name",
+  "customer"."c_address" AS "c_address",
+  "customer"."c_phone" AS "c_phone",
+  "customer"."c_comment" AS "c_comment"
+FROM (
+  SELECT
+    "customer"."c_custkey" AS "c_custkey",
+    "customer"."c_name" AS "c_name",
+    "customer"."c_address" AS "c_address",
+    "customer"."c_nationkey" AS "c_nationkey",
+    "customer"."c_phone" AS "c_phone",
+    "customer"."c_acctbal" AS "c_acctbal",
+    "customer"."c_comment" AS "c_comment"
+  FROM "customer" AS "customer"
+) AS "customer"
+JOIN (
+  SELECT
+    "orders"."o_orderkey" AS "o_orderkey",
+    "orders"."o_custkey" AS "o_custkey",
+    "orders"."o_orderdate" AS "o_orderdate"
+  FROM "orders" AS "orders"
+  WHERE
+    "orders"."o_orderdate" < CAST('1994-01-01' AS DATE)
+    AND "orders"."o_orderdate" >= CAST('1993-10-01' AS DATE)
+) AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey",
+    "lineitem"."l_extendedprice" AS "l_extendedprice",
+    "lineitem"."l_discount" AS "l_discount",
+    "lineitem"."l_returnflag" AS "l_returnflag"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_returnflag" = 'R'
+) AS "lineitem"
+  ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
+JOIN (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name"
+  FROM "nation" AS "nation"
+) AS "nation"
+  ON "customer"."c_nationkey" = "nation"."n_nationkey"
+GROUP BY
+  "customer"."c_custkey",
+  "customer"."c_name",
+  "customer"."c_acctbal",
+  "customer"."c_phone",
+  "nation"."n_name",
+  "customer"."c_address",
+  "customer"."c_comment"
+ORDER BY
+  "revenue" DESC
+LIMIT 20;
+
+--------------------------------------
+-- TPC-H 11
+--------------------------------------
+select
+        ps_partkey,
+        sum(ps_supplycost * ps_availqty) as value
+from
+        partsupp,
+        supplier,
+        nation
+where
+        ps_suppkey = s_suppkey
+        and s_nationkey = n_nationkey
+        and n_name = 'GERMANY'
+group by
+        ps_partkey having
+                sum(ps_supplycost * ps_availqty) > (
+                        select
+                                sum(ps_supplycost * ps_availqty) * 0.0001
+                        from
+                                partsupp,
+                                supplier,
+                                nation
+                        where
+                                ps_suppkey = s_suppkey
+                                and s_nationkey = n_nationkey
+                                and n_name = 'GERMANY'
+                )
+order by
+        value desc;
+WITH "_e_0" AS (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey",
+    "supplier"."s_nationkey" AS "s_nationkey"
+  FROM "supplier" AS "supplier"
+), "_e_1" AS (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name"
+  FROM "nation" AS "nation"
+  WHERE
+    "nation"."n_name" = 'GERMANY'
+)
+SELECT
+  "partsupp"."ps_partkey" AS "ps_partkey",
+  SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") AS "value"
+FROM (
+  SELECT
+    "partsupp"."ps_partkey" AS "ps_partkey",
+    "partsupp"."ps_suppkey" AS "ps_suppkey",
+    "partsupp"."ps_availqty" AS "ps_availqty",
+    "partsupp"."ps_supplycost" AS "ps_supplycost"
+  FROM "partsupp" AS "partsupp"
+) AS "partsupp"
+JOIN "_e_0" AS "supplier"
+  ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
+JOIN "_e_1" AS "nation"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+GROUP BY
+  "partsupp"."ps_partkey"
+HAVING
+  SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") > (
+    SELECT
+      SUM("partsupp"."ps_supplycost" * "partsupp"."ps_availqty") * 0.0001 AS "_col_0"
+    FROM (
+      SELECT
+        "partsupp"."ps_suppkey" AS "ps_suppkey",
+        "partsupp"."ps_availqty" AS "ps_availqty",
+        "partsupp"."ps_supplycost" AS "ps_supplycost"
+      FROM "partsupp" AS "partsupp"
+    ) AS "partsupp"
+    JOIN "_e_0" AS "supplier"
+      ON "partsupp"."ps_suppkey" = "supplier"."s_suppkey"
+    JOIN "_e_1" AS "nation"
+      ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+  )
+ORDER BY
+  "value" DESC;
+
+--------------------------------------
+-- TPC-H 12
+--------------------------------------
+select
+        l_shipmode,
+        sum(case
+                when o_orderpriority = '1-URGENT'
+                        or o_orderpriority = '2-HIGH'
+                        then 1
+                else 0
+        end) as high_line_count,
+        sum(case
+                when o_orderpriority <> '1-URGENT'
+                        and o_orderpriority <> '2-HIGH'
+                        then 1
+                else 0
+        end) as low_line_count
+from
+        orders,
+        lineitem
+where
+        o_orderkey = l_orderkey
+        and l_shipmode in ('MAIL', 'SHIP')
+        and l_commitdate < l_receiptdate
+        and l_shipdate < l_commitdate
+        and l_receiptdate >= date '1994-01-01'
+        and l_receiptdate < date '1994-01-01' + interval '1' year
+group by
+        l_shipmode
+order by
+        l_shipmode;
+SELECT
+  "lineitem"."l_shipmode" AS "l_shipmode",
+  SUM(CASE
+      WHEN "orders"."o_orderpriority" = '1-URGENT'
+        OR "orders"."o_orderpriority" = '2-HIGH'
+      THEN 1
+      ELSE 0
+  END) AS "high_line_count",
+  SUM(CASE
+      WHEN "orders"."o_orderpriority" <> '1-URGENT'
+        AND "orders"."o_orderpriority" <> '2-HIGH'
+      THEN 1
+      ELSE 0
+  END) AS "low_line_count"
+FROM (
+  SELECT
+    "orders"."o_orderkey" AS "o_orderkey",
+    "orders"."o_orderpriority" AS "o_orderpriority"
+  FROM "orders" AS "orders"
+) AS "orders"
+JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey",
+    "lineitem"."l_shipdate" AS "l_shipdate",
+    "lineitem"."l_commitdate" AS "l_commitdate",
+    "lineitem"."l_receiptdate" AS "l_receiptdate",
+    "lineitem"."l_shipmode" AS "l_shipmode"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_commitdate" < "lineitem"."l_receiptdate"
+    AND "lineitem"."l_receiptdate" < CAST('1995-01-01' AS DATE)
+    AND "lineitem"."l_receiptdate" >= CAST('1994-01-01' AS DATE)
+    AND "lineitem"."l_shipdate" < "lineitem"."l_commitdate"
+    AND "lineitem"."l_shipmode" IN ('MAIL', 'SHIP')
+) AS "lineitem"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+GROUP BY
+  "lineitem"."l_shipmode"
+ORDER BY
+  "l_shipmode";
+
+--------------------------------------
+-- TPC-H 13
+--------------------------------------
+select
+        c_count,
+        count(*) as custdist
+from
+        (
+                select
+                        c_custkey,
+                        count(o_orderkey)
+                from
+                        customer left outer join orders on
+                                c_custkey = o_custkey
+                                and o_comment not like '%special%requests%'
+                group by
+                        c_custkey
+        ) as c_orders (c_custkey, c_count)
+group by
+        c_count
+order by
+        custdist desc,
+        c_count desc;
+SELECT
+  "c_orders"."c_count" AS "c_count",
+  COUNT(*) AS "custdist"
+FROM (
+  SELECT
+    COUNT("orders"."o_orderkey") AS "c_count"
+  FROM (
+    SELECT
+      "customer"."c_custkey" AS "c_custkey"
+    FROM "customer" AS "customer"
+  ) AS "customer"
+  LEFT JOIN (
+    SELECT
+      "orders"."o_orderkey" AS "o_orderkey",
+      "orders"."o_custkey" AS "o_custkey",
+      "orders"."o_comment" AS "o_comment"
+    FROM "orders" AS "orders"
+    WHERE
+      NOT "orders"."o_comment" LIKE '%special%requests%'
+  ) AS "orders"
+    ON "customer"."c_custkey" = "orders"."o_custkey"
+  GROUP BY
+    "customer"."c_custkey"
+) AS "c_orders"
+GROUP BY
+  "c_orders"."c_count"
+ORDER BY
+  "custdist" DESC,
+  "c_count" DESC;
+
+--------------------------------------
+-- TPC-H 14
+--------------------------------------
+select
+        100.00 * sum(case
+                when p_type like 'PROMO%'
+                        then l_extendedprice * (1 - l_discount)
+                else 0
+        end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
+from
+        lineitem,
+        part
+where
+        l_partkey = p_partkey
+        and l_shipdate >= date '1995-09-01'
+        and l_shipdate < date '1995-09-01' + interval '1' month;
+SELECT
+  100.00 * SUM(CASE
+      WHEN "part"."p_type" LIKE 'PROMO%'
+      THEN "lineitem"."l_extendedprice" * (
+          1 - "lineitem"."l_discount"
+        )
+      ELSE 0
+  END) / SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+  )) AS "promo_revenue"
+FROM (
+  SELECT
+    "lineitem"."l_partkey" AS "l_partkey",
+    "lineitem"."l_extendedprice" AS "l_extendedprice",
+    "lineitem"."l_discount" AS "l_discount",
+    "lineitem"."l_shipdate" AS "l_shipdate"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_shipdate" < CAST('1995-10-01' AS DATE)
+    AND "lineitem"."l_shipdate" >= CAST('1995-09-01' AS DATE)
+) AS "lineitem"
+JOIN (
+  SELECT
+    "part"."p_partkey" AS "p_partkey",
+    "part"."p_type" AS "p_type"
+  FROM "part" AS "part"
+) AS "part"
+  ON "lineitem"."l_partkey" = "part"."p_partkey";
+
+--------------------------------------
+-- TPC-H 15
+--------------------------------------
+with revenue (supplier_no, total_revenue) as (
+        select
+                l_suppkey,
+                sum(l_extendedprice * (1 - l_discount))
+        from
+                lineitem
+        where
+                l_shipdate >= date '1996-01-01'
+                and l_shipdate < date '1996-01-01' + interval '3' month
+        group by
+                l_suppkey)
+select
+        s_suppkey,
+        s_name,
+        s_address,
+        s_phone,
+        total_revenue
+from
+        supplier,
+        revenue
+where
+        s_suppkey = supplier_no
+        and total_revenue = (
+                select
+                        max(total_revenue)
+                from
+                        revenue
+        )
+order by
+        s_suppkey;
+WITH "revenue" AS (
+  SELECT
+    "lineitem"."l_suppkey" AS "supplier_no",
+    SUM("lineitem"."l_extendedprice" * (
+        1 - "lineitem"."l_discount"
+    )) AS "total_revenue"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_shipdate" < CAST('1996-04-01' AS DATE)
+    AND "lineitem"."l_shipdate" >= CAST('1996-01-01' AS DATE)
+  GROUP BY
+    "lineitem"."l_suppkey"
+)
+SELECT
+  "supplier"."s_suppkey" AS "s_suppkey",
+  "supplier"."s_name" AS "s_name",
+  "supplier"."s_address" AS "s_address",
+  "supplier"."s_phone" AS "s_phone",
+  "revenue"."total_revenue" AS "total_revenue"
+FROM (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey",
+    "supplier"."s_name" AS "s_name",
+    "supplier"."s_address" AS "s_address",
+    "supplier"."s_phone" AS "s_phone"
+  FROM "supplier" AS "supplier"
+) AS "supplier"
+JOIN "revenue"
+  ON "revenue"."total_revenue" = (
+    SELECT
+      MAX("revenue"."total_revenue") AS "_col_0"
+    FROM "revenue"
+  )
+  AND "supplier"."s_suppkey" = "revenue"."supplier_no"
+ORDER BY
+  "s_suppkey";
+
+--------------------------------------
+-- TPC-H 16
+--------------------------------------
+select
+        p_brand,
+        p_type,
+        p_size,
+        count(distinct ps_suppkey) as supplier_cnt
+from
+        partsupp,
+        part
+where
+        p_partkey = ps_partkey
+        and p_brand <> 'Brand#45'
+        and p_type not like 'MEDIUM POLISHED%'
+        and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
+        and ps_suppkey not in (
+                select
+                        s_suppkey
+                from
+                        supplier
+                where
+                        s_comment like '%Customer%Complaints%'
+        )
+group by
+        p_brand,
+        p_type,
+        p_size
+order by
+        supplier_cnt desc,
+        p_brand,
+        p_type,
+        p_size;
+SELECT
+  "part"."p_brand" AS "p_brand",
+  "part"."p_type" AS "p_type",
+  "part"."p_size" AS "p_size",
+  COUNT(DISTINCT "partsupp"."ps_suppkey") AS "supplier_cnt"
+FROM (
+  SELECT
+    "partsupp"."ps_partkey" AS "ps_partkey",
+    "partsupp"."ps_suppkey" AS "ps_suppkey"
+  FROM "partsupp" AS "partsupp"
+) AS "partsupp"
+LEFT JOIN (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey"
+  FROM "supplier" AS "supplier"
+  WHERE
+    "supplier"."s_comment" LIKE '%Customer%Complaints%'
+  GROUP BY
+    "supplier"."s_suppkey"
+) AS "_u_0"
+  ON "partsupp"."ps_suppkey" = "_u_0"."s_suppkey"
+JOIN (
+  SELECT
+    "part"."p_partkey" AS "p_partkey",
+    "part"."p_brand" AS "p_brand",
+    "part"."p_type" AS "p_type",
+    "part"."p_size" AS "p_size"
+  FROM "part" AS "part"
+  WHERE
+    "part"."p_brand" <> 'Brand#45'
+    AND "part"."p_size" IN (49, 14, 23, 45, 19, 3, 36, 9)
+    AND NOT "part"."p_type" LIKE 'MEDIUM POLISHED%'
+) AS "part"
+  ON "part"."p_partkey" = "partsupp"."ps_partkey"
+WHERE
+  "_u_0"."s_suppkey" IS NULL
+GROUP BY
+  "part"."p_brand",
+  "part"."p_type",
+  "part"."p_size"
+ORDER BY
+  "supplier_cnt" DESC,
+  "p_brand",
+  "p_type",
+  "p_size";
+
+--------------------------------------
+-- TPC-H 17
+--------------------------------------
+select
+        sum(l_extendedprice) / 7.0 as avg_yearly
+from
+        lineitem,
+        part
+where
+        p_partkey = l_partkey
+        and p_brand = 'Brand#23'
+        and p_container = 'MED BOX'
+        and l_quantity < (
+                select
+                        0.2 * avg(l_quantity)
+                from
+                        lineitem
+                where
+                        l_partkey = p_partkey
+        );
+SELECT
+  SUM("lineitem"."l_extendedprice") / 7.0 AS "avg_yearly"
+FROM (
+  SELECT
+    "lineitem"."l_partkey" AS "l_partkey",
+    "lineitem"."l_quantity" AS "l_quantity",
+    "lineitem"."l_extendedprice" AS "l_extendedprice"
+  FROM "lineitem" AS "lineitem"
+) AS "lineitem"
+JOIN (
+  SELECT
+    "part"."p_partkey" AS "p_partkey",
+    "part"."p_brand" AS "p_brand",
+    "part"."p_container" AS "p_container"
+  FROM "part" AS "part"
+  WHERE
+    "part"."p_brand" = 'Brand#23'
+    AND "part"."p_container" = 'MED BOX'
+) AS "part"
+  ON "part"."p_partkey" = "lineitem"."l_partkey"
+LEFT JOIN (
+  SELECT
+    0.2 * AVG("lineitem"."l_quantity") AS "_col_0",
+    "lineitem"."l_partkey" AS "_u_1"
+  FROM "lineitem" AS "lineitem"
+  GROUP BY
+    "lineitem"."l_partkey"
+) AS "_u_0"
+  ON "_u_0"."_u_1" = "part"."p_partkey"
+WHERE
+  "lineitem"."l_quantity" < "_u_0"."_col_0"
+  AND NOT "_u_0"."_u_1" IS NULL;
+
+--------------------------------------
+-- TPC-H 18
+--------------------------------------
+select
+        c_name,
+        c_custkey,
+        o_orderkey,
+        o_orderdate,
+        o_totalprice,
+        sum(l_quantity)
+from
+        customer,
+        orders,
+        lineitem
+where
+        o_orderkey in (
+                select
+                        l_orderkey
+                from
+                        lineitem
+                group by
+                        l_orderkey having
+                                sum(l_quantity) > 300
+        )
+        and c_custkey = o_custkey
+        and o_orderkey = l_orderkey
+group by
+        c_name,
+        c_custkey,
+        o_orderkey,
+        o_orderdate,
+        o_totalprice
+order by
+        o_totalprice desc,
+        o_orderdate
+limit
+        100;
+SELECT
+  "customer"."c_name" AS "c_name",
+  "customer"."c_custkey" AS "c_custkey",
+  "orders"."o_orderkey" AS "o_orderkey",
+  "orders"."o_orderdate" AS "o_orderdate",
+  "orders"."o_totalprice" AS "o_totalprice",
+  SUM("lineitem"."l_quantity") AS "_col_5"
+FROM (
+  SELECT
+    "customer"."c_custkey" AS "c_custkey",
+    "customer"."c_name" AS "c_name"
+  FROM "customer" AS "customer"
+) AS "customer"
+JOIN (
+  SELECT
+    "orders"."o_orderkey" AS "o_orderkey",
+    "orders"."o_custkey" AS "o_custkey",
+    "orders"."o_totalprice" AS "o_totalprice",
+    "orders"."o_orderdate" AS "o_orderdate"
+  FROM "orders" AS "orders"
+) AS "orders"
+  ON "customer"."c_custkey" = "orders"."o_custkey"
+LEFT JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey"
+  FROM "lineitem" AS "lineitem"
+  GROUP BY
+    "lineitem"."l_orderkey",
+    "lineitem"."l_orderkey"
+  HAVING
+    SUM("lineitem"."l_quantity") > 300
+) AS "_u_0"
+  ON "orders"."o_orderkey" = "_u_0"."l_orderkey"
+JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey",
+    "lineitem"."l_quantity" AS "l_quantity"
+  FROM "lineitem" AS "lineitem"
+) AS "lineitem"
+  ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
+WHERE
+  NOT "_u_0"."l_orderkey" IS NULL
+GROUP BY
+  "customer"."c_name",
+  "customer"."c_custkey",
+  "orders"."o_orderkey",
+  "orders"."o_orderdate",
+  "orders"."o_totalprice"
+ORDER BY
+  "o_totalprice" DESC,
+  "o_orderdate"
+LIMIT 100;
+
+--------------------------------------
+-- TPC-H 19
+--------------------------------------
+select
+        sum(l_extendedprice* (1 - l_discount)) as revenue
+from
+        lineitem,
+        part
+where
+        (
+                p_partkey = l_partkey
+                and p_brand = 'Brand#12'
+                and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+                and l_quantity >= 1 and l_quantity <= 11
+                and p_size between 1 and 5
+                and l_shipmode in ('AIR', 'AIR REG')
+                and l_shipinstruct = 'DELIVER IN PERSON'
+        )
+        or
+        (
+                p_partkey = l_partkey
+                and p_brand = 'Brand#23'
+                and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+                and l_quantity >= 10 and l_quantity <= 20
+                and p_size between 1 and 10
+                and l_shipmode in ('AIR', 'AIR REG')
+                and l_shipinstruct = 'DELIVER IN PERSON'
+        )
+        or
+        (
+                p_partkey = l_partkey
+                and p_brand = 'Brand#34'
+                and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+                and l_quantity >= 20 and l_quantity <= 30
+                and p_size between 1 and 15
+                and l_shipmode in ('AIR', 'AIR REG')
+                and l_shipinstruct = 'DELIVER IN PERSON'
+        );
+SELECT
+  SUM("lineitem"."l_extendedprice" * (
+      1 - "lineitem"."l_discount"
+  )) AS "revenue"
+FROM (
+  SELECT
+    "lineitem"."l_partkey" AS "l_partkey",
+    "lineitem"."l_quantity" AS "l_quantity",
+    "lineitem"."l_extendedprice" AS "l_extendedprice",
+    "lineitem"."l_discount" AS "l_discount",
+    "lineitem"."l_shipinstruct" AS "l_shipinstruct",
+    "lineitem"."l_shipmode" AS "l_shipmode"
+  FROM "lineitem" AS "lineitem"
+) AS "lineitem"
+JOIN (
+  SELECT
+    "part"."p_partkey" AS "p_partkey",
+    "part"."p_brand" AS "p_brand",
+    "part"."p_size" AS "p_size",
+    "part"."p_container" AS "p_container"
+  FROM "part" AS "part"
+) AS "part"
+  ON (
+    "part"."p_brand" = 'Brand#12'
+    AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+    AND "part"."p_size" BETWEEN 1 AND 5
+  )
+  OR (
+    "part"."p_brand" = 'Brand#23'
+    AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+    AND "part"."p_size" BETWEEN 1 AND 10
+  )
+  OR (
+    "part"."p_brand" = 'Brand#34'
+    AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+    AND "part"."p_size" BETWEEN 1 AND 15
+  )
+WHERE
+  (
+    "lineitem"."l_quantity" <= 11
+    AND "lineitem"."l_quantity" >= 1
+    AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
+    AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
+    AND "part"."p_brand" = 'Brand#12'
+    AND "part"."p_container" IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+    AND "part"."p_size" BETWEEN 1 AND 5
+  )
+  OR (
+    "lineitem"."l_quantity" <= 20
+    AND "lineitem"."l_quantity" >= 10
+    AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
+    AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
+    AND "part"."p_brand" = 'Brand#23'
+    AND "part"."p_container" IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+    AND "part"."p_size" BETWEEN 1 AND 10
+  )
+  OR (
+    "lineitem"."l_quantity" <= 30
+    AND "lineitem"."l_quantity" >= 20
+    AND "lineitem"."l_shipinstruct" = 'DELIVER IN PERSON'
+    AND "lineitem"."l_shipmode" IN ('AIR', 'AIR REG')
+    AND "part"."p_brand" = 'Brand#34'
+    AND "part"."p_container" IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+    AND "part"."p_partkey" = "lineitem"."l_partkey"
+    AND "part"."p_size" BETWEEN 1 AND 15
+  );
+
+--------------------------------------
+-- TPC-H 20
+--------------------------------------
+select
+        s_name,
+        s_address
+from
+        supplier,
+        nation
+where
+        s_suppkey in (
+                select
+                        ps_suppkey
+                from
+                        partsupp
+                where
+                        ps_partkey in (
+                                select
+                                        p_partkey
+                                from
+                                        part
+                                where
+                                        p_name like 'forest%'
+                        )
+                        and ps_availqty > (
+                                select
+                                        0.5 * sum(l_quantity)
+                                from
+                                        lineitem
+                                where
+                                        l_partkey = ps_partkey
+                                        and l_suppkey = ps_suppkey
+                                        and l_shipdate >= date '1994-01-01'
+                                        and l_shipdate < date '1994-01-01' + interval '1' year
+                        )
+        )
+        and s_nationkey = n_nationkey
+        and n_name = 'CANADA'
+order by
+        s_name;
+SELECT
+  "supplier"."s_name" AS "s_name",
+  "supplier"."s_address" AS "s_address"
+FROM (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey",
+    "supplier"."s_name" AS "s_name",
+    "supplier"."s_address" AS "s_address",
+    "supplier"."s_nationkey" AS "s_nationkey"
+  FROM "supplier" AS "supplier"
+) AS "supplier"
+LEFT JOIN (
+  SELECT
+    "partsupp"."ps_suppkey" AS "ps_suppkey"
+  FROM "partsupp" AS "partsupp"
+  LEFT JOIN (
+    SELECT
+      0.5 * SUM("lineitem"."l_quantity") AS "_col_0",
+      "lineitem"."l_partkey" AS "_u_1",
+      "lineitem"."l_suppkey" AS "_u_2"
+    FROM "lineitem" AS "lineitem"
+    WHERE
+      "lineitem"."l_shipdate" < CAST('1995-01-01' AS DATE)
+      AND "lineitem"."l_shipdate" >= CAST('1994-01-01' AS DATE)
+    GROUP BY
+      "lineitem"."l_partkey",
+      "lineitem"."l_suppkey"
+  ) AS "_u_0"
+    ON "_u_0"."_u_1" = "partsupp"."ps_partkey"
+    AND "_u_0"."_u_2" = "partsupp"."ps_suppkey"
+  LEFT JOIN (
+    SELECT
+      "part"."p_partkey" AS "p_partkey"
+    FROM "part" AS "part"
+    WHERE
+      "part"."p_name" LIKE 'forest%'
+    GROUP BY
+      "part"."p_partkey"
+  ) AS "_u_3"
+    ON "partsupp"."ps_partkey" = "_u_3"."p_partkey"
+  WHERE
+    "partsupp"."ps_availqty" > "_u_0"."_col_0"
+    AND NOT "_u_0"."_u_1" IS NULL
+    AND NOT "_u_0"."_u_2" IS NULL
+    AND NOT "_u_3"."p_partkey" IS NULL
+  GROUP BY
+    "partsupp"."ps_suppkey"
+) AS "_u_4"
+  ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
+JOIN (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name"
+  FROM "nation" AS "nation"
+  WHERE
+    "nation"."n_name" = 'CANADA'
+) AS "nation"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+WHERE
+  NOT "_u_4"."ps_suppkey" IS NULL
+ORDER BY
+  "s_name";
+
+--------------------------------------
+-- TPC-H 21
+--------------------------------------
+select
+        s_name,
+        count(*) as numwait
+from
+        supplier,
+        lineitem l1,
+        orders,
+        nation
+where
+        s_suppkey = l1.l_suppkey
+        and o_orderkey = l1.l_orderkey
+        and o_orderstatus = 'F'
+        and l1.l_receiptdate > l1.l_commitdate
+        and exists (
+                select
+                        *
+                from
+                        lineitem l2
+                where
+                        l2.l_orderkey = l1.l_orderkey
+                        and l2.l_suppkey <> l1.l_suppkey
+        )
+        and not exists (
+                select
+                        *
+                from
+                        lineitem l3
+                where
+                        l3.l_orderkey = l1.l_orderkey
+                        and l3.l_suppkey <> l1.l_suppkey
+                        and l3.l_receiptdate > l3.l_commitdate
+        )
+        and s_nationkey = n_nationkey
+        and n_name = 'SAUDI ARABIA'
+group by
+        s_name
+order by
+        numwait desc,
+        s_name
+limit
+        100;
+SELECT
+  "supplier"."s_name" AS "s_name",
+  COUNT(*) AS "numwait"
+FROM (
+  SELECT
+    "supplier"."s_suppkey" AS "s_suppkey",
+    "supplier"."s_name" AS "s_name",
+    "supplier"."s_nationkey" AS "s_nationkey"
+  FROM "supplier" AS "supplier"
+) AS "supplier"
+JOIN (
+  SELECT
+    "lineitem"."l_orderkey" AS "l_orderkey",
+    "lineitem"."l_suppkey" AS "l_suppkey",
+    "lineitem"."l_commitdate" AS "l_commitdate",
+    "lineitem"."l_receiptdate" AS "l_receiptdate"
+  FROM "lineitem" AS "lineitem"
+  WHERE
+    "lineitem"."l_receiptdate" > "lineitem"."l_commitdate"
+) AS "l1"
+  ON "supplier"."s_suppkey" = "l1"."l_suppkey"
+LEFT JOIN (
+  SELECT
+    "l2"."l_orderkey" AS "l_orderkey",
+    ARRAY_AGG("l2"."l_suppkey") AS "_u_1"
+  FROM "lineitem" AS "l2"
+  GROUP BY
+    "l2"."l_orderkey"
+) AS "_u_0"
+  ON "_u_0"."l_orderkey" = "l1"."l_orderkey"
+LEFT JOIN (
+  SELECT
+    "l3"."l_orderkey" AS "l_orderkey",
+    ARRAY_AGG("l3"."l_suppkey") AS "_u_3"
+  FROM "lineitem" AS "l3"
+  WHERE
+    "l3"."l_receiptdate" > "l3"."l_commitdate"
+  GROUP BY
+    "l3"."l_orderkey"
+) AS "_u_2"
+  ON "_u_2"."l_orderkey" = "l1"."l_orderkey"
+JOIN (
+  SELECT
+    "orders"."o_orderkey" AS "o_orderkey",
+    "orders"."o_orderstatus" AS "o_orderstatus"
+  FROM "orders" AS "orders"
+  WHERE
+    "orders"."o_orderstatus" = 'F'
+) AS "orders"
+  ON "orders"."o_orderkey" = "l1"."l_orderkey"
+JOIN (
+  SELECT
+    "nation"."n_nationkey" AS "n_nationkey",
+    "nation"."n_name" AS "n_name"
+  FROM "nation" AS "nation"
+  WHERE
+    "nation"."n_name" = 'SAUDI ARABIA'
+) AS "nation"
+  ON "supplier"."s_nationkey" = "nation"."n_nationkey"
+WHERE
+  (
+    "_u_2"."l_orderkey" IS NULL
+    OR NOT ARRAY_ANY("_u_2"."_u_3", "_x" -> "_x" <> "l1"."l_suppkey")
+  )
+  AND ARRAY_ANY("_u_0"."_u_1", "_x" -> "_x" <> "l1"."l_suppkey")
+  AND NOT "_u_0"."l_orderkey" IS NULL
+GROUP BY
+  "supplier"."s_name"
+ORDER BY
+  "numwait" DESC,
+  "s_name"
+LIMIT 100;
+
+--------------------------------------
+-- TPC-H 22
+--------------------------------------
+select
+        cntrycode,
+        count(*) as numcust,
+        sum(c_acctbal) as totacctbal
+from
+        (
+                select
+                        substring(c_phone, 1, 2) as cntrycode,
+                        c_acctbal
+                from
+                        customer
+                where
+                        substring(c_phone, 1, 2) in
+                                ('13', '31', '23', '29', '30', '18', '17')
+                        and c_acctbal > (
+                                select
+                                        avg(c_acctbal)
+                                from
+                                        customer
+                                where
+                                        c_acctbal > 0.00
+                                        and substring(c_phone, 1, 2) in
+                                                ('13', '31', '23', '29', '30', '18', '17')
+                        )
+                        and not exists (
+                                select
+                                        *
+                                from
+                                        orders
+                                where
+                                        o_custkey = c_custkey
+                        )
+        ) as custsale
+group by
+        cntrycode
+order by
+        cntrycode;
+SELECT
+  "custsale"."cntrycode" AS "cntrycode",
+  COUNT(*) AS "numcust",
+  SUM("custsale"."c_acctbal") AS "totacctbal"
+FROM (
+  SELECT
+    SUBSTRING("customer"."c_phone", 1, 2) AS "cntrycode",
+    "customer"."c_acctbal" AS "c_acctbal"
+  FROM "customer" AS "customer"
+  LEFT JOIN (
+    SELECT
+      "orders"."o_custkey" AS "_u_1"
+    FROM "orders" AS "orders"
+    GROUP BY
+      "orders"."o_custkey"
+  ) AS "_u_0"
+    ON "_u_0"."_u_1" = "customer"."c_custkey"
+  WHERE
+    "_u_0"."_u_1" IS NULL
+    AND "customer"."c_acctbal" > (
+      SELECT
+        AVG("customer"."c_acctbal") AS "_col_0"
+      FROM "customer" AS "customer"
+      WHERE
+        "customer"."c_acctbal" > 0.00
+        AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
+    )
+    AND SUBSTRING("customer"."c_phone", 1, 2) IN ('13', '31', '23', '29', '30', '18', '17')
+) AS "custsale"
+GROUP BY
+  "custsale"."cntrycode"
+ORDER BY
+  "cntrycode";
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
new file mode 100644
index 0000000..9c4bd27
--- /dev/null
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -0,0 +1,206 @@
+--------------------------------------
+-- Unnest Subqueries
+--------------------------------------
+SELECT *
+FROM x AS x
+WHERE
+  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)
+;
+SELECT
+  *
+FROM x AS x
+LEFT JOIN (
+  SELECT
+    y.a AS a
+  FROM y
+  GROUP BY
+    y.a
+) AS "_u_0"
+  ON x.a = "_u_0"."a"
+LEFT JOIN (
+  SELECT
+    y.b AS b
+  FROM y
+  GROUP BY
+    y.b
+) AS "_u_1"
+  ON x.a = "_u_1"."b"
+LEFT JOIN (
+  SELECT
+    y.a AS a
+  FROM y
+  GROUP BY
+    y.a
+) AS "_u_2"
+  ON x.a = "_u_2"."a"
+LEFT JOIN (
+  SELECT
+    SUM(y.b) AS b,
+    y.a AS _u_4
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.a
+) AS "_u_3"
+  ON x.a = "_u_3"."_u_4"
+LEFT JOIN (
+  SELECT
+    SUM(y.b) AS b,
+    y.a AS _u_6
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.a
+) AS "_u_5"
+  ON x.a = "_u_5"."_u_6"
+LEFT JOIN (
+  SELECT
+    y.a AS a
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.a
+) AS "_u_7"
+  ON "_u_7".a = x.a
+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
+    ARRAY_AGG(y.a) AS a,
+    y.b AS _u_10
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.b
+) AS "_u_9"
+  ON "_u_9"."_u_10" = x.a
+LEFT JOIN (
+  SELECT
+    SUM(y.a) AS a,
+    y.a AS _u_12,
+    ARRAY_AGG(y.b) AS _u_13
+  FROM y
+  WHERE
+    TRUE
+    AND TRUE
+    AND TRUE
+  GROUP BY
+    y.a
+) AS "_u_11"
+  ON "_u_11"."_u_12" = x.a
+  AND "_u_11"."_u_12" = x.b
+LEFT JOIN (
+  SELECT
+    y.a AS a
+  FROM y
+  WHERE
+    TRUE
+  GROUP BY
+    y.a
+) AS "_u_14"
+  ON x.a = "_u_14".a
+WHERE
+  NOT "_u_0"."a" IS NULL
+  AND NOT "_u_1"."b" IS NULL
+  AND NOT "_u_2"."a" IS NULL
+  AND (
+    x.a = "_u_3".b
+    AND NOT "_u_3"."_u_4" IS NULL
+  )
+  AND (
+    x.a > "_u_5".b
+    AND NOT "_u_5"."_u_6" IS NULL
+  )
+  AND (
+    None = "_u_7".a
+    AND NOT "_u_7".a IS NULL
+  )
+  AND NOT (
+    x.a = "_u_8".a
+    AND NOT "_u_8".a IS NULL
+  )
+  AND (
+    ARRAY_ANY("_u_9".a, _x -> _x = x.a)
+    AND NOT "_u_9"."_u_10" IS NULL
+  )
+  AND (
+    (
+      (
+        x.a < "_u_11".a
+        AND NOT "_u_11"."_u_12" IS NULL
+      )
+      AND NOT "_u_11"."_u_12" IS NULL
+    )
+    AND ARRAY_ANY("_u_11"."_u_13", "_x" -> "_x" <> x.d)
+  )
+  AND (
+    NOT "_u_14".a IS NULL
+    AND NOT "_u_14".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
+  );
+