summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to 'tests')
-rw-r--r--tests/dialects/test_bigquery.py107
-rw-r--r--tests/dialects/test_clickhouse.py16
-rw-r--r--tests/dialects/test_dialect.py1
-rw-r--r--tests/dialects/test_duckdb.py1
-rw-r--r--tests/dialects/test_mysql.py4
-rw-r--r--tests/dialects/test_redshift.py30
-rw-r--r--tests/dialects/test_snowflake.py11
-rw-r--r--tests/dialects/test_starrocks.py3
-rw-r--r--tests/fixtures/identity.sql2
-rw-r--r--tests/test_parser.py5
-rw-r--r--tests/test_transforms.py38
11 files changed, 173 insertions, 45 deletions
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index c6cfe01..f881385 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -558,27 +558,6 @@ LANGUAGE js AS
},
)
self.validate_all(
- "SELECT FORMAT_DATE('%Y%m%d', '2023-12-25')",
- write={
- "bigquery": "SELECT FORMAT_DATE('%Y%m%d', '2023-12-25')",
- "duckdb": "SELECT STRFTIME(CAST('2023-12-25' AS DATE), '%Y%m%d')",
- },
- )
- self.validate_all(
- "SELECT FORMAT_DATETIME('%Y%m%d %H:%M:%S', DATETIME '2023-12-25 15:30:00')",
- write={
- "bigquery": "SELECT FORMAT_DATETIME('%Y%m%d %H:%M:%S', CAST('2023-12-25 15:30:00' AS DATETIME))",
- "duckdb": "SELECT STRFTIME(CAST('2023-12-25 15:30:00' AS TIMESTAMP), '%Y%m%d %H:%M:%S')",
- },
- )
- self.validate_all(
- "SELECT FORMAT_DATETIME('%x', '2023-12-25 15:30:00')",
- write={
- "bigquery": "SELECT FORMAT_DATETIME('%x', '2023-12-25 15:30:00')",
- "duckdb": "SELECT STRFTIME(CAST('2023-12-25 15:30:00' AS TIMESTAMP), '%x')",
- },
- )
- self.validate_all(
"SELECT COUNTIF(x)",
read={
"clickhouse": "SELECT countIf(x)",
@@ -685,7 +664,7 @@ LANGUAGE js AS
write={
"bigquery": "SELECT DATETIME_ADD('2023-01-01T00:00:00', INTERVAL '1' MILLISECOND)",
"databricks": "SELECT TIMESTAMPADD(MILLISECOND, '1', '2023-01-01T00:00:00')",
- "duckdb": "SELECT CAST('2023-01-01T00:00:00' AS DATETIME) + INTERVAL '1' MILLISECOND",
+ "duckdb": "SELECT CAST('2023-01-01T00:00:00' AS TIMESTAMP) + INTERVAL '1' MILLISECOND",
"snowflake": "SELECT TIMESTAMPADD(MILLISECOND, '1', '2023-01-01T00:00:00')",
},
),
@@ -696,7 +675,7 @@ LANGUAGE js AS
write={
"bigquery": "SELECT DATETIME_SUB('2023-01-01T00:00:00', INTERVAL '1' MILLISECOND)",
"databricks": "SELECT TIMESTAMPADD(MILLISECOND, '1' * -1, '2023-01-01T00:00:00')",
- "duckdb": "SELECT CAST('2023-01-01T00:00:00' AS DATETIME) - INTERVAL '1' MILLISECOND",
+ "duckdb": "SELECT CAST('2023-01-01T00:00:00' AS TIMESTAMP) - INTERVAL '1' MILLISECOND",
},
),
)
@@ -706,7 +685,7 @@ LANGUAGE js AS
write={
"bigquery": "SELECT DATETIME_TRUNC('2023-01-01T01:01:01', HOUR)",
"databricks": "SELECT DATE_TRUNC('HOUR', '2023-01-01T01:01:01')",
- "duckdb": "SELECT DATE_TRUNC('HOUR', CAST('2023-01-01T01:01:01' AS DATETIME))",
+ "duckdb": "SELECT DATE_TRUNC('HOUR', CAST('2023-01-01T01:01:01' AS TIMESTAMP))",
},
),
)
@@ -1611,6 +1590,55 @@ WHERE
"snowflake": """SELECT TRANSFORM(GET_PATH(PARSE_JSON('{"arr": [1, "a"]}'), 'arr'), x -> CAST(x AS VARCHAR))""",
},
)
+ self.validate_all(
+ "SELECT STRPOS('foo@example.com', '@')",
+ write={
+ "bigquery": "SELECT STRPOS('foo@example.com', '@')",
+ "duckdb": "SELECT STRPOS('foo@example.com', '@')",
+ "snowflake": "SELECT POSITION('@', 'foo@example.com')",
+ },
+ )
+ self.validate_all(
+ "SELECT ts + MAKE_INTERVAL(1, 2, minute => 5, day => 3)",
+ write={
+ "bigquery": "SELECT ts + MAKE_INTERVAL(1, 2, day => 3, minute => 5)",
+ "duckdb": "SELECT ts + INTERVAL '1 year 2 month 5 minute 3 day'",
+ "snowflake": "SELECT ts + INTERVAL '1 year, 2 month, 5 minute, 3 day'",
+ },
+ )
+ self.validate_all(
+ """SELECT INT64(JSON_QUERY(JSON '{"key": 2000}', '$.key'))""",
+ write={
+ "bigquery": """SELECT INT64(JSON_QUERY(PARSE_JSON('{"key": 2000}'), '$.key'))""",
+ "duckdb": """SELECT CAST(JSON('{"key": 2000}') -> '$.key' AS BIGINT)""",
+ "snowflake": """SELECT CAST(GET_PATH(PARSE_JSON('{"key": 2000}'), 'key') AS BIGINT)""",
+ },
+ )
+
+ self.validate_identity(
+ "CONTAINS_SUBSTRING(a, b, json_scope => 'JSON_KEYS_AND_VALUES')"
+ ).assert_is(exp.Anonymous)
+
+ self.validate_all(
+ """CONTAINS_SUBSTRING(a, b)""",
+ read={
+ "": "CONTAINS(a, b)",
+ "spark": "CONTAINS(a, b)",
+ "databricks": "CONTAINS(a, b)",
+ "snowflake": "CONTAINS(a, b)",
+ "duckdb": "CONTAINS(a, b)",
+ "oracle": "CONTAINS(a, b)",
+ },
+ write={
+ "": "CONTAINS(LOWER(a), LOWER(b))",
+ "spark": "CONTAINS(LOWER(a), LOWER(b))",
+ "databricks": "CONTAINS(LOWER(a), LOWER(b))",
+ "snowflake": "CONTAINS(LOWER(a), LOWER(b))",
+ "duckdb": "CONTAINS(LOWER(a), LOWER(b))",
+ "oracle": "CONTAINS(LOWER(a), LOWER(b))",
+ "bigquery": "CONTAINS_SUBSTRING(a, b)",
+ },
+ )
def test_errors(self):
with self.assertRaises(TokenError):
@@ -2213,3 +2241,34 @@ OPTIONS (
"databricks": "REGEXP_EXTRACT_ALL('a1_a2a3_a4A5a6', '(a)[0-9]')",
},
)
+
+ def test_format_temporal(self):
+ self.validate_all(
+ "SELECT FORMAT_DATE('%Y%m%d', '2023-12-25')",
+ write={
+ "bigquery": "SELECT FORMAT_DATE('%Y%m%d', '2023-12-25')",
+ "duckdb": "SELECT STRFTIME(CAST('2023-12-25' AS DATE), '%Y%m%d')",
+ },
+ )
+ self.validate_all(
+ "SELECT FORMAT_DATETIME('%Y%m%d %H:%M:%S', DATETIME '2023-12-25 15:30:00')",
+ write={
+ "bigquery": "SELECT FORMAT_DATETIME('%Y%m%d %H:%M:%S', CAST('2023-12-25 15:30:00' AS DATETIME))",
+ "duckdb": "SELECT STRFTIME(CAST('2023-12-25 15:30:00' AS TIMESTAMP), '%Y%m%d %H:%M:%S')",
+ },
+ )
+ self.validate_all(
+ "SELECT FORMAT_DATETIME('%x', '2023-12-25 15:30:00')",
+ write={
+ "bigquery": "SELECT FORMAT_DATETIME('%x', '2023-12-25 15:30:00')",
+ "duckdb": "SELECT STRFTIME(CAST('2023-12-25 15:30:00' AS TIMESTAMP), '%x')",
+ },
+ )
+ self.validate_all(
+ """SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2050-12-25 15:30:55+00")""",
+ write={
+ "bigquery": "SELECT FORMAT_TIMESTAMP('%b-%d-%Y', CAST('2050-12-25 15:30:55+00' AS TIMESTAMP))",
+ "duckdb": "SELECT STRFTIME(CAST(CAST('2050-12-25 15:30:55+00' AS TIMESTAMPTZ) AS TIMESTAMP), '%b-%d-%Y')",
+ "snowflake": "SELECT TO_CHAR(CAST(CAST('2050-12-25 15:30:55+00' AS TIMESTAMPTZ) AS TIMESTAMP), 'mon-DD-yyyy')",
+ },
+ )
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 5a4461e..19b3ce3 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -251,7 +251,7 @@ class TestClickhouse(Validator):
},
write={
"clickhouse": "SELECT CAST('2020-01-01' AS Nullable(DateTime)) + INTERVAL '500' MICROSECOND",
- "duckdb": "SELECT CAST('2020-01-01' AS DATETIME) + INTERVAL '500' MICROSECOND",
+ "duckdb": "SELECT CAST('2020-01-01' AS TIMESTAMP) + INTERVAL '500' MICROSECOND",
"postgres": "SELECT CAST('2020-01-01' AS TIMESTAMP) + INTERVAL '500 MICROSECOND'",
},
)
@@ -1245,3 +1245,17 @@ LIFETIME(MIN 0 MAX 0)""",
scopes = traverse_scope(parse_one(sql, dialect=self.dialect))
self.assertEqual(len(scopes), 1)
self.assertEqual(set(scopes[0].sources), {"t"})
+
+ def test_window_functions(self):
+ self.validate_identity(
+ "SELECT row_number(column1) OVER (PARTITION BY column2 ORDER BY column3) FROM table"
+ )
+ self.validate_identity(
+ "SELECT row_number() OVER (PARTITION BY column2 ORDER BY column3) FROM table"
+ )
+
+ def test_functions(self):
+ self.validate_identity("SELECT TRANSFORM(foo, [1, 2], ['first', 'second']) FROM table")
+ self.validate_identity(
+ "SELECT TRANSFORM(foo, [1, 2], ['first', 'second'], 'default') FROM table"
+ )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 170b64b..f0711fc 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1688,6 +1688,7 @@ class TestDialect(Validator):
"duckdb": "STRPOS(haystack, needle)",
"postgres": "STRPOS(haystack, needle)",
"presto": "STRPOS(haystack, needle)",
+ "bigquery": "STRPOS(haystack, needle)",
"spark": "LOCATE(needle, haystack)",
"clickhouse": "position(haystack, needle)",
"snowflake": "POSITION(needle, haystack)",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 3d4fe9c..6007e99 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -382,6 +382,7 @@ class TestDuckDB(Validator):
self.validate_identity(
"ATTACH DATABASE ':memory:' AS new_database", check_command_warning=True
)
+ self.validate_identity("DETACH DATABASE new_database", check_command_warning=True)
self.validate_identity(
"SELECT {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}"
)
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 52b04ea..d7aef4f 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -82,6 +82,10 @@ class TestMySQL(Validator):
"CREATE OR REPLACE VIEW my_view AS SELECT column1 AS `boo`, column2 AS `foo` FROM my_table WHERE column3 = 'some_value' UNION SELECT q.* FROM fruits_table, JSON_TABLE(Fruits, '$[*]' COLUMNS(id VARCHAR(255) PATH '$.$id', value VARCHAR(255) PATH '$.value')) AS q",
)
self.validate_identity(
+ "/*left*/ EXPLAIN SELECT /*hint*/ col FROM t1 /*right*/",
+ "/* left */ DESCRIBE /* hint */ SELECT col FROM t1 /* right */",
+ )
+ self.validate_identity(
"CREATE TABLE t (name VARCHAR)",
"CREATE TABLE t (name TEXT)",
)
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 01c7f78..971d81b 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -228,21 +228,21 @@ class TestRedshift(Validator):
self.validate_all(
"SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
write={
- "bigquery": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "databricks": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "drill": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "hive": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "mysql": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "oracle": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) _t WHERE _row_number = 1",
- "presto": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "redshift": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "snowflake": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "spark": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "sqlite": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "starrocks": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "tableau": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "teradata": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
- "trino": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "bigquery": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "databricks": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "drill": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "hive": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "mysql": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "oracle": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) _t WHERE _row_number = 1",
+ "presto": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "redshift": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "snowflake": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "spark": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "sqlite": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "starrocks": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "tableau": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "teradata": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "trino": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1",
"tsql": "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
},
)
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e2db661..515a07c 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -606,6 +606,17 @@ WHERE
},
)
self.validate_all(
+ "DIV0(a - b, c - d)",
+ write={
+ "snowflake": "IFF((c - d) = 0 AND NOT (a - b) IS NULL, 0, (a - b) / (c - d))",
+ "sqlite": "IIF((c - d) = 0 AND NOT (a - b) IS NULL, 0, CAST((a - b) AS REAL) / (c - d))",
+ "presto": "IF((c - d) = 0 AND NOT (a - b) IS NULL, 0, CAST((a - b) AS DOUBLE) / (c - d))",
+ "spark": "IF((c - d) = 0 AND NOT (a - b) IS NULL, 0, (a - b) / (c - d))",
+ "hive": "IF((c - d) = 0 AND NOT (a - b) IS NULL, 0, (a - b) / (c - d))",
+ "duckdb": "CASE WHEN (c - d) = 0 AND NOT (a - b) IS NULL THEN 0 ELSE (a - b) / (c - d) END",
+ },
+ )
+ self.validate_all(
"ZEROIFNULL(foo)",
write={
"snowflake": "IFF(foo IS NULL, 0, foo)",
diff --git a/tests/dialects/test_starrocks.py b/tests/dialects/test_starrocks.py
index 1edd7c6..bf72485 100644
--- a/tests/dialects/test_starrocks.py
+++ b/tests/dialects/test_starrocks.py
@@ -39,6 +39,9 @@ class TestStarrocks(Validator):
self.validate_identity(
"""SELECT CAST(PARSE_JSON(fieldvalue) -> '00000000-0000-0000-0000-00000000' AS VARCHAR) AS `code` FROM (SELECT '{"00000000-0000-0000-0000-00000000":"code01"}') AS t(fieldvalue)"""
)
+ self.validate_identity(
+ "SELECT text FROM example_table", write_sql="SELECT `text` FROM example_table"
+ )
def test_time(self):
self.validate_identity("TIMESTAMP('2022-01-01')")
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 33199de..0e13a64 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -882,4 +882,4 @@ GRANT SELECT ON TABLE tbl TO user
GRANT SELECT, INSERT ON FUNCTION tbl TO user
GRANT SELECT ON orders TO ROLE PUBLIC
GRANT SELECT ON nation TO alice WITH GRANT OPTION
-GRANT DELETE ON SCHEMA finance TO bob \ No newline at end of file
+GRANT DELETE ON SCHEMA finance TO bob
diff --git a/tests/test_parser.py b/tests/test_parser.py
index ba1240c..b60d719 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -879,3 +879,8 @@ class TestParser(unittest.TestCase):
expr = parse_one(sql)
self.assertIsInstance(expr, exp.Insert)
self.assertIsInstance(expr.expression.expressions[0].expressions[0], cls)
+
+ def test_drop_column(self):
+ ast = parse_one("ALTER TABLE tbl DROP COLUMN col")
+ self.assertEqual(len(list(ast.find_all(exp.Table))), 1)
+ self.assertEqual(len(list(ast.find_all(exp.Column))), 1)
diff --git a/tests/test_transforms.py b/tests/test_transforms.py
index e7d596c..b54cc01 100644
--- a/tests/test_transforms.py
+++ b/tests/test_transforms.py
@@ -55,17 +55,17 @@ class TestTransforms(unittest.TestCase):
self.validate(
eliminate_distinct_on,
"SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
- "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
)
self.validate(
eliminate_distinct_on,
"SELECT DISTINCT ON (a) a, b FROM x",
- "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a) AS _row_number FROM x) AS _t WHERE _row_number = 1",
)
self.validate(
eliminate_distinct_on,
"SELECT DISTINCT ON (a, b) a, b FROM x ORDER BY c DESC",
- "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ "SELECT a, b FROM (SELECT a AS a, b AS b, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
)
self.validate(
eliminate_distinct_on,
@@ -75,7 +75,37 @@ class TestTransforms(unittest.TestCase):
self.validate(
eliminate_distinct_on,
"SELECT DISTINCT ON (_row_number) _row_number FROM x ORDER BY c DESC",
- "SELECT _row_number FROM (SELECT _row_number, ROW_NUMBER() OVER (PARTITION BY _row_number ORDER BY c DESC) AS _row_number_2 FROM x) AS _t WHERE _row_number_2 = 1",
+ "SELECT _row_number FROM (SELECT _row_number AS _row_number, ROW_NUMBER() OVER (PARTITION BY _row_number ORDER BY c DESC) AS _row_number_2 FROM x) AS _t WHERE _row_number_2 = 1",
+ )
+ self.validate(
+ eliminate_distinct_on,
+ "SELECT DISTINCT ON (x.a, x.b) x.a, x.b FROM x ORDER BY c DESC",
+ "SELECT a, b FROM (SELECT x.a AS a, x.b AS b, ROW_NUMBER() OVER (PARTITION BY x.a, x.b ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ )
+ self.validate(
+ eliminate_distinct_on,
+ "SELECT DISTINCT ON (a) x.a, y.a FROM x CROSS JOIN y ORDER BY c DESC",
+ "SELECT a, a_2 FROM (SELECT x.a AS a, y.a AS a_2, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x CROSS JOIN y) AS _t WHERE _row_number = 1",
+ )
+ self.validate(
+ eliminate_distinct_on,
+ "SELECT DISTINCT ON (a) a, a + b FROM x ORDER BY c DESC",
+ "SELECT a, _col FROM (SELECT a AS a, a + b AS _col, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ )
+ self.validate(
+ eliminate_distinct_on,
+ "SELECT DISTINCT ON (a) * FROM x ORDER BY c DESC",
+ "SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1",
+ )
+ self.validate(
+ eliminate_distinct_on,
+ 'SELECT DISTINCT ON (a) a AS "A", b FROM x ORDER BY c DESC',
+ 'SELECT "A", b FROM (SELECT a AS "A", b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1',
+ )
+ self.validate(
+ eliminate_distinct_on,
+ 'SELECT DISTINCT ON (a) "A", b FROM x ORDER BY c DESC',
+ 'SELECT "A", b FROM (SELECT "A" AS "A", b AS b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1',
)
def test_eliminate_qualify(self):
generated by cgit v1.2.3 (git 2.39.1) at 2025-01-25 16:13:37 +0000