diff options
Diffstat (limited to 'tests')
| -rw-r--r-- | tests/dialects/test_athena.py | 112 | ||||
| -rw-r--r-- | tests/dialects/test_bigquery.py | 3 | ||||
| -rw-r--r-- | tests/dialects/test_clickhouse.py | 14 | ||||
| -rw-r--r-- | tests/dialects/test_databricks.py | 7 | ||||
| -rw-r--r-- | tests/dialects/test_dialect.py | 38 | ||||
| -rw-r--r-- | tests/dialects/test_duckdb.py | 26 | ||||
| -rw-r--r-- | tests/dialects/test_mysql.py | 8 | ||||
| -rw-r--r-- | tests/dialects/test_oracle.py | 4 | ||||
| -rw-r--r-- | tests/dialects/test_postgres.py | 4 | ||||
| -rw-r--r-- | tests/dialects/test_prql.py | 186 | ||||
| -rw-r--r-- | tests/dialects/test_snowflake.py | 36 | ||||
| -rw-r--r-- | tests/fixtures/optimizer/simplify.sql | 3 | ||||
| -rw-r--r-- | tests/test_build.py | 30 | ||||
| -rw-r--r-- | tests/test_expressions.py | 1 | ||||
| -rw-r--r-- | tests/test_optimizer.py | 32 |
15 files changed, 439 insertions, 65 deletions
diff --git a/tests/dialects/test_athena.py b/tests/dialects/test_athena.py index 6ec870b..bf54914 100644 --- a/tests/dialects/test_athena.py +++ b/tests/dialects/test_athena.py @@ -24,16 +24,67 @@ class TestAthena(Validator): check_command_warning=True, ) + self.validate_identity( + "/* leading comment */CREATE SCHEMA foo", + write_sql="/* leading comment */ CREATE SCHEMA `foo`", + identify=True, + ) + self.validate_identity( + "/* leading comment */SELECT * FROM foo", + write_sql='/* leading comment */ SELECT * FROM "foo"', + identify=True, + ) + + def test_ddl(self): + # Hive-like, https://docs.aws.amazon.com/athena/latest/ug/create-table.html + self.validate_identity("CREATE EXTERNAL TABLE foo (id INT) COMMENT 'test comment'") + self.validate_identity( + "CREATE EXTERNAL TABLE foo (id INT, val STRING) CLUSTERED BY (id, val) INTO 10 BUCKETS" + ) + self.validate_identity( + "CREATE EXTERNAL TABLE foo (id INT, val STRING) STORED AS PARQUET LOCATION 's3://foo' TBLPROPERTIES ('has_encryped_data'='true', 'classification'='test')" + ) + self.validate_identity( + "CREATE EXTERNAL TABLE IF NOT EXISTS foo (a INT, b STRING) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ('case.insensitive'='FALSE') LOCATION 's3://table/path'" + ) + self.validate_identity( + """CREATE EXTERNAL TABLE x (y INT) ROW FORMAT SERDE 'serde' ROW FORMAT DELIMITED FIELDS TERMINATED BY '1' WITH SERDEPROPERTIES ('input.regex'='')""", + ) + self.validate_identity( + """CREATE EXTERNAL TABLE `my_table` (`a7` ARRAY<DATE>) ROW FORMAT SERDE 'a' STORED AS INPUTFORMAT 'b' OUTPUTFORMAT 'c' LOCATION 'd' TBLPROPERTIES ('e'='f')""" + ) + + # Iceberg, https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.html + self.validate_identity( + "CREATE TABLE iceberg_table (`id` BIGINT, `data` STRING, category STRING) PARTITIONED BY (category, BUCKET(16, id)) LOCATION 's3://amzn-s3-demo-bucket/your-folder/' TBLPROPERTIES ('table_type'='ICEBERG', 'write_compression'='snappy')" + ) + + # CTAS goes to the Trino engine, where the table properties cant be encased in single quotes like they can for Hive + # ref: https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html#ctas-table-properties + self.validate_identity( + "CREATE TABLE foo WITH (table_type='ICEBERG', external_location='s3://foo/') AS SELECT * FROM a" + ) + self.validate_identity( + "CREATE TABLE foo AS WITH foo AS (SELECT a, b FROM bar) SELECT * FROM foo" + ) + def test_ddl_quoting(self): self.validate_identity("CREATE SCHEMA `foo`") self.validate_identity("CREATE SCHEMA foo") - self.validate_identity("CREATE SCHEMA foo", write_sql="CREATE SCHEMA `foo`", identify=True) - self.validate_identity("CREATE EXTERNAL TABLE `foo` (`id` INTEGER) LOCATION 's3://foo/'") - self.validate_identity("CREATE EXTERNAL TABLE foo (id INTEGER) LOCATION 's3://foo/'") + self.validate_identity("CREATE EXTERNAL TABLE `foo` (`id` INT) LOCATION 's3://foo/'") + self.validate_identity("CREATE EXTERNAL TABLE foo (id INT) LOCATION 's3://foo/'") + self.validate_identity( + "CREATE EXTERNAL TABLE foo (id INT) LOCATION 's3://foo/'", + write_sql="CREATE EXTERNAL TABLE `foo` (`id` INT) LOCATION 's3://foo/'", + identify=True, + ) + + self.validate_identity("CREATE TABLE foo AS SELECT * FROM a") + self.validate_identity('CREATE TABLE "foo" AS SELECT * FROM "a"') self.validate_identity( - "CREATE EXTERNAL TABLE foo (id INTEGER) LOCATION 's3://foo/'", - write_sql="CREATE EXTERNAL TABLE `foo` (`id` INTEGER) LOCATION 's3://foo/'", + "CREATE TABLE `foo` AS SELECT * FROM `a`", + write_sql='CREATE TABLE "foo" AS SELECT * FROM "a"', identify=True, ) @@ -52,8 +103,8 @@ class TestAthena(Validator): # As a side effect of being able to parse both quote types, we can also fix the quoting on incorrectly quoted source queries self.validate_identity('CREATE SCHEMA "foo"', write_sql="CREATE SCHEMA `foo`") self.validate_identity( - 'CREATE EXTERNAL TABLE "foo" ("id" INTEGER) LOCATION \'s3://foo/\'', - write_sql="CREATE EXTERNAL TABLE `foo` (`id` INTEGER) LOCATION 's3://foo/'", + 'CREATE EXTERNAL TABLE "foo" ("id" INT) LOCATION \'s3://foo/\'', + write_sql="CREATE EXTERNAL TABLE `foo` (`id` INT) LOCATION 's3://foo/'", ) self.validate_identity('DROP TABLE "foo"', write_sql="DROP TABLE `foo`") self.validate_identity( @@ -61,6 +112,22 @@ class TestAthena(Validator): write_sql='CREATE VIEW "foo" AS SELECT "id" FROM "tbl"', ) + self.validate_identity( + 'ALTER TABLE "foo" ADD COLUMNS ("id" STRING)', + write_sql="ALTER TABLE `foo` ADD COLUMNS (`id` STRING)", + ) + self.validate_identity( + 'ALTER TABLE "foo" DROP COLUMN "id"', write_sql="ALTER TABLE `foo` DROP COLUMN `id`" + ) + + self.validate_identity( + 'CREATE TABLE "foo" AS WITH "foo" AS (SELECT "a", "b" FROM "bar") SELECT * FROM "foo"' + ) + self.validate_identity( + 'CREATE TABLE `foo` AS WITH `foo` AS (SELECT "a", `b` FROM "bar") SELECT * FROM "foo"', + write_sql='CREATE TABLE "foo" AS WITH "foo" AS (SELECT "a", "b" FROM "bar") SELECT * FROM "foo"', + ) + def test_dml_quoting(self): self.validate_identity("SELECT a AS foo FROM tbl") self.validate_identity('SELECT "a" AS "foo" FROM "tbl"') @@ -69,3 +136,34 @@ class TestAthena(Validator): write_sql='SELECT "a" AS "foo" FROM "tbl"', identify=True, ) + + self.validate_identity("INSERT INTO foo (id) VALUES (1)") + self.validate_identity('INSERT INTO "foo" ("id") VALUES (1)') + self.validate_identity( + 'INSERT INTO `foo` ("id") VALUES (1)', + write_sql='INSERT INTO "foo" ("id") VALUES (1)', + identify=True, + ) + + self.validate_identity("UPDATE foo SET id = 3 WHERE id = 7") + self.validate_identity('UPDATE "foo" SET "id" = 3 WHERE "id" = 7') + self.validate_identity( + 'UPDATE `foo` SET "id" = 3 WHERE `id` = 7', + write_sql='UPDATE "foo" SET "id" = 3 WHERE "id" = 7', + identify=True, + ) + + self.validate_identity("DELETE FROM foo WHERE id > 10") + self.validate_identity('DELETE FROM "foo" WHERE "id" > 10') + self.validate_identity( + "DELETE FROM `foo` WHERE `id` > 10", + write_sql='DELETE FROM "foo" WHERE "id" > 10', + identify=True, + ) + + self.validate_identity("WITH foo AS (SELECT a, b FROM bar) SELECT * FROM foo") + self.validate_identity( + "WITH foo AS (SELECT a, b FROM bar) SELECT * FROM foo", + write_sql='WITH "foo" AS (SELECT "a", "b" FROM "bar") SELECT * FROM "foo"', + identify=True, + ) diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index c8c2176..b883896 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -192,6 +192,9 @@ LANGUAGE js AS self.validate_identity("CAST(x AS TIMESTAMPTZ)", "CAST(x AS TIMESTAMP)") self.validate_identity("CAST(x AS RECORD)", "CAST(x AS STRUCT)") self.validate_identity( + "MERGE INTO dataset.NewArrivals USING (SELECT * FROM UNNEST([('microwave', 10, 'warehouse #1'), ('dryer', 30, 'warehouse #1'), ('oven', 20, 'warehouse #2')])) ON FALSE WHEN NOT MATCHED THEN INSERT ROW WHEN NOT MATCHED BY SOURCE THEN DELETE" + ) + self.validate_identity( "SELECT * FROM `SOME_PROJECT_ID.SOME_DATASET_ID.INFORMATION_SCHEMA.SOME_VIEW`" ) self.validate_identity( diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py index ea6064a..d203141 100644 --- a/tests/dialects/test_clickhouse.py +++ b/tests/dialects/test_clickhouse.py @@ -28,6 +28,7 @@ class TestClickhouse(Validator): self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)") self.assertIsNone(expr._meta) + self.validate_identity("SELECT toString(CHAR(104.1, 101, 108.9, 108.9, 111, 32))") self.validate_identity("@macro").assert_is(exp.Parameter).this.assert_is(exp.Var) self.validate_identity("SELECT toFloat(like)") self.validate_identity("SELECT like") @@ -160,10 +161,10 @@ class TestClickhouse(Validator): ) self.validate_all( - "char(67) || char(65) || char(84)", + "CHAR(67) || CHAR(65) || CHAR(84)", read={ - "clickhouse": "char(67) || char(65) || char(84)", - "oracle": "chr(67) || chr(65) || chr(84)", + "clickhouse": "CHAR(67) || CHAR(65) || CHAR(84)", + "oracle": "CHR(67) || CHR(65) || CHR(84)", }, ) self.validate_all( @@ -469,15 +470,15 @@ class TestClickhouse(Validator): self.validate_identity("SELECT FORMAT") self.validate_identity("1 AS FORMAT").assert_is(exp.Alias) - self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d', '%T')") + self.validate_identity("SELECT formatDateTime(NOW(), '%Y-%m-%d', '%T')") self.validate_all( - "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", + "SELECT formatDateTime(NOW(), '%Y-%m-%d')", read={ "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')", "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", }, write={ - "clickhouse": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", + "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')", "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')", }, ) @@ -496,6 +497,7 @@ class TestClickhouse(Validator): ) self.validate_identity("ALTER TABLE visits REPLACE PARTITION ID '201901' FROM visits_tmp") self.validate_identity("ALTER TABLE visits ON CLUSTER test_cluster DROP COLUMN col1") + self.validate_identity("DELETE FROM tbl ON CLUSTER test_cluster WHERE date = '2019-01-01'") self.assertIsInstance( parse_one("Tuple(select Int64)", into=exp.DataType, read="clickhouse"), exp.DataType diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py index a46794b..c2965e8 100644 --- a/tests/dialects/test_databricks.py +++ b/tests/dialects/test_databricks.py @@ -142,6 +142,13 @@ class TestDatabricks(Validator): """SELECT raw:`zip code`, raw:`fb:testid`, raw:store['bicycle'], raw:store["zip code"]""", """SELECT raw:["zip code"], raw:["fb:testid"], raw:store.bicycle, raw:store["zip code"]""", ) + self.validate_all( + "SELECT col:`fr'uit`", + write={ + "databricks": """SELECT col:["fr'uit"]""", + "postgres": "SELECT JSON_EXTRACT_PATH(col, 'fr''uit')", + }, + ) def test_datediff(self): self.validate_all( diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index f0faccb..19effb3 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -1788,6 +1788,13 @@ class TestDialect(Validator): }, ) self.validate_all( + "FILTER(the_array, x -> x > 0)", + write={ + "presto": "FILTER(the_array, x -> x > 0)", + "starrocks": "ARRAY_FILTER(the_array, x -> x > 0)", + }, + ) + self.validate_all( "a / b", write={ "bigquery": "a / b", @@ -2853,3 +2860,34 @@ FROM subquery2""", "bigquery": "RTRIM('Hello World', 'd')", }, ) + + def test_uuid(self): + self.validate_all( + "UUID()", + read={ + "hive": "UUID()", + "spark2": "UUID()", + "spark": "UUID()", + "databricks": "UUID()", + "duckdb": "UUID()", + "presto": "UUID()", + "trino": "UUID()", + "mysql": "UUID()", + "postgres": "GEN_RANDOM_UUID()", + "bigquery": "GENERATE_UUID()", + "snowflake": "UUID_STRING()", + }, + write={ + "hive": "UUID()", + "spark2": "UUID()", + "spark": "UUID()", + "databricks": "UUID()", + "duckdb": "UUID()", + "presto": "UUID()", + "trino": "UUID()", + "mysql": "UUID()", + "postgres": "GEN_RANDOM_UUID()", + "bigquery": "GENERATE_UUID()", + "snowflake": "UUID_STRING()", + }, + ) diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index 18a030c..84c82f1 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -18,6 +18,23 @@ class TestDuckDB(Validator): "WITH _data AS (SELECT [STRUCT(1 AS a, 2 AS b), STRUCT(2 AS a, 3 AS b)] AS col) SELECT col.b FROM _data, UNNEST(_data.col) AS col WHERE col.a = 1", ) + struct_array_type = exp.maybe_parse( + "STRUCT(k TEXT, v STRUCT(v_str TEXT, v_int INT, v_int_arr INT[]))[]", + into=exp.DataType, + dialect="duckdb", + ) + self.assertEqual( + struct_array_type.sql("duckdb"), + "STRUCT(k TEXT, v STRUCT(v_str TEXT, v_int INT, v_int_arr INT[]))[]", + ) + + self.validate_all( + "CAST(x AS UUID)", + write={ + "bigquery": "CAST(x AS STRING)", + "duckdb": "CAST(x AS UUID)", + }, + ) self.validate_all( """SELECT CASE WHEN JSON_VALID('{"x: 1}') THEN '{"x: 1}' ELSE NULL END""", read={ @@ -810,6 +827,15 @@ class TestDuckDB(Validator): self.validate_identity("SELECT * FROM (DESCRIBE t)") + self.validate_identity("SELECT UNNEST([*COLUMNS('alias_.*')]) AS column_name") + self.validate_identity( + "SELECT COALESCE(*COLUMNS(*)) FROM (SELECT NULL, 2, 3) AS t(a, b, c)" + ) + self.validate_identity( + "SELECT id, STRUCT_PACK(*COLUMNS('m\\d')) AS measurements FROM many_measurements", + """SELECT id, {'_0': *COLUMNS('m\\d')} AS measurements FROM many_measurements""", + ) + def test_array_index(self): with self.assertLogs(helper_logger) as cm: self.validate_all( diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py index 2fd9ef0..2f1f7ba 100644 --- a/tests/dialects/test_mysql.py +++ b/tests/dialects/test_mysql.py @@ -82,6 +82,10 @@ class TestMySQL(Validator): "CREATE OR REPLACE VIEW my_view AS SELECT column1 AS `boo`, column2 AS `foo` FROM my_table WHERE column3 = 'some_value' UNION SELECT q.* FROM fruits_table, JSON_TABLE(Fruits, '$[*]' COLUMNS(id VARCHAR(255) PATH '$.$id', value VARCHAR(255) PATH '$.value')) AS q", ) self.validate_identity( + "CREATE TABLE t (name VARCHAR)", + "CREATE TABLE t (name TEXT)", + ) + self.validate_identity( "ALTER TABLE t ADD KEY `i` (`c`)", "ALTER TABLE t ADD INDEX `i` (`c`)", ) @@ -176,6 +180,10 @@ class TestMySQL(Validator): "REPLACE INTO table SELECT id FROM table2 WHERE cnt > 100", check_command_warning=True ) self.validate_identity( + "CAST(x AS VARCHAR)", + "CAST(x AS CHAR)", + ) + self.validate_identity( """SELECT * FROM foo WHERE 3 MEMBER OF(info->'$.value')""", """SELECT * FROM foo WHERE 3 MEMBER OF(JSON_EXTRACT(info, '$.value'))""", ) diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index 8bdc4af..28a8da9 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -70,6 +70,10 @@ class TestOracle(Validator): "SELECT MIN(column_name) KEEP (DENSE_RANK FIRST ORDER BY column_name DESC) FROM table_name" ) self.validate_identity( + "SELECT TRUNC(SYSDATE)", + "SELECT TRUNC(SYSDATE, 'DD')", + ) + self.validate_identity( """SELECT JSON_OBJECT(KEY 'key1' IS emp.column1, KEY 'key2' IS emp.column1) "emp_key" FROM emp""", """SELECT JSON_OBJECT('key1': emp.column1, 'key2': emp.column1) AS "emp_key" FROM emp""", ) diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index f3f21a9..ab06683 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -794,6 +794,9 @@ class TestPostgres(Validator): self.validate_identity( 'SELECT js, js IS JSON ARRAY WITH UNIQUE KEYS AS "array w. UK?", js IS JSON ARRAY WITHOUT UNIQUE KEYS AS "array w/o UK?", js IS JSON ARRAY UNIQUE KEYS AS "array w UK 2?" FROM t' ) + self.validate_identity( + "MERGE INTO target_table USING source_table AS source ON target.id = source.id WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN DO NOTHING RETURNING MERGE_ACTION(), *" + ) def test_ddl(self): # Checks that user-defined types are parsed into DataType instead of Identifier @@ -811,6 +814,7 @@ class TestPostgres(Validator): cdef.args["kind"].assert_is(exp.DataType) self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)") + self.validate_identity('ALTER INDEX "IX_Ratings_Column1" RENAME TO "IX_Ratings_Column2"') self.validate_identity('CREATE TABLE x (a TEXT COLLATE "de_DE")') self.validate_identity('CREATE TABLE x (a TEXT COLLATE pg_catalog."default")') self.validate_identity("CREATE TABLE t (col INT[3][5])") diff --git a/tests/dialects/test_prql.py b/tests/dialects/test_prql.py index 5b438f1..2e35bb2 100644 --- a/tests/dialects/test_prql.py +++ b/tests/dialects/test_prql.py @@ -5,77 +5,165 @@ class TestPRQL(Validator): dialect = "prql" def test_prql(self): - self.validate_identity("from x", "SELECT * FROM x") - self.validate_identity("from x derive a + 1", "SELECT *, a + 1 FROM x") - self.validate_identity("from x derive x = a + 1", "SELECT *, a + 1 AS x FROM x") - self.validate_identity("from x derive {a + 1}", "SELECT *, a + 1 FROM x") - self.validate_identity("from x derive {x = a + 1, b}", "SELECT *, a + 1 AS x, b FROM x") - self.validate_identity( - "from x derive {x = a + 1, b} select {y = x, 2}", "SELECT a + 1 AS y, 2 FROM x" - ) - self.validate_identity("from x take 10", "SELECT * FROM x LIMIT 10") - self.validate_identity("from x take 10 take 5", "SELECT * FROM x LIMIT 5") - self.validate_identity("from x filter age > 25", "SELECT * FROM x WHERE age > 25") - self.validate_identity( + self.validate_all( + "from x", + write={ + "": "SELECT * FROM x", + }, + ) + self.validate_all( + "from x derive a + 1", + write={ + "": "SELECT *, a + 1 FROM x", + }, + ) + self.validate_all( + "from x derive x = a + 1", + write={ + "": "SELECT *, a + 1 AS x FROM x", + }, + ) + self.validate_all( + "from x derive {a + 1}", + write={ + "": "SELECT *, a + 1 FROM x", + }, + ) + self.validate_all( + "from x derive {x = a + 1, b}", + write={ + "": "SELECT *, a + 1 AS x, b FROM x", + }, + ) + self.validate_all( + "from x derive {x = a + 1, b} select {y = x, 2}", + write={"": "SELECT a + 1 AS y, 2 FROM x"}, + ) + self.validate_all( + "from x take 10", + write={ + "": "SELECT * FROM x LIMIT 10", + }, + ) + self.validate_all( + "from x take 10 take 5", + write={ + "": "SELECT * FROM x LIMIT 5", + }, + ) + self.validate_all( + "from x filter age > 25", + write={ + "": "SELECT * FROM x WHERE age > 25", + }, + ) + self.validate_all( "from x derive {x = a + 1, b} filter age > 25", - "SELECT *, a + 1 AS x, b FROM x WHERE age > 25", + write={ + "": "SELECT *, a + 1 AS x, b FROM x WHERE age > 25", + }, ) - self.validate_identity("from x filter dept != 'IT'", "SELECT * FROM x WHERE dept <> 'IT'") - self.validate_identity( - "from x filter p == 'product' select { a, b }", "SELECT a, b FROM x WHERE p = 'product'" + self.validate_all( + "from x filter dept != 'IT'", + write={ + "": "SELECT * FROM x WHERE dept <> 'IT'", + }, ) - self.validate_identity( - "from x filter age > 25 filter age < 27", "SELECT * FROM x WHERE age > 25 AND age < 27" + self.validate_all( + "from x filter p == 'product' select { a, b }", + write={"": "SELECT a, b FROM x WHERE p = 'product'"}, ) - self.validate_identity( - "from x filter (age > 25 && age < 27)", "SELECT * FROM x WHERE (age > 25 AND age < 27)" + self.validate_all( + "from x filter age > 25 filter age < 27", + write={"": "SELECT * FROM x WHERE age > 25 AND age < 27"}, ) - self.validate_identity( - "from x filter (age > 25 || age < 27)", "SELECT * FROM x WHERE (age > 25 OR age < 27)" + self.validate_all( + "from x filter (age > 25 && age < 27)", + write={"": "SELECT * FROM x WHERE (age > 25 AND age < 27)"}, ) - self.validate_identity( + self.validate_all( + "from x filter (age > 25 || age < 27)", + write={"": "SELECT * FROM x WHERE (age > 25 OR age < 27)"}, + ) + self.validate_all( "from x filter (age > 25 || age < 22) filter age > 26 filter age < 27", - "SELECT * FROM x WHERE ((age > 25 OR age < 22) AND age > 26) AND age < 27", + write={ + "": "SELECT * FROM x WHERE ((age > 25 OR age < 22) AND age > 26) AND age < 27", + }, ) - self.validate_identity( + self.validate_all( "from x sort age", - "SELECT * FROM x ORDER BY age", + write={ + "": "SELECT * FROM x ORDER BY age", + }, ) - self.validate_identity( + self.validate_all( "from x sort {-age}", - "SELECT * FROM x ORDER BY age DESC", + write={ + "": "SELECT * FROM x ORDER BY age DESC", + }, ) - self.validate_identity( + self.validate_all( "from x sort {age, name}", - "SELECT * FROM x ORDER BY age, name", + write={ + "": "SELECT * FROM x ORDER BY age, name", + }, ) - self.validate_identity( + self.validate_all( "from x sort {-age, +name}", - "SELECT * FROM x ORDER BY age DESC, name", - ) - self.validate_identity("from x append y", "SELECT * FROM x UNION ALL SELECT * FROM y") - self.validate_identity("from x remove y", "SELECT * FROM x EXCEPT ALL SELECT * FROM y") - self.validate_identity( - "from x intersect y", "SELECT * FROM x INTERSECT ALL SELECT * FROM y" - ) - self.validate_identity( + write={ + "": "SELECT * FROM x ORDER BY age DESC, name", + }, + ) + self.validate_all( + "from x append y", + write={ + "": "SELECT * FROM x UNION ALL SELECT * FROM y", + }, + ) + self.validate_all( + "from x remove y", + write={ + "": "SELECT * FROM x EXCEPT ALL SELECT * FROM y", + }, + ) + self.validate_all( + "from x intersect y", + write={"": "SELECT * FROM x INTERSECT ALL SELECT * FROM y"}, + ) + self.validate_all( "from x filter a == null filter null != b", - "SELECT * FROM x WHERE a IS NULL AND NOT b IS NULL", + write={ + "": "SELECT * FROM x WHERE a IS NULL AND NOT b IS NULL", + }, ) - self.validate_identity( + self.validate_all( "from x filter (a > 1 || null != b || c != null)", - "SELECT * FROM x WHERE (a > 1 OR NOT b IS NULL OR NOT c IS NULL)", + write={ + "": "SELECT * FROM x WHERE (a > 1 OR NOT b IS NULL OR NOT c IS NULL)", + }, + ) + self.validate_all( + "from a aggregate { average x }", + write={ + "": "SELECT AVG(x) FROM a", + }, ) - self.validate_identity("from a aggregate { average x }", "SELECT AVG(x) FROM a") - self.validate_identity( + self.validate_all( "from a aggregate { average x, min y, ct = sum z }", - "SELECT AVG(x), MIN(y), COALESCE(SUM(z), 0) AS ct FROM a", + write={ + "": "SELECT AVG(x), MIN(y), COALESCE(SUM(z), 0) AS ct FROM a", + }, ) - self.validate_identity( + self.validate_all( "from a aggregate { average x, min y, sum z }", - "SELECT AVG(x), MIN(y), COALESCE(SUM(z), 0) FROM a", + write={ + "": "SELECT AVG(x), MIN(y), COALESCE(SUM(z), 0) FROM a", + }, ) - self.validate_identity( + self.validate_all( "from a aggregate { min y, b = stddev x, max z }", - "SELECT MIN(y), STDDEV(x) AS b, MAX(z) FROM a", + write={ + "": "SELECT MIN(y), STDDEV(x) AS b, MAX(z) FROM a", + }, ) diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 3e0d600..40413c8 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -107,10 +107,10 @@ WHERE "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (SELECT DISTINCT quarter FROM ad_campaign_types_by_quarter WHERE television = TRUE ORDER BY quarter)) ORDER BY empid" ) self.validate_identity( - "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY ORDER BY quarter)) ORDER BY empid" + "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)) ORDER BY empid" ) self.validate_identity( - "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR IN (ANY)) ORDER BY empid" + "SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY)) ORDER BY empid" ) self.validate_identity( "MERGE INTO my_db AS ids USING (SELECT new_id FROM my_model WHERE NOT col IS NULL) AS new_ids ON ids.type = new_ids.type AND ids.source = new_ids.source WHEN NOT MATCHED THEN INSERT VALUES (new_ids.new_id)" @@ -904,6 +904,28 @@ WHERE }, ) + self.validate_identity( + "SELECT UUID_STRING(), UUID_STRING('fe971b24-9572-4005-b22f-351e9c09274d', 'foo')" + ) + + self.validate_all( + "UUID_STRING('fe971b24-9572-4005-b22f-351e9c09274d', 'foo')", + read={ + "snowflake": "UUID_STRING('fe971b24-9572-4005-b22f-351e9c09274d', 'foo')", + }, + write={ + "hive": "UUID()", + "spark2": "UUID()", + "spark": "UUID()", + "databricks": "UUID()", + "duckdb": "UUID()", + "presto": "UUID()", + "trino": "UUID()", + "postgres": "GEN_RANDOM_UUID()", + "bigquery": "GENERATE_UUID()", + }, + ) + def test_null_treatment(self): self.validate_all( r"SELECT FIRST_VALUE(TABLE1.COLUMN1) OVER (PARTITION BY RANDOM_COLUMN1, RANDOM_COLUMN2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MY_ALIAS FROM TABLE1", @@ -2115,6 +2137,16 @@ SINGLE = TRUE""", self.validate_identity("SELECT t.$23:a.b", "SELECT GET_PATH(t.$23, 'a.b')") self.validate_identity("SELECT t.$17:a[0].b[0].c", "SELECT GET_PATH(t.$17, 'a[0].b[0].c')") + self.validate_all( + """ + SELECT col:"customer's department" + """, + write={ + "snowflake": """SELECT GET_PATH(col, '["customer\\'s department"]')""", + "postgres": "SELECT JSON_EXTRACT_PATH(col, 'customer''s department')", + }, + ) + def test_alter_set_unset(self): self.validate_identity("ALTER TABLE tbl SET DATA_RETENTION_TIME_IN_DAYS=1") self.validate_identity("ALTER TABLE tbl SET DEFAULT_DDL_COLLATION='test'") diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index ab8f963..1f60741 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -552,6 +552,9 @@ CAST('2023-01-01 22:00:00' AS DATETIME); DATE_ADD(x, 1, 'MONTH'); DATE_ADD(x, 1, 'MONTH'); +DATE_ADD(x, 1); +DATE_ADD(x, 1, 'DAY'); + -------------------------------------- -- Comparisons -------------------------------------- diff --git a/tests/test_build.py b/tests/test_build.py index e074fea..d169530 100644 --- a/tests/test_build.py +++ b/tests/test_build.py @@ -731,6 +731,36 @@ class TestBuild(unittest.TestCase): lambda: exp.rename_column("table1", "c1", "c2"), "ALTER TABLE table1 RENAME COLUMN c1 TO c2", ), + ( + lambda: exp.merge( + "WHEN MATCHED THEN UPDATE SET col1 = source.col1", + "WHEN NOT MATCHED THEN INSERT (col1) VALUES (source.col1)", + into="target_table", + using="source_table", + on="target_table.id = source_table.id", + ), + "MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET col1 = source.col1 WHEN NOT MATCHED THEN INSERT (col1) VALUES (source.col1)", + ), + ( + lambda: exp.merge( + "WHEN MATCHED AND source.is_deleted = 1 THEN DELETE", + "WHEN MATCHED THEN UPDATE SET val = source.val", + "WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)", + into="target_table", + using="source_table", + on="target_table.id = source_table.id", + ), + "MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED AND source.is_deleted = 1 THEN DELETE WHEN MATCHED THEN UPDATE SET val = source.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)", + ), + ( + lambda: exp.merge( + "WHEN MATCHED THEN UPDATE SET target.name = source.name", + into=exp.table_("target_table").as_("target"), + using=exp.table_("source_table").as_("source"), + on="target.id = source.id", + ), + "MERGE INTO target_table AS target USING source_table AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.name = source.name", + ), ]: with self.subTest(sql): self.assertEqual(expression().sql(dialect[0] if dialect else None), sql) diff --git a/tests/test_expressions.py b/tests/test_expressions.py index 9bb00de..8e2ac45 100644 --- a/tests/test_expressions.py +++ b/tests/test_expressions.py @@ -1035,7 +1035,6 @@ FROM foo""", self.assertEqual(exp.DataType.build("GEOGRAPHY").sql(), "GEOGRAPHY") self.assertEqual(exp.DataType.build("GEOMETRY").sql(), "GEOMETRY") self.assertEqual(exp.DataType.build("STRUCT").sql(), "STRUCT") - self.assertEqual(exp.DataType.build("NULLABLE").sql(), "NULLABLE") self.assertEqual(exp.DataType.build("HLLSKETCH", dialect="redshift").sql(), "HLLSKETCH") self.assertEqual(exp.DataType.build("HSTORE", dialect="postgres").sql(), "HSTORE") self.assertEqual(exp.DataType.build("NULL").sql(), "NULL") diff --git a/tests/test_optimizer.py b/tests/test_optimizer.py index fe5a4d7..857ba1a 100644 --- a/tests/test_optimizer.py +++ b/tests/test_optimizer.py @@ -397,6 +397,38 @@ class TestOptimizer(unittest.TestCase): "SELECT u.user_id AS user_id, l.log_date AS log_date FROM users AS u CROSS JOIN LATERAL (SELECT l1.log_date AS log_date FROM (SELECT l.log_date AS log_date FROM logs AS l WHERE l.user_id = u.user_id AND l.log_date <= 100 ORDER BY l.log_date LIMIT 1) AS l1) AS l", ) + self.assertEqual( + optimizer.qualify.qualify( + parse_one( + "SELECT A.b_id FROM A JOIN B ON A.b_id=B.b_id JOIN C USING(c_id)", + dialect="postgres", + ), + schema={ + "A": {"b_id": "int"}, + "B": {"b_id": "int", "c_id": "int"}, + "C": {"c_id": "int"}, + }, + quote_identifiers=False, + ).sql("postgres"), + "SELECT a.b_id AS b_id FROM a AS a JOIN b AS b ON a.b_id = b.b_id JOIN c AS c ON b.c_id = c.c_id", + ) + self.assertEqual( + optimizer.qualify.qualify( + parse_one( + "SELECT A.b_id FROM A JOIN B ON A.b_id=B.b_id JOIN C ON B.b_id = C.b_id JOIN D USING(d_id)", + dialect="postgres", + ), + schema={ + "A": {"b_id": "int"}, + "B": {"b_id": "int", "d_id": "int"}, + "C": {"b_id": "int"}, + "D": {"d_id": "int"}, + }, + quote_identifiers=False, + ).sql("postgres"), + "SELECT a.b_id AS b_id FROM a AS a JOIN b AS b ON a.b_id = b.b_id JOIN c AS c ON b.b_id = c.b_id JOIN d AS d ON b.d_id = d.d_id", + ) + self.check_file( "qualify_columns", qualify_columns, |