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/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 +- 11 files changed, 224 insertions(+), 32 deletions(-) (limited to 'tests/dialects') 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'''", -- cgit v1.2.3