Repository: tobymao/sqlglot

Issue:
Incorrect conversion of SELECT with REPLACE to RENAME for Snowflake
**Problem**
sqlglot changes `SELECT * REPLACE` to `SELECT * RENAME` for Snowflake. They have very different meanings: `rename` renames columns for output, while `replace` replaces an existing column with an expression.  Also, the query fails on Snowflake, because `rename` expects only `name as name` while `replace` is given in form `expr as name`.

**Fully reproducible code snippet**
```
>>> import sqlglot
>>> sqlglot.parse_one("SELECT * REPLACE (CAST(col AS TEXT) AS scol) FROM (SELECT 1 AS col, '2' as scol)", read="snowflake").sql("snowflake")
"SELECT * RENAME (CAST(col AS TEXT) AS scol) FROM (SELECT 1 AS col, '2' AS scol)"
```
This query fails on Snowflake with errors `Syntax error: unexpected '('` and `syntax error line 1 at position 35 unexpected 'AS'.`

**Official Documentation**
https://docs.snowflake.com/en/sql-reference/sql/select


Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 5837d2ff..f7559854 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -5,6 +5,7 @@ import typing as t
 from sqlglot import exp, generator, parser, tokens, transforms
 from sqlglot.dialects.dialect import (
     Dialect,
+    JSON_EXTRACT_TYPE,
     NormalizationStrategy,
     approx_count_distinct_sql,
     arg_max_or_min_no_count,
@@ -156,6 +157,13 @@ def _unix_to_time_sql(self: DuckDB.Generator, expression: exp.UnixToTime) -> str
     return self.func("TO_TIMESTAMP", exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)))
 
 
+def _arrow_json_extract_sql(self: DuckDB.Generator, expression: JSON_EXTRACT_TYPE) -> str:
+    arrow_sql = arrow_json_extract_sql(self, expression)
+    if not expression.same_parent and isinstance(expression.parent, exp.Binary):
+        arrow_sql = self.wrap(arrow_sql)
+    return arrow_sql
+
+
 class DuckDB(Dialect):
     NULL_ORDERING = "nulls_are_last"
     SUPPORTS_USER_DEFINED_TYPES = False
@@ -367,6 +375,7 @@ class DuckDB(Dialect):
         CAN_IMPLEMENT_ARRAY_ANY = True
         SUPPORTS_TO_NUMBER = False
         COPY_HAS_INTO_KEYWORD = False
+        STAR_EXCEPT = "EXCLUDE"
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -405,8 +414,8 @@ class DuckDB(Dialect):
             exp.IntDiv: lambda self, e: self.binary(e, "//"),
             exp.IsInf: rename_func("ISINF"),
             exp.IsNan: rename_func("ISNAN"),
-            exp.JSONExtract: arrow_json_extract_sql,
-            exp.JSONExtractScalar: arrow_json_extract_sql,
+            exp.JSONExtract: _arrow_json_extract_sql,
+            exp.JSONExtractScalar: _arrow_json_extract_sql,
             exp.JSONFormat: _json_format_sql,
             exp.LogicalOr: rename_func("BOOL_OR"),
             exp.LogicalAnd: rename_func("BOOL_AND"),
@@ -498,8 +507,6 @@ class DuckDB(Dialect):
             exp.DataType.Type.TIMESTAMP_NS: "TIMESTAMP_NS",
         }
 
-        STAR_MAPPING = {**generator.Generator.STAR_MAPPING, "except": "EXCLUDE"}
-
         UNWRAPPED_INTERVAL_VALUES = (exp.Literal, exp.Paren)
 
         # DuckDB doesn't generally support CREATE TABLE .. properties
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 84bf8a4c..9625faad 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -737,7 +737,6 @@ class Snowflake(Dialect):
             "NCHAR VARYING": TokenType.VARCHAR,
             "PUT": TokenType.COMMAND,
             "REMOVE": TokenType.COMMAND,
