diff options
Diffstat (limited to 'tests/dialects/test_redshift.py')
| -rw-r--r-- | tests/dialects/test_redshift.py | 38 |
1 files changed, 25 insertions, 13 deletions
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py index 506f429..a91f4f9 100644 --- a/tests/dialects/test_redshift.py +++ b/tests/dialects/test_redshift.py @@ -139,6 +139,15 @@ class TestRedshift(Validator): "presto": "LENGTH(x)", }, ) + self.validate_all( + "x LIKE 'abc' || '%'", + read={ + "duckdb": "STARTS_WITH(x, 'abc')", + }, + write={ + "redshift": "x LIKE 'abc' || '%'", + }, + ) self.validate_all( "SELECT SYSDATE", @@ -204,18 +213,6 @@ class TestRedshift(Validator): }, ) self.validate_all( - "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid", - write={ - "redshift": "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid", - }, - ) - self.validate_all( - 'SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE \'%start\\_%\' LIMIT 5', - write={ - "redshift": 'SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE \'%start\\_%\' LIMIT 5' - }, - ) - self.validate_all( "SELECT DISTINCT ON (a) a, b FROM x ORDER BY c DESC", write={ "bigquery": "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", @@ -293,6 +290,7 @@ class TestRedshift(Validator): ) def test_identity(self): + 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')") self.validate_identity("SELECT * FROM #x") @@ -306,6 +304,12 @@ 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 * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid" + ) + self.validate_identity( + """SELECT tablename, "column" FROM pg_table_def WHERE "column" LIKE '%start\\\\_%' LIMIT 5""" + ) + self.validate_identity( """SELECT JSON_EXTRACT_PATH_TEXT('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}', 'f4', 'f6', TRUE)""" ) self.validate_identity( @@ -463,6 +467,10 @@ FROM ( "": "INSERT INTO t(a, b) SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS t (a, b)", }, ) + self.validate_identity("CREATE TABLE table_backup BACKUP NO AS SELECT * FROM event") + self.validate_identity("CREATE TABLE table_backup BACKUP YES AS SELECT * FROM event") + self.validate_identity("CREATE TABLE table_backup (i INTEGER, b VARCHAR) BACKUP NO") + self.validate_identity("CREATE TABLE table_backup (i INTEGER, b VARCHAR) BACKUP YES") def test_create_table_like(self): self.validate_identity( @@ -499,7 +507,11 @@ FROM ( def test_varchar_max(self): self.validate_all( - "CREATE TABLE TEST (cola VARCHAR(MAX))", + 'CREATE TABLE "TEST" ("cola" VARCHAR(MAX))', + read={ + "redshift": "CREATE TABLE TEST (cola VARCHAR(max))", + "tsql": "CREATE TABLE TEST (cola VARCHAR(max))", + }, write={ "redshift": 'CREATE TABLE "TEST" ("cola" VARCHAR(MAX))', }, |