Adding upstream version 17.11.0.upstream/17.11.0

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

8 files changed, 126 insertions, 43 deletions

diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 8d01ebe..b5f91cf 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -200,8 +200,15 @@ class TestBigQuery(Validator):
         )
         self.validate_all(
             "REGEXP_CONTAINS('foo', '.*')",
-            read={"bigquery": "REGEXP_CONTAINS('foo', '.*')"},
-            write={"mysql": "REGEXP_LIKE('foo', '.*')"},
+            read={
+                "bigquery": "REGEXP_CONTAINS('foo', '.*')",
+                "mysql": "REGEXP_LIKE('foo', '.*')",
+                "starrocks": "REGEXP('foo', '.*')",
+            },
+            write={
+                "mysql": "REGEXP_LIKE('foo', '.*')",
+                "starrocks": "REGEXP('foo', '.*')",
+            },
         ),
         self.validate_all(
             '"""x"""',
@@ -481,13 +488,21 @@ class TestBigQuery(Validator):
             write={
                 "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), DAY)",
                 "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+                "starrocks": "DATE_DIFF('DAY', CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+            },
+        )
+        self.validate_all(
+            "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), DAY)",
+            read={
+                "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+                "starrocks": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
             },
         )
         self.validate_all(
             "DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', MINUTE)",
             write={
                 "bigquery": "DATE_DIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE), MINUTE)",
-                "mysql": "DATEDIFF(CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
+                "starrocks": "DATE_DIFF('MINUTE', CAST('2010-07-07' AS DATE), CAST('2008-12-25' AS DATE))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 5284700..5c35d8f 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -583,6 +583,38 @@ class TestDuckDB(Validator):
             write={"duckdb": "SELECT a, BOOL_OR(b) FROM table GROUP BY a"},
         )
 
+    def test_encode_decode(self):
+        self.validate_all(
+            "ENCODE(x)",
+            read={
+                "spark": "ENCODE(x, 'utf-8')",
+                "presto": "TO_UTF8(x)",
+            },
+            write={
+                "duckdb": "ENCODE(x)",
+                "spark": "ENCODE(x, 'utf-8')",
+                "presto": "TO_UTF8(x)",
+            },
+        )
+        self.validate_all(
+            "DECODE(x)",
+            read={
+                "spark": "DECODE(x, 'utf-8')",
+                "presto": "FROM_UTF8(x)",
+            },
+            write={
+                "duckdb": "DECODE(x)",
+                "spark": "DECODE(x, 'utf-8')",
+                "presto": "FROM_UTF8(x)",
+            },
+        )
+        self.validate_all(
+            "DECODE(x)",
+            read={
+                "presto": "FROM_UTF8(x, y)",
+            },
+        )
+
     def test_rename_table(self):
         self.validate_all(
             "ALTER TABLE db.t1 RENAME TO db.t2",
diff --git a/tests/dialects/test_hive.py b/tests/dialects/test_hive.py
index 6dd484f..4c463f7 100644
--- a/tests/dialects/test_hive.py
+++ b/tests/dialects/test_hive.py
@@ -390,6 +390,7 @@ class TestHive(Validator):
         )
 
     def test_hive(self):
+        self.validate_identity("SELECT transform")
         self.validate_identity("SELECT * FROM test DISTRIBUTE BY y SORT BY x DESC ORDER BY l")
         self.validate_identity(
             "SELECT * FROM test WHERE RAND() <= 0.1 DISTRIBUTE BY RAND() SORT BY RAND()"
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index a2800bd..ec1ad30 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -466,6 +466,7 @@ class TestPresto(Validator):
             read={"spark": "STARTSWITH('abc', 'a')"},
             write={
                 "presto": "STARTS_WITH('abc', 'a')",
+                "snowflake": "STARTSWITH('abc', 'a')",
                 "spark": "STARTSWITH('abc', 'a')",
             },
         )
@@ -740,46 +741,44 @@ class TestPresto(Validator):
         )
 
     def test_encode_decode(self):
+        self.validate_identity("FROM_UTF8(x, y)")
+
         self.validate_all(
             "TO_UTF8(x)",
+            read={
+                "duckdb": "ENCODE(x)",
+                "spark": "ENCODE(x, 'utf-8')",
+            },
             write={
+                "duckdb": "ENCODE(x)",
+                "presto": "TO_UTF8(x)",
                 "spark": "ENCODE(x, 'utf-8')",
             },
         )
         self.validate_all(
             "FROM_UTF8(x)",
-            write={
+            read={
+                "duckdb": "DECODE(x)",
                 "spark": "DECODE(x, 'utf-8')",
             },
-        )
-        self.validate_all(
-            "FROM_UTF8(x, y)",
-            write={
-                "presto": "FROM_UTF8(x, y)",
-            },
-        )
-        self.validate_all(
-            "ENCODE(x, 'utf-8')",
-            write={
-                "presto": "TO_UTF8(x)",
-            },
-        )
-        self.validate_all(
-            "DECODE(x, 'utf-8')",
             write={
+                "duckdb": "DECODE(x)",
                 "presto": "FROM_UTF8(x)",
+                "spark": "DECODE(x, 'utf-8')",
             },
         )
         self.validate_all(
             "ENCODE(x, 'invalid')",
             write={
                 "presto": UnsupportedError,
+                "duckdb": UnsupportedError,
             },
         )
         self.validate_all(
             "DECODE(x, 'invalid')",
             write={
                 "presto": UnsupportedError,
+                "duckdb": UnsupportedError,
             },
         )
 
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 90c953f..96e9e20 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -5,15 +5,16 @@ class TestRedshift(Validator):
     dialect = "redshift"
 
     def test_redshift(self):
-        self.validate_identity("SELECT * FROM #x")
-        self.validate_identity("SELECT INTERVAL '5 day'")
-        self.validate_identity("foo$")
-        self.validate_identity("$foo")
-
+        self.validate_all(
+            "SELECT INTERVAL '5 days'",
+            read={
+                "": "SELECT INTERVAL '5' days",
+            },
+        )
         self.validate_all(
             "SELECT ADD_MONTHS('2008-03-31', 1)",
             write={
-                "redshift": "SELECT DATEADD(month, 1, '2008-03-31')",
+                "redshift": "SELECT DATEADD(month, 1, CAST('2008-03-31' AS DATE))",
                 "trino": "SELECT DATE_ADD('month', 1, CAST(CAST('2008-03-31' AS TIMESTAMP) AS DATE))",
             },
         )
@@ -88,11 +89,6 @@ class TestRedshift(Validator):
                 "snowflake": "SELECT DATE_PART(month, CAST('20220502' AS DATE))",
             },
         )
