Adding upstream version 20.3.0.upstream/20.3.0

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

1 files changed, 97 insertions, 6 deletions

diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 997c27b..4d8168a 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -36,6 +36,8 @@ WHERE
   )""",
         )
 
+        self.validate_identity("SELECT TO_ARRAY(CAST(x AS ARRAY))")
+        self.validate_identity("SELECT TO_ARRAY(CAST(['test'] AS VARIANT))")
         self.validate_identity("SELECT user_id, value FROM table_name sample ($s) SEED (0)")
         self.validate_identity("SELECT ARRAY_UNIQUE_AGG(x)")
         self.validate_identity("SELECT OBJECT_CONSTRUCT()")
@@ -73,6 +75,18 @@ WHERE
             'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
         )
         self.validate_identity(
+            "SELECT * FROM foo at",
+            "SELECT * FROM foo AS at",
+        )
+        self.validate_identity(
+            "SELECT * FROM foo before",
+            "SELECT * FROM foo AS before",
+        )
+        self.validate_identity(
+            "SELECT * FROM foo at (col)",
+            "SELECT * FROM foo AS at(col)",
+        )
+        self.validate_identity(
             "SELECT * FROM unnest(x) with ordinality",
             "SELECT * FROM TABLE(FLATTEN(INPUT => x)) AS _u(seq, key, path, index, value, this)",
         )
@@ -115,11 +129,37 @@ WHERE
             "SELECT TO_TIMESTAMP(x) FROM t",
             "SELECT CAST(x AS TIMESTAMPNTZ) FROM t",
         )
+        self.validate_identity(
+            "CAST(x AS BYTEINT)",
+            "CAST(x AS INT)",
+        )
+        self.validate_identity(
+            "CAST(x AS CHAR VARYING)",
+            "CAST(x AS VARCHAR)",
+        )
+        self.validate_identity(
+            "CAST(x AS CHARACTER VARYING)",
+            "CAST(x AS VARCHAR)",
+        )
+        self.validate_identity(
+            "CAST(x AS NCHAR VARYING)",
+            "CAST(x AS VARCHAR)",
+        )
 
-        self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"})
-        self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
-        self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
-        self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
+        self.validate_all(
+            "SELECT TO_ARRAY(['test'])",
+            write={
+                "snowflake": "SELECT TO_ARRAY(['test'])",
+                "spark": "SELECT ARRAY('test')",
+            },
+        )
+        self.validate_all(
+            "SELECT TO_ARRAY(['test'])",
+            write={
+                "snowflake": "SELECT TO_ARRAY(['test'])",
+                "spark": "SELECT ARRAY('test')",
+            },
+        )
         self.validate_all(
             # We need to qualify the columns in this query because "value" would be ambiguous
             'WITH t(x, "value") AS (SELECT [1, 2, 3], 1) SELECT IFF(_u.pos = _u_2.pos_2, _u_2."value", NULL) AS "value" FROM t, TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(0, (GREATEST(ARRAY_SIZE(t.x)) - 1) + 1))) AS _u(seq, key, path, index, pos, this) CROSS JOIN TABLE(FLATTEN(INPUT => t.x)) AS _u_2(seq, key, path, pos_2, "value", this) WHERE _u.pos = _u_2.pos_2 OR (_u.pos > (ARRAY_SIZE(t.x) - 1) AND _u_2.pos_2 = (ARRAY_SIZE(t.x) - 1))',
@@ -489,8 +529,8 @@ WHERE
         self.validate_all(
             "TO_ARRAY(x)",
             write={
-                "spark": "ARRAY(x)",
-                "snowflake": "[x]",
+                "spark": "IF(x IS NULL, NULL, ARRAY(x))",
+                "snowflake": "TO_ARRAY(x)",
             },
         )
         self.validate_all(
@@ -626,6 +666,10 @@ WHERE
             "SELECT * FROM @mystage t (c1)",
             "SELECT * FROM @mystage AS t(c1)",
         )
+        self.validate_identity(
+            "SELECT * FROM @foo/bar (PATTERN => 'test', FILE_FORMAT => ds_sandbox.test.my_csv_format) AS bla",
+            "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla",
+        )
 
     def test_sample(self):
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)")
@@ -775,6 +819,53 @@ WHERE
             },
         )
 
+    def test_historical_data(self):
+        self.validate_identity("SELECT * FROM my_table AT (STATEMENT => $query_id_var)")
+        self.validate_identity("SELECT * FROM my_table AT (OFFSET => -60 * 5)")
+        self.validate_identity("SELECT * FROM my_table BEFORE (STATEMENT => $query_id_var)")
+        self.validate_identity("SELECT * FROM my_table BEFORE (OFFSET => -60 * 5)")
+        self.validate_identity("CREATE SCHEMA restored_schema CLONE my_schema AT (OFFSET => -3600)")
+        self.validate_identity(
+            "CREATE TABLE restored_table CLONE my_table AT (TIMESTAMP => CAST('Sat, 09 May 2015 01:01:00 +0300' AS TIMESTAMPTZ))",
+        )
+        self.validate_identity(
+            "CREATE DATABASE restored_db CLONE my_db BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (TIMESTAMP => TO_TIMESTAMP(1432669154242, 3))"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (OFFSET => -60 * 5) AS T WHERE T.flag = 'valid'"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726')"
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp)",
+            "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPNTZ))",
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table AT(TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz)",
+            "SELECT * FROM my_table AT (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))",
+        )
+        self.validate_identity(
+            "SELECT * FROM my_table BEFORE (TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp_tz);",
+            "SELECT * FROM my_table BEFORE (TIMESTAMP => CAST('Fri, 01 May 2015 16:20:00 -0700' AS TIMESTAMPTZ))",
+        )
+        self.validate_identity(
+            """
+            SELECT oldt.* , newt.*
+            FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt
+            FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt
+            ON oldt.id = newt.id
+            WHERE oldt.id IS NULL OR newt.id IS NULL;
+            """,
+            "SELECT oldt.*, newt.* FROM my_table BEFORE (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt FULL OUTER JOIN my_table AT (STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt ON oldt.id = newt.id WHERE oldt.id IS NULL OR newt.id IS NULL",
+        )
+
     def test_ddl(self):
         self.validate_identity(
             """create external table et2(