diff options
Diffstat (limited to 'tests/fixtures')
26 files changed, 3800 insertions, 0 deletions
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql new file mode 100644 index 0000000..40f11a2 --- /dev/null +++ b/tests/fixtures/identity.sql @@ -0,0 +1,514 @@ +SUM(1) +SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y +1 +1.0 +1E2 +1E+2 +1E-2 +1.1E10 +1.12e-10 +-11.023E7 * 3 +(1 * 2) / (3 - 5) +((TRUE)) +'' +'''' +'x' +'\x' +"x" +"" +x +x % 1 +x < 1 +x <= 1 +x > 1 +x >= 1 +x <> 1 +x = y OR x > 1 +x & 1 +x | 1 +x ^ 1 +~x +x << 1 +x >> 1 +x >> 1 | 1 & 1 ^ 1 +x || y +1 - -1 +dec.x + y +a.filter +a.b.c +a.b.c.d +a.b.c.d.e +a.b.c.d.e[0] +a.b.c.d.e[0].f +a[0][0].b.c[1].d.e.f[1][1] +a[0].b[1] +a[0].b.c['d'] +a.b.C() +a['x'].b.C() +a.B() +a['x'].C() +int.x +map.x +x IN (-1, 1) +x IN ('a', 'a''a') +x IN ((1)) +x BETWEEN -1 AND 1 +x BETWEEN 'a' || b AND 'c' || d +NOT x IS NULL +x IS TRUE +x IS FALSE +time +zone +ARRAY<TEXT> +CURRENT_DATE +CURRENT_DATE('UTC') +CURRENT_DATE AT TIME ZONE 'UTC' +CURRENT_DATE AT TIME ZONE zone_column +CURRENT_DATE AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokio' +ARRAY() +ARRAY(1, 2) +ARRAY_CONTAINS(x, 1) +EXTRACT(x FROM y) +EXTRACT(DATE FROM y) +CONCAT_WS('-', 'a', 'b') +CONCAT_WS('-', 'a', 'b', 'c') +POSEXPLODE("x") AS ("a", "b") +POSEXPLODE("x") AS ("a", "b", "c") +STR_POSITION(x, 'a') +STR_POSITION(x, 'a', 3) +SPLIT(SPLIT(referrer, 'utm_source=')[OFFSET(1)], "&")[OFFSET(0)] +x[ORDINAL(1)][SAFE_OFFSET(2)] +x LIKE SUBSTR('abc', 1, 1) +x LIKE y +x LIKE a.y +x LIKE '%y%' +x ILIKE '%y%' +x LIKE '%y%' ESCAPE '\' +x ILIKE '%y%' ESCAPE '\' +1 AS escape +INTERVAL '1' day +INTERVAL '1' month +INTERVAL '1 day' +INTERVAL 2 months +INTERVAL 1 + 3 days +TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY) +DATETIME_DIFF(CURRENT_DATE, 1, DAY) +QUANTILE(x, 0.5) +REGEXP_REPLACE('new york', '(\w)(\w*)', x -> UPPER(x[1]) || LOWER(x[2])) +REGEXP_LIKE('new york', '.') +REGEXP_SPLIT('new york', '.') +SPLIT('new york', '.') +X((y AS z)).1 +(x AS y, y AS z) +REPLACE(1) +DATE(x) = DATE(y) +TIMESTAMP(DATE(x)) +TIMESTAMP_TRUNC(COALESCE(time_field, CURRENT_TIMESTAMP()), DAY) +COUNT(DISTINCT CASE WHEN DATE_TRUNC(DATE(time_field), isoweek) = DATE_TRUNC(DATE(time_field2), isoweek) THEN report_id ELSE NULL END) +x[y - 1] +CASE WHEN SUM(x) > 3 THEN 1 END OVER (PARTITION BY x) +SUM(ROW() OVER (PARTITION BY x)) +SUM(ROW() OVER (PARTITION BY x + 1)) +SUM(ROW() OVER (PARTITION BY x AND y)) +(ROW() OVER ()) +CASE WHEN (x > 1) THEN 1 ELSE 0 END +CASE (1) WHEN 1 THEN 1 ELSE 0 END +CASE 1 WHEN 1 THEN 1 ELSE 0 END +x AT TIME ZONE 'UTC' +CAST('2025-11-20 00:00:00+00' AS TIMESTAMP) AT TIME ZONE 'Africa/Cairo' +SET x = 1 +SET -v +ADD JAR s3://bucket +ADD JARS s3://bucket, c +ADD FILE s3://file +ADD FILES s3://file, s3://a +ADD ARCHIVE s3://file +ADD ARCHIVES s3://file, s3://a +BEGIN IMMEDIATE TRANSACTION +COMMIT +USE db +NOT 1 +NOT NOT 1 +SELECT * FROM test +SELECT *, 1 FROM test +SELECT * FROM a.b +SELECT * FROM a.b.c +SELECT * FROM table +SELECT 1 +SELECT 1 FROM test +SELECT * FROM a, b, (SELECT 1) AS c +SELECT a FROM test +SELECT 1 AS filter +SELECT SUM(x) AS filter +SELECT 1 AS range FROM test +SELECT 1 AS count FROM test +SELECT 1 AS comment FROM test +SELECT 1 AS numeric FROM test +SELECT 1 AS number FROM test +SELECT t.count +SELECT DISTINCT x FROM test +SELECT DISTINCT x, y FROM test +SELECT DISTINCT TIMESTAMP_TRUNC(time_field, MONTH) AS time_value FROM "table" +SELECT DISTINCT ON (x) x, y FROM z +SELECT DISTINCT ON (x, y + 1) * FROM z +SELECT DISTINCT ON (x.y) * FROM z +SELECT top.x +SELECT TIMESTAMP(DATE_TRUNC(DATE(time_field), MONTH)) AS time_value FROM "table" +SELECT GREATEST((3 + 1), LEAST(3, 4)) +SELECT TRANSFORM(a, b -> b) AS x +SELECT AGGREGATE(a, (a, b) -> a + b) AS x +SELECT SUM(DISTINCT x) +SELECT SUM(x IGNORE NULLS) AS x +SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x +SELECT ARRAY_AGG(STRUCT(x, x AS y) ORDER BY z DESC) AS x +SELECT LAST_VALUE(x IGNORE NULLS) OVER y AS x +SELECT LAG(x) OVER (ORDER BY y) AS x +SELECT LEAD(a) OVER (ORDER BY b) AS a +SELECT LEAD(a, 1) OVER (PARTITION BY a ORDER BY a) AS x +SELECT LEAD(a, 1, b) OVER (PARTITION BY a ORDER BY a) AS x +SELECT X((a, b) -> a + b, z -> z) AS x +SELECT X(a -> "a" + ("z" - 1)) +SELECT EXISTS(ARRAY(2, 3), x -> x % 2 = 0) +SELECT test.* FROM test +SELECT a AS b FROM test +SELECT "a"."b" FROM "a" +SELECT "a".b FROM a +SELECT a.b FROM "a" +SELECT a.b FROM a +SELECT '"hi' AS x FROM x +SELECT 1 AS "|sum" FROM x +SELECT '\"hi' AS x FROM x +SELECT 1 AS b FROM test +SELECT 1 AS "b" FROM test +SELECT 1 + 1 FROM test +SELECT 1 - 1 FROM test +SELECT 1 * 1 FROM test +SELECT 1 % 1 FROM test +SELECT 1 / 1 FROM test +SELECT 1 < 2 FROM test +SELECT 1 <= 2 FROM test +SELECT 1 > 2 FROM test +SELECT 1 >= 2 FROM test +SELECT 1 <> 2 FROM test +SELECT JSON_EXTRACT(x, '$.name') +SELECT JSON_EXTRACT_SCALAR(x, '$.name') +SELECT x LIKE '%x%' FROM test +SELECT * FROM test LIMIT 100 +SELECT * FROM test LIMIT 100 OFFSET 200 +SELECT * FROM test FETCH FIRST 1 ROWS ONLY +SELECT * FROM test FETCH NEXT 1 ROWS ONLY +SELECT (1 > 2) AS x FROM test +SELECT NOT (1 > 2) FROM test +SELECT 1 + 2 AS x FROM test +SELECT a, b, 1 < 1 FROM test +SELECT a FROM test WHERE NOT FALSE +SELECT a FROM test WHERE a = 1 +SELECT a FROM test WHERE a = 1 AND b = 2 +SELECT a FROM test WHERE a IN (SELECT b FROM z) +SELECT a FROM test WHERE a IN ((SELECT 1), 2) +SELECT * FROM x WHERE y IN ((SELECT 1) EXCEPT (SELECT 2)) +SELECT * FROM x WHERE y IN (SELECT 1 UNION SELECT 2) +SELECT * FROM x WHERE y IN ((SELECT 1 UNION SELECT 2)) +SELECT * FROM x WHERE y IN (WITH z AS (SELECT 1) SELECT * FROM z) +SELECT a FROM test WHERE (a > 1) +SELECT a FROM test WHERE a > (SELECT 1 FROM x GROUP BY y) +SELECT a FROM test WHERE EXISTS(SELECT 1) +SELECT a FROM test WHERE EXISTS(SELECT * FROM x UNION SELECT * FROM Y) OR TRUE +SELECT a FROM test WHERE TRUE OR NOT EXISTS(SELECT * FROM x) +SELECT a AS any, b AS some, c AS all, d AS exists FROM test WHERE a = ANY (SELECT 1) +SELECT a FROM test WHERE a > ALL (SELECT 1) +SELECT a FROM test WHERE (a, b) IN (SELECT 1, 2) +SELECT a FROM test ORDER BY a +SELECT a FROM test ORDER BY a, b +SELECT x FROM tests ORDER BY a DESC, b DESC, c +SELECT a FROM test ORDER BY a > 1 +SELECT * FROM test ORDER BY DATE DESC, TIMESTAMP DESC +SELECT * FROM test DISTRIBUTE BY y SORT BY x DESC ORDER BY l +SELECT * FROM test CLUSTER BY y +SELECT * FROM test CLUSTER BY y +SELECT * FROM test WHERE RAND() <= 0.1 DISTRIBUTE BY RAND() SORT BY RAND() +SELECT a, b FROM test GROUP BY 1 +SELECT a, b FROM test GROUP BY a +SELECT a, b FROM test WHERE a = 1 GROUP BY a HAVING a = 2 +SELECT a, b FROM test WHERE a = 1 GROUP BY a HAVING a = 2 ORDER BY a +SELECT a, b FROM test WHERE a = 1 GROUP BY CASE 1 WHEN 1 THEN 1 END +SELECT a FROM test GROUP BY GROUPING SETS (()) +SELECT a FROM test GROUP BY GROUPING SETS (x, ()) +SELECT a FROM test GROUP BY GROUPING SETS (x, (x, y), (x, y, z), q) +SELECT a FROM test GROUP BY CUBE (x) +SELECT a FROM test GROUP BY ROLLUP (x) +SELECT a FROM test GROUP BY CUBE (x) ROLLUP (x, y, z) +SELECT CASE WHEN a < b THEN 1 WHEN a < c THEN 2 ELSE 3 END FROM test +SELECT CASE 1 WHEN 1 THEN 1 ELSE 2 END +SELECT CASE 1 WHEN 1 THEN MAP('a', 'b') ELSE MAP('b', 'c') END['a'] +SELECT CASE 1 + 2 WHEN 1 THEN 1 ELSE 2 END +SELECT CASE TEST(1) + x[0] WHEN 1 THEN 1 ELSE 2 END +SELECT CASE x[0] WHEN 1 THEN 1 ELSE 2 END +SELECT CASE a.b WHEN 1 THEN 1 ELSE 2 END +SELECT CASE CASE x > 1 WHEN TRUE THEN 1 END WHEN 1 THEN 1 ELSE 2 END +SELECT a FROM (SELECT a FROM test) AS x +SELECT a FROM (SELECT a FROM (SELECT a FROM test) AS y) AS x +SELECT a FROM test WHERE a IN (1, 2, 3) OR b BETWEEN 1 AND 4 +SELECT a FROM test AS x TABLESAMPLE(BUCKET 1 OUT OF 5) +SELECT a FROM test TABLESAMPLE(BUCKET 1 OUT OF 5) +SELECT a FROM test TABLESAMPLE(BUCKET 1 OUT OF 5 ON x) +SELECT a FROM test TABLESAMPLE(BUCKET 1 OUT OF 5 ON RAND()) +SELECT a FROM test TABLESAMPLE(0.1 PERCENT) +SELECT a FROM test TABLESAMPLE(100) +SELECT a FROM test TABLESAMPLE(100 ROWS) +SELECT a FROM test TABLESAMPLE BERNOULLI (50) +SELECT a FROM test TABLESAMPLE SYSTEM (75) +SELECT ABS(a) FROM test +SELECT AVG(a) FROM test +SELECT CEIL(a) FROM test +SELECT COUNT(a) FROM test +SELECT COUNT(1) FROM test +SELECT COUNT(*) FROM test +SELECT COUNT(DISTINCT a) FROM test +SELECT EXP(a) FROM test +SELECT FLOOR(a) FROM test +SELECT FIRST(a) FROM test +SELECT GREATEST(a, b, c) FROM test +SELECT LAST(a) FROM test +SELECT LN(a) FROM test +SELECT LOG10(a) FROM test +SELECT MAX(a) FROM test +SELECT MIN(a) FROM test +SELECT POWER(a, 2) FROM test +SELECT QUANTILE(a, 0.95) FROM test +SELECT ROUND(a) FROM test +SELECT ROUND(a, 2) FROM test +SELECT SUM(a) FROM test +SELECT SQRT(a) FROM test +SELECT STDDEV(a) FROM test +SELECT STDDEV_POP(a) FROM test +SELECT STDDEV_SAMP(a) FROM test +SELECT VARIANCE(a) FROM test +SELECT VARIANCE_POP(a) FROM test +SELECT CAST(a AS INT) FROM test +SELECT CAST(a AS DATETIME) FROM test +SELECT CAST(a AS VARCHAR) FROM test +SELECT CAST(a < 1 AS INT) FROM test +SELECT CAST(a IS NULL AS INT) FROM test +SELECT COUNT(CAST(1 < 2 AS INT)) FROM test +SELECT COUNT(CASE WHEN CAST(1 < 2 AS BOOLEAN) THEN 1 END) FROM test +SELECT CAST(a AS DECIMAL) FROM test +SELECT CAST(a AS DECIMAL(1)) FROM test +SELECT CAST(a AS DECIMAL(1, 2)) FROM test +SELECT CAST(a AS MAP<INT, INT>) FROM test +SELECT CAST(a AS TIMESTAMP) FROM test +SELECT CAST(a AS DATE) FROM test +SELECT CAST(a AS ARRAY<INT>) FROM test +SELECT TRY_CAST(a AS INT) FROM test +SELECT COALESCE(a, b, c) FROM test +SELECT IFNULL(a, b) FROM test +SELECT ANY_VALUE(a) FROM test +SELECT 1 FROM a JOIN b ON a.x = b.x +SELECT 1 FROM a JOIN b AS c ON a.x = b.x +SELECT 1 FROM a INNER JOIN b ON a.x = b.x +SELECT 1 FROM a LEFT JOIN b ON a.x = b.x +SELECT 1 FROM a RIGHT JOIN b ON a.x = b.x +SELECT 1 FROM a CROSS JOIN b ON a.x = b.x +SELECT 1 FROM a JOIN b USING (x) +SELECT 1 FROM a JOIN b USING (x, y, z) +SELECT 1 FROM a JOIN (SELECT a FROM c) AS b ON a.x = b.x AND a.x < 2 +SELECT 1 FROM a UNION SELECT 2 FROM b +SELECT 1 FROM a UNION ALL SELECT 2 FROM b +SELECT 1 FROM a JOIN b ON a.foo = b.bar JOIN c ON a.foo = c.bar +SELECT 1 FROM a LEFT JOIN b ON a.foo = b.bar JOIN c ON a.foo = c.bar +SELECT 1 FROM a LEFT INNER JOIN b ON a.foo = b.bar +SELECT 1 FROM a LEFT OUTER JOIN b ON a.foo = b.bar +SELECT 1 FROM a OUTER JOIN b ON a.foo = b.bar +SELECT 1 FROM a FULL JOIN b ON a.foo = b.bar +SELECT 1 UNION ALL SELECT 2 +SELECT 1 EXCEPT SELECT 2 +SELECT 1 EXCEPT SELECT 2 +SELECT 1 INTERSECT SELECT 2 +SELECT 1 INTERSECT SELECT 2 +SELECT 1 AS delete, 2 AS alter +SELECT * FROM (x) +SELECT * FROM ((x)) +SELECT * FROM ((SELECT 1)) +SELECT * FROM (SELECT 1) AS x +SELECT * FROM (SELECT 1 UNION SELECT 2) AS x +SELECT * FROM (SELECT 1 UNION ALL SELECT 2) AS x +SELECT * FROM (SELECT 1 UNION ALL SELECT 2) +SELECT * FROM ((SELECT 1) AS a UNION ALL (SELECT 2) AS b) +SELECT * FROM ((SELECT 1) AS a(b)) +SELECT * FROM x AS y(a, b) +SELECT * EXCEPT (a, b) +SELECT * REPLACE (a AS b, b AS C) +SELECT * REPLACE (a + 1 AS b, b AS C) +SELECT * EXCEPT (a, b) REPLACE (a AS b, b AS C) +SELECT a.* EXCEPT (a, b), b.* REPLACE (a AS b, b AS C) +SELECT zoo, animals FROM (VALUES ('oakland', ARRAY('a', 'b')), ('sf', ARRAY('b', 'c'))) AS t(zoo, animals) +WITH a AS (SELECT 1) SELECT 1 UNION ALL SELECT 2 +WITH a AS (SELECT 1) SELECT 1 UNION SELECT 2 +WITH a AS (SELECT 1) SELECT 1 INTERSECT SELECT 2 +WITH a AS (SELECT 1) SELECT 1 EXCEPT SELECT 2 +WITH a AS (SELECT 1) SELECT 1 EXCEPT SELECT 2 +(SELECT 1) UNION (SELECT 2) +(SELECT 1) UNION SELECT 2 +SELECT 1 UNION (SELECT 2) +(SELECT 1) ORDER BY x LIMIT 1 OFFSET 1 +(SELECT 1 UNION SELECT 2) UNION (SELECT 2 UNION ALL SELECT 3) +(SELECT 1 UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1 +(SELECT 1 UNION SELECT 2) CLUSTER BY y DESC +(SELECT 1 UNION SELECT 2) SORT BY z +(SELECT 1 UNION SELECT 2) DISTRIBUTE BY z +(SELECT 1 UNION SELECT 2) DISTRIBUTE BY z SORT BY x +SELECT 1 UNION (SELECT 2) ORDER BY x +(SELECT 1) UNION SELECT 2 ORDER BY x +SELECT * FROM (((SELECT 1) UNION SELECT 2) ORDER BY x LIMIT 1 OFFSET 1) +SELECT * FROM ((SELECT 1 AS x) CROSS JOIN (SELECT 2 AS y)) AS z +((SELECT 1) EXCEPT (SELECT 2)) +VALUES (1) UNION SELECT * FROM x +WITH a AS (SELECT 1) SELECT a.* FROM a +WITH a AS (SELECT 1), b AS (SELECT 2) SELECT a.*, b.* FROM a CROSS JOIN b +WITH a AS (WITH b AS (SELECT 1 AS x) SELECT b.x FROM b) SELECT a.x FROM a +WITH RECURSIVE T(n) AS (VALUES (1) UNION ALL SELECT n + 1 FROM t WHERE n < 100) SELECT SUM(n) FROM t +WITH RECURSIVE T(n, m) AS (VALUES (1, 2) UNION ALL SELECT n + 1, n + 2 FROM t) SELECT SUM(n) FROM t +WITH baz AS (SELECT 1 AS col) UPDATE bar SET cid = baz.col1 FROM baz +SELECT * FROM (WITH y AS (SELECT 1 AS z) SELECT z FROM y) AS x +SELECT RANK() OVER () FROM x +SELECT RANK() OVER () AS y FROM x +SELECT RANK() OVER (PARTITION BY a) FROM x +SELECT RANK() OVER (PARTITION BY a, b) FROM x +SELECT RANK() OVER (ORDER BY a) FROM x +SELECT RANK() OVER (ORDER BY a, b) FROM x +SELECT RANK() OVER (PARTITION BY a ORDER BY a) FROM x +SELECT RANK() OVER (PARTITION BY a, b ORDER BY a, b DESC) FROM x +SELECT SUM(x) OVER (PARTITION BY a) AS y FROM x +SELECT SUM(x) OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +SELECT SUM(x) OVER (PARTITION BY a ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +SELECT SUM(x) OVER (PARTITION BY a ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +SELECT SUM(x) OVER (PARTITION BY a ORDER BY b RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW) +SELECT SUM(x) OVER (PARTITION BY a ORDER BY b RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '2' DAYS FOLLOWING) +SELECT SUM(x) OVER (PARTITION BY a ORDER BY b RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND UNBOUNDED FOLLOWING) +SELECT SUM(x) OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING AND PRECEDING) +SELECT SUM(x) OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) +SELECT SUM(x) OVER (PARTITION BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) +SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 AND 3) +SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND 3) +SELECT SUM(x) OVER (PARTITION BY a RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) +SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x) AS y +SELECT LISTAGG(x) WITHIN GROUP (ORDER BY x DESC) +SELECT SUM(x) FILTER(WHERE x > 1) +SELECT SUM(x) FILTER(WHERE x > 1) OVER (ORDER BY y) +SELECT COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +SELECT a['1'], b[0], x.c[0], "x".d['1'] FROM x +SELECT ARRAY(1, 2, 3) FROM x +SELECT ARRAY(ARRAY(1), ARRAY(2)) FROM x +SELECT MAP[ARRAY(1), ARRAY(2)] FROM x +SELECT MAP(ARRAY(1), ARRAY(2)) FROM x +SELECT MAX(ARRAY(1, 2, 3)) FROM x +SELECT ARRAY(ARRAY(0))[0][0] FROM x +SELECT MAP[ARRAY('x'), ARRAY(0)]['x'] FROM x +SELECT student, score FROM tests LATERAL VIEW EXPLODE(scores) +SELECT student, score FROM tests LATERAL VIEW EXPLODE(scores) AS score +SELECT student, score FROM tests LATERAL VIEW EXPLODE(scores) t AS score +SELECT student, score FROM tests LATERAL VIEW EXPLODE(scores) t AS score, name +SELECT student, score FROM tests LATERAL VIEW OUTER EXPLODE(scores) t AS score, name +SELECT tf.* FROM (SELECT 0) AS t LATERAL VIEW STACK(1, 2) tf +SELECT tf.* FROM (SELECT 0) AS t LATERAL VIEW STACK(1, 2) tf AS col0, col1, col2 +SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t(score) +SELECT student, score FROM tests CROSS JOIN UNNEST(scores) AS t(a, b) +SELECT student, score FROM tests CROSS JOIN UNNEST(scores) WITH ORDINALITY AS t(a, b) +SELECT student, score FROM tests CROSS JOIN UNNEST(x.scores) AS t(score) +SELECT student, score FROM tests CROSS JOIN UNNEST(ARRAY(x.scores)) AS t(score) +CREATE TABLE a.b AS SELECT 1 +CREATE TABLE a.b AS SELECT a FROM a.c +CREATE TABLE IF NOT EXISTS x AS SELECT a FROM d +CREATE TEMPORARY TABLE x AS SELECT a FROM d +CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d +CREATE VIEW x AS SELECT a FROM b +CREATE VIEW IF NOT EXISTS x AS SELECT a FROM b +CREATE OR REPLACE VIEW x AS SELECT * +CREATE OR REPLACE TEMPORARY VIEW x AS SELECT * +CREATE TEMPORARY VIEW x AS SELECT a FROM d +CREATE TEMPORARY VIEW IF NOT EXISTS x AS SELECT a FROM d +CREATE TEMPORARY VIEW x AS WITH y AS (SELECT 1) SELECT * FROM y +CREATE TABLE z (a INT, b VARCHAR, c VARCHAR(100), d DECIMAL(5, 3)) +CREATE TABLE z (a ARRAY<TEXT>, b MAP<TEXT, DOUBLE>, c DECIMAL(5, 3)) +CREATE TABLE z (a INT, b VARCHAR COMMENT 'z', c VARCHAR(100) COMMENT 'z', d DECIMAL(5, 3)) +CREATE TABLE z (a INT(11) DEFAULT UUID()) +CREATE TABLE z (a INT(11) DEFAULT NULL COMMENT '客户id') +CREATE TABLE z (a INT(11) NOT NULL DEFAULT 1) +CREATE TABLE z (a INT(11) NOT NULL COLLATE utf8_bin AUTO_INCREMENT) +CREATE TABLE z (a INT, PRIMARY KEY(a)) +CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x' +CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x' +CREATE TABLE z (a INT DEFAULT NULL, PRIMARY KEY(a)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x' +CREATE TABLE z WITH (FORMAT='parquet') AS SELECT 1 +CREATE TABLE z WITH (FORMAT='ORC', x = '2') AS SELECT 1 +CREATE TABLE z WITH (TABLE_FORMAT='iceberg', FORMAT='parquet') AS SELECT 1 +CREATE TABLE z WITH (TABLE_FORMAT='iceberg', FORMAT='ORC', x = '2') AS SELECT 1 +CREATE TABLE z (z INT) WITH (PARTITIONED_BY=(x INT, y INT)) +CREATE TABLE z (z INT) WITH (PARTITIONED_BY=(x INT)) AS SELECT 1 +CREATE TABLE z AS (WITH cte AS (SELECT 1) SELECT * FROM cte) +CREATE TABLE z AS ((WITH cte AS (SELECT 1) SELECT * FROM cte)) +CREATE TABLE z (a INT UNIQUE) +CREATE TABLE z (a INT AUTO_INCREMENT) +CREATE TABLE z (a INT UNIQUE AUTO_INCREMENT) +CREATE TEMPORARY FUNCTION f +CREATE TEMPORARY FUNCTION f AS 'g' +CREATE FUNCTION f +CREATE FUNCTION f AS 'g' +CREATE INDEX abc ON t (a) +CREATE INDEX abc ON t (a, b, b) +CREATE UNIQUE INDEX abc ON t (a, b, b) +CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b) +CACHE TABLE x +CACHE LAZY TABLE x +CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') +CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS SELECT 1 +CACHE LAZY TABLE x OPTIONS('storageLevel' = 'value') AS WITH a AS (SELECT 1) SELECT a.* FROM a +CACHE LAZY TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a +CACHE TABLE x AS WITH a AS (SELECT 1) SELECT a.* FROM a +CALL catalog.system.iceberg_procedure_name(named_arg_1 => 'arg_1', named_arg_2 => 'arg_2') +INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y +INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD') SELECT x FROM y +INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y +INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD', hour='hh') SELECT x FROM y +ALTER TYPE electronic_mail RENAME TO email +ANALYZE a.y +DELETE FROM x WHERE y > 1 +DELETE FROM y +DROP TABLE a +DROP TABLE a.b +DROP TABLE IF EXISTS a +DROP TABLE IF EXISTS a.b +DROP VIEW a +DROP VIEW a.b +DROP VIEW IF EXISTS a +DROP VIEW IF EXISTS a.b +SHOW TABLES +EXPLAIN SELECT * FROM x +INSERT INTO x SELECT * FROM y +INSERT INTO x (SELECT * FROM y) +INSERT INTO x WITH y AS (SELECT 1) SELECT * FROM y +INSERT INTO x.z IF EXISTS SELECT * FROM y +INSERT INTO x VALUES (1, 'a', 2.0) +INSERT INTO x VALUES (1, 'a', 2.0), (1, 'a', 3.0), (X(), y[1], z.x) +INSERT INTO y (a, b, c) SELECT a, b, c FROM x +INSERT OVERWRITE TABLE x IF EXISTS SELECT * FROM y +INSERT OVERWRITE TABLE a.b IF EXISTS SELECT * FROM y +SELECT 1 FROM PARQUET_SCAN('/x/y/*') AS y +UNCACHE TABLE x +UNCACHE TABLE IF EXISTS x +UPDATE tbl_name SET foo = 123 +UPDATE tbl_name SET foo = 123, bar = 345 +UPDATE db.tbl_name SET foo = 123 WHERE tbl_name.bar = 234 +UPDATE db.tbl_name SET foo = 123, foo_1 = 234 WHERE tbl_name.bar = 234 +TRUNCATE TABLE x +OPTIMIZE TABLE y +WITH a AS (SELECT 1) INSERT INTO b SELECT * FROM a +WITH a AS (SELECT * FROM b) UPDATE a SET col = 1 +WITH a AS (SELECT * FROM b) CREATE TABLE b AS SELECT * FROM a +WITH a AS (SELECT * FROM b) DELETE FROM a +WITH a AS (SELECT * FROM b) CACHE TABLE a +SELECT ? AS ? FROM x WHERE b BETWEEN ? AND ? GROUP BY ?, 1 LIMIT ? +WITH a AS ((SELECT b.foo AS foo, b.bar AS bar FROM b) UNION ALL (SELECT c.foo AS foo, c.bar AS bar FROM c)) SELECT * FROM a +WITH a AS ((SELECT 1 AS b) UNION ALL (SELECT 1 AS b)) SELECT * FROM a +SELECT (WITH x AS (SELECT 1 AS y) SELECT * FROM x) AS z 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 + ); + diff --git a/tests/fixtures/partial.sql b/tests/fixtures/partial.sql new file mode 100644 index 0000000..c6be364 --- /dev/null +++ b/tests/fixtures/partial.sql @@ -0,0 +1,8 @@ +SELECT a FROM +SELECT a FROM x WHERE +SELECT a + +a * +SELECT a FROM x JOIN +SELECT a FROM x GROUP BY +WITH a AS (SELECT 1), b AS (SELECT 2) +SELECT FROM x diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql new file mode 100644 index 0000000..5ed74f4 --- /dev/null +++ b/tests/fixtures/pretty.sql @@ -0,0 +1,285 @@ +SELECT * FROM test; +SELECT + * +FROM test; + +WITH a AS ((SELECT 1 AS b) UNION ALL (SELECT 2 AS b)) SELECT * FROM a; +WITH a AS ( + ( + SELECT + 1 AS b + ) + UNION ALL + ( + SELECT + 2 AS b + ) +) +SELECT + * +FROM a; + +WITH cte1 AS ( + SELECT a, z and e AS b + FROM cte + WHERE x IN (1, 2, 3) AND z < -1 OR z > 1 AND w = 'AND' +), cte2 AS ( + SELECT RANK() OVER (PARTITION BY a, b ORDER BY x DESC) a, b + FROM cte + CROSS JOIN ( + SELECT 1 + UNION ALL + SELECT 2 + UNION ALL + SELECT CASE x AND 1 + 1 = 2 + WHEN TRUE THEN 1 AND 4 + 3 AND Z + WHEN x and y THEN 2 + ELSE 3 AND 4 AND g END + UNION ALL + SELECT 1 + FROM (SELECT 1) AS x, y, (SELECT 2) z + UNION ALL + SELECT MAX(COALESCE(x AND y, a and b and c, d and e)), FOO(CASE WHEN a and b THEN c and d ELSE 3 END) + GROUP BY x, GROUPING SETS (a, (b, c)) CUBE(y, z) + ) x +) +SELECT a, b c FROM ( + SELECT a w, 1 + 1 AS c + FROM foo + WHERE w IN (SELECT z FROM q) + GROUP BY a, b +) x +LEFT JOIN ( + SELECT a, b + FROM (SELECT * FROM bar WHERE (c > 1 AND d > 1) OR e > 1 GROUP BY a HAVING a > 1 LIMIT 10) z +) y ON x.a = y.b AND x.a > 1 OR (x.c = y.d OR x.c = y.e); +WITH cte1 AS ( + SELECT + a, + z + AND e AS b + FROM cte + WHERE + x IN (1, 2, 3) + AND z < -1 + OR z > 1 + AND w = 'AND' +), cte2 AS ( + SELECT + RANK() OVER (PARTITION BY a, b ORDER BY x DESC) AS a, + b + FROM cte + CROSS JOIN ( + SELECT + 1 + UNION ALL + SELECT + 2 + UNION ALL + SELECT + CASE x + AND 1 + 1 = 2 + WHEN TRUE + THEN 1 + AND 4 + 3 + AND Z + WHEN x + AND y + THEN 2 + ELSE 3 + AND 4 + AND g + END + UNION ALL + SELECT + 1 + FROM ( + SELECT + 1 + ) AS x, y, ( + SELECT + 2 + ) AS z + UNION ALL + SELECT + MAX(COALESCE(x + AND y, a + AND b + AND c, d + AND e)), + FOO(CASE + WHEN a + AND b + THEN c + AND d + ELSE 3 + END) + GROUP BY + x + GROUPING SETS ( + a, + (b, c) + ) + CUBE ( + y, + z + ) + ) AS x +) +SELECT + a, + b AS c +FROM ( + SELECT + a AS w, + 1 + 1 AS c + FROM foo + WHERE + w IN ( + SELECT + z + FROM q + ) + GROUP BY + a, + b +) AS x +LEFT JOIN ( + SELECT + a, + b + FROM ( + SELECT + * + FROM bar + WHERE + ( + c > 1 + AND d > 1 + ) + OR e > 1 + GROUP BY + a + HAVING + a > 1 + LIMIT 10 + ) AS z +) AS y + ON x.a = y.b + AND x.a > 1 + OR ( + x.c = y.d + OR x.c = y.e + ); + +SELECT myCol1, myCol2 FROM baseTable LATERAL VIEW OUTER explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(col2) myTable2 AS myCol2 +where a > 1 and b > 2 or c > 3; + +SELECT + myCol1, + myCol2 +FROM baseTable +LATERAL VIEW OUTER +EXPLODE(col1) myTable1 AS myCol1 +LATERAL VIEW +EXPLODE(col2) myTable2 AS myCol2 +WHERE + a > 1 + AND b > 2 + OR c > 3; + +SELECT * FROM (WITH y AS ( SELECT 1 AS z) SELECT z from y) x; +SELECT + * +FROM ( + WITH y AS ( + SELECT + 1 AS z + ) + SELECT + z + FROM y +) AS x; + +INSERT OVERWRITE TABLE x VALUES (1, 2.0, '3.0'), (4, 5.0, '6.0'); +INSERT OVERWRITE TABLE x VALUES + (1, 2.0, '3.0'), + (4, 5.0, '6.0'); + +WITH regional_sales AS ( + SELECT region, SUM(amount) AS total_sales + FROM orders + GROUP BY region + ), top_regions AS ( + SELECT region + FROM regional_sales + WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) +) +SELECT region, +product, +SUM(quantity) AS product_units, +SUM(amount) AS product_sales +FROM orders +WHERE region IN (SELECT region FROM top_regions) +GROUP BY region, product; +WITH regional_sales AS ( + SELECT + region, + SUM(amount) AS total_sales + FROM orders + GROUP BY + region +), top_regions AS ( + SELECT + region + FROM regional_sales + WHERE + total_sales > ( + SELECT + SUM(total_sales) / 10 + FROM regional_sales + ) +) +SELECT + region, + product, + SUM(quantity) AS product_units, + SUM(amount) AS product_sales +FROM orders +WHERE + region IN ( + SELECT + region + FROM top_regions + ) +GROUP BY + region, + product; + +CREATE TABLE "t_customer_account" ( "id" int, "customer_id" int, "bank" varchar(100), "account_no" varchar(100)); +CREATE TABLE "t_customer_account" ( + "id" INT, + "customer_id" INT, + "bank" VARCHAR(100), + "account_no" VARCHAR(100) +); + +CREATE TABLE "t_customer_account" ( + "id" int(11) NOT NULL AUTO_INCREMENT, + "customer_id" int(11) DEFAULT NULL COMMENT '客户id', + "bank" varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '行别', + "account_no" varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '账号', + PRIMARY KEY ("id") +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='客户账户表'; +CREATE TABLE "t_customer_account" ( + "id" INT(11) NOT NULL AUTO_INCREMENT, + "customer_id" INT(11) DEFAULT NULL COMMENT '客户id', + "bank" VARCHAR(100) COLLATE utf8_bin DEFAULT NULL COMMENT '行别', + "account_no" VARCHAR(100) COLLATE utf8_bin DEFAULT NULL COMMENT '账号', + PRIMARY KEY("id") +) +ENGINE=InnoDB +AUTO_INCREMENT=1 +DEFAULT CHARACTER SET=utf8 +COLLATE=utf8_bin +COMMENT='客户账户表'; |