summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_redshift.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_redshift.py')
-rw-r--r--tests/dialects/test_redshift.py64
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,
generated by cgit v1.2.3 (git 2.39.1) at 2024-05-09 04:23:38 +0000