From 43a52bccf5bf17217a2734f4680c00a102f82cac Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 24 Dec 2023 08:49:59 +0100 Subject: Merging upstream version 20.4.0. Signed-off-by: Daniel Baumann --- tests/dialects/test_bigquery.py | 1 + tests/dialects/test_clickhouse.py | 1 + tests/dialects/test_dialect.py | 37 +++++++++++++++++++++++++++++++++++ tests/dialects/test_presto.py | 16 +++++++++++---- tests/fixtures/optimizer/simplify.sql | 14 ++++++++++++- 5 files changed, 64 insertions(+), 5 deletions(-) (limited to 'tests') diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py index 420803a..f263581 100644 --- a/tests/dialects/test_bigquery.py +++ b/tests/dialects/test_bigquery.py @@ -206,6 +206,7 @@ class TestBigQuery(Validator): "duckdb": "SELECT * FROM t WHERE EXISTS(SELECT * FROM UNNEST(nums) AS _t(x) WHERE x > 1)", }, ) + self.validate_identity("UPDATE x SET y = NULL") self.validate_all( "NULL", read={ diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py index 1f528b6..bb993b5 100644 --- a/tests/dialects/test_clickhouse.py +++ b/tests/dialects/test_clickhouse.py @@ -47,6 +47,7 @@ class TestClickhouse(Validator): self.validate_identity("SELECT INTERVAL t.days day") self.validate_identity("SELECT match('abc', '([a-z]+)')") self.validate_identity("dictGet(x, 'y')") + self.validate_identity("SELECT * FROM final") self.validate_identity("SELECT * FROM x FINAL") self.validate_identity("SELECT * FROM x AS y FINAL") self.validate_identity("'a' IN mapKeys(map('a', 1, 'b', 2))") diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py index 49afc62..a49d067 100644 --- a/tests/dialects/test_dialect.py +++ b/tests/dialects/test_dialect.py @@ -2056,3 +2056,40 @@ SELECT self.assertEqual(expression.sql(dialect="mysql"), expected_sql) self.assertEqual(expression.sql(dialect="tsql"), expected_sql) + + def test_random(self): + self.validate_all( + "RAND()", + write={ + "bigquery": "RAND()", + "clickhouse": "randCanonical()", + "databricks": "RAND()", + "doris": "RAND()", + "drill": "RAND()", + "duckdb": "RANDOM()", + "hive": "RAND()", + "mysql": "RAND()", + "oracle": "RAND()", + "postgres": "RANDOM()", + "presto": "RAND()", + "spark": "RAND()", + "sqlite": "RANDOM()", + "tsql": "RAND()", + }, + read={ + "bigquery": "RAND()", + "clickhouse": "randCanonical()", + "databricks": "RAND()", + "doris": "RAND()", + "drill": "RAND()", + "duckdb": "RANDOM()", + "hive": "RAND()", + "mysql": "RAND()", + "oracle": "RAND()", + "postgres": "RANDOM()", + "presto": "RAND()", + "spark": "RAND()", + "sqlite": "RANDOM()", + "tsql": "RAND()", + }, + ) diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py index 97a387c..8b5080c 100644 --- a/tests/dialects/test_presto.py +++ b/tests/dialects/test_presto.py @@ -546,13 +546,21 @@ class TestPresto(Validator): def test_unicode_string(self): for prefix in ("u&", "U&"): - self.validate_identity( + self.validate_all( f"{prefix}'Hello winter \\2603 !'", - "U&'Hello winter \\2603 !'", + write={ + "presto": "U&'Hello winter \\2603 !'", + "snowflake": "'Hello winter \\u2603 !'", + "spark": "'Hello winter \\u2603 !'", + }, ) - self.validate_identity( + self.validate_all( f"{prefix}'Hello winter #2603 !' UESCAPE '#'", - "U&'Hello winter #2603 !' UESCAPE '#'", + write={ + "presto": "U&'Hello winter #2603 !' UESCAPE '#'", + "snowflake": "'Hello winter \\u2603 !'", + "spark": "'Hello winter \\u2603 !'", + }, ) def test_presto(self): diff --git a/tests/fixtures/optimizer/simplify.sql b/tests/fixtures/optimizer/simplify.sql index fbf5d2c..d3b03fb 100644 --- a/tests/fixtures/optimizer/simplify.sql +++ b/tests/fixtures/optimizer/simplify.sql @@ -696,6 +696,18 @@ x <> 1; NOT 1 <> x; x = 1; +x > CAST('2024-01-01' AS DATE) OR x > CAST('2023-12-31' AS DATE); +x > CAST('2023-12-31' AS DATE); + +CAST(x AS DATE) > CAST('2024-01-01' AS DATE) OR CAST(x AS DATE) > CAST('2023-12-31' AS DATE); +CAST(x AS DATE) > CAST('2023-12-31' AS DATE); + +FUN() > 0 OR FUN() > 1; +FUN() > 0; + +RAND() > 0 OR RAND() > 1; +RAND() > 0 OR RAND() > 1; + -------------------------------------- -- COALESCE -------------------------------------- @@ -835,7 +847,7 @@ DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); DATE_TRUNC('quarter', x) = CAST('2021-01-02' AS DATE); DATE_TRUNC('year', x) <> CAST('2021-01-01' AS DATE); -x < CAST('2021-01-01' AS DATE) AND x >= CAST('2022-01-01' AS DATE); +FALSE; -- Always true, except for nulls DATE_TRUNC('year', x) <> CAST('2021-01-02' AS DATE); -- cgit v1.2.3