From 7a2201963d5b03bd1828d350ccaecb4eda30d30c Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Wed, 4 Jan 2023 08:24:08 +0100 Subject: Merging upstream version 10.4.2. Signed-off-by: Daniel Baumann --- tests/dataframe/unit/test_column.py | 4 +- tests/dataframe/unit/test_functions.py | 4 +- tests/dialects/test_bigquery.py | 6 ++ tests/dialects/test_databricks.py | 70 ++++++++++++++++ tests/dialects/test_dialect.py | 18 ++-- tests/dialects/test_duckdb.py | 19 ++++- tests/dialects/test_hive.py | 8 +- tests/dialects/test_mysql.py | 6 +- tests/dialects/test_postgres.py | 97 +++++++++++++++++++--- tests/dialects/test_redshift.py | 2 +- tests/dialects/test_snowflake.py | 14 ++++ tests/dialects/test_spark.py | 12 +++ tests/dialects/test_tsql.py | 4 +- tests/fixtures/identity.sql | 35 +++++++- tests/fixtures/optimizer/canonicalize.sql | 8 +- tests/fixtures/optimizer/optimizer.sql | 78 +++++++++++++++++ tests/fixtures/optimizer/pushdown_projections.sql | 20 ++--- tests/fixtures/optimizer/qualify_columns.sql | 24 +++--- .../optimizer/qualify_columns__with_invisible.sql | 8 +- tests/fixtures/optimizer/unnest_subqueries.sql | 56 ++++++------- tests/test_build.py | 13 +++ tests/test_executor.py | 2 +- tests/test_expressions.py | 12 ++- tests/test_optimizer.py | 29 ++++++- tests/test_parser.py | 3 + tests/test_transforms.py | 8 +- tests/test_transpile.py | 29 ++++++- 27 files changed, 480 insertions(+), 109 deletions(-) (limited to 'tests') 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 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 @@ -6,6 +6,12 @@ class TestSnowflake(Validator): dialect = "snowflake" 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={ @@ -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 @@ -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, b MAP, 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): -- cgit v1.2.3