Adding upstream version 18.11.2.upstream/18.11.2

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

1 files changed, 80 insertions, 31 deletions

diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index a217394..7c36bea 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1,6 +1,7 @@
 from unittest import mock
 
 from sqlglot import UnsupportedError, exp, parse_one
+from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
 from tests.dialects.test_dialect import Validator
 
 
@@ -8,34 +9,6 @@ class TestSnowflake(Validator):
     dialect = "snowflake"
 
     def test_snowflake(self):
-        self.validate_identity(
-            'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
-        )
-
-        self.validate_all(
-            "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
-            read={
-                "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
-            },
-            write={
-                "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
-                "snowflake": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
-            },
-        )
-        self.validate_all(
-            "SELECT INSERT(a, 0, 0, 'b')",
-            read={
-                "mysql": "SELECT INSERT(a, 0, 0, 'b')",
-                "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
-                "tsql": "SELECT STUFF(a, 0, 0, 'b')",
-            },
-            write={
-                "mysql": "SELECT INSERT(a, 0, 0, 'b')",
-                "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
-                "tsql": "SELECT STUFF(a, 0, 0, 'b')",
-            },
-        )
-
         self.validate_identity("LISTAGG(data['some_field'], ',')")
         self.validate_identity("WEEKOFYEAR(tstamp)")
         self.validate_identity("SELECT SUM(amount) FROM mytable GROUP BY ALL")
@@ -54,7 +27,6 @@ class TestSnowflake(Validator):
         self.validate_identity("$x")  # parameter
         self.validate_identity("a$b")  # valid snowflake identifier
         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")
@@ -65,12 +37,16 @@ class TestSnowflake(Validator):
         self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
         self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')")
         self.validate_identity(
-            'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)'
+            'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
         )
         self.validate_identity(
             "SELECT state, city, SUM(retail_price * quantity) AS gross_revenue FROM sales GROUP BY ALL"
         )
         self.validate_identity(
+            "SELECT * FROM foo window",
+            "SELECT * FROM foo AS window",
+        )
+        self.validate_identity(
             r"SELECT RLIKE(a, $$regular expression with \ characters: \d{2}-\d{3}-\d{4}$$, 'i') FROM log_source",
             r"SELECT REGEXP_LIKE(a, 'regular expression with \\ characters: \\d{2}-\\d{3}-\\d{4}', 'i') FROM log_source",
         )
@@ -88,6 +64,36 @@ class TestSnowflake(Validator):
         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 COLLATE('B', 'und:ci')",
+            write={
+                "bigquery": "SELECT COLLATE('B', 'und:ci')",
+                "snowflake": "SELECT COLLATE('B', 'und:ci')",
+            },
+        )
+        self.validate_all(
+            "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+            read={
+                "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+            },
+            write={
+                "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+                "snowflake": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+            },
+        )
+        self.validate_all(
+            "SELECT INSERT(a, 0, 0, 'b')",
+            read={
+                "mysql": "SELECT INSERT(a, 0, 0, 'b')",
+                "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
+                "tsql": "SELECT STUFF(a, 0, 0, 'b')",
+            },
+            write={
+                "mysql": "SELECT INSERT(a, 0, 0, 'b')",
+                "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
+                "tsql": "SELECT STUFF(a, 0, 0, 'b')",
+            },
+        )
+        self.validate_all(
             "ARRAY_GENERATE_RANGE(0, 3)",
             write={
                 "bigquery": "GENERATE_ARRAY(0, 3 - 1)",
@@ -513,6 +519,40 @@ class TestSnowflake(Validator):
             },
         )
 
+    def test_staged_files(self):
+        # Ensure we don't treat staged file paths as identifiers (i.e. they're not normalized)
+        staged_file = parse_one("SELECT * FROM @foo", read="snowflake")
+        self.assertEqual(
+            normalize_identifiers(staged_file, dialect="snowflake").sql(dialect="snowflake"),
+            staged_file.sql(dialect="snowflake"),
+        )
+
+        self.validate_identity("SELECT * FROM @~")
+        self.validate_identity("SELECT * FROM @~/some/path/to/file.csv")
+        self.validate_identity("SELECT * FROM @mystage")
+        self.validate_identity("SELECT * FROM '@mystage'")
+        self.validate_identity("SELECT * FROM @namespace.mystage/path/to/file.json.gz")
+        self.validate_identity("SELECT * FROM @namespace.%table_name/path/to/file.json.gz")
+        self.validate_identity("SELECT * FROM '@external/location' (FILE_FORMAT => 'path.to.csv')")
+        self.validate_identity("PUT file:///dir/tmp.csv @%table")
+        self.validate_identity(
+            'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)'
+        )
+        self.validate_identity(
+            "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla"
+        )
+        self.validate_identity(
+            "SELECT t.$1, t.$2 FROM @mystage1 (FILE_FORMAT => 'myformat', PATTERN => '.*data.*[.]csv.gz') AS t"
+        )
+        self.validate_identity(
+            "SELECT parse_json($1):a.b FROM @mystage2/data1.json.gz",
+            "SELECT PARSE_JSON($1)['a'].b FROM @mystage2/data1.json.gz",
+        )
+        self.validate_identity(
+            "SELECT * FROM @mystage t (c1)",
+            "SELECT * FROM @mystage AS t(c1)",
+        )
+
     def test_sample(self):
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)")
         self.validate_identity("SELECT * FROM testtable TABLESAMPLE (100)")
@@ -660,7 +700,6 @@ class TestSnowflake(Validator):
         self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
         self.validate_identity("CREATE DATABASE mytestdb_clone CLONE mytestdb")
         self.validate_identity("CREATE SCHEMA mytestschema_clone CLONE testschema")
-        self.validate_identity("CREATE TABLE orders_clone CLONE orders")
         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(
@@ -680,6 +719,16 @@ class TestSnowflake(Validator):
         )
 
         self.validate_all(
+            "CREATE TABLE orders_clone CLONE orders",
+            read={
+                "bigquery": "CREATE TABLE orders_clone CLONE orders",
+            },
+            write={
+                "bigquery": "CREATE TABLE orders_clone CLONE orders",
+                "snowflake": "CREATE TABLE orders_clone CLONE orders",
+            },
+        )
+        self.validate_all(
             "CREATE OR REPLACE TRANSIENT TABLE a (id INT)",
             read={
                 "postgres": "CREATE OR REPLACE TRANSIENT TABLE a (id INT)",