Merging upstream version 11.3.0.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

9 files changed, 202 insertions, 12 deletions

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)",
@@ -1280,6 +1280,16 @@ class TestDialect(Validator):
             },
         )
         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={
                 "hive": "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",
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
@@ -107,6 +107,47 @@ class TestPostgres(Validator):
         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={
                 "": "POWER(x, y)",
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
@@ -39,6 +39,12 @@ class TestRedshift(Validator):
             },
         )
         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={
                 "redshift": "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid",
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,11 +10,59 @@ 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={
                 "snowflake": "CREATE OR REPLACE TEMPORARY TABLE x (y DECIMAL AUTOINCREMENT START 0 INCREMENT 1)",
@@ -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(
@@ -171,6 +223,20 @@ class TestSnowflake(Validator):
             },
         )
         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={
                 "snowflake": UnsupportedError,
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;