From ebec59cc5cb6c6856705bf82ced7fe8d9f75b0d0 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 7 Mar 2023 19:09:31 +0100 Subject: Merging upstream version 11.3.0. Signed-off-by: Daniel Baumann --- tests/dialects/test_dialect.py | 12 +++++- tests/dialects/test_duckdb.py | 4 +- tests/dialects/test_oracle.py | 5 +++ tests/dialects/test_postgres.py | 41 ++++++++++++++++++++ tests/dialects/test_redshift.py | 6 +++ tests/dialects/test_snowflake.py | 68 +++++++++++++++++++++++++++++++++- tests/dialects/test_teradata.py | 21 +++++++---- tests/fixtures/identity.sql | 12 +++++- tests/fixtures/optimizer/optimizer.sql | 45 ++++++++++++++++++++++ 9 files changed, 202 insertions(+), 12 deletions(-) (limited to 'tests') diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 3186390..5054d94 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -520,7 +520,7 @@ class TestDialect(Validator): "mysql": "DATE_ADD(x, INTERVAL 1 DAY)", "postgres": "x + INTERVAL '1' 'day'", "presto": "DATE_ADD('day', 1, x)", - "snowflake": "DATEADD(x, 1, 'day')", + "snowflake": "DATEADD(day, 1, x)", "spark": "DATE_ADD(x, 1)", "sqlite": "DATE(x, '1 day')", "starrocks": "DATE_ADD(x, INTERVAL 1 DAY)", @@ -1279,6 +1279,16 @@ class TestDialect(Validator): "sqlite": "SELECT y AS x FROM my_table AS t", }, ) + self.validate_all( + "SELECT * FROM (SELECT * FROM my_table AS t) AS tbl", + write={ + "drill": "SELECT * FROM (SELECT * FROM my_table AS t) AS tbl", + "hive": "SELECT * FROM (SELECT * FROM my_table AS t) AS tbl", + "oracle": "SELECT * FROM (SELECT * FROM my_table t) tbl", + "postgres": "SELECT * FROM (SELECT * FROM my_table AS t) AS tbl", + "sqlite": "SELECT * FROM (SELECT * FROM my_table AS t) AS tbl", + }, + ) self.validate_all( "WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, e AS d FROM table2) SELECT b, d AS dd FROM cte1 AS t JOIN cte2 WHERE cte1.a = cte2.c", write={ diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py index 46e75c0..0efb7e7 100644 --- a/tests/dialects/test_duckdb.py +++ b/tests/dialects/test_duckdb.py @@ -21,10 +21,10 @@ class TestDuckDB(Validator): self.validate_all( "EPOCH_MS(x)", write={ - "bigquery": "UNIX_TO_TIME(x / 1000)", + "bigquery": "UNIX_TO_TIME(CAST(x / 1000 AS INT64))", "duckdb": "TO_TIMESTAMP(x / 1000)", "presto": "FROM_UNIXTIME(x / 1000)", - "spark": "FROM_UNIXTIME(x / 1000)", + "spark": "FROM_UNIXTIME(CAST(x / 1000 AS INT))", }, ) self.validate_all( diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index f85a117..4dc3f1b 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -7,6 +7,11 @@ class TestOracle(Validator): def test_oracle(self): self.validate_identity("SELECT * FROM V$SESSION") + def test_join_marker(self): + self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y") + self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y = e2.y (+)") + self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y (+)") + def test_xml_table(self): self.validate_identity("XMLTABLE('x')") self.validate_identity("XMLTABLE('x' RETURNING SEQUENCE BY REF)") diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py index f0117bc..5c4a23e 100644 --- a/tests/dialects/test_postgres.py +++ b/tests/dialects/test_postgres.py @@ -106,6 +106,47 @@ class TestPostgres(Validator): self.validate_identity("x ~ 'y'") self.validate_identity("x ~* 'y'") + self.validate_all( + "1 / 2", + read={ + "drill": "1 / 2", + "duckdb": "1 / 2", + "postgres": "1 / 2", + "presto": "1 / 2", + "redshift": "1 / 2", + "sqlite": "1 / 2", + "teradata": "1 / 2", + "trino": "1 / 2", + "tsql": "1 / 2", + }, + write={ + "drill": "1 / 2", + "duckdb": "1 / 2", + "postgres": "1 / 2", + "presto": "1 / 2", + "redshift": "1 / 2", + "sqlite": "1 / 2", + "teradata": "1 / 2", + "trino": "1 / 2", + "tsql": "1 / 2", + "bigquery": "CAST(1 / 2 AS INT64)", + "clickhouse": "CAST(1 / 2 AS Int32)", + "databricks": "CAST(1 / 2 AS INT)", + "hive": "CAST(1 / 2 AS INT)", + "mysql": "CAST(1 / 2 AS INT)", + "oracle": "CAST(1 / 2 AS NUMBER)", + "snowflake": "CAST(1 / 2 AS INT)", + "spark": "CAST(1 / 2 AS INT)", + "starrocks": "CAST(1 / 2 AS INT)", + }, + ) + self.validate_all( + "SELECT (DATE '2016-01-10', DATE '2016-02-01') OVERLAPS (DATE '2016-01-20', DATE '2016-02-10')", + write={ + "postgres": "SELECT (CAST('2016-01-10' AS DATE), CAST('2016-02-01' AS DATE)) OVERLAPS (CAST('2016-01-20' AS DATE), CAST('2016-02-10' AS DATE))", + "tsql": "SELECT (CAST('2016-01-10' AS DATE), CAST('2016-02-01' AS DATE)) OVERLAPS (CAST('2016-01-20' AS DATE), CAST('2016-02-10' AS DATE))", + }, + ) self.validate_all( "x ^ y", write={ diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index fa4d422..640706a 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -38,6 +38,12 @@ class TestRedshift(Validator): "redshift": "SELECT CAST('abc' AS VARBYTE)", }, ) + self.validate_all( + "SELECT 'abc'::CHARACTER", + write={ + "redshift": "SELECT CAST('abc' AS CHAR)", + }, + ) self.validate_all( "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid", write={ diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index a934c78..3358227 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -10,10 +10,58 @@ class TestSnowflake(Validator): self.validate_identity("SELECT REGEXP_LIKE(a, b, c)") self.validate_identity("PUT file:///dir/tmp.csv @%table") self.validate_identity("CREATE TABLE foo (bar FLOAT AUTOINCREMENT START 0 INCREMENT 1)") + self.validate_identity("ALTER TABLE IF EXISTS foo SET TAG a = 'a', b = 'b', c = 'c'") + self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c") self.validate_identity( 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)' ) + self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'") + self.validate_all( + "1 / 2", + read={ + "bigquery": "1 / 2", + "clickhouse": "1 / 2", + "databricks": "1 / 2", + "hive": "1 / 2", + "mysql": "1 / 2", + "oracle": "1 / 2", + "snowflake": "1 / 2", + "spark": "1 / 2", + "starrocks": "1 / 2", + }, + write={ + "bigquery": "1 / 2", + "clickhouse": "1 / 2", + "databricks": "1 / 2", + "hive": "1 / 2", + "mysql": "1 / 2", + "oracle": "1 / 2", + "snowflake": "1 / 2", + "spark": "1 / 2", + "starrocks": "1 / 2", + "drill": "CAST(1 AS DOUBLE) / 2", + "duckdb": "CAST(1 AS DOUBLE) / 2", + "postgres": "CAST(1 AS DOUBLE PRECISION) / 2", + "presto": "CAST(1 AS DOUBLE) / 2", + "redshift": "CAST(1 AS DOUBLE PRECISION) / 2", + "sqlite": "CAST(1 AS REAL) / 2", + "teradata": "CAST(1 AS DOUBLE) / 2", + "trino": "CAST(1 AS DOUBLE) / 2", + "tsql": "CAST(1 AS DOUBLE) / 2", + }, + ) + self.validate_all( + "DIV0(foo, bar)", + write={ + "snowflake": "IFF(bar = 0, 0, foo / bar)", + "sqlite": "CASE WHEN bar = 0 THEN 0 ELSE CAST(foo AS REAL) / bar END", + "presto": "IF(bar = 0, 0, CAST(foo AS DOUBLE) / bar)", + "spark": "IF(bar = 0, 0, foo / bar)", + "hive": "IF(bar = 0, 0, foo / bar)", + "duckdb": "CASE WHEN bar = 0 THEN 0 ELSE CAST(foo AS DOUBLE) / bar END", + }, + ) self.validate_all( "CREATE OR REPLACE TEMPORARY TABLE x (y NUMBER IDENTITY(0, 1))", write={ @@ -63,9 +111,13 @@ class TestSnowflake(Validator): }, ) self.validate_all( - "SELECT * EXCLUDE a, b RENAME (c AS d, E as F) FROM xxx", + "SELECT * EXCLUDE (a, b) RENAME (c AS d, E AS F) FROM xxx", + read={ + "duckdb": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx", + }, write={ "snowflake": "SELECT * EXCLUDE (a, b) RENAME (c AS d, E AS F) FROM xxx", + "duckdb": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx", }, ) self.validate_all( @@ -170,6 +222,20 @@ class TestSnowflake(Validator): "snowflake": "SELECT ARRAY_AGG(DISTINCT a)", }, ) + self.validate_all( + "ARRAY_TO_STRING(x, '')", + write={ + "spark": "ARRAY_JOIN(x, '')", + "snowflake": "ARRAY_TO_STRING(x, '')", + }, + ) + self.validate_all( + "TO_ARRAY(x)", + write={ + "spark": "ARRAY(x)", + "snowflake": "[x]", + }, + ) self.validate_all( "SELECT * FROM a INTERSECT ALL SELECT * FROM b", write={ diff --git a/tests/dialects/test_teradata.py b/tests/dialects/test_teradata.py index dd251ab..5d4f7db 100644 --- a/tests/dialects/test_teradata.py +++ b/tests/dialects/test_teradata.py @@ -24,19 +24,21 @@ class TestTeradata(Validator): def test_create(self): self.validate_identity("CREATE TABLE x (y INT) PRIMARY INDEX (y) PARTITION BY y INDEX (y)") + self.validate_identity( + "CREATE TABLE a (b INT) PRIMARY INDEX (y) PARTITION BY RANGE_N(b BETWEEN 'a', 'b' AND 'c' EACH '1')" + ) + self.validate_identity( + "CREATE TABLE a (b INT) PARTITION BY RANGE_N(b BETWEEN 0, 1 AND 2 EACH 1)" + ) + self.validate_identity( + "CREATE TABLE a (b INT) PARTITION BY RANGE_N(b BETWEEN *, 1 AND * EACH b) INDEX (a)" + ) self.validate_all( "REPLACE VIEW a AS (SELECT b FROM c)", write={"teradata": "CREATE OR REPLACE VIEW a AS (SELECT b FROM c)"}, ) - self.validate_all( - "SEL a FROM b", - write={"teradata": "SELECT a FROM b"}, - ) - - self.validate_identity("CREATE VOLATILE TABLE a (b INT)") - def test_insert(self): self.validate_all( "INS INTO x SELECT * FROM y", write={"teradata": "INSERT INTO x SELECT * FROM y"} @@ -54,6 +56,11 @@ class TestTeradata(Validator): self.validate_all("a NE b", write={"teradata": "a <> b"}) self.validate_all("a NOT= b", write={"teradata": "a <> b"}) + self.validate_all( + "SEL a FROM b", + write={"teradata": "SELECT a FROM b"}, + ) + def test_datatype(self): self.validate_all( "CREATE TABLE z (a ST_GEOMETRY(1))", diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql index 5e2260c..0677a05 100644 --- a/tests/fixtures/identity.sql +++ b/tests/fixtures/identity.sql @@ -558,7 +558,7 @@ CREATE TABLE a, BEFORE JOURNAL, AFTER JOURNAL, FREESPACE=1, DEFAULT DATABLOCKSIZ CREATE TABLE a, DUAL JOURNAL, DUAL AFTER JOURNAL, MERGEBLOCKRATIO=1 PERCENT, DATABLOCKSIZE=10 KILOBYTES (a INT) CREATE TABLE a, DUAL BEFORE JOURNAL, LOCAL AFTER JOURNAL, MAXIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=AUTOTEMP(c1 INT) (a INT) CREATE SET GLOBAL TEMPORARY TABLE a, NO BEFORE JOURNAL, NO AFTER JOURNAL, MINIMUM DATABLOCKSIZE, BLOCKCOMPRESSION=NEVER (a INT) -CREATE MULTISET TABLE a, NOT LOCAL AFTER JOURNAL, FREESPACE=1 PERCENT, DATABLOCKSIZE=10 BYTES, WITH NO CONCURRENT ISOLATED LOADING FOR ALL (a INT) +CREATE VOLATILE MULTISET TABLE a, NOT LOCAL AFTER JOURNAL, FREESPACE=1 PERCENT, DATABLOCKSIZE=10 BYTES, WITH NO CONCURRENT ISOLATED LOADING FOR ALL (a INT) CREATE ALGORITHM=UNDEFINED DEFINER=foo@% SQL SECURITY DEFINER VIEW a AS (SELECT a FROM b) CREATE TEMPORARY TABLE x AS SELECT a FROM d CREATE TEMPORARY TABLE IF NOT EXISTS x AS SELECT a FROM d @@ -591,6 +591,8 @@ CREATE UNIQUE INDEX abc ON t (a, b, b) CREATE UNIQUE INDEX IF NOT EXISTS my_idx ON tbl (a, b) CREATE SCHEMA x CREATE SCHEMA IF NOT EXISTS y +CREATE DATABASE x +CREATE DATABASE IF NOT EXISTS y CREATE PROCEDURE IF NOT EXISTS a.b.c() AS 'DECLARE BEGIN; END' CREATE OR REPLACE STAGE DESCRIBE x @@ -619,6 +621,7 @@ 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 +ALTER schema doo ANALYZE a.y DELETE FROM x WHERE y > 1 DELETE FROM y @@ -671,6 +674,11 @@ UPDATE db.tbl_name SET foo = 123, foo_1 = 234 WHERE tbl_name.bar = 234 TRUNCATE TABLE x OPTIMIZE TABLE y VACUUM FREEZE my_table +COMMENT ON ACCESS METHOD gin IS 'GIN index access method' +COMMENT ON COLUMN my_schema.my_table.my_column IS 'Employee ID number' +COMMENT ON DATABASE my_database IS 'Development Database' +COMMENT ON PROCEDURE my_proc(integer, integer) IS 'Runs a report' +COMMENT ON TABLE my_schema.my_table IS 'Employee Information' WITH a AS (SELECT 1) INSERT INTO b SELECT * FROM a WITH a AS (SELECT * FROM b) UPDATE a SET col = 1 WITH a AS (SELECT * FROM b) CREATE TABLE b AS SELECT * FROM a @@ -753,3 +761,5 @@ SELECT RIGHT.FOO FROM BLA AS RIGHT SELECT LEFT FROM LEFT LEFT JOIN RIGHT RIGHT JOIN LEFT SELECT * FROM x WHERE name ILIKE ANY XXX('a', 'b') SELECT * FROM x WHERE name LIKE ANY XXX('a', 'b') +a OVERLAPS b +GRANT INSERT ON foo TO bla diff --git a/tests/fixtures/optimizer/optimizer.sql b/tests/fixtures/optimizer/optimizer.sql index 6ccf24e..a14e325 100644 --- a/tests/fixtures/optimizer/optimizer.sql +++ b/tests/fixtures/optimizer/optimizer.sql @@ -385,3 +385,48 @@ SELECT "x"."a" + 1 AS "b", "x"."b" + 1 AS "c" FROM "x" AS "x"; + +# title: left join doesnt push down predicate to join in merge subqueries +# execute: false +SELECT + main_query.id, + main_query.score +FROM ( + SELECT + alias_1.id, + score + FROM ( + SELECT + company_table.score AS score, + id + FROM company_table + ) AS alias_1 + JOIN ( + SELECT + id + FROM ( + SELECT + company_table_2.id, + CASE WHEN unlocked.company_id IS NULL THEN 0 ELSE 1 END AS is_exported + FROM company_table AS company_table_2 + LEFT JOIN unlocked AS unlocked + ON company_table_2.id = unlocked.company_id + ) + WHERE + NOT id IS NULL AND is_exported = FALSE + ) AS alias_2 + ON ( + alias_1.id = alias_2.id + ) +) AS main_query; +SELECT + "company_table"."id" AS "id", + "company_table"."score" AS "score" +FROM "company_table" AS "company_table" +JOIN "company_table" AS "company_table_2" + ON "company_table"."id" = "company_table_2"."id" +LEFT JOIN "unlocked" AS "unlocked" + ON "company_table_2"."id" = "unlocked"."company_id" +WHERE + CASE WHEN "unlocked"."company_id" IS NULL THEN 0 ELSE 1 END = FALSE + AND NOT "company_table_2"."id" IS NULL; -- cgit v1.2.3