diff options
Diffstat (limited to 'tests/fixtures/optimizer')
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 Binary files differnew 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 Binary files differnew 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 Binary files differnew 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 Binary files differnew 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 Binary files differnew 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 Binary files differnew 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 Binary files differnew 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 Binary files differnew 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 + ); + |