diff options
Diffstat (limited to 'tests/dialects/test_redshift.py')
| -rw-r--r-- | tests/dialects/test_redshift.py | 44 |
1 files changed, 40 insertions, 4 deletions
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index 9ccd955..b6b6ccc 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -6,7 +6,29 @@ 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( + "GETDATE()", + read={ + "duckdb": "CURRENT_TIMESTAMP", + }, + write={ + "duckdb": "CURRENT_TIMESTAMP", + "redshift": "GETDATE()", + }, + ) + self.validate_all( + """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')""", + "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')""", + "redshift": """SELECT JSON_EXTRACT_PATH_TEXT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', 'farm', 'barn', 'color')""", + "spark": """SELECT GET_JSON_OBJECT('{ "farm": {"barn": { "color": "red", "feed stocked": true }}}', '$.farm.barn.color')""", + "sqlite": """SELECT '{ "farm": {"barn": { "color": "red", "feed stocked": true }}}' ->> '$.farm.barn.color'""", + }, + ) self.validate_all( "LISTAGG(sellerid, ', ')", read={ @@ -271,6 +293,7 @@ class TestRedshift(Validator): ) def test_identity(self): + self.validate_identity("CREATE MATERIALIZED VIEW orders AUTO REFRESH YES AS SELECT 1") self.validate_identity("SELECT DATEADD(DAY, 1, 'today')") self.validate_identity("SELECT * FROM #x") self.validate_identity("SELECT INTERVAL '5 DAY'") @@ -283,6 +306,9 @@ class TestRedshift(Validator): self.validate_identity("SELECT APPROXIMATE AS y") self.validate_identity("CREATE TABLE t (c BIGINT IDENTITY(0, 1))") self.validate_identity( + """SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}', 'f4', 'f6', TRUE)""" + ) + self.validate_identity( "SELECT CONCAT('abc', 'def')", "SELECT 'abc' || 'def'", ) @@ -458,16 +484,26 @@ FROM ( ) def test_create_table_like(self): + self.validate_identity( + "CREATE TABLE SOUP (LIKE other_table) DISTKEY(soup1) SORTKEY(soup2) DISTSTYLE ALL" + ) + self.validate_all( - "CREATE TABLE t1 LIKE t2", + "CREATE TABLE t1 (LIKE t2)", write={ + "postgres": "CREATE TABLE t1 (LIKE t2)", + "presto": "CREATE TABLE t1 (LIKE t2)", "redshift": "CREATE TABLE t1 (LIKE t2)", + "trino": "CREATE TABLE t1 (LIKE t2)", }, ) self.validate_all( - "CREATE TABLE SOUP (LIKE other_table) DISTKEY(soup1) SORTKEY(soup2) DISTSTYLE ALL", + "CREATE TABLE t1 (col VARCHAR, LIKE t2)", write={ - "redshift": "CREATE TABLE SOUP (LIKE other_table) DISTKEY(soup1) SORTKEY(soup2) DISTSTYLE ALL", + "postgres": "CREATE TABLE t1 (col VARCHAR, LIKE t2)", + "presto": "CREATE TABLE t1 (col VARCHAR, LIKE t2)", + "redshift": "CREATE TABLE t1 (col VARCHAR, LIKE t2)", + "trino": "CREATE TABLE t1 (col VARCHAR, LIKE t2)", }, ) |