summaryrefslogtreecommitdiffstats
path: root/tests
diff options
context:
space:
mode:
Diffstat (limited to 'tests')
-rw-r--r--tests/dialects/test_bigquery.py1
-rw-r--r--tests/dialects/test_clickhouse.py1
-rw-r--r--tests/dialects/test_dialect.py37
-rw-r--r--tests/dialects/test_presto.py16
-rw-r--r--tests/fixtures/optimizer/simplify.sql14
5 files changed, 64 insertions, 5 deletions
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);