summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_snowflake.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2022-11-11 08:54:30 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2022-11-11 08:54:30 +0000
commit9ebe8c99ba4be74ccebf1b013f4e56ec09e023c1 (patch)
tree7ab2f39fbb6fd832aeea5cef45b54bfd59ba5ba5 /tests/dialects/test_snowflake.py
parentAdding upstream version 9.0.6. (diff)
downloadsqlglot-9ebe8c99ba4be74ccebf1b013f4e56ec09e023c1.tar.xz
sqlglot-9ebe8c99ba4be74ccebf1b013f4e56ec09e023c1.zip
Adding upstream version 10.0.1.upstream/10.0.1
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_snowflake.py')
-rw-r--r--tests/dialects/test_snowflake.py117
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,
+ )