From 42a1548cecf48d18233f56e3385cf9c89abcb9c2 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 3 Mar 2024 15:11:07 +0100 Subject: Merging upstream version 22.2.0. Signed-off-by: Daniel Baumann --- tests/dialects/test_snowflake.py | 167 +++++++++++++++++++++++++++------------ 1 file changed, 117 insertions(+), 50 deletions(-) (limited to 'tests/dialects/test_snowflake.py') diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 4e4feb3..e48f811 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -40,6 +40,7 @@ WHERE )""", ) + self.validate_identity("ALTER TABLE authors ADD CONSTRAINT c1 UNIQUE (id, email)") self.validate_identity("RM @parquet_stage", check_command_warning=True) self.validate_identity("REMOVE @parquet_stage", check_command_warning=True) self.validate_identity("SELECT TIMESTAMP_FROM_PARTS(d, t)") @@ -84,6 +85,7 @@ WHERE self.validate_identity( "SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)" ) + self.validate_identity("x:from", "GET_PATH(x, 'from')") self.validate_identity( "value:values::string", "CAST(GET_PATH(value, 'values') AS TEXT)", @@ -371,15 +373,17 @@ WHERE write={"snowflake": "SELECT * FROM (VALUES (0)) AS foo(bar)"}, ) self.validate_all( - "OBJECT_CONSTRUCT(a, b, c, d)", + "OBJECT_CONSTRUCT('a', b, 'c', d)", read={ - "": "STRUCT(a as b, c as d)", + "": "STRUCT(b as a, d as c)", }, write={ "duckdb": "{'a': b, 'c': d}", - "snowflake": "OBJECT_CONSTRUCT(a, b, c, d)", + "snowflake": "OBJECT_CONSTRUCT('a', b, 'c', d)", }, ) + self.validate_identity("OBJECT_CONSTRUCT(a, b, c, d)") + self.validate_all( "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1", write={ @@ -1461,26 +1465,22 @@ MATCH_RECOGNIZE ( pretty=True, ) - def test_show(self): - # Parsed as Show - self.validate_identity("SHOW PRIMARY KEYS") - self.validate_identity("SHOW PRIMARY KEYS IN ACCOUNT") - self.validate_identity("SHOW PRIMARY KEYS IN DATABASE") - self.validate_identity("SHOW PRIMARY KEYS IN DATABASE foo") - self.validate_identity("SHOW PRIMARY KEYS IN TABLE") - self.validate_identity("SHOW PRIMARY KEYS IN TABLE foo") - self.validate_identity( - 'SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', - 'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"', - ) - self.validate_identity( - 'SHOW TERSE PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', - 'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."customers"', - ) + def test_show_users(self): + self.validate_identity("SHOW USERS") + self.validate_identity("SHOW TERSE USERS") + self.validate_identity("SHOW USERS LIKE '_foo%' STARTS WITH 'bar' LIMIT 5 FROM 'baz'") + + def test_show_schemas(self): self.validate_identity( "show terse schemas in database db1 starts with 'a' limit 10 from 'b'", "SHOW TERSE SCHEMAS IN DATABASE db1 STARTS WITH 'a' LIMIT 10 FROM 'b'", ) + + ast = parse_one("SHOW SCHEMAS IN DATABASE db1", read="snowflake") + self.assertEqual(ast.args.get("scope_kind"), "DATABASE") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1") + + def test_show_objects(self): self.validate_identity( "show terse objects in schema db1.schema1 starts with 'a' limit 10 from 'b'", "SHOW TERSE OBJECTS IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'", @@ -1489,6 +1489,23 @@ MATCH_RECOGNIZE ( "show terse objects in db1.schema1 starts with 'a' limit 10 from 'b'", "SHOW TERSE OBJECTS IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'", ) + + ast = parse_one("SHOW OBJECTS IN db1.schema1", read="snowflake") + self.assertEqual(ast.args.get("scope_kind"), "SCHEMA") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1.schema1") + + def test_show_columns(self): + self.validate_identity("SHOW COLUMNS") + self.validate_identity("SHOW COLUMNS IN TABLE dt_test") + self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN TABLE dt_test") + self.validate_identity("SHOW COLUMNS IN VIEW") + self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN VIEW dt_test") + + ast = parse_one("SHOW COLUMNS LIKE '_testing%' IN dt_test", read="snowflake") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "dt_test") + self.assertEqual(ast.find(exp.Literal).sql(dialect="snowflake"), "'_testing%'") + + def test_show_tables(self): self.validate_identity( "SHOW TABLES LIKE 'line%' IN tpch.public", "SHOW TABLES LIKE 'line%' IN SCHEMA tpch.public", @@ -1506,47 +1523,97 @@ MATCH_RECOGNIZE ( "SHOW TERSE TABLES IN SCHEMA db1.schema1 STARTS WITH 'a' LIMIT 10 FROM 'b'", ) - ast = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."customers"', read="snowflake") - table = ast.find(exp.Table) + ast = parse_one("SHOW TABLES IN db1.schema1", read="snowflake") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1.schema1") + + def test_show_primary_keys(self): + self.validate_identity("SHOW PRIMARY KEYS") + self.validate_identity("SHOW PRIMARY KEYS IN ACCOUNT") + self.validate_identity("SHOW PRIMARY KEYS IN DATABASE") + self.validate_identity("SHOW PRIMARY KEYS IN DATABASE foo") + self.validate_identity("SHOW PRIMARY KEYS IN TABLE") + self.validate_identity("SHOW PRIMARY KEYS IN TABLE foo") + self.validate_identity( + 'SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."foo"', + 'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."foo"', + ) + self.validate_identity( + 'SHOW TERSE PRIMARY KEYS IN "TEST"."PUBLIC"."foo"', + 'SHOW PRIMARY KEYS IN TABLE "TEST"."PUBLIC"."foo"', + ) - self.assertEqual(table.sql(dialect="snowflake"), '"TEST"."PUBLIC"."customers"') + ast = parse_one('SHOW PRIMARY KEYS IN "TEST"."PUBLIC"."foo"', read="snowflake") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), '"TEST"."PUBLIC"."foo"') - self.validate_identity("SHOW COLUMNS") - self.validate_identity("SHOW COLUMNS IN TABLE dt_test") - self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN TABLE dt_test") - self.validate_identity("SHOW COLUMNS IN VIEW") - self.validate_identity("SHOW COLUMNS LIKE '_foo%' IN VIEW dt_test") + def test_show_views(self): + self.validate_identity("SHOW TERSE VIEWS") + self.validate_identity("SHOW VIEWS") + self.validate_identity("SHOW VIEWS LIKE 'foo%'") + self.validate_identity("SHOW VIEWS IN ACCOUNT") + self.validate_identity("SHOW VIEWS IN DATABASE") + self.validate_identity("SHOW VIEWS IN DATABASE foo") + self.validate_identity("SHOW VIEWS IN SCHEMA foo") + self.validate_identity( + "SHOW VIEWS IN foo", + "SHOW VIEWS IN SCHEMA foo", + ) - self.validate_identity("SHOW USERS") - self.validate_identity("SHOW TERSE USERS") - self.validate_identity("SHOW USERS LIKE '_foo%' STARTS WITH 'bar' LIMIT 5 FROM 'baz'") + ast = parse_one("SHOW VIEWS IN db1.schema1", read="snowflake") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), "db1.schema1") - ast = parse_one("SHOW COLUMNS LIKE '_testing%' IN dt_test", read="snowflake") - table = ast.find(exp.Table) - literal = ast.find(exp.Literal) + def test_show_unique_keys(self): + self.validate_identity("SHOW UNIQUE KEYS") + self.validate_identity("SHOW UNIQUE KEYS IN ACCOUNT") + self.validate_identity("SHOW UNIQUE KEYS IN DATABASE") + self.validate_identity("SHOW UNIQUE KEYS IN DATABASE foo") + self.validate_identity("SHOW UNIQUE KEYS IN TABLE") + self.validate_identity("SHOW UNIQUE KEYS IN TABLE foo") + self.validate_identity( + 'SHOW UNIQUE KEYS IN "TEST"."PUBLIC"."foo"', + 'SHOW UNIQUE KEYS IN SCHEMA "TEST"."PUBLIC"."foo"', + ) + self.validate_identity( + 'SHOW TERSE UNIQUE KEYS IN "TEST"."PUBLIC"."foo"', + 'SHOW UNIQUE KEYS IN SCHEMA "TEST"."PUBLIC"."foo"', + ) - self.assertEqual(table.sql(dialect="snowflake"), "dt_test") + ast = parse_one('SHOW UNIQUE KEYS IN "TEST"."PUBLIC"."foo"', read="snowflake") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), '"TEST"."PUBLIC"."foo"') - self.assertEqual(literal.sql(dialect="snowflake"), "'_testing%'") + def test_show_imported_keys(self): + self.validate_identity("SHOW IMPORTED KEYS") + self.validate_identity("SHOW IMPORTED KEYS IN ACCOUNT") + self.validate_identity("SHOW IMPORTED KEYS IN DATABASE") + self.validate_identity("SHOW IMPORTED KEYS IN DATABASE foo") + self.validate_identity("SHOW IMPORTED KEYS IN TABLE") + self.validate_identity("SHOW IMPORTED KEYS IN TABLE foo") + self.validate_identity( + 'SHOW IMPORTED KEYS IN "TEST"."PUBLIC"."foo"', + 'SHOW IMPORTED KEYS IN SCHEMA "TEST"."PUBLIC"."foo"', + ) + self.validate_identity( + 'SHOW TERSE IMPORTED KEYS IN "TEST"."PUBLIC"."foo"', + 'SHOW IMPORTED KEYS IN SCHEMA "TEST"."PUBLIC"."foo"', + ) - ast = parse_one("SHOW SCHEMAS IN DATABASE db1", read="snowflake") - self.assertEqual(ast.args.get("scope_kind"), "DATABASE") - table = ast.find(exp.Table) - self.assertEqual(table.sql(dialect="snowflake"), "db1") + ast = parse_one('SHOW IMPORTED KEYS IN "TEST"."PUBLIC"."foo"', read="snowflake") + self.assertEqual(ast.find(exp.Table).sql(dialect="snowflake"), '"TEST"."PUBLIC"."foo"') - ast = parse_one("SHOW OBJECTS IN db1.schema1", read="snowflake") - self.assertEqual(ast.args.get("scope_kind"), "SCHEMA") - table = ast.find(exp.Table) - self.assertEqual(table.sql(dialect="snowflake"), "db1.schema1") + def test_show_sequences(self): + self.validate_identity("SHOW TERSE SEQUENCES") + self.validate_identity("SHOW SEQUENCES") + self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN ACCOUNT") + self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN DATABASE") + self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN DATABASE foo") + self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN SCHEMA") + self.validate_identity("SHOW SEQUENCES LIKE '_foo%' IN SCHEMA foo") + self.validate_identity( + "SHOW SEQUENCES LIKE '_foo%' IN foo", + "SHOW SEQUENCES LIKE '_foo%' IN SCHEMA foo", + ) - ast = parse_one("SHOW TABLES IN db1.schema1", read="snowflake") + ast = parse_one("SHOW SEQUENCES IN dt_test", read="snowflake") self.assertEqual(ast.args.get("scope_kind"), "SCHEMA") - table = ast.find(exp.Table) - self.assertEqual(table.sql(dialect="snowflake"), "db1.schema1") - - users_exp = self.validate_identity("SHOW USERS") - self.assertTrue(isinstance(users_exp, exp.Show)) - self.assertEqual(users_exp.this, "USERS") def test_storage_integration(self): self.validate_identity( -- cgit v1.2.3