-        self.validate_all("SELECT INTERVAL '5 days'", read={"": "SELECT INTERVAL '5' days"})
-        self.validate_all("CONVERT(INT, x)", write={"redshift": "CAST(x AS INTEGER)"})
-        self.validate_all(
-            "DATEADD('day', ndays, caldate)", write={"redshift": "DATEADD(day, ndays, caldate)"}
-        )
         self.validate_all(
             'create table "group" ("col" char(10))',
             write={
@@ -186,7 +182,7 @@ class TestRedshift(Validator):
         self.validate_all(
             "DATEDIFF('day', a, b)",
             write={
-                "redshift": "DATEDIFF(day, a, b)",
+                "redshift": "DATEDIFF(day, CAST(a AS DATE), CAST(b AS DATE))",
                 "presto": "DATE_DIFF('day', CAST(CAST(a AS TIMESTAMP) AS DATE), CAST(CAST(b AS TIMESTAMP) AS DATE))",
             },
         )
@@ -198,15 +194,18 @@ class TestRedshift(Validator):
         )
 
     def test_identity(self):
+        self.validate_identity("SELECT * FROM #x")
+        self.validate_identity("SELECT INTERVAL '5 day'")
+        self.validate_identity("foo$")
+        self.validate_identity("$foo")
         self.validate_identity("CAST('bla' AS SUPER)")
         self.validate_identity("CREATE TABLE real1 (realcol REAL)")
         self.validate_identity("CAST('foo' AS HLLSKETCH)")
-        self.validate_identity("SELECT DATEADD(day, 1, 'today')")
         self.validate_identity("'abc' SIMILAR TO '(b|c)%'")
+        self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)")
         self.validate_identity(
             "SELECT caldate + INTERVAL '1 second' AS dateplus FROM date WHERE caldate = '12-31-2008'"
         )
-        self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)")
         self.validate_identity(
             "SELECT COUNT(*) FROM event WHERE eventname LIKE '%Ring%' OR eventname LIKE '%Die%'"
         )
@@ -225,6 +224,22 @@ class TestRedshift(Validator):
         self.validate_identity(
             "CREATE TABLE SOUP (SOUP1 VARCHAR(50) NOT NULL ENCODE ZSTD, SOUP2 VARCHAR(70) NULL ENCODE DELTA)"
         )
+        self.validate_identity(
+            "SELECT DATEADD(day, 1, 'today')",
+            "SELECT DATEADD(day, 1, CAST('today' AS DATE))",
+        )
+        self.validate_identity(
+            "SELECT DATEADD('day', ndays, caldate)",
+            "SELECT DATEADD(day, ndays, CAST(caldate AS DATE))",
+        )
+        self.validate_identity(
+            "CONVERT(INT, x)",
+            "CAST(x AS INTEGER)",
+        )
+        self.validate_identity(
+            "SELECT DATE_ADD('day', 1, DATE('2023-01-01'))",
+            "SELECT DATEADD(day, 1, CAST(DATE('2023-01-01') AS DATE))",
+        )
 
     def test_values(self):
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index a889e1d..3053d47 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -8,6 +8,7 @@ class TestSnowflake(Validator):
     dialect = "snowflake"
 
     def test_snowflake(self):
