summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to 'tests')
-rw-r--r--tests/dialects/test_bigquery.py29
-rw-r--r--tests/dialects/test_clickhouse.py43
-rw-r--r--tests/dialects/test_dialect.py15
-rw-r--r--tests/dialects/test_duckdb.py19
-rw-r--r--tests/dialects/test_hive.py5
-rw-r--r--tests/dialects/test_mysql.py1
-rw-r--r--tests/dialects/test_presto.py2
-rw-r--r--tests/dialects/test_prql.py8
-rw-r--r--tests/dialects/test_snowflake.py23
-rw-r--r--tests/dialects/test_spark.py4
-rw-r--r--tests/dialects/test_tsql.py11
-rw-r--r--tests/fixtures/identity.sql5
-rw-r--r--tests/fixtures/optimizer/merge_subqueries.sql16
-rw-r--r--tests/fixtures/optimizer/normalize_identifiers.sql4
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql9
-rw-r--r--tests/fixtures/optimizer/simplify.sql5
-rw-r--r--tests/fixtures/optimizer/tpc-ds/tpc-ds.sql15
-rw-r--r--tests/fixtures/pretty.sql4
-rw-r--r--tests/test_expressions.py4
-rw-r--r--tests/test_optimizer.py11
-rw-r--r--tests/test_parser.py5
-rw-r--r--tests/test_transforms.py20
-rw-r--r--tests/test_transpile.py26
23 files changed, 247 insertions, 37 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 300d492..301cd57 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -20,6 +20,27 @@ class TestBigQuery(Validator):
maxDiff = None
def test_bigquery(self):
+ self.validate_identity(
+ "[a, a(1, 2,3,4444444444444444, tttttaoeunthaoentuhaoentuheoantu, toheuntaoheutnahoeunteoahuntaoeh), b(3, 4,5), c, d, tttttttttttttttteeeeeeeeeeeeeett, 12312312312]",
+ """[
+ a,
+ a(
+ 1,
+ 2,
+ 3,
+ 4444444444444444,
+ tttttaoeunthaoentuhaoentuheoantu,
+ toheuntaoheutnahoeunteoahuntaoeh
+ ),
+ b(3, 4, 5),
+ c,
+ d,
+ tttttttttttttttteeeeeeeeeeeeeett,
+ 12312312312
+]""",
+ pretty=True,
+ )
+
self.validate_all(
"SELECT STRUCT(1, 2, 3), STRUCT(), STRUCT('abc'), STRUCT(1, t.str_col), STRUCT(1 as a, 'abc' AS b), STRUCT(str_col AS abc)",
write={
@@ -40,6 +61,10 @@ class TestBigQuery(Validator):
"duckdb": "STRPTIME(x, '%Y-%m-%dT%H:%M:%S.%f%z')",
},
)
+ self.validate_identity(
+ "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%z', x)",
+ "PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%z', x)",
+ )
table = parse_one("x-0._y.z", dialect="bigquery", into=exp.Table)
self.assertEqual(table.catalog, "x-0")
@@ -57,9 +82,13 @@ class TestBigQuery(Validator):
self.assertEqual(exp.to_table("`x.y.z`", dialect="bigquery").sql("bigquery"), "`x.y.z`")
self.assertEqual(exp.to_table("`x`.`y`", dialect="bigquery").sql("bigquery"), "`x`.`y`")
+ column = self.validate_identity("SELECT `db.t`.`c` FROM `db.t`").selects[0]
+ self.assertEqual(len(column.parts), 3)
+
select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`")
self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF")
+ self.validate_identity("assert.true(1 = 1)")
self.validate_identity("SELECT ARRAY_TO_STRING(list, '--') AS text")
self.validate_identity("SELECT jsondoc['some_key']")
self.validate_identity("SELECT `p.d.UdF`(data).* FROM `p.d.t`")
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index df3caaf..af552d1 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -93,6 +93,9 @@ class TestClickhouse(Validator):
self.validate_identity("""SELECT JSONExtractString('{"x": {"y": 1}}', 'x', 'y')""")
self.validate_identity("SELECT * FROM table LIMIT 1 BY a, b")
self.validate_identity("SELECT * FROM table LIMIT 2 OFFSET 1 BY a, b")
+ self.validate_identity(
+ "SELECT id, quantileGK(100, 0.95)(reading) OVER (PARTITION BY id ORDER BY id RANGE BETWEEN 30000 PRECEDING AND CURRENT ROW) AS window FROM table"
+ )
self.validate_identity(
"SELECT $1$foo$1$",
@@ -409,6 +412,19 @@ class TestClickhouse(Validator):
self.validate_identity("SELECT FORMAT")
self.validate_identity("1 AS FORMAT").assert_is(exp.Alias)
+ self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d', '%T')")
+ self.validate_all(
+ "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+ read={
+ "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')",
+ "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+ },
+ write={
+ "clickhouse": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+ "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+ },
+ )
+
def test_cte(self):
self.validate_identity("WITH 'x' AS foo SELECT foo")
self.validate_identity("WITH ['c'] AS field_names SELECT field_names")
@@ -813,3 +829,30 @@ LIFETIME(MIN 0 MAX 0)""",
self.validate_identity(
"CREATE TABLE t1 (a String EPHEMERAL, b String EPHEMERAL func(), c String MATERIALIZED func(), d String ALIAS func()) ENGINE=TinyLog()"
)
+
+ def test_agg_functions(self):
+ def extract_agg_func(query):
+ return parse_one(query, read="clickhouse").selects[0].this
+
+ self.assertIsInstance(
+ extract_agg_func("select quantileGK(100, 0.95) OVER (PARTITION BY id) FROM table"),
+ exp.AnonymousAggFunc,
+ )
+ self.assertIsInstance(
+ extract_agg_func(
+ "select quantileGK(100, 0.95)(reading) OVER (PARTITION BY id) FROM table"
+ ),
+ exp.ParameterizedAgg,
+ )
+ self.assertIsInstance(
+ extract_agg_func("select quantileGKIf(100, 0.95) OVER (PARTITION BY id) FROM table"),
+ exp.CombinedAggFunc,
+ )
+ self.assertIsInstance(
+ extract_agg_func(
+ "select quantileGKIf(100, 0.95)(reading) OVER (PARTITION BY id) FROM table"
+ ),
+ exp.CombinedParameterizedAgg,
+ )
+
+ parse_one("foobar(x)").assert_is(exp.Anonymous)
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 691beb9..ea38521 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2426,3 +2426,18 @@ FROM c""",
"""CREATE TEMPORARY SEQUENCE seq START WITH = 1 INCREMENT BY = 2""",
"""CREATE TEMPORARY SEQUENCE seq START WITH 1 INCREMENT BY 2""",
)
+
+ def test_reserved_keywords(self):
+ order = exp.select("*").from_("order")
+
+ for dialect in ("presto", "redshift"):
+ dialect = Dialect.get_or_raise(dialect)
+ self.assertEqual(
+ order.sql(dialect=dialect),
+ f"SELECT * FROM {dialect.IDENTIFIER_START}order{dialect.IDENTIFIER_END}",
+ )
+
+ self.validate_identity(
+ """SELECT partition.d FROM t PARTITION (d)""",
+ """SELECT partition.d FROM t AS PARTITION(d)""",
+ )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 0b13a70..9105a49 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -652,8 +652,14 @@ class TestDuckDB(Validator):
"SELECT CAST('2020-05-06' AS DATE) + INTERVAL 5 DAY",
read={"bigquery": "SELECT DATE_ADD(CAST('2020-05-06' AS DATE), INTERVAL 5 DAY)"},
)
- self.validate_identity("SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t")
- self.validate_identity("SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t")
+ self.validate_identity(
+ "SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t",
+ "SELECT QUANTILE_CONT(y, 0.25 ORDER BY y DESC) FROM t",
+ )
+ self.validate_identity(
+ "SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY y DESC) FROM t",
+ "SELECT QUANTILE_DISC(y, 0.25 ORDER BY y DESC) FROM t",
+ )
self.validate_all(
"SELECT QUANTILE_CONT(x, q) FROM t",
write={
@@ -1075,6 +1081,15 @@ class TestDuckDB(Validator):
write={
"snowflake": "ALTER TABLE db.t1 RENAME TO db.t2",
"duckdb": "ALTER TABLE db.t1 RENAME TO t2",
+ "tsql": "EXEC sp_rename 'db.t1', 't2'",
+ },
+ )
+ self.validate_all(
+ 'ALTER TABLE "db"."t1" RENAME TO "db"."t2"',
+ write={
+ "snowflake": 'ALTER TABLE "db"."t1" RENAME TO "db"."t2"',
+ "duckdb": 'ALTER TABLE "db"."t1" RENAME TO "t2"',
+ "tsql": "EXEC sp_rename '[db].[t1]', 't2'",
},
)
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 33294ee..9215f05 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -429,6 +429,9 @@ class TestHive(Validator):
"INSERT OVERWRITE TABLE zipcodes PARTITION(state = 0) VALUES (896, 'US', 'TAMPA', 33607)"
)
self.validate_identity(
+ "INSERT OVERWRITE DIRECTORY 'x' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '' STORED AS TEXTFILE SELECT * FROM `a`.`b`"
+ )
+ self.validate_identity(
"SELECT a, b, SUM(c) FROM tabl AS t GROUP BY a, b, GROUPING SETS ((a, b), a)"
)
self.validate_identity(
@@ -524,9 +527,11 @@ class TestHive(Validator):
self.validate_all(
"APPROX_COUNT_DISTINCT(a)",
write={
+ "bigquery": "APPROX_COUNT_DISTINCT(a)",
"duckdb": "APPROX_COUNT_DISTINCT(a)",
"presto": "APPROX_DISTINCT(a)",
"hive": "APPROX_COUNT_DISTINCT(a)",
+ "snowflake": "APPROX_COUNT_DISTINCT(a)",
"spark": "APPROX_COUNT_DISTINCT(a)",
},
)
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 6558c97..e8af5c6 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -223,6 +223,7 @@ class TestMySQL(Validator):
self.validate_identity("CHAR(0)")
self.validate_identity("CHAR(77, 121, 83, 81, '76')")
self.validate_identity("CHAR(77, 77.3, '77.3' USING utf8mb4)")
+ self.validate_identity("SELECT * FROM t1 PARTITION(p0)")
def test_types(self):
self.validate_identity("CAST(x AS MEDIUMINT) + CAST(y AS YEAR(4))")
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index e1d8c06..4bafc08 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -871,7 +871,7 @@ class TestPresto(Validator):
"SELECT ARRAY_SORT(x, (left, right) -> -1)",
write={
"duckdb": "SELECT ARRAY_SORT(x)",
- "presto": "SELECT ARRAY_SORT(x, (left, right) -> -1)",
+ "presto": 'SELECT ARRAY_SORT(x, ("left", "right") -> -1)',
"hive": "SELECT SORT_ARRAY(x)",
"spark": "SELECT ARRAY_SORT(x, (left, right) -> -1)",
},
diff --git a/tests/dialects/test_prql.py b/tests/dialects/test_prql.py
index 69e2e28..1a0eec2 100644
--- a/tests/dialects/test_prql.py
+++ b/tests/dialects/test_prql.py
@@ -58,3 +58,11 @@ class TestPRQL(Validator):
self.validate_identity(
"from x intersect y", "SELECT * FROM x INTERSECT ALL SELECT * FROM y"
)
+ self.validate_identity(
+ "from x filter a == null filter null != b",
+ "SELECT * FROM x WHERE a IS NULL AND NOT b IS NULL",
+ )
+ self.validate_identity(
+ "from x filter (a > 1 || null != b || c != null)",
+ "SELECT * FROM x WHERE (a > 1 OR NOT b IS NULL OR NOT c IS NULL)",
+ )
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index b652541..1cbf68c 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -456,7 +456,7 @@ WHERE
},
write={
"": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x NULLS LAST){suffix}",
- "duckdb": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
+ "duckdb": f"SELECT QUANTILE_CONT(x, 0.5 ORDER BY x){suffix}",
"postgres": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
"snowflake": f"SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
},
@@ -821,6 +821,13 @@ WHERE
"snowflake": "CASE WHEN x = a OR (x IS NULL AND a IS NULL) THEN b WHEN x = c OR (x IS NULL AND c IS NULL) THEN d ELSE e END",
},
)
+ self.validate_all(
+ "SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col2) FROM t",
+ write={
+ "duckdb": "SELECT GROUP_CONCAT(col1, ', ' ORDER BY col2) FROM t",
+ "snowflake": "SELECT LISTAGG(col1, ', ') WITHIN GROUP (ORDER BY col2) FROM t",
+ },
+ )
def test_null_treatment(self):
self.validate_all(
@@ -1038,11 +1045,17 @@ WHERE
"SELECT CAST('2019-02-28' AS DATE) + INTERVAL '1 day, 1 year'",
)
- self.validate_identity("DATE(x)").assert_is(exp.Anonymous)
- self.validate_identity("TO_DATE(x)").assert_is(exp.Anonymous)
- self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.Anonymous)
+ self.validate_identity("TO_DATE(x)").assert_is(exp.TsOrDsToDate)
+ self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.TsOrDsToDate)
self.validate_all(
+ "DATE(x)",
+ write={
+ "duckdb": "CAST(x AS DATE)",
+ "snowflake": "TO_DATE(x)",
+ },
+ )
+ self.validate_all(
"TO_DATE(x, 'MM-DD-YYYY')",
write={
"snowflake": "TO_DATE(x, 'mm-DD-yyyy')",
@@ -1490,7 +1503,7 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene
write={
"bigquery": "REGEXP_EXTRACT(subject, pattern, pos, occ)",
"hive": "REGEXP_EXTRACT(subject, pattern, group)",
- "presto": "REGEXP_EXTRACT(subject, pattern, group)",
+ "presto": 'REGEXP_EXTRACT(subject, pattern, "group")',
"snowflake": "REGEXP_SUBSTR(subject, pattern, pos, occ, params, group)",
"spark": "REGEXP_EXTRACT(subject, pattern, group)",
},
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index d2285e0..7534573 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -234,7 +234,7 @@ TBLPROPERTIES (
self.validate_identity("first_value(col, true)", "FIRST_VALUE(col) IGNORE NULLS")
self.validate_identity("last(col, true)", "LAST(col) IGNORE NULLS")
self.validate_identity("last_value(col, true)", "LAST_VALUE(col) IGNORE NULLS")
- self.validate_identity("DESCRIBE EXTENDED db.table")
+ self.validate_identity("DESCRIBE EXTENDED db.tbl")
self.validate_identity("SELECT * FROM test TABLESAMPLE (50 PERCENT)")
self.validate_identity("SELECT * FROM test TABLESAMPLE (5 ROWS)")
self.validate_identity("SELECT * FROM test TABLESAMPLE (BUCKET 4 OUT OF 10)")
@@ -566,7 +566,7 @@ TBLPROPERTIES (
"ARRAY_SORT(x, (left, right) -> -1)",
write={
"duckdb": "ARRAY_SORT(x)",
- "presto": "ARRAY_SORT(x, (left, right) -> -1)",
+ "presto": 'ARRAY_SORT(x, ("left", "right") -> -1)',
"hive": "SORT_ARRAY(x)",
"spark": "ARRAY_SORT(x, (left, right) -> -1)",
},
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index aefd857..4a475f6 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -455,7 +455,6 @@ class TestTSQL(Validator):
self.validate_identity("CAST(x AS UNIQUEIDENTIFIER)")
self.validate_identity("CAST(x AS MONEY)")
self.validate_identity("CAST(x AS SMALLMONEY)")
- self.validate_identity("CAST(x AS ROWVERSION)")
self.validate_identity("CAST(x AS IMAGE)")
self.validate_identity("CAST(x AS SQL_VARIANT)")
self.validate_identity("CAST(x AS BIT)")
@@ -475,6 +474,16 @@ class TestTSQL(Validator):
"hive": "CAST(x AS TIMESTAMP)",
},
)
+ self.validate_all(
+ "CAST(x AS ROWVERSION)",
+ read={
+ "tsql": "CAST(x AS TIMESTAMP)",
+ },
+ write={
+ "tsql": "CAST(x AS ROWVERSION)",
+ "hive": "CAST(x AS BINARY)",
+ },
+ )
def test__types_ints(self):
self.validate_all(
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 5453a78..d51a978 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -659,6 +659,7 @@ 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
+INSERT INTO a.b PARTITION(DAY = '2024-04-14') (col1, col2) SELECT x FROM y
DELETE FROM x WHERE y > 1
DELETE FROM y
DELETE FROM event USING sales WHERE event.eventid = sales.eventid
@@ -690,6 +691,8 @@ INSERT INTO y (a, b, c) SELECT a, b, c FROM x
INSERT INTO y (SELECT 1) UNION (SELECT 2)
INSERT INTO result_table (WITH test AS (SELECT * FROM source_table) SELECT * FROM test)
INSERT INTO "tests_user" ("username", "first_name", "last_name") VALUES ('fiara', 'Fiara', 'Ironhide') RETURNING "tests_user"."id"
+INSERT INTO t1 (tc1 /* tc1 */, tc2 /* tc2 */) SELECT c1 /* sc1 */, c2 /* sc2 */ FROM t
+INSERT INTO t1 ("tc1" /* tc1 */, "tc2" /* tc2 */) SELECT "c1" /* sc1 */, "c2" /* sc2 */ FROM t
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
@@ -864,4 +867,4 @@ SELECT only
TRUNCATE(a, b)
SELECT enum
SELECT unlogged
-SELECT name
+SELECT name \ No newline at end of file
diff --git a/tests/fixtures/optimizer/merge_subqueries.sql b/tests/fixtures/optimizer/merge_subqueries.sql
index f953539..ce5a435 100644
--- a/tests/fixtures/optimizer/merge_subqueries.sql
+++ b/tests/fixtures/optimizer/merge_subqueries.sql
@@ -86,20 +86,20 @@ SELECT * FROM (SELECT * FROM (SELECT * FROM x)) ORDER BY a LIMIT 1;
SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a LIMIT 1;
# title: CTE
-WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x;
-SELECT x.a AS a, x.b AS b FROM x AS x;
+WITH x AS (SELECT a, b FROM main.x) SELECT a, b FROM x;
+SELECT x.a AS a, x.b AS b FROM main.x AS x;
# title: CTE with outer table alias
WITH y AS (SELECT a, b FROM x) SELECT a, b FROM y AS z;
SELECT x.a AS a, x.b AS b FROM x AS x;
# title: Nested CTE
-WITH x2 AS (SELECT a FROM x), x3 AS (SELECT a FROM x2) SELECT a FROM x3;
-SELECT x.a AS a FROM x AS x;
+WITH x2 AS (SELECT a FROM main.x), x3 AS (SELECT a FROM x2) SELECT a FROM x3;
+SELECT x.a AS a FROM main.x AS x;
# title: CTE WHERE clause is merged
-WITH x AS (SELECT a, b FROM x WHERE a > 1) SELECT a, SUM(b) AS b FROM x GROUP BY a;
-SELECT x.a AS a, SUM(x.b) AS b FROM x AS x WHERE x.a > 1 GROUP BY x.a;
+WITH x AS (SELECT a, b FROM main.x WHERE a > 1) SELECT a, SUM(b) AS b FROM x GROUP BY a;
+SELECT x.a AS a, SUM(x.b) AS b FROM main.x AS x WHERE x.a > 1 GROUP BY x.a;
# title: CTE Outer query has join
WITH x2 AS (SELECT a, b FROM x WHERE a > 1) SELECT a, c FROM x2 AS x JOIN y ON x.b = y.b;
@@ -110,8 +110,8 @@ WITH y AS (SELECT a, b FROM x AS q) SELECT a, b FROM y AS z;
SELECT q.a AS a, q.b AS b FROM x AS q;
# title: Nested CTE
-SELECT * FROM (WITH x AS (SELECT a, b FROM x) SELECT a, b FROM x);
-SELECT x.a AS a, x.b AS b FROM x AS x;
+SELECT * FROM (WITH x AS (SELECT a, b FROM main.x) SELECT a, b FROM x);
+SELECT x.a AS a, x.b AS b FROM main.x AS x;
# title: Inner select is an expression
SELECT a FROM (SELECT a FROM (SELECT COALESCE(a) AS a FROM x LEFT JOIN y ON x.a = y.b) AS x) AS x;
diff --git a/tests/fixtures/optimizer/normalize_identifiers.sql b/tests/fixtures/optimizer/normalize_identifiers.sql
index 7ddcc6d..b210fa7 100644
--- a/tests/fixtures/optimizer/normalize_identifiers.sql
+++ b/tests/fixtures/optimizer/normalize_identifiers.sql
@@ -68,8 +68,8 @@ SELECT a /* sqlglot.meta case_sensitive */, b FROM table /* sqlglot.meta case_se
SELECT a /* sqlglot.meta case_sensitive */, B FROM table /* sqlglot.meta case_sensitive */;
# dialect: redshift
-SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table;
-SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM table;
+SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM tbl;
+SELECT COALESCE(json_val.a /* sqlglot.meta case_sensitive */, json_val.A /* sqlglot.meta case_sensitive */) FROM tbl;
SELECT @X;
SELECT @X;
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 8baf961..b020a27 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -494,8 +494,13 @@ SELECT x AS x, y AS y FROM UNNEST([1, 2]) AS x WITH OFFSET AS y;
# dialect: bigquery
# execute: false
-select x, a, x.a from unnest([STRUCT(1 AS a)]) as x;
-SELECT x AS x, a AS a, x.a AS a FROM UNNEST([STRUCT(1 AS a)]) AS x;
+select x, a, x.a from unnest([STRUCT(1 AS a)]) as x CROSS JOIN m;
+SELECT x AS x, a AS a, x.a AS a FROM UNNEST([STRUCT(1 AS a)]) AS x CROSS JOIN m AS m;
+
+# dialect: bigquery
+# execute: false
+WITH cte AS (SELECT [STRUCT(1 AS a)] AS x) select a, x, m.a from cte, UNNEST(x) AS m CROSS JOIN n;
+WITH cte AS (SELECT [STRUCT(1 AS a)] AS x) SELECT a AS a, cte.x AS x, m.a AS a FROM cte AS cte, UNNEST(cte.x) AS m CROSS JOIN n AS n;
# dialect: presto
SELECT x.a, i.b FROM x CROSS JOIN UNNEST(SPLIT(CAST(b AS VARCHAR), ',')) AS i(b);
diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql
index a10942d..6af51bf 100644
--- a/tests/fixtures/optimizer/simplify.sql
+++ b/tests/fixtures/optimizer/simplify.sql
@@ -304,6 +304,9 @@ x * (1 - y);
(((x % 20) = 0) = TRUE);
((x % 20) = 0) = TRUE;
+ANY(t.value);
+ANY(t.value);
+
--------------------------------------
-- Literals
--------------------------------------
@@ -866,7 +869,7 @@ CONCAT_WS(sep, 'a', 'b');
CONCAT_WS(sep, 'a', 'b');
'a' || 'b' || x;
-CONCAT('ab', x);
+'ab' || x;
CONCAT(a, b) IN (SELECT * FROM foo WHERE cond);
CONCAT(a, b) IN (SELECT * FROM foo WHERE cond);
diff --git a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
index 35fbb70..a357b07 100644
--- a/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
+++ b/tests/fixtures/optimizer/tpc-ds/tpc-ds.sql
@@ -822,7 +822,7 @@ WITH "salesreturns" AS (
), "x" AS (
SELECT
'store channel' AS "channel",
- CONCAT('store', "ssr"."s_store_id") AS "id",
+ 'store' || "ssr"."s_store_id" AS "id",
"ssr"."sales" AS "sales",
"ssr"."returns1" AS "returns1",
"ssr"."profit" - "ssr"."profit_loss" AS "profit"
@@ -830,7 +830,7 @@ WITH "salesreturns" AS (
UNION ALL
SELECT
'catalog channel' AS "channel",
- CONCAT('catalog_page', "csr"."cp_catalog_page_id") AS "id",
+ 'catalog_page' || "csr"."cp_catalog_page_id" AS "id",
"csr"."sales" AS "sales",
"csr"."returns1" AS "returns1",
"csr"."profit" - "csr"."profit_loss" AS "profit"
@@ -838,7 +838,7 @@ WITH "salesreturns" AS (
UNION ALL
SELECT
'web channel' AS "channel",
- CONCAT('web_site', "wsr"."web_site_id") AS "id",
+ 'web_site' || "wsr"."web_site_id" AS "id",
"wsr"."sales" AS "sales",
"wsr"."returns1" AS "returns1",
"wsr"."profit" - "wsr"."profit_loss" AS "profit"
@@ -10927,7 +10927,7 @@ WITH "date_dim_2" AS (
), "x" AS (
SELECT
'store channel' AS "channel",
- CONCAT('store', "ssr"."store_id") AS "id",
+ 'store' || "ssr"."store_id" AS "id",
"ssr"."sales" AS "sales",
"ssr"."returns1" AS "returns1",
"ssr"."profit" AS "profit"
@@ -10935,7 +10935,7 @@ WITH "date_dim_2" AS (
UNION ALL
SELECT
'catalog channel' AS "channel",
- CONCAT('catalog_page', "csr"."catalog_page_id") AS "id",
+ 'catalog_page' || "csr"."catalog_page_id" AS "id",
"csr"."sales" AS "sales",
"csr"."returns1" AS "returns1",
"csr"."profit" AS "profit"
@@ -10943,7 +10943,7 @@ WITH "date_dim_2" AS (
UNION ALL
SELECT
'web channel' AS "channel",
- CONCAT('web_site', "wsr"."web_site_id") AS "id",
+ 'web_site' || "wsr"."web_site_id" AS "id",
"wsr"."sales" AS "sales",
"wsr"."returns1" AS "returns1",
"wsr"."profit" AS "profit"
@@ -11368,7 +11368,7 @@ ORDER BY c_customer_id
LIMIT 100;
SELECT
"customer"."c_customer_id" AS "customer_id",
- CONCAT("customer"."c_last_name", ', ', "customer"."c_first_name") AS "customername"
+ "customer"."c_last_name" || ', ' || "customer"."c_first_name" AS "customername"
FROM "customer" AS "customer"
JOIN "customer_address" AS "customer_address"
ON "customer"."c_current_addr_sk" = "customer_address"."ca_address_sk"
@@ -12743,4 +12743,3 @@ ORDER BY
"sm_type",
"cc_name"
LIMIT 100;
-
diff --git a/tests/fixtures/pretty.sql b/tests/fixtures/pretty.sql
index fac08be..62ba01c 100644
--- a/tests/fixtures/pretty.sql
+++ b/tests/fixtures/pretty.sql
@@ -336,8 +336,8 @@ SELECT * /* multi
comment */;
SELECT
* /* multi
- line
- comment */;
+ line
+ comment */;
WITH table_data AS (
SELECT 'bob' AS name, ARRAY['banana', 'apple', 'orange'] AS fruit_basket
)
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 85560b8..81cfb86 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -871,6 +871,8 @@ FROM foo""",
FROM foo""",
)
+ self.assertEqual(parse_one('max(x) as "a b" -- comment').comments, [" comment"])
+
def test_to_interval(self):
self.assertEqual(exp.to_interval("1day").sql(), "INTERVAL '1' DAY")
self.assertEqual(exp.to_interval(" 5 months").sql(), "INTERVAL '5' MONTHS")
@@ -1104,6 +1106,6 @@ FROM foo""",
parse_one("x").assert_is(exp.Column)
with self.assertRaisesRegex(
- AssertionError, "x is not <class 'sqlglot.expressions.Identifier'>\."
+ AssertionError, "x is not <class 'sqlglot.expressions.Identifier'>\\."
):
parse_one("x").assert_is(exp.Identifier)
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index c0b362c..758b60c 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -229,6 +229,17 @@ class TestOptimizer(unittest.TestCase):
@patch("sqlglot.generator.logger")
def test_qualify_columns(self, logger):
self.assertEqual(
+ optimizer.qualify.qualify(
+ parse_one(
+ "SELECT `my_db.my_table`.`my_column` FROM `my_db.my_table`",
+ read="bigquery",
+ ),
+ dialect="bigquery",
+ ).sql(dialect="bigquery"),
+ "SELECT `my_table`.`my_column` AS `my_column` FROM `my_db.my_table` AS `my_table`",
+ )
+
+ self.assertEqual(
optimizer.qualify_columns.qualify_columns(
parse_one(
"WITH RECURSIVE t AS (SELECT 1 AS x UNION ALL SELECT x + 1 FROM t AS child WHERE x < 10) SELECT * FROM t"
diff --git a/tests/test_parser.py b/tests/test_parser.py
index 791d352..6bcdb64 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -87,6 +87,9 @@ class TestParser(unittest.TestCase):
self.assertIsNotNone(parse_one("date").find(exp.Column))
+ def test_tuple(self):
+ parse_one("(a,)").assert_is(exp.Tuple)
+
def test_structs(self):
cast = parse_one("cast(x as struct<int>)")
self.assertIsInstance(cast.to.expressions[0], exp.DataType)
@@ -439,7 +442,7 @@ class TestParser(unittest.TestCase):
self.assertEqual(expression.comments, ["comment2"])
self.assertEqual(expression.this.comments, ["comment3"])
- self.assertEqual(expression.args.get("with").comments, ["comment1.1", "comment1.2"])
+ self.assertEqual(expression.args["with"].comments, ["comment1.1", "comment1.2"])
def test_type_literals(self):
self.assertEqual(parse_one("int 1"), parse_one("CAST(1 AS INT)"))
diff --git a/tests/test_transforms.py b/tests/test_transforms.py
index 8f14ae4..73d6705 100644
--- a/tests/test_transforms.py
+++ b/tests/test_transforms.py
@@ -111,6 +111,26 @@ class TestTransforms(unittest.TestCase):
"SELECT x FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY p)",
"SELECT x FROM (SELECT x, ROW_NUMBER() OVER (PARTITION BY p) AS _w, p FROM y) AS _t WHERE _w",
)
+ self.validate(
+ eliminate_qualify,
+ "SELECT x AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY z)",
+ "SELECT z FROM (SELECT x AS z, ROW_NUMBER() OVER (PARTITION BY x) AS _w, x FROM y) AS _t WHERE _w",
+ )
+ self.validate(
+ eliminate_qualify,
+ "SELECT SOME_UDF(x) AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY x ORDER BY z)",
+ "SELECT z FROM (SELECT SOME_UDF(x) AS z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY SOME_UDF(x)) AS _w, x FROM y) AS _t WHERE _w",
+ )
+ self.validate(
+ eliminate_qualify,
+ "SELECT x, t, x || t AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY x ORDER BY z DESC)",
+ "SELECT x, t, z FROM (SELECT x, t, x || t AS z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY x || t DESC) AS _w FROM y) AS _t WHERE _w",
+ )
+ self.validate(
+ eliminate_qualify,
+ "SELECT y.x AS x, y.t AS z FROM y QUALIFY ROW_NUMBER() OVER (PARTITION BY x ORDER BY x DESC, z)",
+ "SELECT x, z FROM (SELECT y.x AS x, y.t AS z, ROW_NUMBER() OVER (PARTITION BY y.x ORDER BY y.x DESC, y.t) AS _w, y.t FROM y) AS _t WHERE _w",
+ )
def test_remove_precision_parameterized_types(self):
self.validate(
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 95fba30..22085b3 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -124,6 +124,10 @@ class TestTranspile(unittest.TestCase):
"SELECT * FROM t1 /* x */ UNION ALL SELECT * FROM t2",
)
self.validate(
+ "/* comment */ SELECT * FROM a UNION SELECT * FROM b",
+ "/* comment */ SELECT * FROM a UNION SELECT * FROM b",
+ )
+ self.validate(
"SELECT * FROM t1\n/*x*/\nINTERSECT ALL SELECT * FROM t2",
"SELECT * FROM t1 /* x */ INTERSECT ALL SELECT * FROM t2",
)
@@ -524,6 +528,28 @@ INNER JOIN y
USING (id)""",
pretty=True,
)
+ self.validate(
+ """with x as (
+ SELECT *
+ /*
+NOTE: LEFT JOIN because blah blah blah
+ */
+ FROM a
+)
+select * from x""",
+ """WITH x AS (
+ SELECT
+ *
+ /*
+NOTE: LEFT JOIN because blah blah blah
+ */
+ FROM a
+)
+SELECT
+ *
+FROM x""",
+ pretty=True,
+ )
def test_types(self):
self.validate("INT 1", "CAST(1 AS INT)")
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-02 20:28:29 +0000