diff options
Diffstat (limited to '')
| -rw-r--r-- | tests/dialects/test_redshift.py | 47 |
1 files changed, 44 insertions, 3 deletions
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index 844fe46..c13b61a 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -6,7 +6,6 @@ class TestRedshift(Validator): dialect = "redshift" def test_redshift(self): - self.validate_identity("1 div", "1 AS div") self.validate_all( "SELECT SPLIT_TO_ARRAY('12,345,6789')", write={ @@ -28,7 +27,7 @@ class TestRedshift(Validator): """SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color')""", write={ "bigquery": """SELECT JSON_EXTRACT_SCALAR('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""", - "databricks": """SELECT GET_JSON_OBJECT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""", + "databricks": """SELECT '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}':farm.barn.color""", "duckdb": """SELECT '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}' ->> '$.farm.barn.color'""", "postgres": """SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color')""", "presto": """SELECT JSON_EXTRACT_SCALAR('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""", @@ -228,7 +227,7 @@ class TestRedshift(Validator): "drill": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1", "hive": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1", "mysql": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY CASE WHEN c IS NULL THEN 1 ELSE 0 END DESC, c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1", - "oracle": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) _t WHERE _row_number = 1", + "oracle": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) _t WHERE _row_number = 1", "presto": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC NULLS FIRST) AS _row_number FROM x) AS _t WHERE _row_number = 1", "redshift": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1", "snowflake": "SELECT a, b FROM (SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY c DESC) AS _row_number FROM x) AS _t WHERE _row_number = 1", @@ -259,6 +258,12 @@ class TestRedshift(Validator): "postgres": "COALESCE(a, b, c, d)", }, ) + + self.validate_identity( + "DATEDIFF(days, a, b)", + "DATEDIFF(DAY, a, b)", + ) + self.validate_all( "DATEDIFF('day', a, b)", write={ @@ -281,6 +286,9 @@ class TestRedshift(Validator): "redshift": "SELECT DATEADD(MONTH, 18, '2008-02-28')", "snowflake": "SELECT DATEADD(MONTH, 18, CAST('2008-02-28' AS TIMESTAMP))", "tsql": "SELECT DATEADD(MONTH, 18, CAST('2008-02-28' AS DATETIME2))", + "spark": "SELECT DATE_ADD(MONTH, 18, '2008-02-28')", + "spark2": "SELECT ADD_MONTHS('2008-02-28', 18)", + "databricks": "SELECT DATE_ADD(MONTH, 18, '2008-02-28')", }, ) self.validate_all( @@ -297,7 +305,16 @@ class TestRedshift(Validator): }, ) + self.validate_all( + "SELECT EXTRACT(EPOCH FROM CURRENT_DATE)", + write={ + "snowflake": "SELECT DATE_PART(EPOCH, CURRENT_DATE)", + "redshift": "SELECT EXTRACT(EPOCH FROM CURRENT_DATE)", + }, + ) + def test_identity(self): + self.validate_identity("1 div", "1 AS div") self.validate_identity("LISTAGG(DISTINCT foo, ', ')") self.validate_identity("CREATE MATERIALIZED VIEW orders AUTO REFRESH YES AS SELECT 1") self.validate_identity("SELECT DATEADD(DAY, 1, 'today')") @@ -321,6 +338,10 @@ class TestRedshift(Validator): """SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}', 'f4', 'f6', TRUE)""" ) self.validate_identity( + 'DATE_PART(year, "somecol")', + 'EXTRACT(year FROM "somecol")', + ).this.assert_is(exp.Var) + self.validate_identity( "SELECT CONCAT('abc', 'def')", "SELECT 'abc' || 'def'", ) @@ -413,6 +434,14 @@ ORDER BY ) self.validate_identity("SELECT JSON_PARSE('[]')") + self.validate_identity("SELECT ARRAY(1, 2, 3)") + self.validate_identity("SELECT ARRAY[1, 2, 3]") + + self.validate_identity( + """SELECT CONVERT_TIMEZONE('America/New_York', '2024-08-06 09:10:00.000')""", + """SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', '2024-08-06 09:10:00.000')""", + ) + def test_values(self): # Test crazy-sized VALUES clause to UNION ALL conversion to ensure we don't get RecursionError values = [str(v) for v in range(0, 10000)] @@ -585,3 +614,15 @@ FROM ( self.assertEqual( ast.sql("redshift"), "SELECT * FROM x AS a, a.b AS c, c.d.e AS f, f.g.h.i.j.k AS l" ) + + def test_join_markers(self): + self.validate_identity( + "select a.foo, b.bar, a.baz from a, b where a.baz = b.baz (+)", + "SELECT a.foo, b.bar, a.baz FROM a, b WHERE a.baz = b.baz (+)", + ) + + def test_time(self): + self.validate_all( + "TIME_TO_STR(a, '%Y-%m-%d %H:%M:%S.%f')", + write={"redshift": "TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS.US')"}, + ) |