diff options
Diffstat (limited to 'tests/dialects/test_snowflake.py')
| -rw-r--r-- | tests/dialects/test_snowflake.py | 35 |
1 files changed, 35 insertions, 0 deletions
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 @@ -18,6 +18,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={ "": "TO_CHAR(x, y)", |