Adding upstream version 20.4.0.upstream/20.4.0

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

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);