From c66e4a33e1a07c439f03fe47f146a6c6482bf6df Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Thu, 8 Feb 2024 06:38:42 +0100
Subject: Merging upstream version 21.0.1.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/dialects/test_redshift.py | 44 +++++++++++++++++++++++++++++++++++++----
 1 file changed, 40 insertions(+), 4 deletions(-)

(limited to 'tests/dialects/test_redshift.py')

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'")
@@ -282,6 +305,9 @@ class TestRedshift(Validator):
         self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)")
         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)",
             },
         )
 
-- 
cgit v1.2.3