Adding upstream version 10.0.1.upstream/10.0.1

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

1 files changed, 113 insertions, 4 deletions

diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index fea2311..1846b17 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -336,7 +336,8 @@ class TestSnowflake(Validator):
     def test_table_literal(self):
         # All examples from https://docs.snowflake.com/en/sql-reference/literals-table.html
         self.validate_all(
-            r"""SELECT * FROM TABLE('MYTABLE')""", write={"snowflake": r"""SELECT * FROM TABLE('MYTABLE')"""}
+            r"""SELECT * FROM TABLE('MYTABLE')""",
+            write={"snowflake": r"""SELECT * FROM TABLE('MYTABLE')"""},
         )
 
         self.validate_all(
@@ -352,15 +353,123 @@ class TestSnowflake(Validator):
             write={"snowflake": r"""SELECT * FROM TABLE('MYDB. "MYSCHEMA"."MYTABLE"')"""},
         )
 
-        self.validate_all(r"""SELECT * FROM TABLE($MYVAR)""", write={"snowflake": r"""SELECT * FROM TABLE($MYVAR)"""})
+        self.validate_all(
+            r"""SELECT * FROM TABLE($MYVAR)""",
+            write={"snowflake": r"""SELECT * FROM TABLE($MYVAR)"""},
+        )
 
-        self.validate_all(r"""SELECT * FROM TABLE(?)""", write={"snowflake": r"""SELECT * FROM TABLE(?)"""})
+        self.validate_all(
+            r"""SELECT * FROM TABLE(?)""", write={"snowflake": r"""SELECT * FROM TABLE(?)"""}
+        )
 
         self.validate_all(
-            r"""SELECT * FROM TABLE(:BINDING)""", write={"snowflake": r"""SELECT * FROM TABLE(:BINDING)"""}
+            r"""SELECT * FROM TABLE(:BINDING)""",
+            write={"snowflake": r"""SELECT * FROM TABLE(:BINDING)"""},
         )
 
         self.validate_all(
             r"""SELECT * FROM TABLE($MYVAR) WHERE COL1 = 10""",
             write={"snowflake": r"""SELECT * FROM TABLE($MYVAR) WHERE COL1 = 10"""},
         )
+
+    def test_flatten(self):
+        self.validate_all(
+            """
+            select
+              dag_report.acct_id,
+              dag_report.report_date,
+              dag_report.report_uuid,
+              dag_report.airflow_name,
+              dag_report.dag_id,
+              f.value::varchar as operator
+            from cs.telescope.dag_report,
+            table(flatten(input=>split(operators, ','))) f
+            """,
+            write={
+                "snowflake": """SELECT
+  dag_report.acct_id,
+  dag_report.report_date,
+  dag_report.report_uuid,
+  dag_report.airflow_name,
+  dag_report.dag_id,
+  CAST(f.value AS VARCHAR) AS operator
+FROM cs.telescope.dag_report, TABLE(FLATTEN(input => SPLIT(operators, ','))) AS f"""
+            },
+            pretty=True,
+        )
+
+        # All examples from https://docs.snowflake.com/en/sql-reference/functions/flatten.html#syntax
+        self.validate_all(
+            "SELECT * FROM TABLE(FLATTEN(input => parse_json('[1, ,77]'))) f",
+            write={
+                "snowflake": "SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('[1, ,77]'))) AS f"
+            },
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), outer => true)) f""",
+            write={
+                "snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('{"a":1, "b":[77,88]}'), outer => TRUE)) AS f"""
+            },
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), path => 'b')) f""",
+            write={
+                "snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('{"a":1, "b":[77,88]}'), path => 'b')) AS f"""
+            },
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('[]'))) f""",
+            write={"snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('[]'))) AS f"""},
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('[]'), outer => true)) f""",
+            write={
+                "snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('[]'), outer => TRUE)) AS f"""
+            },
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) f""",
+            write={
+                "snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'))) AS f"""
+            },
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => true)) f""",
+            write={
+                "snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => TRUE)) AS f"""
+            },
+        )
+
+        self.validate_all(
+            """SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => true, mode => 'object')) f""",
+            write={
+                "snowflake": """SELECT * FROM TABLE(FLATTEN(input => PARSE_JSON('{"a":1, "b":[77,88], "c": {"d":"X"}}'), recursive => TRUE, mode => 'object')) AS f"""
+            },
+        )
+
+        self.validate_all(
+            """
+            SELECT id as "ID",
+              f.value AS "Contact",
+              f1.value:type AS "Type",
+              f1.value:content AS "Details"
+            FROM persons p,
+              lateral flatten(input => p.c, path => 'contact') f,
+              lateral flatten(input => f.value:business) f1
+            """,
+            write={
+                "snowflake": """SELECT
+  id AS "ID",
+  f.value AS "Contact",
+  f1.value['type'] AS "Type",
+  f1.value['content'] AS "Details"
+FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') f, LATERAL FLATTEN(input => f.value['business']) f1""",
+            },
+            pretty=True,
+        )