Adding upstream version 20.9.0.upstream/20.9.0

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

1 files changed, 34 insertions, 30 deletions

diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index c6be789..88fef67 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -6,6 +6,7 @@ class TestRedshift(Validator):
     dialect = "redshift"
 
     def test_redshift(self):
+        self.validate_identity("CREATE MATERIALIZED VIEW orders AUTO REFRESH YES AS SELECT 1")
         self.validate_all(
             "LISTAGG(sellerid, ', ')",
             read={
@@ -55,7 +56,7 @@ class TestRedshift(Validator):
             },
         )
         self.validate_all(
-            "SELECT INTERVAL '5 days'",
+            "SELECT INTERVAL '5 DAYS'",
             read={
                 "": "SELECT INTERVAL '5' days",
             },
@@ -64,10 +65,10 @@ class TestRedshift(Validator):
             "SELECT ADD_MONTHS('2008-03-31', 1)",
             write={
                 "bigquery": "SELECT DATE_ADD(CAST('2008-03-31' AS DATETIME), INTERVAL 1 MONTH)",
-                "duckdb": "SELECT CAST('2008-03-31' AS TIMESTAMP) + INTERVAL 1 month",
-                "redshift": "SELECT DATEADD(month, 1, '2008-03-31')",
-                "trino": "SELECT DATE_ADD('month', 1, CAST('2008-03-31' AS TIMESTAMP))",
-                "tsql": "SELECT DATEADD(month, 1, CAST('2008-03-31' AS DATETIME2))",
+                "duckdb": "SELECT CAST('2008-03-31' AS TIMESTAMP) + INTERVAL 1 MONTH",
+                "redshift": "SELECT DATEADD(MONTH, 1, '2008-03-31')",
+                "trino": "SELECT DATE_ADD('MONTH', 1, CAST('2008-03-31' AS TIMESTAMP))",
+                "tsql": "SELECT DATEADD(MONTH, 1, CAST('2008-03-31' AS DATETIME2))",
             },
         )
         self.validate_all(
@@ -234,45 +235,45 @@ class TestRedshift(Validator):
         self.validate_all(
             "DATEDIFF('day', a, b)",
             write={
-                "bigquery": "DATE_DIFF(CAST(b AS DATETIME), CAST(a AS DATETIME), day)",
-                "duckdb": "DATE_DIFF('day', CAST(a AS TIMESTAMP), CAST(b AS TIMESTAMP))",
+                "bigquery": "DATE_DIFF(CAST(b AS DATETIME), CAST(a AS DATETIME), DAY)",
+                "duckdb": "DATE_DIFF('DAY', CAST(a AS TIMESTAMP), CAST(b AS TIMESTAMP))",
                 "hive": "DATEDIFF(b, a)",
-                "redshift": "DATEDIFF(day, a, b)",
-                "presto": "DATE_DIFF('day', CAST(a AS TIMESTAMP), CAST(b AS TIMESTAMP))",
+                "redshift": "DATEDIFF(DAY, a, b)",
+                "presto": "DATE_DIFF('DAY', CAST(a AS TIMESTAMP), CAST(b AS TIMESTAMP))",
             },
         )
         self.validate_all(
             "SELECT DATEADD(month, 18, '2008-02-28')",
             write={
                 "bigquery": "SELECT DATE_ADD(CAST('2008-02-28' AS DATETIME), INTERVAL 18 MONTH)",
-                "duckdb": "SELECT CAST('2008-02-28' AS TIMESTAMP) + INTERVAL 18 month",
+                "duckdb": "SELECT CAST('2008-02-28' AS TIMESTAMP) + INTERVAL 18 MONTH",
                 "hive": "SELECT ADD_MONTHS('2008-02-28', 18)",
                 "mysql": "SELECT DATE_ADD('2008-02-28', INTERVAL 18 MONTH)",
-                "postgres": "SELECT CAST('2008-02-28' AS TIMESTAMP) + INTERVAL '18 month'",
-                "presto": "SELECT DATE_ADD('month', 18, CAST('2008-02-28' AS TIMESTAMP))",
-                "redshift": "SELECT DATEADD(month, 18, '2008-02-28')",
-                "snowflake": "SELECT DATEADD(month, 18, CAST('2008-02-28' AS TIMESTAMPNTZ))",
-                "tsql": "SELECT DATEADD(month, 18, CAST('2008-02-28' AS DATETIME2))",
+                "postgres": "SELECT CAST('2008-02-28' AS TIMESTAMP) + INTERVAL '18 MONTH'",
+                "presto": "SELECT DATE_ADD('MONTH', 18, CAST('2008-02-28' AS TIMESTAMP))",
+                "redshift": "SELECT DATEADD(MONTH, 18, '2008-02-28')",
+                "snowflake": "SELECT DATEADD(MONTH, 18, CAST('2008-02-28' AS TIMESTAMPNTZ))",
+                "tsql": "SELECT DATEADD(MONTH, 18, CAST('2008-02-28' AS DATETIME2))",
             },
         )
         self.validate_all(
             "SELECT DATEDIFF(week, '2009-01-01', '2009-12-31')",
             write={
-                "bigquery": "SELECT DATE_DIFF(CAST('2009-12-31' AS DATETIME), CAST('2009-01-01' AS DATETIME), week)",
-                "duckdb": "SELECT DATE_DIFF('week', CAST('2009-01-01' AS TIMESTAMP), CAST('2009-12-31' AS TIMESTAMP))",
+                "bigquery": "SELECT DATE_DIFF(CAST('2009-12-31' AS DATETIME), CAST('2009-01-01' AS DATETIME), WEEK)",
+                "duckdb": "SELECT DATE_DIFF('WEEK', CAST('2009-01-01' AS TIMESTAMP), CAST('2009-12-31' AS TIMESTAMP))",
                 "hive": "SELECT CAST(DATEDIFF('2009-12-31', '2009-01-01') / 7 AS INT)",
                 "postgres": "SELECT CAST(EXTRACT(days FROM (CAST('2009-12-31' AS TIMESTAMP) - CAST('2009-01-01' AS TIMESTAMP))) / 7 AS BIGINT)",
-                "presto": "SELECT DATE_DIFF('week', CAST('2009-01-01' AS TIMESTAMP), CAST('2009-12-31' AS TIMESTAMP))",
-                "redshift": "SELECT DATEDIFF(week, '2009-01-01', '2009-12-31')",
-                "snowflake": "SELECT DATEDIFF(week, '2009-01-01', '2009-12-31')",
-                "tsql": "SELECT DATEDIFF(week, '2009-01-01', '2009-12-31')",
+                "presto": "SELECT DATE_DIFF('WEEK', CAST('2009-01-01' AS TIMESTAMP), CAST('2009-12-31' AS TIMESTAMP))",
+                "redshift": "SELECT DATEDIFF(WEEK, '2009-01-01', '2009-12-31')",
+                "snowflake": "SELECT DATEDIFF(WEEK, '2009-01-01', '2009-12-31')",
+                "tsql": "SELECT DATEDIFF(WEEK, '2009-01-01', '2009-12-31')",
             },
         )
 
     def test_identity(self):
-        self.validate_identity("SELECT DATEADD(day, 1, 'today')")
+        self.validate_identity("SELECT DATEADD(DAY, 1, 'today')")
         self.validate_identity("SELECT * FROM #x")
-        self.validate_identity("SELECT INTERVAL '5 day'")
+        self.validate_identity("SELECT INTERVAL '5 DAY'")
         self.validate_identity("foo$")
         self.validate_identity("CAST('bla' AS SUPER)")
         self.validate_identity("CREATE TABLE real1 (realcol REAL)")
@@ -295,11 +296,11 @@ class TestRedshift(Validator):
         )
         self.validate_identity(
             "SELECT DATE_DIFF('month', CAST('2020-02-29 00:00:00' AS TIMESTAMP), CAST('2020-03-02 00:00:00' AS TIMESTAMP))",
-            "SELECT DATEDIFF(month, CAST('2020-02-29 00:00:00' AS TIMESTAMP), CAST('2020-03-02 00:00:00' AS TIMESTAMP))",
+            "SELECT DATEDIFF(MONTH, CAST('2020-02-29 00:00:00' AS TIMESTAMP), CAST('2020-03-02 00:00:00' AS TIMESTAMP))",
         )
         self.validate_identity(
             "SELECT * FROM x WHERE y = DATEADD('month', -1, DATE_TRUNC('month', (SELECT y FROM #temp_table)))",
-            "SELECT * FROM x WHERE y = DATEADD(month, -1, DATE_TRUNC('month', (SELECT y FROM #temp_table)))",
+            "SELECT * FROM x WHERE y = DATEADD(MONTH, -1, DATE_TRUNC('MONTH', (SELECT y FROM #temp_table)))",
         )
         self.validate_identity(
             "SELECT 'a''b'",
@@ -310,16 +311,16 @@ class TestRedshift(Validator):
             "CREATE TABLE t (c BIGINT IDENTITY(0, 1))",
         )
         self.validate_identity(
-            "SELECT DATEADD(hour, 0, CAST('2020-02-02 01:03:05.124' AS TIMESTAMP))"
+            "SELECT DATEADD(HOUR, 0, CAST('2020-02-02 01:03:05.124' AS TIMESTAMP))"
         )
         self.validate_identity(
-            "SELECT DATEDIFF(second, '2020-02-02 00:00:00.000', '2020-02-02 01:03:05.124')"
+            "SELECT DATEDIFF(SECOND, '2020-02-02 00:00:00.000', '2020-02-02 01:03:05.124')"
         )
         self.validate_identity(
             "CREATE OR REPLACE VIEW v1 AS SELECT id, AVG(average_metric1) AS m1, AVG(average_metric2) AS m2 FROM t GROUP BY id WITH NO SCHEMA BINDING"
         )
         self.validate_identity(
-            "SELECT caldate + INTERVAL '1 second' AS dateplus FROM date WHERE caldate = '12-31-2008'"
+            "SELECT caldate + INTERVAL '1 SECOND' AS dateplus FROM date WHERE caldate = '12-31-2008'"
         )
         self.validate_identity(
             "SELECT COUNT(*) FROM event WHERE eventname LIKE '%Ring%' OR eventname LIKE '%Die%'"
@@ -341,7 +342,7 @@ class TestRedshift(Validator):
         )
         self.validate_identity(
             "SELECT DATEADD('day', ndays, caldate)",
-            "SELECT DATEADD(day, ndays, caldate)",
+            "SELECT DATEADD(DAY, ndays, caldate)",
         )
         self.validate_identity(
             "CONVERT(INT, x)",
@@ -349,8 +350,11 @@ class TestRedshift(Validator):
         )
         self.validate_identity(
             "SELECT DATE_ADD('day', 1, DATE('2023-01-01'))",
-            "SELECT DATEADD(day, 1, DATE('2023-01-01'))",
+            "SELECT DATEADD(DAY, 1, DATE('2023-01-01'))",
         )
+
+        self.validate_identity("SELECT * FROM x AS a, a.b AS c, c.d.e AS f, f.g.h.i.j.k AS l")
+
         self.validate_identity(
             """SELECT
   c_name,