From 57c3067868d0a1da90ec0f2201dd91f031241274 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 19 Mar 2023 11:22:04 +0100 Subject: Adding upstream version 11.4.1. Signed-off-by: Daniel Baumann --- tests/dialects/test_snowflake.py | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+) (limited to 'tests/dialects/test_snowflake.py') diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py index 1ac910c..5f6efce 100644 --- a/tests/dialects/test_snowflake.py +++ b/tests/dialects/test_snowflake.py @@ -17,6 +17,41 @@ class TestSnowflake(Validator): ) self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'") + self.validate_all( + "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1", + write={ + "": "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) = 1", + "databricks": "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) = 1", + "hive": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) AS _w FROM qt) AS _t WHERE _w = 1", + "presto": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS _w FROM qt) AS _t WHERE _w = 1", + "snowflake": "SELECT i, p, o FROM qt QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1", + "spark": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) AS _w FROM qt) AS _t WHERE _w = 1", + "sqlite": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o NULLS LAST) AS _w FROM qt) AS _t WHERE _w = 1", + "trino": "SELECT i, p, o FROM (SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS _w FROM qt) AS _t WHERE _w = 1", + }, + ) + self.validate_all( + "SELECT BOOLOR_AGG(c1), BOOLOR_AGG(c2) FROM test", + write={ + "": "SELECT LOGICAL_OR(c1), LOGICAL_OR(c2) FROM test", + "duckdb": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test", + "postgres": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test", + "snowflake": "SELECT BOOLOR_AGG(c1), BOOLOR_AGG(c2) FROM test", + "spark": "SELECT BOOL_OR(c1), BOOL_OR(c2) FROM test", + "sqlite": "SELECT MAX(c1), MAX(c2) FROM test", + }, + ) + self.validate_all( + "SELECT BOOLAND_AGG(c1), BOOLAND_AGG(c2) FROM test", + write={ + "": "SELECT LOGICAL_AND(c1), LOGICAL_AND(c2) FROM test", + "duckdb": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test", + "postgres": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test", + "snowflake": "SELECT BOOLAND_AGG(c1), BOOLAND_AGG(c2) FROM test", + "spark": "SELECT BOOL_AND(c1), BOOL_AND(c2) FROM test", + "sqlite": "SELECT MIN(c1), MIN(c2) FROM test", + }, + ) self.validate_all( "TO_CHAR(x, y)", read={ -- cgit v1.2.3