summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_snowflake.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-15 05:02:12 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-15 05:02:12 +0000
commit07115fb6b7dc48595d30c3d1568fbeff0388d096 (patch)
treef8017ffa5da7c1a44443bbf5fc6c3937b243ac5e /tests/dialects/test_snowflake.py
parentAdding upstream version 23.7.0. (diff)
downloadsqlglot-07115fb6b7dc48595d30c3d1568fbeff0388d096.tar.xz
sqlglot-07115fb6b7dc48595d30c3d1568fbeff0388d096.zip
Adding upstream version 23.10.0.upstream/23.10.0
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.py41
1 files changed, 23 insertions, 18 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index a41d35a..b652541 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -66,6 +66,7 @@ WHERE
self.validate_identity("SELECT DAYOFYEAR(CURRENT_TIMESTAMP())")
self.validate_identity("LISTAGG(data['some_field'], ',')")
self.validate_identity("WEEKOFYEAR(tstamp)")
+ self.validate_identity("SELECT QUARTER(CURRENT_TIMESTAMP())")
self.validate_identity("SELECT SUM(amount) FROM mytable GROUP BY ALL")
self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT foo FROM IDENTIFIER('x')")
self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT IDENTIFIER('foo') FROM x")
@@ -1575,22 +1576,26 @@ FROM persons AS p, LATERAL FLATTEN(input => p.c, path => 'contact') AS _flattene
)
def test_match_recognize(self):
- for row in (
- "ONE ROW PER MATCH",
- "ALL ROWS PER MATCH",
- "ALL ROWS PER MATCH SHOW EMPTY MATCHES",
- "ALL ROWS PER MATCH OMIT EMPTY MATCHES",
- "ALL ROWS PER MATCH WITH UNMATCHED ROWS",
- ):
- for after in (
- "AFTER MATCH SKIP",
- "AFTER MATCH SKIP PAST LAST ROW",
- "AFTER MATCH SKIP TO NEXT ROW",
- "AFTER MATCH SKIP TO FIRST x",
- "AFTER MATCH SKIP TO LAST x",
+ for window_frame in ("", "FINAL ", "RUNNING "):
+ for row in (
+ "ONE ROW PER MATCH",
+ "ALL ROWS PER MATCH",
+ "ALL ROWS PER MATCH SHOW EMPTY MATCHES",
+ "ALL ROWS PER MATCH OMIT EMPTY MATCHES",
+ "ALL ROWS PER MATCH WITH UNMATCHED ROWS",
):
- self.validate_identity(
- f"""SELECT
+ for after in (
+ "AFTER MATCH SKIP",
+ "AFTER MATCH SKIP PAST LAST ROW",
+ "AFTER MATCH SKIP TO NEXT ROW",
+ "AFTER MATCH SKIP TO FIRST x",
+ "AFTER MATCH SKIP TO LAST x",
+ ):
+ with self.subTest(
+ f"MATCH_RECOGNIZE with window frame {window_frame}, rows {row}, after {after}: "
+ ):
+ self.validate_identity(
+ f"""SELECT
*
FROM x
MATCH_RECOGNIZE (
@@ -1598,15 +1603,15 @@ MATCH_RECOGNIZE (
ORDER BY
x DESC
MEASURES
- y AS b
+ {window_frame}y AS b
{row}
{after}
PATTERN (^ S1 S2*? ( {{- S3 -}} S4 )+ | PERMUTE(S1, S2){{1,2}} $)
DEFINE
x AS y
)""",
- pretty=True,
- )
+ pretty=True,
+ )
def test_show_users(self):
self.validate_identity("SHOW USERS")