summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-04 07:24:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-01-04 07:24:05 +0000
commit621555af37594a213d91ea113d5fc7739af84d40 (patch)
tree5aaa3b586692062accffc21cfaaa5a3917ee77b3 /tests
parentAdding upstream version 10.2.9. (diff)
downloadsqlglot-621555af37594a213d91ea113d5fc7739af84d40.tar.xz
sqlglot-621555af37594a213d91ea113d5fc7739af84d40.zip
Adding upstream version 10.4.2.upstream/10.4.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests')
-rw-r--r--tests/dataframe/unit/test_column.py4
-rw-r--r--tests/dataframe/unit/test_functions.py4
-rw-r--r--tests/dialects/test_bigquery.py6
-rw-r--r--tests/dialects/test_databricks.py70
-rw-r--r--tests/dialects/test_dialect.py18
-rw-r--r--tests/dialects/test_duckdb.py19
-rw-r--r--tests/dialects/test_hive.py8
-rw-r--r--tests/dialects/test_mysql.py6
-rw-r--r--tests/dialects/test_postgres.py97
-rw-r--r--tests/dialects/test_redshift.py2
-rw-r--r--tests/dialects/test_snowflake.py14
-rw-r--r--tests/dialects/test_spark.py12
-rw-r--r--tests/dialects/test_tsql.py4
-rw-r--r--tests/fixtures/identity.sql35
-rw-r--r--tests/fixtures/optimizer/canonicalize.sql8
-rw-r--r--tests/fixtures/optimizer/optimizer.sql78
-rw-r--r--tests/fixtures/optimizer/pushdown_projections.sql20
-rw-r--r--tests/fixtures/optimizer/qualify_columns.sql24
-rw-r--r--tests/fixtures/optimizer/qualify_columns__with_invisible.sql8
-rw-r--r--tests/fixtures/optimizer/unnest_subqueries.sql56
-rw-r--r--tests/test_build.py13
-rw-r--r--tests/test_executor.py2
-rw-r--r--tests/test_expressions.py12
-rw-r--r--tests/test_optimizer.py29
-rw-r--r--tests/test_parser.py3
-rw-r--r--tests/test_transforms.py8
-rw-r--r--tests/test_transpile.py29
27 files changed, 480 insertions, 109 deletions
diff --git a/tests/dataframe/unit/test_column.py b/tests/dataframe/unit/test_column.py
index da18502..665cc91 100644
--- a/tests/dataframe/unit/test_column.py
+++ b/tests/dataframe/unit/test_column.py
@@ -150,8 +150,8 @@ class TestDataframeColumn(unittest.TestCase):
F.col("cola").between(datetime.date(2022, 1, 1), datetime.date(2022, 3, 1)).sql(),
)
self.assertEqual(
- "cola BETWEEN CAST('2022-01-01 01:01:01.000000' AS TIMESTAMP) "
- "AND CAST('2022-03-01 01:01:01.000000' AS TIMESTAMP)",
+ "cola BETWEEN CAST('2022-01-01T01:01:01+00:00' AS TIMESTAMP) "
+ "AND CAST('2022-03-01T01:01:01+00:00' AS TIMESTAMP)",
F.col("cola")
.between(datetime.datetime(2022, 1, 1, 1, 1, 1), datetime.datetime(2022, 3, 1, 1, 1, 1))
.sql(),
diff --git a/tests/dataframe/unit/test_functions.py b/tests/dataframe/unit/test_functions.py
index 99b140d..37ea2e1 100644
--- a/tests/dataframe/unit/test_functions.py
+++ b/tests/dataframe/unit/test_functions.py
@@ -30,7 +30,7 @@ class TestFunctions(unittest.TestCase):
test_date = SF.lit(datetime.date(2022, 1, 1))
self.assertEqual("TO_DATE('2022-01-01')", test_date.sql())
test_datetime = SF.lit(datetime.datetime(2022, 1, 1, 1, 1, 1))
- self.assertEqual("CAST('2022-01-01 01:01:01.000000' AS TIMESTAMP)", test_datetime.sql())
+ self.assertEqual("CAST('2022-01-01T01:01:01+00:00' AS TIMESTAMP)", test_datetime.sql())
test_dict = SF.lit({"cola": 1, "colb": "test"})
self.assertEqual("STRUCT(1 AS cola, 'test' AS colb)", test_dict.sql())
@@ -52,7 +52,7 @@ class TestFunctions(unittest.TestCase):
test_date = SF.col(datetime.date(2022, 1, 1))
self.assertEqual("TO_DATE('2022-01-01')", test_date.sql())
test_datetime = SF.col(datetime.datetime(2022, 1, 1, 1, 1, 1))
- self.assertEqual("CAST('2022-01-01 01:01:01.000000' AS TIMESTAMP)", test_datetime.sql())
+ self.assertEqual("CAST('2022-01-01T01:01:01+00:00' AS TIMESTAMP)", test_datetime.sql())
test_dict = SF.col({"cola": 1, "colb": "test"})
self.assertEqual("STRUCT(1 AS cola, 'test' AS colb)", test_dict.sql())
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 1d60ec6..258e47f 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -318,3 +318,9 @@ class TestBigQuery(Validator):
self.validate_identity(
"CREATE TABLE FUNCTION a(x INT64) RETURNS TABLE <q STRING, r INT64> AS SELECT s, t"
)
+
+ def test_group_concat(self):
+ self.validate_all(
+ "SELECT a, GROUP_CONCAT(b) FROM table GROUP BY a",
+ write={"bigquery": "SELECT a, STRING_AGG(b) FROM table GROUP BY a"},
+ )
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index 2168f55..7560d61 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -12,6 +12,76 @@ class TestDatabricks(Validator):
"databricks": "SELECT DATEDIFF(year, 'start', 'end')",
},
)
+ self.validate_all(
+ "SELECT DATEDIFF(microsecond, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(microsecond, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) * 1000000 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(millisecond, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(millisecond, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) * 1000 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(second, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(second, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(minute, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(minute, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 60 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(hour, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(hour, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 3600 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(day, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(day, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(epoch FROM CAST('end' AS TIMESTAMP) - CAST('start' AS TIMESTAMP)) / 86400 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(week, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(week, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 48 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 4 + EXTRACT(day FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) / 7 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(month, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(month, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 12 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(quarter, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(quarter, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) * 4 + EXTRACT(month FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) / 3 AS BIGINT)",
+ },
+ )
+ self.validate_all(
+ "SELECT DATEDIFF(year, 'start', 'end')",
+ write={
+ "databricks": "SELECT DATEDIFF(year, 'start', 'end')",
+ "postgres": "SELECT CAST(EXTRACT(year FROM AGE(CAST('end' AS TIMESTAMP), CAST('start' AS TIMESTAMP))) AS BIGINT)",
+ },
+ )
def test_add_date(self):
self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index ee67bf1..ced7102 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -333,7 +333,7 @@ class TestDialect(Validator):
"drill": "CAST('2020-01-01' AS DATE)",
"duckdb": "CAST('2020-01-01' AS DATE)",
"hive": "TO_DATE('2020-01-01')",
- "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%s')",
+ "presto": "CAST('2020-01-01' AS TIMESTAMP)",
"starrocks": "TO_DATE('2020-01-01')",
},
)
@@ -343,7 +343,7 @@ class TestDialect(Validator):
"drill": "CAST('2020-01-01' AS TIMESTAMP)",
"duckdb": "CAST('2020-01-01' AS TIMESTAMP)",
"hive": "CAST('2020-01-01' AS TIMESTAMP)",
- "presto": "DATE_PARSE('2020-01-01', '%Y-%m-%d %H:%i:%s')",
+ "presto": "CAST('2020-01-01' AS TIMESTAMP)",
},
)
self.validate_all(
@@ -723,23 +723,23 @@ class TestDialect(Validator):
read={
"postgres": "x->'y'",
"presto": "JSON_EXTRACT(x, 'y')",
- "starrocks": "x->'y'",
+ "starrocks": "x -> 'y'",
},
write={
"oracle": "JSON_EXTRACT(x, 'y')",
- "postgres": "x->'y'",
+ "postgres": "x -> 'y'",
"presto": "JSON_EXTRACT(x, 'y')",
- "starrocks": "x->'y'",
+ "starrocks": "x -> 'y'",
},
)
self.validate_all(
"JSON_EXTRACT_SCALAR(x, 'y')",
read={
- "postgres": "x->>'y'",
+ "postgres": "x ->> 'y'",
"presto": "JSON_EXTRACT_SCALAR(x, 'y')",
},
write={
- "postgres": "x->>'y'",
+ "postgres": "x ->> 'y'",
"presto": "JSON_EXTRACT_SCALAR(x, 'y')",
},
)
@@ -749,7 +749,7 @@ class TestDialect(Validator):
"postgres": "x#>'y'",
},
write={
- "postgres": "x#>'y'",
+ "postgres": "x #> 'y'",
},
)
self.validate_all(
@@ -758,7 +758,7 @@ class TestDialect(Validator):
"postgres": "x#>>'y'",
},
write={
- "postgres": "x#>>'y'",
+ "postgres": "x #>> 'y'",
},
)
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 99b0493..a37062c 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -59,7 +59,7 @@ class TestDuckDB(Validator):
"TO_TIMESTAMP(x)",
write={
"duckdb": "CAST(x AS TIMESTAMP)",
- "presto": "DATE_PARSE(x, '%Y-%m-%d %H:%i:%s')",
+ "presto": "CAST(x AS TIMESTAMP)",
"hive": "CAST(x AS TIMESTAMP)",
},
)
@@ -302,3 +302,20 @@ class TestDuckDB(Validator):
read="duckdb",
unsupported_level=ErrorLevel.IMMEDIATE,
)
+
+ def test_array(self):
+ self.validate_identity("ARRAY(SELECT id FROM t)")
+
+ def test_cast(self):
+ self.validate_all(
+ "123::CHARACTER VARYING",
+ write={
+ "duckdb": "CAST(123 AS TEXT)",
+ },
+ )
+
+ def test_bool_or(self):
+ self.validate_all(
+ "SELECT a, LOGICAL_OR(b) FROM table GROUP BY a",
+ write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
+ )
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 5ac8714..a7f3b8f 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -268,10 +268,10 @@ class TestHive(Validator):
self.validate_all(
"DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
write={
- "duckdb": "STRFTIME('2020-01-01', '%Y-%m-%d %H:%M:%S')",
- "presto": "DATE_FORMAT('2020-01-01', '%Y-%m-%d %H:%i:%S')",
- "hive": "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
- "spark": "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
+ "duckdb": "STRFTIME(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%M:%S')",
+ "presto": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), '%Y-%m-%d %H:%i:%S')",
+ "hive": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
+ "spark": "DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')",
},
)
self.validate_all(
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 5064dbe..7cd686d 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -91,12 +91,12 @@ class TestMySQL(Validator):
},
)
self.validate_all(
- "N 'some text'",
+ "N'some text'",
read={
- "mysql": "N'some text'",
+ "mysql": "n'some text'",
},
write={
- "mysql": "N 'some text'",
+ "mysql": "N'some text'",
},
)
self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 962b28b..1e048d5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -3,6 +3,7 @@ from tests.dialects.test_dialect import Validator
class TestPostgres(Validator):
+ maxDiff = None
dialect = "postgres"
def test_ddl(self):
@@ -94,6 +95,7 @@ class TestPostgres(Validator):
self.validate_identity("COMMENT ON TABLE mytable IS 'this'")
self.validate_identity("SELECT e'\\xDEADBEEF'")
self.validate_identity("SELECT CAST(e'\\176' AS BYTEA)")
+ self.validate_identity("""SELECT * FROM JSON_TO_RECORDSET(z) AS y("rank" INT)""")
self.validate_all(
"END WORK AND NO CHAIN",
@@ -112,6 +114,14 @@ class TestPostgres(Validator):
"spark": "CREATE TABLE x (a UUID, b BINARY)",
},
)
+ self.validate_all(
+ "123::CHARACTER VARYING",
+ write={"postgres": "CAST(123 AS VARCHAR)"},
+ )
+ self.validate_all(
+ "TO_TIMESTAMP(123::DOUBLE PRECISION)",
+ write={"postgres": "TO_TIMESTAMP(CAST(123 AS DOUBLE PRECISION))"},
+ )
self.validate_identity(
"CREATE TABLE A (LIKE B INCLUDING CONSTRAINT INCLUDING COMPRESSION EXCLUDING COMMENTS)"
@@ -193,15 +203,21 @@ class TestPostgres(Validator):
},
)
self.validate_all(
- "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
- read={
+ "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) pname ON TRUE WHERE pname IS NULL",
+ write={
"postgres": "SELECT m.name FROM manufacturers AS m LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE WHERE pname IS NULL",
},
)
self.validate_all(
"SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) v1, LATERAL VERTICES(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
- read={
- "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",
+ write={
+ "postgres": "SELECT p1.id, p2.id, v1, v2 FROM polygons AS p1, polygons AS p2, LATERAL VERTICES(p1.poly) AS v1, LATERAL VERTICES(p2.poly) AS v2 WHERE (v1 <-> v2) < 10 AND p1.id <> p2.id",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM r CROSS JOIN LATERAL unnest(array(1)) AS s(location)",
+ write={
+ "postgres": "SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)",
},
)
self.validate_all(
@@ -218,35 +234,46 @@ class TestPostgres(Validator):
)
self.validate_all(
"'[1,2,3]'::json->2",
- write={"postgres": "CAST('[1,2,3]' AS 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'"""},
+ 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'"""},
+ 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'"""},
+ 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'"},
+ 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'"""},
+ 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}'"""},
+ 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}'"""},
+ write={"postgres": """CAST('{"a":[1,2,3],"b":[4,5,6]}' AS JSON) #>> '{a,2}'"""},
+ )
+ self.validate_all(
+ """SELECT JSON_ARRAY_ELEMENTS((foo->'sections')::JSON) AS sections""",
+ write={
+ "postgres": """SELECT JSON_ARRAY_ELEMENTS(CAST((foo -> 'sections') AS JSON)) AS sections""",
+ "presto": """SELECT JSON_ARRAY_ELEMENTS(CAST((JSON_EXTRACT(foo, 'sections')) AS JSON)) AS sections""",
+ },
+ )
+ self.validate_all(
+ """x ? 'x'""",
+ write={"postgres": "x ? 'x'"},
)
self.validate_all(
"SELECT $$a$$",
@@ -260,3 +287,49 @@ class TestPostgres(Validator):
"UPDATE MYTABLE T1 SET T1.COL = 13",
write={"postgres": "UPDATE MYTABLE AS T1 SET T1.COL = 13"},
)
+
+ self.validate_identity("x ~ 'y'")
+ self.validate_identity("x ~* 'y'")
+ self.validate_all(
+ "x !~ 'y'",
+ write={"postgres": "NOT x ~ 'y'"},
+ )
+ self.validate_all(
+ "x !~* 'y'",
+ write={"postgres": "NOT x ~* 'y'"},
+ )
+
+ self.validate_all(
+ "x ~~ 'y'",
+ write={"postgres": "x LIKE 'y'"},
+ )
+ self.validate_all(
+ "x ~~* 'y'",
+ write={"postgres": "x ILIKE 'y'"},
+ )
+ self.validate_all(
+ "x !~~ 'y'",
+ write={"postgres": "NOT x LIKE 'y'"},
+ )
+ self.validate_all(
+ "x !~~* 'y'",
+ write={"postgres": "NOT x ILIKE 'y'"},
+ )
+ self.validate_all(
+ "'45 days'::interval day",
+ write={"postgres": "CAST('45 days' AS INTERVAL day)"},
+ )
+ self.validate_all(
+ "'x' 'y' 'z'",
+ write={"postgres": "CONCAT('x', 'y', 'z')"},
+ )
+ self.validate_identity("SELECT ARRAY(SELECT 1)")
+
+ self.validate_all(
+ "x::cstring",
+ write={"postgres": "CAST(x AS CSTRING)"},
+ )
+
+ self.validate_identity(
+ "SELECT SUM(x) OVER a, SUM(y) OVER b FROM c WINDOW a AS (PARTITION BY d), b AS (PARTITION BY e)"
+ )
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 3034df5..f650c98 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -53,7 +53,7 @@ class TestRedshift(Validator):
self.validate_all(
"SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC",
write={
- "redshift": 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS "_row_number" FROM x) 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) WHERE "_row_number" = 1',
},
)
self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e3d0cff..df62c6c 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -7,6 +7,12 @@ class TestSnowflake(Validator):
def test_snowflake(self):
self.validate_all(
+ "SELECT * FROM xxx WHERE col ilike '%Don''t%'",
+ write={
+ "snowflake": "SELECT * FROM xxx WHERE col ILIKE '%Don\\'t%'",
+ },
+ )
+ self.validate_all(
'x:a:"b c"',
write={
"duckdb": "x['a']['b c']",
@@ -509,3 +515,11 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS f, LATERA
"snowflake": "SELECT 1 MINUS SELECT 1",
},
)
+
+ def test_values(self):
+ self.validate_all(
+ 'SELECT c0, c1 FROM (VALUES (1, 2), (3, 4)) AS "t0"(c0, c1)',
+ read={
+ "spark": "SELECT `c0`, `c1` FROM (VALUES (1, 2), (3, 4)) AS `t0`(`c0`, `c1`)",
+ },
+ )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index 3a9f918..7395e72 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -101,6 +101,18 @@ TBLPROPERTIES (
"spark": "CACHE TABLE testCache OPTIONS('storageLevel' = 'DISK_ONLY') AS SELECT * FROM testData"
},
)
+ self.validate_all(
+ "ALTER TABLE StudentInfo ADD COLUMNS (LastName STRING, DOB TIMESTAMP)",
+ write={
+ "spark": "ALTER TABLE StudentInfo ADD COLUMNS (LastName STRING, DOB TIMESTAMP)",
+ },
+ )
+ self.validate_all(
+ "ALTER TABLE StudentInfo DROP COLUMNS (LastName, DOB)",
+ write={
+ "spark": "ALTER TABLE StudentInfo DROP COLUMNS (LastName, DOB)",
+ },
+ )
def test_to_date(self):
self.validate_all(
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index e4c6e60..b4ac094 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -431,11 +431,11 @@ class TestTSQL(Validator):
def test_string(self):
self.validate_all(
"SELECT N'test'",
- write={"spark": "SELECT 'test'"},
+ write={"spark": "SELECT N'test'"},
)
self.validate_all(
"SELECT n'test'",
- write={"spark": "SELECT 'test'"},
+ write={"spark": "SELECT N'test'"},
)
self.validate_all(
"SELECT '''test'''",
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index e12b673..e6a6e6b 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -17,6 +17,7 @@ SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) / y
'\x'
"x"
""
+N'abc'
x
x % 1
x < 1
@@ -33,6 +34,10 @@ x << 1
x >> 1
x >> 1 | 1 & 1 ^ 1
x || y
+x[ : ]
+x[1 : ]
+x[1 : 2]
+x[-4 : -1]
1 - -1
- -5
dec.x + y
@@ -62,6 +67,8 @@ x BETWEEN 'a' || b AND 'c' || d
NOT x IS NULL
x IS TRUE
x IS FALSE
+x IS TRUE IS TRUE
+x LIKE y IS TRUE
time
zone
ARRAY<TEXT>
@@ -93,10 +100,11 @@ x LIKE '%y%' ESCAPE '\'
x ILIKE '%y%' ESCAPE '\'
1 AS escape
INTERVAL '1' day
-INTERVAL '1' month
+INTERVAL '1' MONTH
INTERVAL '1 day'
INTERVAL 2 months
-INTERVAL 1 + 3 days
+INTERVAL 1 + 3 DAYS
+CAST('45' AS INTERVAL DAYS)
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), 1, DAY)
DATETIME_DIFF(CURRENT_DATE, 1, DAY)
QUANTILE(x, 0.5)
@@ -144,6 +152,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 COALESCE(offset, 1)
SELECT t.count
SELECT DISTINCT x FROM test
SELECT DISTINCT x, y FROM test
@@ -196,6 +205,7 @@ SELECT JSON_EXTRACT_SCALAR(x, '$.name')
SELECT x LIKE '%x%' FROM test
SELECT * FROM test LIMIT 100
SELECT * FROM test LIMIT 100 OFFSET 200
+SELECT * FROM test FETCH FIRST ROWS ONLY
SELECT * FROM test FETCH FIRST 1 ROWS ONLY
SELECT * FROM test FETCH NEXT 1 ROWS ONLY
SELECT (1 > 2) AS x FROM test
@@ -460,6 +470,7 @@ CREATE TABLE z (end INT)
CREATE TABLE z (a ARRAY<TEXT>, b MAP<TEXT, DOUBLE>, c DECIMAL(5, 3))
CREATE TABLE z (a INT, b VARCHAR COMMENT 'z', c VARCHAR(100) COMMENT 'z', d DECIMAL(5, 3))
CREATE TABLE z (a INT(11) DEFAULT UUID())
+CREATE TABLE z (n INT DEFAULT 0 NOT NULL)
CREATE TABLE z (a INT(11) DEFAULT NULL COMMENT '客户id')
CREATE TABLE z (a INT(11) NOT NULL DEFAULT 1)
CREATE TABLE z (a INT(11) NOT NULL DEFAULT -1)
@@ -511,7 +522,13 @@ INSERT OVERWRITE TABLE a.b PARTITION(ds) SELECT x FROM y
INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD') SELECT x FROM y
INSERT OVERWRITE TABLE a.b PARTITION(ds, hour) SELECT x FROM y
INSERT OVERWRITE TABLE a.b PARTITION(ds='YYYY-MM-DD', hour='hh') SELECT x FROM y
+ALTER AGGREGATE bla(foo) OWNER TO CURRENT_USER
+ALTER RULE foo ON bla RENAME TO baz
+ALTER ROLE CURRENT_USER WITH REPLICATION
+ALTER SEQUENCE IF EXISTS baz RESTART WITH boo
ALTER TYPE electronic_mail RENAME TO email
+ALTER VIEW foo ALTER COLUMN bla SET DEFAULT 'NOT SET'
+ALTER DOMAIN foo VALIDATE CONSTRAINT bla
ANALYZE a.y
DELETE FROM x WHERE y > 1
DELETE FROM y
@@ -596,3 +613,17 @@ SELECT x AS INTO FROM bla
SELECT * INTO newevent FROM event
SELECT * INTO TEMPORARY newevent FROM event
SELECT * INTO UNLOGGED newevent FROM event
+ALTER TABLE integers ADD COLUMN k INT
+ALTER TABLE integers ADD COLUMN IF NOT EXISTS k INT
+ALTER TABLE IF EXISTS integers ADD COLUMN k INT
+ALTER TABLE integers ADD COLUMN l INT DEFAULT 10
+ALTER TABLE measurements ADD COLUMN mtime TIMESTAMPTZ DEFAULT NOW()
+ALTER TABLE integers DROP COLUMN k
+ALTER TABLE integers DROP COLUMN IF EXISTS k
+ALTER TABLE integers DROP COLUMN k CASCADE
+ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR
+ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR USING CONCAT(i, '_', j)
+ALTER TABLE integers ALTER COLUMN i SET DEFAULT 10
+ALTER TABLE integers ALTER COLUMN i DROP DEFAULT
+ALTER TABLE mydataset.mytable DROP COLUMN A, DROP COLUMN IF EXISTS B
+ALTER TABLE mydataset.mytable ADD COLUMN A TEXT, ADD COLUMN IF NOT EXISTS B INT
diff --git a/tests/fixtures/optimizer/canonicalize.sql b/tests/fixtures/optimizer/canonicalize.sql
index 8880881..8c7cd45 100644
--- a/tests/fixtures/optimizer/canonicalize.sql
+++ b/tests/fixtures/optimizer/canonicalize.sql
@@ -1,11 +1,11 @@
SELECT w.d + w.e AS c FROM w AS w;
-SELECT CONCAT(w.d, w.e) AS c FROM w AS w;
+SELECT CONCAT("w"."d", "w"."e") AS "c" FROM "w" AS "w";
SELECT CAST(w.d AS DATE) > w.e AS a FROM w AS w;
-SELECT CAST(w.d AS DATE) > CAST(w.e AS DATE) AS a FROM w AS w;
+SELECT CAST("w"."d" AS DATE) > CAST("w"."e" AS DATE) AS "a" FROM "w" AS "w";
SELECT CAST(1 AS VARCHAR) AS a FROM w AS w;
-SELECT CAST(1 AS VARCHAR) AS a FROM w AS w;
+SELECT CAST(1 AS VARCHAR) AS "a" FROM "w" AS "w";
SELECT CAST(1 + 3.2 AS DOUBLE) AS a FROM w AS w;
-SELECT 1 + 3.2 AS a FROM w AS w;
+SELECT 1 + 3.2 AS "a" FROM "w" AS "w";
diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql
index a692c7d..b502d81 100644
--- a/tests/fixtures/optimizer/optimizer.sql
+++ b/tests/fixtures/optimizer/optimizer.sql
@@ -291,3 +291,81 @@ SELECT a1 FROM cte1;
SELECT
"x"."a" AS "a1"
FROM "x" AS "x";
+
+# title: recursive cte
+WITH RECURSIVE cte1 AS (
+ SELECT *
+ FROM (
+ SELECT 1 AS a, 2 AS b
+ ) base
+ CROSS JOIN (SELECT 3 c) y
+ UNION ALL
+ SELECT *
+ FROM cte1
+ WHERE a < 1
+)
+SELECT *
+FROM cte1;
+WITH RECURSIVE "base" AS (
+ SELECT
+ 1 AS "a",
+ 2 AS "b"
+), "y" AS (
+ SELECT
+ 3 AS "c"
+), "cte1" AS (
+ SELECT
+ "base"."a" AS "a",
+ "base"."b" AS "b",
+ "y"."c" AS "c"
+ FROM "base" AS "base"
+ CROSS JOIN "y" AS "y"
+ UNION ALL
+ SELECT
+ "cte1"."a" AS "a",
+ "cte1"."b" AS "b",
+ "cte1"."c" AS "c"
+ FROM "cte1"
+ WHERE
+ "cte1"."a" < 1
+)
+SELECT
+ "cte1"."a" AS "a",
+ "cte1"."b" AS "b",
+ "cte1"."c" AS "c"
+FROM "cte1";
+
+# title: right join should not push down to from
+SELECT x.a, y.b
+FROM x
+RIGHT JOIN y
+ON x.a = y.b
+WHERE x.b = 1;
+SELECT
+ "x"."a" AS "a",
+ "y"."b" AS "b"
+FROM "x" AS "x"
+RIGHT JOIN "y" AS "y"
+ ON "x"."a" = "y"."b"
+WHERE
+ "x"."b" = 1;
+
+# title: right join can push down to itself
+SELECT x.a, y.b
+FROM x
+RIGHT JOIN y
+ON x.a = y.b
+WHERE y.b = 1;
+WITH "y_2" AS (
+ SELECT
+ "y"."b" AS "b"
+ FROM "y" AS "y"
+ WHERE
+ "y"."b" = 1
+)
+SELECT
+ "x"."a" AS "a",
+ "y"."b" AS "b"
+FROM "x" AS "x"
+RIGHT JOIN "y_2" AS "y"
+ ON "x"."a" = "y"."b";
diff --git a/tests/fixtures/optimizer/pushdown_projections.sql b/tests/fixtures/optimizer/pushdown_projections.sql
index ba4bf45..2a21f65 100644
--- a/tests/fixtures/optimizer/pushdown_projections.sql
+++ b/tests/fixtures/optimizer/pushdown_projections.sql
@@ -1,32 +1,32 @@
SELECT a FROM (SELECT * FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
SELECT 1 FROM (SELECT * FROM x) WHERE b = 2;
-SELECT 1 AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS "_q_0" WHERE "_q_0".b = 2;
+SELECT 1 AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS _q_0 WHERE _q_0.b = 2;
SELECT (SELECT c FROM y WHERE q.b = y.b) FROM (SELECT * FROM x) AS q;
-SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS "_col_0" FROM (SELECT x.b AS b FROM x AS x) AS q;
+SELECT (SELECT y.c AS c FROM y AS y WHERE q.b = y.b) AS _col_0 FROM (SELECT x.b AS b FROM x AS x) AS q;
SELECT a FROM x JOIN (SELECT b, c FROM y) AS z ON x.b = z.b;
SELECT x.a AS a FROM x AS x JOIN (SELECT y.b AS b FROM y AS y) AS z ON x.b = z.b;
SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
-SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2;
+SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
SELECT x1.a FROM (SELECT * FROM x) AS x1, (SELECT * FROM x) AS x2;
-SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS "_" FROM x AS x) AS x2;
+SELECT x1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS x1, (SELECT 1 AS _ FROM x AS x) AS x2;
SELECT a FROM (SELECT DISTINCT a, b FROM x);
-SELECT "_q_0".a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT DISTINCT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
SELECT a FROM (SELECT a, b FROM x UNION ALL SELECT a, b FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION ALL SELECT x.a AS a FROM x AS x) AS _q_0;
WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x UNION ALL SELECT z.b AS b, z.c AS c FROM z) SELECT a, b FROM t1;
WITH t1 AS (SELECT x.a AS a, x.b AS b FROM x AS x UNION ALL SELECT z.b AS b, z.c AS c FROM z AS z) SELECT t1.a AS a, t1.b AS b FROM t1;
SELECT a FROM (SELECT a, b FROM x UNION SELECT a, b FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x UNION SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
WITH y AS (SELECT * FROM x) SELECT a FROM y;
WITH y AS (SELECT x.a AS a FROM x AS x) SELECT y.a AS a FROM y;
@@ -38,10 +38,10 @@ WITH z AS (SELECT * FROM x) SELECT a FROM z UNION SELECT a FROM z;
WITH z AS (SELECT x.a AS a FROM x AS x) SELECT z.a AS a FROM z UNION SELECT z.a AS a FROM z;
SELECT b FROM (SELECT a, SUM(b) AS b FROM x GROUP BY a);
-SELECT "_q_0".b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS "_q_0";
+SELECT _q_0.b AS b FROM (SELECT SUM(x.b) AS b FROM x AS x GROUP BY x.a) AS _q_0;
SELECT b FROM (SELECT a, SUM(b) AS b FROM x ORDER BY a);
-SELECT "_q_0".b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS "_q_0";
+SELECT _q_0.b AS b FROM (SELECT x.a AS a, SUM(x.b) AS b FROM x AS x ORDER BY a) AS _q_0;
SELECT x FROM (VALUES(1, 2)) AS q(x, y);
SELECT q.x AS x FROM (VALUES (1, 2)) AS q(x, y);
diff --git a/tests/fixtures/optimizer/qualify_columns.sql b/tests/fixtures/optimizer/qualify_columns.sql
index 1176078..9c5a0be 100644
--- a/tests/fixtures/optimizer/qualify_columns.sql
+++ b/tests/fixtures/optimizer/qualify_columns.sql
@@ -21,15 +21,15 @@ SELECT x.a AS b FROM x AS x;
# execute: false
SELECT 1, 2 FROM x;
-SELECT 1 AS "_col_0", 2 AS "_col_1" FROM x AS x;
+SELECT 1 AS _col_0, 2 AS _col_1 FROM x AS x;
# execute: false
SELECT a + b FROM x;
-SELECT x.a + x.b AS "_col_0" FROM x AS x;
+SELECT x.a + x.b AS _col_0 FROM x AS x;
# execute: false
SELECT a, SUM(b) FROM x WHERE a > 1 AND b > 1 GROUP BY a;
-SELECT x.a AS a, SUM(x.b) AS "_col_1" FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a;
+SELECT x.a AS a, SUM(x.b) AS _col_1 FROM x AS x WHERE x.a > 1 AND x.b > 1 GROUP BY x.a;
SELECT SUM(a) AS c FROM x HAVING SUM(a) > 3;
SELECT SUM(x.a) AS c FROM x AS x HAVING SUM(x.a) > 3;
@@ -59,7 +59,7 @@ SELECT SUM(x.a) AS c, SUM(x.b) AS d FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
# execute: false
SELECT SUM(a), SUM(b) AS c FROM x ORDER BY 1, 2;
-SELECT SUM(x.a) AS "_col_0", SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
+SELECT SUM(x.a) AS _col_0, SUM(x.b) AS c FROM x AS x ORDER BY SUM(x.a), SUM(x.b);
SELECT a AS j, b FROM x GROUP BY j, b;
SELECT x.a AS j, x.b AS b FROM x AS x GROUP BY x.a, x.b;
@@ -72,7 +72,7 @@ SELECT x.a AS a, x.b AS b FROM x AS x ORDER BY x.a, x.b;
# execute: false
SELECT DATE(a), DATE(b) AS c FROM x GROUP BY 1, 2;
-SELECT DATE(x.a) AS "_col_0", DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b);
+SELECT DATE(x.a) AS _col_0, DATE(x.b) AS c FROM x AS x GROUP BY DATE(x.a), DATE(x.b);
SELECT SUM(x.a) AS c FROM x JOIN y ON x.b = y.b GROUP BY c;
SELECT SUM(x.a) AS c FROM x AS x JOIN y AS y ON x.b = y.b GROUP BY y.c;
@@ -130,10 +130,10 @@ SELECT a FROM (SELECT a FROM x AS x) y;
SELECT y.a AS a FROM (SELECT x.a AS a FROM x AS x) AS y;
SELECT a FROM (SELECT a AS a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
SELECT a FROM (SELECT a FROM (SELECT a FROM x));
-SELECT "_q_1".a AS a FROM (SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0") AS "_q_1";
+SELECT _q_1.a AS a FROM (SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0) AS _q_1;
SELECT x.a FROM x AS x JOIN (SELECT * FROM x) AS y ON x.a = y.a;
SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a, x.b AS b FROM x AS x) AS y ON x.a = y.a;
@@ -157,7 +157,7 @@ SELECT a FROM x UNION SELECT a FROM x UNION SELECT a FROM x;
SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x;
SELECT a FROM (SELECT a FROM x UNION SELECT a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x UNION SELECT x.a AS a FROM x AS x) AS _q_0;
--------------------------------------
-- Subqueries
@@ -167,10 +167,10 @@ SELECT x.a AS a FROM x AS x WHERE x.b IN (SELECT y.c AS c FROM y AS y);
# execute: false
SELECT (SELECT c FROM y) FROM x;
-SELECT (SELECT y.c AS c FROM y AS y) AS "_col_0" FROM x AS x;
+SELECT (SELECT y.c AS c FROM y AS y) AS _col_0 FROM x AS x;
SELECT a FROM (SELECT a FROM x) WHERE a IN (SELECT b FROM (SELECT b FROM y));
-SELECT "_q_1".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_1" WHERE "_q_1".a IN (SELECT "_q_0".b AS b FROM (SELECT y.b AS b FROM y AS y) AS "_q_0");
+SELECT _q_1.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_1 WHERE _q_1.a IN (SELECT _q_0.b AS b FROM (SELECT y.b AS b FROM y AS y) AS _q_0);
--------------------------------------
-- Correlated subqueries
@@ -215,10 +215,10 @@ SELECT x.*, y.* FROM x JOIN y ON x.b = y.b;
SELECT x.a AS a, x.b AS b, y.b AS b, y.c AS c FROM x AS x JOIN y AS y ON x.b = y.b;
SELECT a FROM (SELECT * FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a, x.b AS b FROM x AS x) AS _q_0;
SELECT * FROM (SELECT a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
--------------------------------------
-- CTEs
diff --git a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
index ee46c23..05253f3 100644
--- a/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
+++ b/tests/fixtures/optimizer/qualify_columns__with_invisible.sql
@@ -11,10 +11,10 @@ SELECT x.b AS b FROM x AS x;
-- Derived tables
--------------------------------------
SELECT x.a FROM x AS x JOIN (SELECT * FROM x);
-SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT x.a AS a FROM x AS x JOIN (SELECT x.a AS a FROM x AS x) AS _q_0;
SELECT x.b FROM x AS x JOIN (SELECT b FROM x);
-SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS "_q_0";
+SELECT x.b AS b FROM x AS x JOIN (SELECT x.b AS b FROM x AS x) AS _q_0;
--------------------------------------
-- Expand *
@@ -29,7 +29,7 @@ SELECT * FROM y JOIN z ON y.c = z.c;
SELECT y.b AS b, z.b AS b FROM y AS y JOIN z AS z ON y.c = z.c;
SELECT a FROM (SELECT * FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
SELECT * FROM (SELECT a FROM x);
-SELECT "_q_0".a AS a FROM (SELECT x.a AS a FROM x AS x) AS "_q_0";
+SELECT _q_0.a AS a FROM (SELECT x.a AS a FROM x AS x) AS _q_0;
diff --git a/tests/fixtures/optimizer/unnest_subqueries.sql b/tests/fixtures/optimizer/unnest_subqueries.sql
index dc373a0..a444945 100644
--- a/tests/fixtures/optimizer/unnest_subqueries.sql
+++ b/tests/fixtures/optimizer/unnest_subqueries.sql
@@ -30,14 +30,14 @@ CROSS JOIN (
SELECT
SUM(y.a) AS a
FROM y
-) AS "_u_0"
+) AS _u_0
LEFT JOIN (
SELECT
y.a AS a
FROM y
GROUP BY
y.a
-) AS "_u_1"
+) AS _u_1
ON x.a = "_u_1"."a"
LEFT JOIN (
SELECT
@@ -45,7 +45,7 @@ LEFT JOIN (
FROM y
GROUP BY
y.b
-) AS "_u_2"
+) AS _u_2
ON x.a = "_u_2"."b"
LEFT JOIN (
SELECT
@@ -53,7 +53,7 @@ LEFT JOIN (
FROM y
GROUP BY
y.a
-) AS "_u_3"
+) AS _u_3
ON x.a = "_u_3"."a"
LEFT JOIN (
SELECT
@@ -64,8 +64,8 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_4"
- ON x.a = "_u_4"."_u_5"
+) AS _u_4
+ ON x.a = _u_4._u_5
LEFT JOIN (
SELECT
SUM(y.b) AS b,
@@ -75,8 +75,8 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_6"
- ON x.a = "_u_6"."_u_7"
+) AS _u_6
+ ON x.a = _u_6._u_7
LEFT JOIN (
SELECT
y.a AS a
@@ -85,8 +85,8 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_8"
- ON "_u_8".a = x.a
+) AS _u_8
+ ON _u_8.a = x.a
LEFT JOIN (
SELECT
y.a AS a
@@ -95,8 +95,8 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_9"
- ON "_u_9".a = x.a
+) AS _u_9
+ ON _u_9.a = x.a
LEFT JOIN (
SELECT
ARRAY_AGG(y.a) AS a,
@@ -106,8 +106,8 @@ LEFT JOIN (
TRUE
GROUP BY
y.b
-) AS "_u_10"
- ON "_u_10"."_u_11" = x.a
+) AS _u_10
+ ON _u_10._u_11 = x.a
LEFT JOIN (
SELECT
SUM(y.a) AS a,
@@ -118,8 +118,8 @@ LEFT JOIN (
TRUE AND TRUE AND TRUE
GROUP BY
y.a
-) AS "_u_12"
- ON "_u_12"."_u_13" = x.a AND "_u_12"."_u_13" = x.b
+) AS _u_12
+ ON _u_12._u_13 = x.a AND _u_12._u_13 = x.b
LEFT JOIN (
SELECT
y.a AS a
@@ -128,38 +128,38 @@ LEFT JOIN (
TRUE
GROUP BY
y.a
-) AS "_u_15"
- ON x.a = "_u_15".a
+) AS _u_15
+ ON x.a = _u_15.a
WHERE
- x.a = "_u_0".a
+ x.a = _u_0.a
AND NOT "_u_1"."a" IS NULL
AND NOT "_u_2"."b" IS NULL
AND NOT "_u_3"."a" IS NULL
AND (
- x.a = "_u_4".b AND NOT "_u_4"."_u_5" IS NULL
+ x.a = _u_4.b AND NOT _u_4._u_5 IS NULL
)
AND (
- x.a > "_u_6".b AND NOT "_u_6"."_u_7" IS NULL
+ x.a > _u_6.b AND NOT _u_6._u_7 IS NULL
)
AND (
- None = "_u_8".a AND NOT "_u_8".a IS NULL
+ None = _u_8.a AND NOT _u_8.a IS NULL
)
AND NOT (
- x.a = "_u_9".a AND NOT "_u_9".a IS NULL
+ x.a = _u_9.a AND NOT _u_9.a IS NULL
)
AND (
- ARRAY_ANY("_u_10".a, _x -> _x = x.a) AND NOT "_u_10"."_u_11" IS NULL
+ ARRAY_ANY(_u_10.a, _x -> _x = x.a) AND NOT _u_10._u_11 IS NULL
)
AND (
(
(
- x.a < "_u_12".a AND NOT "_u_12"."_u_13" IS NULL
- ) AND NOT "_u_12"."_u_13" IS NULL
+ x.a < _u_12.a AND NOT _u_12._u_13 IS NULL
+ ) AND NOT _u_12._u_13 IS NULL
)
- AND ARRAY_ANY("_u_12"."_u_14", "_x" -> "_x" <> x.d)
+ AND ARRAY_ANY(_u_12._u_14, "_x" -> _x <> x.d)
)
AND (
- NOT "_u_15".a IS NULL AND NOT "_u_15".a IS NULL
+ NOT _u_15.a IS NULL AND NOT _u_15.a IS NULL
)
AND x.a IN (
SELECT
diff --git a/tests/test_build.py b/tests/test_build.py
index b014a3a..a1a268d 100644
--- a/tests/test_build.py
+++ b/tests/test_build.py
@@ -481,6 +481,19 @@ class TestBuild(unittest.TestCase):
),
(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"),
+ (
+ lambda: select("AVG(a) OVER b")
+ .from_("table")
+ .window("b AS (PARTITION BY c ORDER BY d)"),
+ "SELECT AVG(a) OVER b FROM table WINDOW b AS (PARTITION BY c ORDER BY d)",
+ ),
+ (
+ lambda: select("AVG(a) OVER b", "MIN(c) OVER d")
+ .from_("table")
+ .window("b AS (PARTITION BY e ORDER BY f)")
+ .window("d AS (PARTITION BY g ORDER BY h)"),
+ "SELECT AVG(a) OVER b, MIN(c) OVER d FROM table WINDOW b AS (PARTITION BY e ORDER BY f), d AS (PARTITION BY g ORDER BY h)",
+ ),
]:
with self.subTest(sql):
self.assertEqual(expression().sql(dialect[0] if dialect else None), sql)
diff --git a/tests/test_executor.py b/tests/test_executor.py
index 4fe6399..b705551 100644
--- a/tests/test_executor.py
+++ b/tests/test_executor.py
@@ -74,7 +74,7 @@ class TestExecutor(unittest.TestCase):
)
return expression
- for i, (sql, _) in enumerate(self.sqls[0:18]):
+ for i, (sql, _) in enumerate(self.sqls):
with self.subTest(f"tpch-h {i + 1}"):
a = self.cached_execute(sql)
sql = parse_one(sql).transform(to_csv).sql(pretty=True)
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 0e13ade..1e23983 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -1,4 +1,5 @@
import datetime
+import math
import unittest
from sqlglot import alias, exp, parse_one
@@ -491,7 +492,7 @@ class TestExpressions(unittest.TestCase):
self.assertEqual(alias("foo", "bar-1").sql(), 'foo AS "bar-1"')
self.assertEqual(alias("foo", "bar_1").sql(), "foo AS bar_1")
self.assertEqual(alias("foo * 2", "2bar").sql(), 'foo * 2 AS "2bar"')
- self.assertEqual(alias('"foo"', "_bar").sql(), '"foo" AS "_bar"')
+ self.assertEqual(alias('"foo"', "_bar").sql(), '"foo" AS _bar')
self.assertEqual(alias("foo", "bar", quoted=True).sql(), 'foo AS "bar"')
def test_unit(self):
@@ -503,6 +504,8 @@ class TestExpressions(unittest.TestCase):
def test_identifier(self):
self.assertTrue(exp.to_identifier('"x"').quoted)
self.assertFalse(exp.to_identifier("x").quoted)
+ self.assertTrue(exp.to_identifier("foo ").quoted)
+ self.assertFalse(exp.to_identifier("_x").quoted)
def test_function_normalizer(self):
self.assertEqual(parse_one("HELLO()").sql(normalize_functions="lower"), "hello()")
@@ -549,14 +552,15 @@ class TestExpressions(unittest.TestCase):
([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.000000')",
+ datetime.datetime(2022, 10, 1, 1, 1, 1, 1),
+ "TIME_STR_TO_TIME('2022-10-01T01:01:01.000001+00:00')",
),
(
datetime.datetime(2022, 10, 1, 1, 1, 1, tzinfo=datetime.timezone.utc),
- "TIME_STR_TO_TIME('2022-10-01 01:01:01.000000+0000')",
+ "TIME_STR_TO_TIME('2022-10-01T01:01:01+00:00')",
),
(datetime.date(2022, 10, 1), "DATE_STR_TO_DATE('2022-10-01')"),
+ (math.nan, "NULL"),
]:
with self.subTest(value):
self.assertEqual(exp.convert(value).sql(), expected)
diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py
index 0c5f6cd..1c97be7 100644
--- a/tests/test_optimizer.py
+++ b/tests/test_optimizer.py
@@ -164,9 +164,6 @@ class TestOptimizer(unittest.TestCase):
with self.assertRaises(OptimizeError):
optimizer.qualify_columns.qualify_columns(parse_one(sql), schema=self.schema)
- def test_quote_identities(self):
- self.check_file("quote_identities", optimizer.quote_identities.quote_identities)
-
def test_lower_identities(self):
self.check_file("lower_identities", optimizer.lower_identities.lower_identities)
@@ -555,3 +552,29 @@ FROM READ_CSV('tests/fixtures/optimizer/tpc-h/nation.csv.gz', 'delimiter', '|')
parse_one(f"SELECT {func}(x.{col}) AS _col_0 FROM x AS x"), schema=schema
)
self.assertEqual(expression.expressions[0].type.this, target_type)
+
+ def test_recursive_cte(self):
+ query = parse_one(
+ """
+ with recursive t(n) AS
+ (
+ select 1
+ union all
+ select n + 1
+ FROM t
+ where n < 3
+ ), y AS (
+ select n
+ FROM t
+ union all
+ select n + 1
+ FROM y
+ where n < 2
+ )
+ select * from y
+ """
+ )
+
+ scope_t, scope_y = build_scope(query).cte_scopes
+ self.assertEqual(set(scope_t.cte_sources), {"t"})
+ self.assertEqual(set(scope_y.cte_sources), {"t", "y"})
diff --git a/tests/test_parser.py b/tests/test_parser.py
index 0be15e4..ae2e4cd 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -76,6 +76,9 @@ class TestParser(unittest.TestCase):
tables = [t.sql() for t in parse_one("select * from a, b.c, .d").find_all(exp.Table)]
self.assertEqual(tables, ["a", "b.c", "d"])
+ def test_union_order(self):
+ self.assertIsInstance(parse_one("SELECT * FROM (SELECT 1) UNION SELECT 2"), exp.Union)
+
def test_select(self):
self.assertIsNotNone(parse_one("select 1 natural"))
self.assertIsNotNone(parse_one("select * from (select 1) x order by x.y").args["order"])
diff --git a/tests/test_transforms.py b/tests/test_transforms.py
index 0bcd2ca..cfb8d2b 100644
--- a/tests/test_transforms.py
+++ b/tests/test_transforms.py
@@ -40,17 +40,17 @@ class TestTime(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) WHERE "_row_number" = 1',
+ 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) 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) AS "_row_number" FROM x) WHERE "_row_number" = 1',
+ 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a) AS _row_number FROM x) 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) WHERE "_row_number" = 1',
+ 'SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY c DESC) AS _row_number FROM x) WHERE "_row_number" = 1',
)
self.validate(
eliminate_distinct_on,
@@ -60,5 +60,5 @@ class TestTime(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) WHERE "_row_number_2" = 1',
+ 'SELECT _row_number FROM (SELECT _row_number, ROW_NUMBER() OVER (PARTITION BY _row_number ORDER BY c DESC) AS _row_number_2 FROM x) WHERE "_row_number_2" = 1',
)
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index 7bf53e5..9253ded 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -28,7 +28,7 @@ class TestTranspile(unittest.TestCase):
self.assertEqual(transpile("SELECT 1 current_datetime")[0], "SELECT 1 AS current_datetime")
self.assertEqual(transpile("SELECT 1 row")[0], "SELECT 1 AS row")
- for key in ("union", "filter", "over", "from", "join"):
+ for key in ("union", "over", "from", "join"):
with self.subTest(f"alias {key}"):
self.validate(f"SELECT x AS {key}", f"SELECT x AS {key}")
self.validate(f'SELECT x "{key}"', f'SELECT x AS "{key}"')
@@ -263,6 +263,25 @@ FROM bar /* comment 5 */, tbl /* comment 6 */""",
"WITH a AS (SELECT 1), WITH b AS (SELECT 2) SELECT *",
"WITH a AS (SELECT 1), b AS (SELECT 2) SELECT *",
)
+ self.validate(
+ "WITH A(filter) AS (VALUES 1, 2, 3) SELECT * FROM A WHERE filter >= 2",
+ "WITH A(filter) AS (VALUES (1), (2), (3)) SELECT * FROM A WHERE filter >= 2",
+ )
+
+ def test_alter(self):
+ self.validate(
+ "ALTER TABLE integers ADD k INTEGER",
+ "ALTER TABLE integers ADD COLUMN k INT",
+ )
+ self.validate("ALTER TABLE integers DROP k", "ALTER TABLE integers DROP COLUMN k")
+ self.validate(
+ "ALTER TABLE integers ALTER i SET DATA TYPE VARCHAR",
+ "ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR",
+ )
+ self.validate(
+ "ALTER TABLE integers ALTER i TYPE VARCHAR COLLATE foo USING bar",
+ "ALTER TABLE integers ALTER COLUMN i TYPE VARCHAR COLLATE foo USING bar",
+ )
def test_time(self):
self.validate("TIMESTAMP '2020-01-01'", "CAST('2020-01-01' AS TIMESTAMP)")
@@ -403,6 +422,14 @@ FROM bar /* comment 5 */, tbl /* comment 6 */""",
with self.subTest(sql):
self.assertEqual(transpile(sql)[0], sql.strip())
+ def test_normalize_name(self):
+ self.assertEqual(
+ transpile("cardinality(x)", read="presto", write="presto", normalize_functions="lower")[
+ 0
+ ],
+ "cardinality(x)",
+ )
+
def test_partial(self):
for sql in load_sql_fixtures("partial.sql"):
with self.subTest(sql):