From 41e67f6ce6b4b732d02e421d6825c18b8d15a59d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Thu, 23 May 2024 09:22:20 +0200 Subject: Adding upstream version 24.0.0. Signed-off-by: Daniel Baumann --- tests/dialects/test_databricks.py | 55 +++++++++++++++++++++++---------------- tests/dialects/test_dialect.py | 8 +++--- tests/dialects/test_doris.py | 14 +++++++++- tests/dialects/test_mysql.py | 8 ++++-- tests/dialects/test_postgres.py | 20 ++++++++++++-- tests/dialects/test_snowflake.py | 11 +++++++- tests/dialects/test_teradata.py | 2 +- 7 files changed, 84 insertions(+), 34 deletions(-) (limited to 'tests/dialects') diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py index 14a6bf3..f050cfa 100644 --- a/tests/dialects/test_databricks.py +++ b/tests/dialects/test_databricks.py @@ -20,7 +20,6 @@ class TestDatabricks(Validator): self.validate_identity("SELECT CAST('23:00:00' AS INTERVAL MINUTE TO SECOND)") self.validate_identity("CREATE TABLE target SHALLOW CLONE source") self.validate_identity("INSERT INTO a REPLACE WHERE cond VALUES (1), (2)") - self.validate_identity("SELECT c1 : price") self.validate_identity("CREATE FUNCTION a.b(x INT) RETURNS INT RETURN x + 1") self.validate_identity("CREATE FUNCTION a AS b") self.validate_identity("SELECT ${x} FROM ${y} WHERE ${z} > 1") @@ -68,6 +67,20 @@ class TestDatabricks(Validator): }, ) + self.validate_all( + "SELECT X'1A2B'", + read={ + "spark2": "SELECT X'1A2B'", + "spark": "SELECT X'1A2B'", + "databricks": "SELECT x'1A2B'", + }, + write={ + "spark2": "SELECT X'1A2B'", + "spark": "SELECT X'1A2B'", + "databricks": "SELECT X'1A2B'", + }, + ) + with self.assertRaises(ParseError): transpile( "CREATE FUNCTION add_one(x INT) RETURNS INT LANGUAGE PYTHON AS $foo$def add_one(x):\n return x+1$$", @@ -82,37 +95,33 @@ class TestDatabricks(Validator): # https://docs.databricks.com/sql/language-manual/functions/colonsign.html def test_json(self): - self.validate_identity("""SELECT c1 : price FROM VALUES ('{ "price": 5 }') AS T(c1)""") - - self.validate_all( + self.validate_identity( + """SELECT c1 : price FROM VALUES ('{ "price": 5 }') AS T(c1)""", + """SELECT GET_JSON_OBJECT(c1, '$.price') FROM VALUES ('{ "price": 5 }') AS T(c1)""", + ) + self.validate_identity( """SELECT c1:['price'] FROM VALUES('{ "price": 5 }') AS T(c1)""", - write={ - "databricks": """SELECT c1 : ARRAY('price') FROM VALUES ('{ "price": 5 }') AS T(c1)""", - }, + """SELECT GET_JSON_OBJECT(c1, '$.price') FROM VALUES ('{ "price": 5 }') AS T(c1)""", ) - self.validate_all( + self.validate_identity( """SELECT c1:item[1].price FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - write={ - "databricks": """SELECT c1 : item[1].price FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - }, + """SELECT GET_JSON_OBJECT(c1, '$.item[1].price') FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", ) - self.validate_all( + self.validate_identity( """SELECT c1:item[*].price FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - write={ - "databricks": """SELECT c1 : item[*].price FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - }, + """SELECT GET_JSON_OBJECT(c1, '$.item[*].price') FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", ) - self.validate_all( + self.validate_identity( """SELECT from_json(c1:item[*].price, 'ARRAY')[0] FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - write={ - "databricks": """SELECT FROM_JSON(c1 : item[*].price, 'ARRAY')[0] FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - }, + """SELECT FROM_JSON(GET_JSON_OBJECT(c1, '$.item[*].price'), 'ARRAY')[0] FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", ) - self.validate_all( + self.validate_identity( """SELECT inline(from_json(c1:item[*], 'ARRAY>')) FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - write={ - "databricks": """SELECT INLINE(FROM_JSON(c1 : item[*], 'ARRAY>')) FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", - }, + """SELECT INLINE(FROM_JSON(GET_JSON_OBJECT(c1, '$.item[*]'), 'ARRAY>')) FROM VALUES ('{ "item": [ { "model" : "basic", "price" : 6.12 }, { "model" : "medium", "price" : 9.24 } ] }') AS T(c1)""", + ) + self.validate_identity( + "SELECT c1 : price", + "SELECT GET_JSON_OBJECT(c1, '$.price')", ) def test_datediff(self): diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 77306dc..9888a5d 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -1163,7 +1163,7 @@ class TestDialect(Validator): read={ "bigquery": "JSON_EXTRACT(x, '$.y')", "duckdb": "x -> 'y'", - "doris": "x -> '$.y'", + "doris": "JSON_EXTRACT(x, '$.y')", "mysql": "JSON_EXTRACT(x, '$.y')", "postgres": "x->'y'", "presto": "JSON_EXTRACT(x, '$.y')", @@ -1174,7 +1174,7 @@ class TestDialect(Validator): write={ "bigquery": "JSON_EXTRACT(x, '$.y')", "clickhouse": "JSONExtractString(x, 'y')", - "doris": "x -> '$.y'", + "doris": "JSON_EXTRACT(x, '$.y')", "duckdb": "x -> '$.y'", "mysql": "JSON_EXTRACT(x, '$.y')", "oracle": "JSON_EXTRACT(x, '$.y')", @@ -1218,7 +1218,7 @@ class TestDialect(Validator): read={ "bigquery": "JSON_EXTRACT(x, '$.y[0].z')", "duckdb": "x -> '$.y[0].z'", - "doris": "x -> '$.y[0].z'", + "doris": "JSON_EXTRACT(x, '$.y[0].z')", "mysql": "JSON_EXTRACT(x, '$.y[0].z')", "presto": "JSON_EXTRACT(x, '$.y[0].z')", "snowflake": "GET_PATH(x, 'y[0].z')", @@ -1228,7 +1228,7 @@ class TestDialect(Validator): write={ "bigquery": "JSON_EXTRACT(x, '$.y[0].z')", "clickhouse": "JSONExtractString(x, 'y', 1, 'z')", - "doris": "x -> '$.y[0].z'", + "doris": "JSON_EXTRACT(x, '$.y[0].z')", "duckdb": "x -> '$.y[0].z'", "mysql": "JSON_EXTRACT(x, '$.y[0].z')", "oracle": "JSON_EXTRACT(x, '$.y[0].z')", diff --git a/tests/dialects/test_doris.py b/tests/dialects/test_doris.py index 035289b..f7fce02 100644 --- a/tests/dialects/test_doris.py +++ b/tests/dialects/test_doris.py @@ -14,7 +14,9 @@ class TestDoris(Validator): ) self.validate_all( "SELECT MAX_BY(a, b), MIN_BY(c, d)", - read={"clickhouse": "SELECT argMax(a, b), argMin(c, d)"}, + read={ + "clickhouse": "SELECT argMax(a, b), argMin(c, d)", + }, ) self.validate_all( "SELECT ARRAY_SUM(x -> x * x, ARRAY(2, 3))", @@ -36,6 +38,16 @@ class TestDoris(Validator): "oracle": "ADD_MONTHS(d, n)", }, ) + self.validate_all( + """SELECT JSON_EXTRACT(CAST('{"key": 1}' AS JSONB), '$.key')""", + read={ + "postgres": """SELECT '{"key": 1}'::jsonb ->> 'key'""", + }, + write={ + "doris": """SELECT JSON_EXTRACT(CAST('{"key": 1}' AS JSONB), '$.key')""", + "postgres": """SELECT JSON_EXTRACT_PATH(CAST('{"key": 1}' AS JSONB), 'key')""", + }, + ) def test_identity(self): self.validate_identity("COALECSE(a, b, c, d)") diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index 84fb3c2..591b5dd 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -155,6 +155,10 @@ class TestMySQL(Validator): """SELECT * FROM foo WHERE 3 MEMBER OF(info->'$.value')""", """SELECT * FROM foo WHERE 3 MEMBER OF(JSON_EXTRACT(info, '$.value'))""", ) + self.validate_identity( + "SELECT 1 AS row", + "SELECT 1 AS `row`", + ) # Index hints self.validate_identity( @@ -334,7 +338,7 @@ class TestMySQL(Validator): write_CC = { "bigquery": "SELECT 0xCC", "clickhouse": "SELECT 0xCC", - "databricks": "SELECT 204", + "databricks": "SELECT X'CC'", "drill": "SELECT 204", "duckdb": "SELECT 204", "hive": "SELECT 204", @@ -355,7 +359,7 @@ class TestMySQL(Validator): write_CC_with_leading_zeros = { "bigquery": "SELECT 0x0000CC", "clickhouse": "SELECT 0x0000CC", - "databricks": "SELECT 204", + "databricks": "SELECT X'0000CC'", "drill": "SELECT 204", "duckdb": "SELECT 204", "hive": "SELECT 204", diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index a8a6c12..8ba4e96 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -38,8 +38,6 @@ class TestPostgres(Validator): self.validate_identity("CAST(x AS TSTZMULTIRANGE)") self.validate_identity("CAST(x AS DATERANGE)") self.validate_identity("CAST(x AS DATEMULTIRANGE)") - self.validate_identity("SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]") - self.validate_identity("SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]") self.validate_identity("x$") self.validate_identity("SELECT ARRAY[1, 2, 3]") self.validate_identity("SELECT ARRAY(SELECT 1)") @@ -64,6 +62,10 @@ class TestPostgres(Validator): self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True) self.validate_identity("SELECT CURRENT_USER") self.validate_identity("SELECT * FROM ONLY t1") + self.validate_identity( + "SELECT ARRAY[1, 2, 3] <@ ARRAY[1, 2]", + "SELECT ARRAY[1, 2] @> ARRAY[1, 2, 3]", + ) self.validate_identity( """UPDATE "x" SET "y" = CAST('0 days 60.000000 seconds' AS INTERVAL) WHERE "x"."id" IN (2, 3)""" ) @@ -325,6 +327,17 @@ class TestPostgres(Validator): "CAST(x AS BIGINT)", ) + self.validate_all( + "SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]", + read={ + "duckdb": "SELECT ARRAY_HAS_ALL([1, 2, 3], [1, 2])", + }, + write={ + "duckdb": "SELECT ARRAY_HAS_ALL([1, 2, 3], [1, 2])", + "mysql": UnsupportedError, + "postgres": "SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2]", + }, + ) self.validate_all( "SELECT REGEXP_REPLACE('mr .', '[^a-zA-Z]', '', 'g')", write={ @@ -740,6 +753,9 @@ class TestPostgres(Validator): self.validate_identity("ALTER TABLE t1 SET ACCESS METHOD method") self.validate_identity("ALTER TABLE t1 SET TABLESPACE tablespace") self.validate_identity("ALTER TABLE t1 SET (fillfactor = 5, autovacuum_enabled = TRUE)") + self.validate_identity( + "CREATE FUNCTION pymax(a INT, b INT) RETURNS INT LANGUAGE plpython3u AS $$\n if a > b:\n return a\n return b\n$$", + ) self.validate_identity( "CREATE TABLE t (vid INT NOT NULL, CONSTRAINT ht_vid_nid_fid_idx EXCLUDE (INT4RANGE(vid, nid) WITH &&, INT4RANGE(fid, fid, '[]') WITH &&))" ) diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index d3c47af..f8c2ea1 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -10,6 +10,11 @@ class TestSnowflake(Validator): dialect = "snowflake" def test_snowflake(self): + self.validate_identity( + "transform(x, a int -> a + a + 1)", + "TRANSFORM(x, a -> CAST(a AS INT) + CAST(a AS INT) + 1)", + ) + self.validate_all( "ARRAY_CONSTRUCT_COMPACT(1, null, 2)", write={ @@ -321,10 +326,12 @@ WHERE """SELECT PARSE_JSON('{"fruit":"banana"}'):fruit""", write={ "bigquery": """SELECT JSON_EXTRACT(PARSE_JSON('{"fruit":"banana"}'), '$.fruit')""", + "databricks": """SELECT GET_JSON_OBJECT('{"fruit":"banana"}', '$.fruit')""", "duckdb": """SELECT JSON('{"fruit":"banana"}') -> '$.fruit'""", "mysql": """SELECT JSON_EXTRACT('{"fruit":"banana"}', '$.fruit')""", "presto": """SELECT JSON_EXTRACT(JSON_PARSE('{"fruit":"banana"}'), '$.fruit')""", "snowflake": """SELECT GET_PATH(PARSE_JSON('{"fruit":"banana"}'), 'fruit')""", + "spark": """SELECT GET_JSON_OBJECT('{"fruit":"banana"}', '$.fruit')""", "tsql": """SELECT ISNULL(JSON_QUERY('{"fruit":"banana"}', '$.fruit'), JSON_VALUE('{"fruit":"banana"}', '$.fruit'))""", }, ) @@ -1198,6 +1205,8 @@ WHERE self.validate_identity("CREATE TABLE IDENTIFIER('foo') (COLUMN1 VARCHAR, COLUMN2 VARCHAR)") self.validate_identity("CREATE TABLE IDENTIFIER($foo) (col1 VARCHAR, col2 VARCHAR)") self.validate_identity("CREATE TAG cost_center ALLOWED_VALUES 'a', 'b'") + self.validate_identity("CREATE WAREHOUSE x").this.assert_is(exp.Identifier) + self.validate_identity("CREATE STREAMLIT x").this.assert_is(exp.Identifier) self.validate_identity( "CREATE OR REPLACE TAG IF NOT EXISTS cost_center COMMENT='cost_center tag'" ).this.assert_is(exp.Identifier) @@ -1825,7 +1834,7 @@ STORAGE_AWS_ROLE_ARN='arn:aws:iam::001234567890:role/myrole' ENABLED=TRUE STORAGE_ALLOWED_LOCATIONS=('s3://mybucket1/path1/', 's3://mybucket2/path2/')""", pretty=True, - ) + ).this.assert_is(exp.Identifier) def test_swap(self): ast = parse_one("ALTER TABLE a SWAP WITH b", read="snowflake") diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py index 010b683..74d5f88 100644 --- a/tests/dialects/test_teradata.py +++ b/tests/dialects/test_teradata.py @@ -38,7 +38,7 @@ class TestTeradata(Validator): "UPDATE A FROM schema.tableA AS A, (SELECT col1 FROM schema.tableA GROUP BY col1) AS B SET col2 = '' WHERE A.col1 = B.col1", write={ "teradata": "UPDATE A FROM schema.tableA AS A, (SELECT col1 FROM schema.tableA GROUP BY col1) AS B SET col2 = '' WHERE A.col1 = B.col1", - "mysql": "UPDATE A SET col2 = '' FROM schema.tableA AS A, (SELECT col1 FROM schema.tableA GROUP BY col1) AS B WHERE A.col1 = B.col1", + "mysql": "UPDATE A SET col2 = '' FROM `schema`.tableA AS A, (SELECT col1 FROM `schema`.tableA GROUP BY col1) AS B WHERE A.col1 = B.col1", }, ) -- cgit v1.2.3