summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--tests/dialects/test_bigquery.py3
-rw-r--r--tests/dialects/test_dialect.py2
-rw-r--r--tests/dialects/test_duckdb.py17
-rw-r--r--tests/dialects/test_hive.py18
-rw-r--r--tests/dialects/test_mysql.py1
-rw-r--r--tests/dialects/test_postgres.py41
-rw-r--r--tests/dialects/test_presto.py14
-rw-r--r--tests/dialects/test_spark.py8
-rw-r--r--tests/dialects/test_tsql.py3
-rw-r--r--tests/fixtures/identity.sql17
-rw-r--r--tests/fixtures/optimizer/eliminate_ctes.sql48
-rw-r--r--tests/fixtures/optimizer/eliminate_joins.sql317
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql100
-rw-r--r--tests/fixtures/optimizer/optimizer.sql4
-rw-r--r--tests/fixtures/optimizer/pushdown_predicates.sql3
-rw-r--r--tests/fixtures/optimizer/tpc-h/tpc-h.sql92
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql32
-rw-r--r--tests/fixtures/pretty.sql103
-rw-r--r--tests/test_build.py5
-rw-r--r--tests/test_expressions.py26
-rw-r--r--tests/test_optimizer.py14
21 files changed, 745 insertions, 123 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 8921924..14fea9d 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -164,7 +164,7 @@ class TestBigQuery(Validator):
"CREATE TABLE db.example_table (col_a struct<struct_col_a:int, struct_col_b:string>)",
write={
"bigquery": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT64, struct_col_b STRING>)",
- "duckdb": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT, struct_col_b TEXT>)",
+ "duckdb": "CREATE TABLE db.example_table (col_a STRUCT(struct_col_a INT, struct_col_b TEXT))",
"presto": "CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b VARCHAR))",
"hive": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT, struct_col_b STRING>)",
"spark": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a: INT, struct_col_b: STRING>)",
@@ -174,6 +174,7 @@ class TestBigQuery(Validator):
"CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT64, struct_col_b STRUCT<nested_col_a STRING, nested_col_b STRING>>)",
write={
"bigquery": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT64, struct_col_b STRUCT<nested_col_a STRING, nested_col_b STRING>>)",
+ "duckdb": "CREATE TABLE db.example_table (col_a STRUCT(struct_col_a BIGINT, struct_col_b STRUCT(nested_col_a TEXT, nested_col_b TEXT)))",
"presto": "CREATE TABLE db.example_table (col_a ROW(struct_col_a BIGINT, struct_col_b ROW(nested_col_a VARCHAR, nested_col_b VARCHAR)))",
"hive": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a BIGINT, struct_col_b STRUCT<nested_col_a STRING, nested_col_b STRING>>)",
"spark": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a: LONG, struct_col_b: STRUCT<nested_col_a: STRING, nested_col_b: STRING>>)",
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 53edb42..a1e1262 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1056,6 +1056,7 @@ class TestDialect(Validator):
self.validate_all(
"CREATE TABLE t (c CHAR, nc NCHAR, v1 VARCHAR, v2 VARCHAR2, nv NVARCHAR, nv2 NVARCHAR2)",
write={
+ "duckdb": "CREATE TABLE t (c CHAR, nc CHAR, v1 TEXT, v2 TEXT, nv TEXT, nv2 TEXT)",
"hive": "CREATE TABLE t (c CHAR, nc CHAR, v1 STRING, v2 STRING, nv STRING, nv2 STRING)",
"oracle": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR2, v2 VARCHAR2, nv NVARCHAR2, nv2 NVARCHAR2)",
"postgres": "CREATE TABLE t (c CHAR, nc CHAR, v1 VARCHAR, v2 VARCHAR, nv VARCHAR, nv2 VARCHAR)",
@@ -1096,6 +1097,7 @@ class TestDialect(Validator):
self.validate_all(
"CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 TEXT, c2 TEXT(1024))",
write={
+ "duckdb": "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 TEXT, c2 TEXT(1024))",
"hive": "CREATE TABLE t (b1 BINARY, b2 BINARY(1024), c1 STRING, c2 STRING(1024))",
"oracle": "CREATE TABLE t (b1 BLOB, b2 BLOB(1024), c1 CLOB, c2 CLOB(1024))",
"postgres": "CREATE TABLE t (b1 BYTEA, b2 BYTEA(1024), c1 TEXT, c2 TEXT(1024))",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 96e51df..298b3e9 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -66,6 +66,23 @@ class TestDuckDB(Validator):
def test_duckdb(self):
self.validate_all(
+ "CREATE TABLE IF NOT EXISTS table (cola INT, colb STRING) USING ICEBERG PARTITIONED BY (colb)",
+ write={
+ "duckdb": "CREATE TABLE IF NOT EXISTS table (cola INT, colb TEXT)",
+ },
+ )
+
+ self.validate_all(
+ "COL::BIGINT[]",
+ write={
+ "duckdb": "CAST(COL AS BIGINT[])",
+ "presto": "CAST(COL AS ARRAY(BIGINT))",
+ "hive": "CAST(COL AS ARRAY<BIGINT>)",
+ "spark": "CAST(COL AS ARRAY<LONG>)",
+ },
+ )
+
+ self.validate_all(
"LIST_VALUE(0, 1, 2)",
read={
"spark": "ARRAY(0, 1, 2)",
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index acb3be9..69c7630 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -127,21 +127,23 @@ class TestHive(Validator):
def test_ddl(self):
self.validate_all(
- "CREATE TABLE test STORED AS parquet TBLPROPERTIES ('x'='1', 'Z'='2') AS SELECT 1",
- write={
- "presto": "CREATE TABLE test WITH (FORMAT='parquet', x='1', Z='2') AS SELECT 1",
- "hive": "CREATE TABLE test STORED AS PARQUET TBLPROPERTIES ('x'='1', 'Z'='2') AS SELECT 1",
- "spark": "CREATE TABLE test USING PARQUET TBLPROPERTIES ('x'='1', 'Z'='2') AS SELECT 1",
- },
- )
- self.validate_all(
"CREATE TABLE x (w STRING) PARTITIONED BY (y INT, z INT)",
write={
+ "duckdb": "CREATE TABLE x (w TEXT)", # Partition columns should exist in table
"presto": "CREATE TABLE x (w VARCHAR, y INTEGER, z INTEGER) WITH (PARTITIONED_BY=ARRAY['y', 'z'])",
"hive": "CREATE TABLE x (w STRING) PARTITIONED BY (y INT, z INT)",
"spark": "CREATE TABLE x (w STRING) PARTITIONED BY (y INT, z INT)",
},
)
+ self.validate_all(
+ "CREATE TABLE test STORED AS parquet TBLPROPERTIES ('x'='1', 'Z'='2') AS SELECT 1",
+ write={
+ "duckdb": "CREATE TABLE test AS SELECT 1",
+ "presto": "CREATE TABLE test WITH (FORMAT='parquet', x='1', Z='2') AS SELECT 1",
+ "hive": "CREATE TABLE test STORED AS PARQUET TBLPROPERTIES ('x'='1', 'Z'='2') AS SELECT 1",
+ "spark": "CREATE TABLE test USING PARQUET TBLPROPERTIES ('x'='1', 'Z'='2') AS SELECT 1",
+ },
+ )
def test_lateral_view(self):
self.validate_all(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 02dc1ad..723e27c 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -8,6 +8,7 @@ class TestMySQL(Validator):
self.validate_all(
"CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
write={
+ "duckdb": "CREATE TABLE z (a INT)",
"mysql": "CREATE TABLE z (a INT) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin COMMENT='x'",
"spark": "CREATE TABLE z (a INT) COMMENT 'x'",
},
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index dc93c3a..4b8f3c3 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -68,10 +68,12 @@ class TestPostgres(Validator):
self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)")
self.validate_identity("SELECT TO_TIMESTAMP(1284352323.5), TO_TIMESTAMP('05 Dec 2000', 'DD Mon YYYY')")
+ self.validate_identity("COMMENT ON TABLE mytable IS 'this'")
self.validate_all(
"CREATE TABLE x (a UUID, b BYTEA)",
write={
+ "duckdb": "CREATE TABLE x (a UUID, b BINARY)",
"presto": "CREATE TABLE x (a UUID, b VARBINARY)",
"hive": "CREATE TABLE x (a UUID, b BINARY)",
"spark": "CREATE TABLE x (a UUID, b BINARY)",
@@ -163,3 +165,42 @@ class TestPostgres(Validator):
"postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id",
},
)
+ self.validate_all(
+ "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE NOT deleted IS NULL",
+ read={"postgres": "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE deleted NOTNULL"},
+ )
+ self.validate_all(
+ "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE NOT deleted IS NULL",
+ read={"postgres": "SELECT id, email, CAST(deleted AS TEXT) FROM users WHERE NOT deleted ISNULL"},
+ )
+ self.validate_all(
+ "'[1,2,3]'::json->2",
+ write={"postgres": "CAST('[1,2,3]' AS JSON)->'2'"},
+ )
+ self.validate_all(
+ """'{"a":1,"b":2}'::json->'b'""",
+ write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->'b'"""},
+ )
+ self.validate_all(
+ """'{"x": {"y": 1}}'::json->'x'->'y'""", write={"postgres": """CAST('{"x": {"y": 1}}' AS JSON)->'x'->'y'"""}
+ )
+ self.validate_all(
+ """'{"x": {"y": 1}}'::json->'x'::json->'y'""",
+ write={"postgres": """CAST(CAST('{"x": {"y": 1}}' AS JSON)->'x' AS JSON)->'y'"""},
+ )
+ self.validate_all(
+ """'[1,2,3]'::json->>2""",
+ write={"postgres": "CAST('[1,2,3]' AS JSON)->>'2'"},
+ )
+ self.validate_all(
+ """'{"a":1,"b":2}'::json->>'b'""",
+ write={"postgres": """CAST('{"a":1,"b":2}' AS JSON)->>'b'"""},
+ )
+ self.validate_all(
+ """'{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'""",
+ write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>'{a,2}'"""},
+ )
+ self.validate_all(
+ """'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'""",
+ write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON)#>>'{a,2}'"""},
+ )
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index b0d9ad9..10c9d35 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -10,7 +10,7 @@ class TestPresto(Validator):
"CAST(a AS ARRAY(INT))",
write={
"bigquery": "CAST(a AS ARRAY<INT64>)",
- "duckdb": "CAST(a AS ARRAY<INT>)",
+ "duckdb": "CAST(a AS INT[])",
"presto": "CAST(a AS ARRAY(INTEGER))",
"spark": "CAST(a AS ARRAY<INT>)",
},
@@ -28,7 +28,7 @@ class TestPresto(Validator):
"CAST(ARRAY[1, 2] AS ARRAY(BIGINT))",
write={
"bigquery": "CAST([1, 2] AS ARRAY<INT64>)",
- "duckdb": "CAST(LIST_VALUE(1, 2) AS ARRAY<BIGINT>)",
+ "duckdb": "CAST(LIST_VALUE(1, 2) AS BIGINT[])",
"presto": "CAST(ARRAY[1, 2] AS ARRAY(BIGINT))",
"spark": "CAST(ARRAY(1, 2) AS ARRAY<LONG>)",
},
@@ -37,7 +37,7 @@ class TestPresto(Validator):
"CAST(MAP(ARRAY[1], ARRAY[1]) AS MAP(INT,INT))",
write={
"bigquery": "CAST(MAP([1], [1]) AS MAP<INT64, INT64>)",
- "duckdb": "CAST(MAP(LIST_VALUE(1), LIST_VALUE(1)) AS MAP<INT, INT>)",
+ "duckdb": "CAST(MAP(LIST_VALUE(1), LIST_VALUE(1)) AS MAP(INT, INT))",
"presto": "CAST(MAP(ARRAY[1], ARRAY[1]) AS MAP(INTEGER, INTEGER))",
"hive": "CAST(MAP(1, 1) AS MAP<INT, INT>)",
"spark": "CAST(MAP_FROM_ARRAYS(ARRAY(1), ARRAY(1)) AS MAP<INT, INT>)",
@@ -47,7 +47,7 @@ class TestPresto(Validator):
"CAST(MAP(ARRAY['a','b','c'], ARRAY[ARRAY[1], ARRAY[2], ARRAY[3]]) AS MAP(VARCHAR, ARRAY(INT)))",
write={
"bigquery": "CAST(MAP(['a', 'b', 'c'], [[1], [2], [3]]) AS MAP<STRING, ARRAY<INT64>>)",
- "duckdb": "CAST(MAP(LIST_VALUE('a', 'b', 'c'), LIST_VALUE(LIST_VALUE(1), LIST_VALUE(2), LIST_VALUE(3))) AS MAP<TEXT, ARRAY<INT>>)",
+ "duckdb": "CAST(MAP(LIST_VALUE('a', 'b', 'c'), LIST_VALUE(LIST_VALUE(1), LIST_VALUE(2), LIST_VALUE(3))) AS MAP(TEXT, INT[]))",
"presto": "CAST(MAP(ARRAY['a', 'b', 'c'], ARRAY[ARRAY[1], ARRAY[2], ARRAY[3]]) AS MAP(VARCHAR, ARRAY(INTEGER)))",
"hive": "CAST(MAP('a', ARRAY(1), 'b', ARRAY(2), 'c', ARRAY(3)) AS MAP<STRING, ARRAY<INT>>)",
"spark": "CAST(MAP_FROM_ARRAYS(ARRAY('a', 'b', 'c'), ARRAY(ARRAY(1), ARRAY(2), ARRAY(3))) AS MAP<STRING, ARRAY<INT>>)",
@@ -171,6 +171,7 @@ class TestPresto(Validator):
self.validate_all(
"CREATE TABLE test WITH (FORMAT = 'PARQUET') AS SELECT 1",
write={
+ "duckdb": "CREATE TABLE test AS SELECT 1",
"presto": "CREATE TABLE test WITH (FORMAT='PARQUET') AS SELECT 1",
"hive": "CREATE TABLE test STORED AS PARQUET AS SELECT 1",
"spark": "CREATE TABLE test USING PARQUET AS SELECT 1",
@@ -179,6 +180,7 @@ class TestPresto(Validator):
self.validate_all(
"CREATE TABLE test WITH (FORMAT = 'PARQUET', X = '1', Z = '2') AS SELECT 1",
write={
+ "duckdb": "CREATE TABLE test AS SELECT 1",
"presto": "CREATE TABLE test WITH (FORMAT='PARQUET', X='1', Z='2') AS SELECT 1",
"hive": "CREATE TABLE test STORED AS PARQUET TBLPROPERTIES ('X'='1', 'Z'='2') AS SELECT 1",
"spark": "CREATE TABLE test USING PARQUET TBLPROPERTIES ('X'='1', 'Z'='2') AS SELECT 1",
@@ -187,6 +189,7 @@ class TestPresto(Validator):
self.validate_all(
"CREATE TABLE x (w VARCHAR, y INTEGER, z INTEGER) WITH (PARTITIONED_BY=ARRAY['y', 'z'])",
write={
+ "duckdb": "CREATE TABLE x (w TEXT, y INT, z INT)",
"presto": "CREATE TABLE x (w VARCHAR, y INTEGER, z INTEGER) WITH (PARTITIONED_BY=ARRAY['y', 'z'])",
"hive": "CREATE TABLE x (w STRING) PARTITIONED BY (y INT, z INT)",
"spark": "CREATE TABLE x (w STRING) PARTITIONED BY (y INT, z INT)",
@@ -195,6 +198,7 @@ class TestPresto(Validator):
self.validate_all(
"CREATE TABLE x WITH (bucket_by = ARRAY['y'], bucket_count = 64) AS SELECT 1 AS y",
write={
+ "duckdb": "CREATE TABLE x AS SELECT 1 AS y",
"presto": "CREATE TABLE x WITH (bucket_by=ARRAY['y'], bucket_count=64) AS SELECT 1 AS y",
"hive": "CREATE TABLE x TBLPROPERTIES ('bucket_by'=ARRAY('y'), 'bucket_count'=64) AS SELECT 1 AS y",
"spark": "CREATE TABLE x TBLPROPERTIES ('bucket_by'=ARRAY('y'), 'bucket_count'=64) AS SELECT 1 AS y",
@@ -203,6 +207,7 @@ class TestPresto(Validator):
self.validate_all(
"CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b VARCHAR))",
write={
+ "duckdb": "CREATE TABLE db.example_table (col_a STRUCT(struct_col_a INT, struct_col_b TEXT))",
"presto": "CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b VARCHAR))",
"hive": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT, struct_col_b STRING>)",
"spark": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a: INT, struct_col_b: STRING>)",
@@ -211,6 +216,7 @@ class TestPresto(Validator):
self.validate_all(
"CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b ROW(nested_col_a VARCHAR, nested_col_b VARCHAR)))",
write={
+ "duckdb": "CREATE TABLE db.example_table (col_a STRUCT(struct_col_a INT, struct_col_b STRUCT(nested_col_a TEXT, nested_col_b TEXT)))",
"presto": "CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b ROW(nested_col_a VARCHAR, nested_col_b VARCHAR)))",
"hive": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT, struct_col_b STRUCT<nested_col_a STRING, nested_col_b STRING>>)",
"spark": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a: INT, struct_col_b: STRUCT<nested_col_a: STRING, nested_col_b: STRING>>)",
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 9a7e64c..339d1a6 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -8,6 +8,7 @@ class TestSpark(Validator):
self.validate_all(
"CREATE TABLE db.example_table (col_a struct<struct_col_a:int, struct_col_b:string>)",
write={
+ "duckdb": "CREATE TABLE db.example_table (col_a STRUCT(struct_col_a INT, struct_col_b TEXT))",
"presto": "CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b VARCHAR))",
"hive": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT, struct_col_b STRING>)",
"spark": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a: INT, struct_col_b: STRING>)",
@@ -17,6 +18,7 @@ class TestSpark(Validator):
"CREATE TABLE db.example_table (col_a struct<struct_col_a:int, struct_col_b:struct<nested_col_a:string, nested_col_b:string>>)",
write={
"bigquery": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT64, struct_col_b STRUCT<nested_col_a STRING, nested_col_b STRING>>)",
+ "duckdb": "CREATE TABLE db.example_table (col_a STRUCT(struct_col_a INT, struct_col_b STRUCT(nested_col_a TEXT, nested_col_b TEXT)))",
"presto": "CREATE TABLE db.example_table (col_a ROW(struct_col_a INTEGER, struct_col_b ROW(nested_col_a VARCHAR, nested_col_b VARCHAR)))",
"hive": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a INT, struct_col_b STRUCT<nested_col_a STRING, nested_col_b STRING>>)",
"spark": "CREATE TABLE db.example_table (col_a STRUCT<struct_col_a: INT, struct_col_b: STRUCT<nested_col_a: STRING, nested_col_b: STRING>>)",
@@ -26,6 +28,7 @@ class TestSpark(Validator):
"CREATE TABLE db.example_table (col_a array<int>, col_b array<array<int>>)",
write={
"bigquery": "CREATE TABLE db.example_table (col_a ARRAY<INT64>, col_b ARRAY<ARRAY<INT64>>)",
+ "duckdb": "CREATE TABLE db.example_table (col_a INT[], col_b INT[][])",
"presto": "CREATE TABLE db.example_table (col_a ARRAY(INTEGER), col_b ARRAY(ARRAY(INTEGER)))",
"hive": "CREATE TABLE db.example_table (col_a ARRAY<INT>, col_b ARRAY<ARRAY<INT>>)",
"spark": "CREATE TABLE db.example_table (col_a ARRAY<INT>, col_b ARRAY<ARRAY<INT>>)",
@@ -34,6 +37,7 @@ class TestSpark(Validator):
self.validate_all(
"CREATE TABLE x USING ICEBERG PARTITIONED BY (MONTHS(y)) LOCATION 's3://z'",
write={
+ "duckdb": "CREATE TABLE x",
"presto": "CREATE TABLE x WITH (TABLE_FORMAT = 'ICEBERG', PARTITIONED_BY=ARRAY['MONTHS'])",
"hive": "CREATE TABLE x USING ICEBERG PARTITIONED BY (MONTHS(y)) LOCATION 's3://z'",
"spark": "CREATE TABLE x USING ICEBERG PARTITIONED BY (MONTHS(y)) LOCATION 's3://z'",
@@ -42,6 +46,7 @@ class TestSpark(Validator):
self.validate_all(
"CREATE TABLE test STORED AS PARQUET AS SELECT 1",
write={
+ "duckdb": "CREATE TABLE test AS SELECT 1",
"presto": "CREATE TABLE test WITH (FORMAT='PARQUET') AS SELECT 1",
"hive": "CREATE TABLE test STORED AS PARQUET AS SELECT 1",
"spark": "CREATE TABLE test USING PARQUET AS SELECT 1",
@@ -50,6 +55,9 @@ class TestSpark(Validator):
self.validate_all(
"""CREATE TABLE blah (col_a INT) COMMENT "Test comment: blah" PARTITIONED BY (date STRING) STORED AS ICEBERG TBLPROPERTIES('x' = '1')""",
write={
+ "duckdb": """CREATE TABLE blah (
+ col_a INT
+)""", # Partition columns should exist in table
"presto": """CREATE TABLE blah (
col_a INTEGER,
date VARCHAR
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 6b0b39b..b061784 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -6,6 +6,9 @@ class TestTSQL(Validator):
def test_tsql(self):
self.validate_identity('SELECT "x"."y" FROM foo')
+ self.validate_identity(
+ "SELECT DISTINCT DepartmentName, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY BaseRate) OVER (PARTITION BY DepartmentName) AS MedianCont FROM dbo.DimEmployee"
+ )
self.validate_all(
"SELECT CAST([a].[b] AS SMALLINT) FROM foo",
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 40e7cc1..57e51e0 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -50,7 +50,9 @@ a.B()
a['x'].C()
int.x
map.x
+SELECT call.x
a.b.INT(1.234)
+INT(x / 100)
x IN (-1, 1)
x IN ('a', 'a''a')
x IN ((1))
@@ -147,6 +149,7 @@ 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 1 AS number # annotation
SELECT t.count
SELECT DISTINCT x FROM test
SELECT DISTINCT x, y FROM test
@@ -159,6 +162,8 @@ 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 COUNT(DISTINCT a, b)
+SELECT COUNT(DISTINCT a, b + 1)
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
@@ -485,6 +490,9 @@ 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)
+CREATE SCHEMA x
+CREATE SCHEMA IF NOT EXISTS y
+DESCRIBE x
DROP INDEX a.b.c
CACHE TABLE x
CACHE LAZY TABLE x
@@ -524,6 +532,15 @@ 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
+INSERT OVERWRITE DIRECTORY 'x' SELECT 1
+INSERT OVERWRITE LOCAL DIRECTORY 'x' SELECT 1
+INSERT OVERWRITE LOCAL DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' LINES TERMINATED BY '4' NULL DEFINED AS '5' SELECT 1
+LOAD DATA INPATH 'x' INTO TABLE y PARTITION(ds='yyyy')
+LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds='yyyy')
+LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds='yyyy') INPUTFORMAT 'y'
+LOAD DATA LOCAL INPATH 'x' INTO TABLE y PARTITION(ds='yyyy') INPUTFORMAT 'y' SERDE 'z'
+LOAD DATA INPATH 'x' INTO TABLE y INPUTFORMAT 'y' SERDE 'z'
+LOAD DATA INPATH 'x' INTO TABLE y.b INPUTFORMAT 'y' SERDE 'z'
SELECT 1 FROM PARQUET_SCAN('/x/y/*') AS y
UNCACHE TABLE x
UNCACHE TABLE IF EXISTS x
diff --git a/tests/fixtures/optimizer/eliminate_ctes.sql b/tests/fixtures/optimizer/eliminate_ctes.sql
new file mode 100644
index 0000000..11e5e4f
--- /dev/null
+++ b/tests/fixtures/optimizer/eliminate_ctes.sql
@@ -0,0 +1,48 @@
+# title: CTE
+WITH q AS (
+ SELECT
+ a
+ FROM x
+)
+SELECT
+ a
+FROM x;
+SELECT
+ a
+FROM x;
+
+# title: Nested CTE
+SELECT
+ a
+FROM (
+ WITH q AS (
+ SELECT
+ a
+ FROM x
+ )
+ SELECT a FROM x
+);
+SELECT
+ a
+FROM (
+ SELECT
+ a
+ FROM x
+);
+
+# title: Chained CTE
+WITH q AS (
+ SELECT
+ a
+ FROM x
+), r AS (
+ SELECT
+ a
+ FROM q
+)
+SELECT
+ a
+FROM x;
+SELECT
+ a
+FROM x;
diff --git a/tests/fixtures/optimizer/eliminate_joins.sql b/tests/fixtures/optimizer/eliminate_joins.sql
new file mode 100644
index 0000000..ac3d92d
--- /dev/null
+++ b/tests/fixtures/optimizer/eliminate_joins.sql
@@ -0,0 +1,317 @@
+# title: Remove left join on distinct derived table
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x;
+
+# title: Remove left join on grouped derived table
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b,
+ SUM(y.c)
+ FROM y
+ GROUP BY y.b
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x;
+
+# title: Remove left join on aggregate derived table
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ SUM(y.b) AS b
+ FROM y
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x;
+
+# title: Noop - not all distinct columns in condition
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b,
+ y.c
+ FROM y
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b,
+ y.c
+ FROM y
+) AS y
+ ON x.b = y.b;
+
+# title: Noop - not all grouped columns in condition
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b,
+ y.c
+ FROM y
+ GROUP BY
+ y.b,
+ y.c
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b,
+ y.c
+ FROM y
+ GROUP BY
+ y.b,
+ y.c
+) AS y
+ ON x.b = y.b;
+
+# title: Noop - not left join
+SELECT
+ x.a
+FROM x
+JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x
+JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+
+# title: Noop - unqualified columns
+SELECT
+ a
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+SELECT
+ a
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+
+# title: Noop - cross join
+SELECT
+ a
+FROM x
+CROSS JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y;
+SELECT
+ a
+FROM x
+CROSS JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y;
+
+# title: Noop - column is used
+SELECT
+ x.a,
+ y.b
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a,
+ y.b
+FROM x
+LEFT JOIN (
+ SELECT DISTINCT
+ y.b
+ FROM y
+) AS y
+ ON x.b = y.b;
+
+# title: Multiple group by columns
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b AS b,
+ y.c + 1 AS d,
+ COUNT(1)
+ FROM y
+ GROUP BY y.b, y.c + 1
+) AS y
+ ON x.b = y.b
+ AND 1 = y.d;
+SELECT
+ x.a
+FROM x;
+
+# title: Chained left joins
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b AS b
+ FROM y
+ GROUP BY y.b
+) AS y
+ ON x.b = y.b
+LEFT JOIN (
+ SELECT
+ y.b AS c
+ FROM y
+ GROUP BY y.b
+) AS z
+ ON y.b = z.c;
+SELECT
+ x.a
+FROM x;
+
+# title: CTE
+WITH z AS (
+ SELECT DISTINCT
+ y.b
+ FROM y
+)
+SELECT
+ x.a
+FROM x
+LEFT JOIN z
+ ON x.b = z.b;
+WITH z AS (
+ SELECT DISTINCT
+ y.b
+ FROM y
+)
+SELECT
+ x.a
+FROM x;
+
+# title: Noop - Not all grouped expressions are in outputs
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b
+ FROM y
+ GROUP BY
+ y.b,
+ y.c
+) AS y
+ ON x.b = y.b;
+SELECT
+ x.a
+FROM x
+LEFT JOIN (
+ SELECT
+ y.b
+ FROM y
+ GROUP BY
+ y.b,
+ y.c
+) AS y
+ ON x.b = y.b;
+
+# title: Cross join on aggregate derived table
+SELECT
+ x.a
+FROM x
+CROSS JOIN (
+ SELECT
+ SUM(y.b) AS b
+ FROM y
+) AS y;
+SELECT
+ x.a
+FROM x;
+
+# title: Cross join on derived table with LIMIT 1
+SELECT
+ x.a
+FROM x
+CROSS JOIN (
+ SELECT
+ y.b AS b
+ FROM y
+ LIMIT 1
+) AS y;
+SELECT
+ x.a
+FROM x;
+
+# title: Cross join on derived table with no FROM clause
+SELECT
+ x.a
+FROM x
+CROSS JOIN (
+ SELECT
+ 1 AS b,
+ 2 AS c
+) AS y;
+SELECT
+ x.a
+FROM x;
+
+# title: Noop - cross join on non-aggregate subquery
+SELECT
+ x.a
+FROM x
+CROSS JOIN (
+ SELECT
+ y.b
+ FROM y
+) AS y;
+SELECT
+ x.a
+FROM x
+CROSS JOIN (
+ SELECT
+ y.b
+ FROM y
+) AS y;
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index c8186cc..a82e1ed 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -187,3 +187,103 @@ FROM (
) AS x
) AS x;
SELECT /*+ BROADCAST(x) */ x.a AS a, x.c AS c FROM (SELECT x.a AS a, COUNT(1) AS c FROM x AS x GROUP BY x.a) AS x;
+
+# title: Test preventing merge of window expressions where clause
+with t1 as (
+ SELECT
+ x.a,
+ x.b,
+ ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+ FROM
+ x
+)
+SELECT
+ t1.a,
+ t1.b
+FROM
+ t1
+WHERE
+ row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 WHERE t1.row_num = 1;
+
+# title: Test preventing merge of window expressions join clause
+with t1 as (
+ SELECT
+ x.a,
+ x.b,
+ ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+ FROM
+ x
+)
+SELECT
+ t1.a,
+ t1.b
+FROM t1 JOIN y ON t1.a = y.c AND t1.row_num = 1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.a AS a, t1.b AS b FROM t1 JOIN y AS y ON t1.a = y.c AND t1.row_num = 1;
+
+# title: Test preventing merge of window expressions agg function
+with t1 as (
+ SELECT
+ x.a,
+ x.b,
+ ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+ FROM
+ x
+)
+SELECT
+ SUM(t1.row_num) as total_rows
+FROM
+ t1;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT SUM(t1.row_num) AS total_rows FROM t1;
+
+# title: Test prevent merging of window if in group by func
+with t1 as (
+ SELECT
+ x.a,
+ x.b,
+ ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+ FROM
+ x
+)
+SELECT
+ t1.row_num AS row_num,
+ SUM(t1.a) AS total
+FROM
+ t1
+GROUP BY t1.row_num
+ORDER BY t1.row_num;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, SUM(t1.a) AS total FROM t1 GROUP BY t1.row_num ORDER BY t1.row_num;
+
+# title: Test prevent merging of window if in order by func
+with t1 as (
+ SELECT
+ x.a,
+ x.b,
+ ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+ FROM
+ x
+)
+SELECT
+ t1.row_num AS row_num,
+ t1.a AS a
+FROM
+ t1
+ORDER BY t1.row_num, t1.a;
+WITH t1 AS (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x) SELECT t1.row_num AS row_num, t1.a AS a FROM t1 ORDER BY t1.row_num, t1.a;
+
+# title: Test allow merging of window function
+with t1 as (
+ SELECT
+ x.a,
+ x.b,
+ ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num
+ FROM
+ x
+)
+SELECT
+ t1.a,
+ t1.b,
+ t1.row_num
+FROM
+ t1;
+SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x AS x;
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index ab4f769..eb7e9cb 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -105,9 +105,7 @@ LEFT JOIN "_u_0" AS "_u_0"
JOIN "y" AS "y"
ON "x"."b" = "y"."b"
WHERE
- "_u_0"."_col_0" >= 0
- AND "x"."a" > 1
- AND NOT "_u_0"."_u_1" IS NULL
+ "_u_0"."_col_0" >= 0 AND "x"."a" > 1 AND NOT "_u_0"."_u_1" IS NULL
GROUP BY
"x"."a";
diff --git a/tests/fixtures/optimizer/pushdown_predicates.sql b/tests/fixtures/optimizer/pushdown_predicates.sql
index 676cb96..ef591ec 100644
--- a/tests/fixtures/optimizer/pushdown_predicates.sql
+++ b/tests/fixtures/optimizer/pushdown_predicates.sql
@@ -30,3 +30,6 @@ SELECT x.a AS a FROM x AS x JOIN (SELECT * FROM y AS y WHERE y.a = 1) AS y ON y.
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;
+
+with t1 as (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) as row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
+WITH t1 AS (SELECT x.a, x.b, ROW_NUMBER() OVER (PARTITION BY x.a ORDER BY x.a) AS row_num FROM x) SELECT t1.a, t1.b FROM t1 WHERE row_num = 1;
diff --git a/tests/fixtures/optimizer/tpc-h/tpc-h.sql b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
index 936a0af..b91205c 100644
--- a/tests/fixtures/optimizer/tpc-h/tpc-h.sql
+++ b/tests/fixtures/optimizer/tpc-h/tpc-h.sql
@@ -28,13 +28,15 @@ SELECT
SUM("lineitem"."l_quantity") AS "sum_qty",
SUM("lineitem"."l_extendedprice") AS "sum_base_price",
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "sum_disc_price",
- SUM("lineitem"."l_extendedprice" * (
+ SUM(
+ "lineitem"."l_extendedprice" * (
1 - "lineitem"."l_discount"
) * (
1 + "lineitem"."l_tax"
- )) AS "sum_charge",
+ )
+ ) AS "sum_charge",
AVG("lineitem"."l_quantity") AS "avg_qty",
AVG("lineitem"."l_extendedprice") AS "avg_price",
AVG("lineitem"."l_discount") AS "avg_disc",
@@ -186,7 +188,7 @@ limit
SELECT
"lineitem"."l_orderkey" AS "l_orderkey",
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "revenue",
CAST("orders"."o_orderdate" AS TEXT) AS "o_orderdate",
"orders"."o_shippriority" AS "o_shippriority"
@@ -286,7 +288,7 @@ order by
SELECT
"nation"."n_name" AS "n_name",
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "revenue"
FROM "customer" AS "customer"
JOIN "orders" AS "orders"
@@ -377,15 +379,14 @@ WITH "n1" AS (
"nation"."n_name" AS "n_name"
FROM "nation" AS "nation"
WHERE
- "nation"."n_name" = 'FRANCE'
- OR "nation"."n_name" = 'GERMANY'
+ "nation"."n_name" = 'FRANCE' OR "nation"."n_name" = 'GERMANY'
)
SELECT
"n1"."n_name" AS "supp_nation",
"n2"."n_name" AS "cust_nation",
EXTRACT(year FROM "lineitem"."l_shipdate") AS "l_year",
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "revenue"
FROM "supplier" AS "supplier"
JOIN "lineitem" AS "lineitem"
@@ -400,12 +401,10 @@ JOIN "n1" AS "n1"
JOIN "n1" AS "n2"
ON "customer"."c_nationkey" = "n2"."n_nationkey"
AND (
- "n1"."n_name" = 'FRANCE'
- OR "n2"."n_name" = 'FRANCE'
+ "n1"."n_name" = 'FRANCE' OR "n2"."n_name" = 'FRANCE'
)
AND (
- "n1"."n_name" = 'GERMANY'
- OR "n2"."n_name" = 'GERMANY'
+ "n1"."n_name" = 'GERMANY' OR "n2"."n_name" = 'GERMANY'
)
GROUP BY
"n1"."n_name",
@@ -458,14 +457,16 @@ order by
o_year;
SELECT
EXTRACT(year FROM "orders"."o_orderdate") AS "o_year",
- SUM(CASE
+ SUM(
+ CASE
WHEN "nation_2"."n_name" = 'BRAZIL'
THEN "lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
- )
+ 1 - "lineitem"."l_discount"
+ )
ELSE 0
- END) / SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ END
+ ) / SUM("lineitem"."l_extendedprice" * (
+ 1 - "lineitem"."l_discount"
)) AS "mkt_share"
FROM "part" AS "part"
JOIN "region" AS "region"
@@ -529,9 +530,11 @@ order by
SELECT
"nation"."n_name" AS "nation",
EXTRACT(year FROM "orders"."o_orderdate") AS "o_year",
- SUM("lineitem"."l_extendedprice" * (
+ SUM(
+ "lineitem"."l_extendedprice" * (
1 - "lineitem"."l_discount"
- ) - "partsupp"."ps_supplycost" * "lineitem"."l_quantity") AS "sum_profit"
+ ) - "partsupp"."ps_supplycost" * "lineitem"."l_quantity"
+ ) AS "sum_profit"
FROM "part" AS "part"
JOIN "lineitem" AS "lineitem"
ON "part"."p_partkey" = "lineitem"."l_partkey"
@@ -593,7 +596,7 @@ SELECT
"customer"."c_custkey" AS "c_custkey",
"customer"."c_name" AS "c_name",
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "revenue",
"customer"."c_acctbal" AS "c_acctbal",
"nation"."n_name" AS "n_name",
@@ -606,8 +609,7 @@ JOIN "orders" AS "orders"
AND "orders"."o_orderdate" < CAST('1994-01-01' AS DATE)
AND "orders"."o_orderdate" >= CAST('1993-10-01' AS DATE)
JOIN "lineitem" AS "lineitem"
- ON "lineitem"."l_orderkey" = "orders"."o_orderkey"
- AND "lineitem"."l_returnflag" = 'R'
+ ON "lineitem"."l_orderkey" = "orders"."o_orderkey" AND "lineitem"."l_returnflag" = 'R'
JOIN "nation" AS "nation"
ON "customer"."c_nationkey" = "nation"."n_nationkey"
GROUP BY
@@ -721,18 +723,20 @@ 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'
+ 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'
+ 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"
+ END
+ ) AS "low_line_count"
FROM "orders" AS "orders"
JOIN "lineitem" AS "lineitem"
ON "lineitem"."l_commitdate" < "lineitem"."l_receiptdate"
@@ -813,14 +817,16 @@ where
and l_shipdate >= date '1995-09-01'
and l_shipdate < date '1995-09-01' + interval '1' month;
SELECT
- 100.00 * SUM(CASE
+ 100.00 * SUM(
+ CASE
WHEN "part"."p_type" LIKE 'PROMO%'
THEN "lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
- )
+ 1 - "lineitem"."l_discount"
+ )
ELSE 0
- END) / SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ END
+ ) / SUM("lineitem"."l_extendedprice" * (
+ 1 - "lineitem"."l_discount"
)) AS "promo_revenue"
FROM "lineitem" AS "lineitem"
JOIN "part" AS "part"
@@ -866,7 +872,7 @@ WITH "revenue" AS (
SELECT
"lineitem"."l_suppkey" AS "supplier_no",
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "total_revenue"
FROM "lineitem" AS "lineitem"
WHERE
@@ -997,8 +1003,7 @@ JOIN "part" AS "part"
LEFT JOIN "_u_0" 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;
+ "lineitem"."l_quantity" < "_u_0"."_col_0" AND NOT "_u_0"."_u_1" IS NULL;
--------------------------------------
-- TPC-H 18
@@ -1114,7 +1119,7 @@ where
);
SELECT
SUM("lineitem"."l_extendedprice" * (
- 1 - "lineitem"."l_discount"
+ 1 - "lineitem"."l_discount"
)) AS "revenue"
FROM "lineitem" AS "lineitem"
JOIN "part" AS "part"
@@ -1233,8 +1238,7 @@ WITH "_u_0" AS (
"partsupp"."ps_suppkey" AS "ps_suppkey"
FROM "partsupp" AS "partsupp"
LEFT JOIN "_u_0" AS "_u_0"
- ON "_u_0"."_u_1" = "partsupp"."ps_partkey"
- AND "_u_0"."_u_2" = "partsupp"."ps_suppkey"
+ ON "_u_0"."_u_1" = "partsupp"."ps_partkey" AND "_u_0"."_u_2" = "partsupp"."ps_suppkey"
LEFT JOIN "_u_3" AS "_u_3"
ON "partsupp"."ps_partkey" = "_u_3"."p_partkey"
WHERE
@@ -1252,8 +1256,7 @@ FROM "supplier" AS "supplier"
LEFT JOIN "_u_4" AS "_u_4"
ON "supplier"."s_suppkey" = "_u_4"."ps_suppkey"
JOIN "nation" AS "nation"
- ON "nation"."n_name" = 'CANADA'
- AND "supplier"."s_nationkey" = "nation"."n_nationkey"
+ ON "nation"."n_name" = 'CANADA' AND "supplier"."s_nationkey" = "nation"."n_nationkey"
WHERE
NOT "_u_4"."ps_suppkey" IS NULL
ORDER BY
@@ -1332,8 +1335,7 @@ LEFT JOIN "_u_0" AS "_u_0"
LEFT JOIN "_u_2" AS "_u_2"
ON "_u_2"."l_orderkey" = "lineitem"."l_orderkey"
JOIN "orders" AS "orders"
- ON "orders"."o_orderkey" = "lineitem"."l_orderkey"
- AND "orders"."o_orderstatus" = 'F'
+ ON "orders"."o_orderkey" = "lineitem"."l_orderkey" AND "orders"."o_orderstatus" = 'F'
JOIN "nation" AS "nation"
ON "nation"."n_name" = 'SAUDI ARABIA'
AND "supplier"."s_nationkey" = "nation"."n_nationkey"
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index 9c4bd27..f53121a 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -108,14 +108,11 @@ LEFT JOIN (
ARRAY_AGG(y.b) AS _u_13
FROM y
WHERE
- TRUE
- AND TRUE
- AND TRUE
+ 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
+ ON "_u_11"."_u_12" = x.a AND "_u_11"."_u_12" = x.b
LEFT JOIN (
SELECT
y.a AS a
@@ -131,38 +128,30 @@ WHERE
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
+ 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
+ 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
+ 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
+ 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
+ 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
+ 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
+ NOT "_u_14".a IS NULL AND NOT "_u_14".a IS NULL
)
AND x.a IN (
SELECT
@@ -203,4 +192,3 @@ WHERE
y.a = x.a
OFFSET 10
);
-
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index 19a7451..2570650 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -56,14 +56,10 @@ LEFT JOIN (
WITH cte1 AS (
SELECT
a,
- z
- AND e AS b
+ z AND e AS b
FROM cte
WHERE
- x IN (1, 2, 3)
- AND z < -1
- OR z > 1
- AND w = 'AND'
+ 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,
@@ -77,18 +73,12 @@ WITH cte1 AS (
2
UNION ALL
SELECT
- CASE x
- AND 1 + 1 = 2
+ CASE x AND 1 + 1 = 2
WHEN TRUE
- THEN 1
- AND 4 + 3
- AND Z
- WHEN x
- AND y
+ THEN 1 AND 4 + 3 AND Z
+ WHEN x AND y
THEN 2
- ELSE 3
- AND 4
- AND g
+ ELSE 3 AND 4 AND g
END
UNION ALL
SELECT
@@ -102,18 +92,8 @@ WITH cte1 AS (
) 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)
+ 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 (
@@ -154,10 +134,8 @@ LEFT JOIN (
FROM bar
WHERE
(
- c > 1
- AND d > 1
- )
- OR e > 1
+ c > 1 AND d > 1
+ ) OR e > 1
GROUP BY
a
HAVING
@@ -165,11 +143,8 @@ LEFT JOIN (
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
+ 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
@@ -184,9 +159,7 @@ EXPLODE(col1) myTable1 AS myCol1
LATERAL VIEW
EXPLODE(col2) myTable2 AS myCol2
WHERE
- a > 1
- AND b > 2
- OR c > 3;
+ a > 1 AND b > 2 OR c > 3;
SELECT * FROM (WITH y AS ( SELECT 1 AS z) SELECT z from y) x;
SELECT
@@ -264,3 +237,53 @@ CREATE TABLE "t_customer_account" (
"account_no" VARCHAR(100)
);
+
+SELECT
+x("aaaaaaaaaaaaaa", "bbbbbbbbbbbbb", "ccccccccc", "ddddddddddddd", "eeeeeeeeeeeee", "fffffff"),
+array("aaaaaaaaaaaaaa", "bbbbbbbbbbbbb", "ccccccccc", "ddddddddddddd", "eeeeeeeeeeeee", "fffffff"),
+array("aaaaaaaaaaaaaa", "bbbbbbbbbbbbb", "ccccccccc", "ddddddddddddd", "eeeeeeeeeeeee", "fffffff", array("aaaaaaaaaaaaaa", "bbbbbbbbbbbbb", "ccccccccc", "ddddddddddddd", "eeeeeeeeeeeee", "fffffff")),
+array(array("aaaaaaaaaaaaaa", "bbbbbbbbbbbbb", "ccccccccc", "ddddddddddddd", "eeeeeeeeeeeee", "fffffff")),
+;
+SELECT
+ X(
+ "aaaaaaaaaaaaaa",
+ "bbbbbbbbbbbbb",
+ "ccccccccc",
+ "ddddddddddddd",
+ "eeeeeeeeeeeee",
+ "fffffff"
+ ),
+ ARRAY(
+ "aaaaaaaaaaaaaa",
+ "bbbbbbbbbbbbb",
+ "ccccccccc",
+ "ddddddddddddd",
+ "eeeeeeeeeeeee",
+ "fffffff"
+ ),
+ ARRAY(
+ "aaaaaaaaaaaaaa",
+ "bbbbbbbbbbbbb",
+ "ccccccccc",
+ "ddddddddddddd",
+ "eeeeeeeeeeeee",
+ "fffffff",
+ ARRAY(
+ "aaaaaaaaaaaaaa",
+ "bbbbbbbbbbbbb",
+ "ccccccccc",
+ "ddddddddddddd",
+ "eeeeeeeeeeeee",
+ "fffffff"
+ )
+ ),
+ ARRAY(
+ ARRAY(
+ "aaaaaaaaaaaaaa",
+ "bbbbbbbbbbbbb",
+ "ccccccccc",
+ "ddddddddddddd",
+ "eeeeeeeeeeeee",
+ "fffffff"
+ )
+ );
diff --git a/tests/test_build.py b/tests/test_build.py
index fa9e7f8..a432ef1 100644
--- a/tests/test_build.py
+++ b/tests/test_build.py
@@ -381,6 +381,11 @@ class TestBuild(unittest.TestCase):
lambda: alias(parse_one("LAG(x) OVER ()"), "a"),
"LAG(x) OVER () AS a",
),
+ (lambda: exp.values([("1", 2)]), "VALUES ('1', 2)"),
+ (lambda: exp.values([("1", 2)], "alias"), "(VALUES ('1', 2)) AS alias"),
+ (lambda: exp.values([("1", 2), ("2", 3)]), "VALUES ('1', 2), ('2', 3)"),
+ (lambda: exp.delete("y", where="x > 1"), "DELETE FROM y WHERE x > 1"),
+ (lambda: exp.delete("y", where=exp.and_("x > 1")), "DELETE FROM y WHERE x > 1"),
]:
with self.subTest(sql):
self.assertEqual(expression().sql(dialect[0] if dialect else None), sql)
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index abc95cb..9ad2bf5 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -1,3 +1,4 @@
+import datetime
import unittest
from sqlglot import alias, exp, parse_one
@@ -29,6 +30,7 @@ class TestExpressions(unittest.TestCase):
self.assertEqual(parse_one("TO_DATE(x)", read="hive"), parse_one("ts_or_ds_to_date(x)"))
self.assertEqual(exp.Table(pivots=[]), exp.Table())
self.assertNotEqual(exp.Table(pivots=[None]), exp.Table())
+ self.assertEqual(exp.DataType.build("int"), exp.DataType(this=exp.DataType.Type.INT, nested=False))
def test_find(self):
expression = parse_one("CREATE TABLE x STORED AS PARQUET AS SELECT * FROM y")
@@ -486,6 +488,8 @@ class TestExpressions(unittest.TestCase):
((1, "2", None), "(1, '2', NULL)"),
([1, "2", None], "ARRAY(1, '2', NULL)"),
({"x": None}, "MAP('x', NULL)"),
+ (datetime.datetime(2022, 10, 1, 1, 1, 1), "TIME_STR_TO_TIME('2022-10-01 01:01:01')"),
+ (datetime.date(2022, 10, 1), "DATE_STR_TO_DATE('2022-10-01')"),
]:
with self.subTest(value):
self.assertEqual(exp.convert(value).sql(), expected)
@@ -496,3 +500,25 @@ class TestExpressions(unittest.TestCase):
[e.alias_or_name for e in expression.expressions],
["a", "B", "c", "D"],
)
+
+ def test_to_table(self):
+ table_only = exp.to_table("table_name")
+ self.assertEqual(table_only.name, "table_name")
+ self.assertIsNone(table_only.args.get("db"))
+ self.assertIsNone(table_only.args.get("catalog"))
+ db_and_table = exp.to_table("db.table_name")
+ self.assertEqual(db_and_table.name, "table_name")
+ self.assertEqual(db_and_table.args.get("db"), exp.to_identifier("db"))
+ self.assertIsNone(db_and_table.args.get("catalog"))
+ catalog_db_and_table = exp.to_table("catalog.db.table_name")
+ self.assertEqual(catalog_db_and_table.name, "table_name")
+ self.assertEqual(catalog_db_and_table.args.get("db"), exp.to_identifier("db"))
+ self.assertEqual(catalog_db_and_table.args.get("catalog"), exp.to_identifier("catalog"))
+
+ def test_union(self):
+ expression = parse_one("SELECT cola, colb UNION SELECT colx, coly")
+ self.assertIsInstance(expression, exp.Union)
+ self.assertEqual(expression.named_selects, ["cola", "colb"])
+ self.assertEqual(
+ expression.selects, [exp.Column(this=exp.to_identifier("cola")), exp.Column(this=exp.to_identifier("colb"))]
+ )
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index 36a7785..a67e9db 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -191,6 +191,20 @@ class TestOptimizer(unittest.TestCase):
optimizer.optimize_joins.optimize_joins,
)
+ def test_eliminate_joins(self):
+ self.check_file(
+ "eliminate_joins",
+ optimizer.eliminate_joins.eliminate_joins,
+ pretty=True,
+ )
+
+ def test_eliminate_ctes(self):
+ self.check_file(
+ "eliminate_ctes",
+ optimizer.eliminate_ctes.eliminate_ctes,
+ pretty=True,
+ )
+
def test_merge_subqueries(self):
optimize = partial(
optimizer.optimize,
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 21:48:46 +0000