-            "RENAME": TokenType.REPLACE,
             "RM": TokenType.COMMAND,
             "SAMPLE": TokenType.TABLE_SAMPLE,
             "SQL_DOUBLE": TokenType.DOUBLE,
@@ -773,6 +772,7 @@ class Snowflake(Dialect):
         STRUCT_DELIMITER = ("(", ")")
         COPY_PARAMS_ARE_WRAPPED = False
         COPY_PARAMS_EQ_REQUIRED = True
+        STAR_EXCEPT = "EXCLUDE"
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -875,11 +875,6 @@ class Snowflake(Dialect):
             exp.DataType.Type.STRUCT: "OBJECT",
         }
 
-        STAR_MAPPING = {
-            "except": "EXCLUDE",
-            "replace": "RENAME",
-        }
-
         PROPERTIES_LOCATION = {
             **generator.Generator.PROPERTIES_LOCATION,
             exp.SetProperty: exp.Properties.Location.UNSUPPORTED,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index b3cd4ceb..cf4be6e1 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3820,7 +3820,7 @@ class Where(Expression):
 
 
 class Star(Expression):
-    arg_types = {"except": False, "replace": False}
+    arg_types = {"except": False, "replace": False, "rename": False}
 
     @property
     def name(self) -> str:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 66eb23a0..418f093b 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -353,6 +353,9 @@ class Generator(metaclass=_Generator):
     # Whether the conditional TRY(expression) function is supported
     TRY_SUPPORTED = True
 
+    # The keyword to use when generating a star projection with excluded columns
+    STAR_EXCEPT = "EXCEPT"
+
     TYPE_MAPPING = {
         exp.DataType.Type.NCHAR: "CHAR",
         exp.DataType.Type.NVARCHAR: "VARCHAR",
@@ -366,11 +369,6 @@ class Generator(metaclass=_Generator):
         exp.DataType.Type.ROWVERSION: "VARBINARY",
     }
 
-    STAR_MAPPING = {
-        "except": "EXCEPT",
-        "replace": "REPLACE",
-    }
-
     TIME_PART_SINGULARS = {
         "MICROSECONDS": "MICROSECOND",
         "SECONDS": "SECOND",
@@ -2308,10 +2306,12 @@ class Generator(metaclass=_Generator):
 
     def star_sql(self, expression: exp.Star) -> str:
         except_ = self.expressions(expression, key="except", flat=True)
-        except_ = f"{self.seg(self.STAR_MAPPING['except'])} ({except_})" if except_ else ""
+        except_ = f"{self.seg(self.STAR_EXCEPT)} ({except_})" if except_ else ""
         replace = self.expressions(expression, key="replace", flat=True)
-        replace = f"{self.seg(self.STAR_MAPPING['replace'])} ({replace})" if replace else ""
-        return f"*{except_}{replace}"
+        replace = f"{self.seg('REPLACE')} ({replace})" if replace else ""
+        rename = self.expressions(expression, key="rename", flat=True)
+        rename = f"{self.seg('RENAME')} ({rename})" if rename else ""
+        return f"*{except_}{replace}{rename}"
 
     def parameter_sql(self, expression: exp.Parameter) -> str:
         this = self.sql(expression, "this")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index e243fc93..8e227d07 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -713,7 +713,12 @@ class Parser(metaclass=_Parser):
         TokenType.FALSE: lambda self, _: self.expression(exp.Boolean, this=False),
         TokenType.SESSION_PARAMETER: lambda self, _: self._parse_session_parameter(),
         TokenType.STAR: lambda self, _: self.expression(
-            exp.Star, **{"except": self._parse_except(), "replace": self._parse_replace()}
+            exp.Star,
+            **{
+                "except": self._parse_star_op("EXCEPT", "EXCLUDE"),
+                "replace": self._parse_star_op("REPLACE"),
+                "rename": self._parse_star_op("RENAME"),
+            },
         ),
     }
 
@@ -5677,23 +5682,14 @@ class Parser(metaclass=_Parser):
             self._advance(-1)
         return None
 
-    def _parse_except(self) -> t.Optional[t.List[exp.Expression]]:
-        if not self._match(TokenType.EXCEPT):
-            return None
-        if self._match(TokenType.L_PAREN, advance=False):
-            return self._parse_wrapped_csv(self._parse_column)
-
-        except_column = self._parse_column()
-        return [except_column] if except_column else None
-
-    def _parse_replace(self) -> t.Optional[t.List[exp.Expression]]:
-        if not self._match(TokenType.REPLACE):
+    def _parse_star_op(self, *keywords: str) -> t.Optional[t.List[exp.Expression]]:
+        if not self._match_texts(keywords):
             return None
         if self._match(TokenType.L_PAREN, advance=False):
             return self._parse_wrapped_csv(self._parse_expression)
 
-        replace_expression = self._parse_expression()
-        return [replace_expression] if replace_expression else None
+        expression = self._parse_expression()
+        return [expression] if expression else None
 
     def _parse_csv(
         self, parse_method: t.Callable, sep: TokenType = TokenType.COMMA


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index c4417783..6eb23939 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -271,6 +271,14 @@ class TestDuckDB(Validator):
         self.validate_identity(
             "SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE"
         )
+        self.validate_identity(
+            "SELECT JSON_EXTRACT_STRING(c, '$.k1') = 'v1'",
+            "SELECT (c ->> '$.k1') = 'v1'",
+        )
+        self.validate_identity(
+            "SELECT JSON_EXTRACT(c, '$.k1') = 'v1'",
+            "SELECT (c -> '$.k1') = 'v1'",
+        )
         self.validate_identity(
             """SELECT '{"foo": [1, 2, 3]}' -> 'foo' -> 0""",
             """SELECT '{"foo": [1, 2, 3]}' -> '$.foo' -> '$[0]'""",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 11d26fd9..2d1a1302 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -90,6 +90,7 @@ WHERE
         self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
         self.validate_identity("ALTER TABLE a SWAP WITH b")
         self.validate_identity("SELECT MATCH_CONDITION")
+        self.validate_identity("SELECT * REPLACE (CAST(col AS TEXT) AS scol) FROM t")
         self.validate_identity(
             "MERGE INTO my_db AS ids USING (SELECT new_id FROM my_model WHERE NOT col IS NULL) AS new_ids ON ids.type = new_ids.type AND ids.source = new_ids.source WHEN NOT MATCHED THEN INSERT VALUES (new_ids.new_id)"
         )
@@ -230,6 +231,38 @@ WHERE
             "CAST(x AS NCHAR VARYING)",
             "CAST(x AS VARCHAR)",
         )
+        self.validate_identity(
+            "CREATE OR REPLACE TEMPORARY TABLE x (y NUMBER IDENTITY(0, 1))",
+            "CREATE OR REPLACE TEMPORARY TABLE x (y DECIMAL(38, 0) AUTOINCREMENT START 0 INCREMENT 1)",
+        )
+        self.validate_identity(
+            "CREATE TEMPORARY TABLE x (y NUMBER AUTOINCREMENT(0, 1))",
+            "CREATE TEMPORARY TABLE x (y DECIMAL(38, 0) AUTOINCREMENT START 0 INCREMENT 1)",
+        )
+        self.validate_identity(
+            "CREATE TABLE x (y NUMBER IDENTITY START 0 INCREMENT 1)",
+            "CREATE TABLE x (y DECIMAL(38, 0) AUTOINCREMENT START 0 INCREMENT 1)",
+        )
+        self.validate_identity(
+            "ALTER TABLE foo ADD COLUMN id INT identity(1, 1)",
+            "ALTER TABLE foo ADD COLUMN id INT AUTOINCREMENT START 1 INCREMENT 1",
+        )
+        self.validate_identity(
+            "SELECT DAYOFWEEK('2016-01-02T23:39:20.123-07:00'::TIMESTAMP)",
+            "SELECT DAYOFWEEK(CAST('2016-01-02T23:39:20.123-07:00' AS TIMESTAMP))",
+        )
+        self.validate_identity(
+            "SELECT * FROM xxx WHERE col ilike '%Don''t%'",
+            "SELECT * FROM xxx WHERE col ILIKE '%Don\\'t%'",
+        )
+        self.validate_identity(
+            "SELECT * EXCLUDE a, b FROM xxx",
+            "SELECT * EXCLUDE (a), b FROM xxx",
+        )
+        self.validate_identity(
+            "SELECT * RENAME a AS b, c AS d FROM xxx",
+            "SELECT * RENAME (a AS b), c AS d FROM xxx",
+        )
 
         self.validate_all(
             "OBJECT_CONSTRUCT_KEEP_NULL('key_1', 'one', 'key_2', NULL)",
@@ -550,60 +583,12 @@ WHERE
             },
         )
         self.validate_all(
-            "CREATE OR REPLACE TEMPORARY TABLE x (y NUMBER IDENTITY(0, 1))",
-            write={
-                "snowflake": "CREATE OR REPLACE TEMPORARY TABLE x (y DECIMAL(38, 0) AUTOINCREMENT START 0 INCREMENT 1)",
-            },
-        )
-        self.validate_all(
-            "CREATE TEMPORARY TABLE x (y NUMBER AUTOINCREMENT(0, 1))",
-            write={
-                "snowflake": "CREATE TEMPORARY TABLE x (y DECIMAL(38, 0) AUTOINCREMENT START 0 INCREMENT 1)",
-            },
-        )
-        self.validate_all(
-            "CREATE TABLE x (y NUMBER IDENTITY START 0 INCREMENT 1)",
-            write={
-                "snowflake": "CREATE TABLE x (y DECIMAL(38, 0) AUTOINCREMENT START 0 INCREMENT 1)",
-            },
-        )
-        self.validate_all(
-            "ALTER TABLE foo ADD COLUMN id INT identity(1, 1)",
-            write={
-                "snowflake": "ALTER TABLE foo ADD COLUMN id INT AUTOINCREMENT START 1 INCREMENT 1",
-            },
-        )
-        self.validate_all(
-            "SELECT DAYOFWEEK('2016-01-02T23:39:20.123-07:00'::TIMESTAMP)",
-            write={
-                "snowflake": "SELECT DAYOFWEEK(CAST('2016-01-02T23:39:20.123-07:00' AS TIMESTAMP))",
-            },
-        )
-        self.validate_all(
-            "SELECT * FROM xxx WHERE col ilike '%Don''t%'",
-            write={
-                "snowflake": "SELECT * FROM xxx WHERE col ILIKE '%Don\\'t%'",
-            },
-        )
-        self.validate_all(
-            "SELECT * EXCLUDE a, b FROM xxx",
-            write={
-                "snowflake": "SELECT * EXCLUDE (a), b FROM xxx",
-            },
-        )
-        self.validate_all(
-            "SELECT * RENAME a AS b, c AS d FROM xxx",
-            write={
-                "snowflake": "SELECT * RENAME (a AS b), c AS d FROM xxx",
-            },
-        )
-        self.validate_all(
-            "SELECT * EXCLUDE (a, b) RENAME (c AS d, E AS F) FROM xxx",
+            "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx",
             read={
                 "duckdb": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx",
             },
             write={
-                "snowflake": "SELECT * EXCLUDE (a, b) RENAME (c AS d, E AS F) FROM xxx",
+                "snowflake": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx",
                 "duckdb": "SELECT * EXCLUDE (a, b) REPLACE (c AS d, E AS F) FROM xxx",
             },
         )