+        self.validate_identity("WEEKOFYEAR(tstamp)")
         self.validate_identity("SELECT SUM(amount) FROM mytable GROUP BY ALL")
         self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT foo FROM IDENTIFIER('x')")
         self.validate_identity("WITH x AS (SELECT 1 AS foo) SELECT IDENTIFIER('foo') FROM x")
@@ -33,12 +34,21 @@ class TestSnowflake(Validator):
         self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING")
         self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
         self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
+        self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')")
         self.validate_identity(
             'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)'
         )
         self.validate_identity(
             "SELECT state, city, SUM(retail_price * quantity) AS gross_revenue FROM sales GROUP BY ALL"
         )
+        self.validate_identity(
+            r"SELECT RLIKE(a, $$regular expression with \ characters: \d{2}-\d{3}-\d{4}$$, 'i') FROM log_source",
+            r"SELECT REGEXP_LIKE(a, 'regular expression with \\ characters: \\d{2}-\\d{3}-\\d{4}', 'i') FROM log_source",
+        )
+        self.validate_identity(
+            r"SELECT $$a ' \ \t \x21 z $ $$",
+            r"SELECT 'a \' \\ \\t \\x21 z $ '",
+        )
 
         self.validate_all("CAST(x AS BYTEINT)", write={"snowflake": "CAST(x AS INT)"})
         self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
@@ -385,13 +395,6 @@ class TestSnowflake(Validator):
             },
         )
         self.validate_all(
-            r"SELECT $$a ' \ \t \x21 z $ $$",
-            write={
-                "snowflake": r"SELECT 'a \' \ \t \x21 z $ '",
-            },
-        )
-        self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')")
-        self.validate_all(
             "SELECT RLIKE(a, b)",
             write={
                 "hive": "SELECT a RLIKE b",
diff --git a/tests/dialects/test_starrocks.py b/tests/dialects/test_starrocks.py
index 96e20da..21a89d7 100644
--- a/tests/dialects/test_starrocks.py
+++ b/tests/dialects/test_starrocks.py
@@ -1,7 +1,7 @@
 from tests.dialects.test_dialect import Validator
 
 
-class TestMySQL(Validator):
+class TestStarrocks(Validator):
     dialect = "starrocks"
 
     def test_identity(self):
@@ -10,11 +10,21 @@ class TestMySQL(Validator):
 
     def test_time(self):
         self.validate_identity("TIMESTAMP('2022-01-01')")
+        self.validate_identity(
+            "SELECT DATE_DIFF('second', '2010-11-30 23:59:59', '2010-11-30 20:58:59')"
+        )
+        self.validate_identity(
+            "SELECT DATE_DIFF('minute', '2010-11-30 23:59:59', '2010-11-30 20:58:59')"
+        )
 
     def test_regex(self):
         self.validate_all(
-            "SELECT REGEXP_LIKE(abc, '%foo%')",
-            write={
+            "SELECT REGEXP(abc, '%foo%')",
+            read={
+                "mysql": "SELECT REGEXP_LIKE(abc, '%foo%')",
                 "starrocks": "SELECT REGEXP(abc, '%foo%')",
             },
+            write={
+                "mysql": "SELECT REGEXP_LIKE(abc, '%foo%')",
+            },
         )
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 5266bd4..f43b41b 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -6,6 +6,10 @@ class TestTSQL(Validator):
     dialect = "tsql"
 
     def test_tsql(self):
+        projection = parse_one("SELECT a = 1", read="tsql").selects[0]
+        projection.assert_is(exp.Alias)
+        projection.args["alias"].assert_is(exp.Identifier)
+
         self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b INTO @y FROM y")
         self.validate_identity("UPDATE x SET y = 1 OUTPUT x.a, x.b FROM y")
         self.validate_identity("INSERT INTO x (y) OUTPUT x.a, x.b INTO l SELECT * FROM z")
@@ -25,6 +29,10 @@ class TestTSQL(Validator):
         self.validate_identity('SELECT "x"."y" FROM foo')
         self.validate_identity("SELECT * FROM #foo")
         self.validate_identity("SELECT * FROM ##foo")
+        self.validate_identity("SELECT a = 1", "SELECT 1 AS a")
+        self.validate_identity(
+            "SELECT a = 1 UNION ALL SELECT a = b", "SELECT 1 AS a UNION ALL SELECT b AS a"
+        )
         self.validate_identity(
             "SELECT x FROM @MyTableVar AS m JOIN Employee ON m.EmployeeID = Employee.EmployeeID"
         )