Repository: tobymao/sqlglot

Issue:
Using singe quote as part of JSON object key leads to invalid SQL generation
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")` - ok
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")` - ok
- Check if the issue still exists on main - checked on sqlglot-25.20.2.dev5

**Overview**

When querying inside JSON object in Snowflake and referenced object's key contains `'` character the statements seems to be parsed correctly but when generating SQL the output is not valid (at least for snowflake and postgres dialects). Replacing single `'` in key name with escaped one: `''` resolves at least this case.

Object key can be referenced using `:` notation (e.g. `json_doc:"Customer's department"`) or using `get_path` function (`GET_PATH(PARSE_JSON(doc), '"Customer''s department"')`) - the result is the same.

**Fully reproducible code snippet**

```python
import sqlglot
sql_text = """select parse_json(doc):"Customer's department"::string as "dept" from json_tbl"""
print(sqlglot.parse_one(sql_text, dialect="snowflake").sql(dialect="snowflake"))
# SELECT CAST(GET_PATH(PARSE_JSON(doc), '["Customer's department"]') AS TEXT) AS "dept" FROM json_tbl

print(sqlglot.parse_one(sql_text, dialect="snowflake").sql(dialect="postgres"))
# SELECT CAST(JSON_EXTRACT_PATH(CAST(doc AS JSON), 'Customer's department') AS TEXT) AS "dept" FROM json_tbl
```
Expecting output for Snowflake to be 
```sql
SELECT CAST(GET_PATH(PARSE_JSON(doc), '["Customer''s department"]') AS TEXT) AS "dept" FROM json_tbl;
-- or
SELECT CAST(GET_PATH(PARSE_JSON(doc), '"Customer''s department"') AS TEXT) AS "dept" FROM json_tbl;
-- or, if not using GET_PATH function the original name without escaped quote can be used
SELECT CAST(PARSE_JSON(doc):"Customer's department" AS TEXT) AS "dept" FROM json_tbl;
```
And for PostgreSQL
```sql
SELECT CAST(JSON_EXTRACT_PATH(CAST(doc AS JSON), 'Customer''s department') AS TEXT) AS "dept" FROM json_tbl;
```

To test query with postgres:
```sql
create table json_tbl(doc jsonb);
insert into json_tbl values('{"Customer''s department": "Department A"}');

-- invalid query:
SELECT CAST(JSON_EXTRACT_PATH(CAST(doc AS JSON), 'Customer's department') AS TEXT) AS "dept" FROM json_tbl;
/*
ERROR:  syntax error at or near "s"
LINE 1: ...ST(JSON_EXTRACT_PATH(CAST(doc AS JSON), 'Customer's departme...
                                                             ^ 

SQL state: 42601
Character: 60
*/
-- valid query:
SELECT CAST(JSON_EXTRACT_PATH(CAST(doc AS JSON), 'Customer''s department') AS TEXT) AS "dept" FROM json_tbl;
```


**Official Documentation**
Please include links to official SQL documentation related to your issue.
- [Snowflake: Double quoted identifiers](https://docs.snowflake.com/en/sql-reference/identifiers-syntax#double-quoted-identifiers)
- [Snowflake: Single-quoted string constants](https://docs.snowflake.com/en/sql-reference/data-types-text#label-single-quoted-string-constants)


Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 39f87547..ec1984e9 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -870,7 +870,7 @@ class ClickHouse(Dialect):
                 "position", e.this, e.args.get("substr"), e.args.get("position")
             ),
             exp.TimeToStr: lambda self, e: self.func(
-                "DATE_FORMAT", e.this, self.format_time(e), e.args.get("zone")
+                "formatDateTime", e.this, self.format_time(e), e.args.get("zone")
             ),
             exp.TimeStrToTime: _timestrtotime_sql,
             exp.TimestampAdd: _datetime_delta_sql("TIMESTAMP_ADD"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index a14c32d3..a7bc2ca4 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -6203,6 +6203,10 @@ class UnixToTimeStr(Func):
     pass
 
 
+class UnpackColumns(Func):
+    pass
+
+
 class Uuid(Func):
     _sql_names = ["UUID", "GEN_RANDOM_UUID", "GENERATE_UUID", "UUID_STRING"]
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 0e8888a6..746712b6 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -154,6 +154,7 @@ class Generator(metaclass=_Generator):
         exp.TransientProperty: lambda *_: "TRANSIENT",
         exp.Union: lambda self, e: self.set_operations(e),
         exp.UnloggedProperty: lambda *_: "UNLOGGED",
+        exp.UnpackColumns: lambda self, e: f"*{self.sql(e.this)}",
         exp.Uuid: lambda *_: "UUID()",
         exp.UppercaseColumnConstraint: lambda *_: "UPPERCASE",
         exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 6ee0cb14..9ea81837 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -432,6 +432,7 @@ class Parser(metaclass=_Parser):
     }
 
     ALTERABLES = {
+        TokenType.INDEX,
         TokenType.TABLE,
         TokenType.VIEW,
     }
@@ -840,14 +841,7 @@ class Parser(metaclass=_Parser):
         TokenType.TRUE: lambda self, _: self.expression(exp.Boolean, this=True),
         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_star_op("EXCEPT", "EXCLUDE"),
-                "replace": self._parse_star_op("REPLACE"),
-                "rename": self._parse_star_op("RENAME"),
-            },
-        ),
+        TokenType.STAR: lambda self, _: self._parse_star_ops(),
     }
 
     PLACEHOLDER_PARSERS = {
@@ -4993,7 +4987,17 @@ class Parser(metaclass=_Parser):
                 end_token = self._prev
 
             if path:
-                json_path.append(self._find_sql(self._tokens[start_index], end_token))
+                path_sql = self._find_sql(self._tokens[start_index], end_token)
+
+                # Escape single quotes from Snowflake's colon extraction (e.g. col:"a'b") as
+                # it'll roundtrip to a string literal in GET_PATH
+                if path_sql[0] == '"':
+                    quote_end = self.dialect.QUOTE_END
+                    path_sql = path_sql.replace(
+                        quote_end, self.dialect.tokenizer_class.STRING_ESCAPES[0] + quote_end
+                    )
+
+                json_path.append(path_sql)
 
         # The VARIANT extract in Snowflake/Databricks is parsed as a JSONExtract; Snowflake uses the json_path in GET_PATH() while
         # Databricks transforms it back to the colon/dot notation
@@ -7374,3 +7378,16 @@ class Parser(metaclass=_Parser):
             this=self._parse_bitwise(),
             form=self._match(TokenType.COMMA) and self._parse_var(),
         )
+
+    def _parse_star_ops(self) -> exp.Star | exp.UnpackColumns:
+        if self._match_text_seq("COLUMNS", "(", advance=False):
+            return exp.UnpackColumns(this=self._parse_function())
+
+        return self.expression(
+            exp.Star,
+            **{  # type: ignore
+                "except": self._parse_star_op("EXCEPT", "EXCLUDE"),
+                "replace": self._parse_star_op("REPLACE"),
+                "rename": self._parse_star_op("RENAME"),
+            },
+        )


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 34d2b4d8..22a71aa8 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -470,15 +470,15 @@ class TestClickhouse(Validator):
         self.validate_identity("SELECT FORMAT")
         self.validate_identity("1 AS FORMAT").assert_is(exp.Alias)
 
-        self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d', '%T')")
+        self.validate_identity("SELECT formatDateTime(NOW(), '%Y-%m-%d', '%T')")
         self.validate_all(
-            "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+            "SELECT formatDateTime(NOW(), '%Y-%m-%d')",
             read={
                 "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')",
                 "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
             },
             write={
-                "clickhouse": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
+                "clickhouse": "SELECT formatDateTime(NOW(), '%Y-%m-%d')",
                 "mysql": "SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')",
             },
         )
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 98d1604b..fbf81dd8 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -817,6 +817,15 @@ class TestDuckDB(Validator):
 
         self.validate_identity("SELECT * FROM (DESCRIBE t)")
 
+        self.validate_identity("SELECT UNNEST([*COLUMNS('alias_.*')]) AS column_name")
+        self.validate_identity(
+            "SELECT COALESCE(*COLUMNS(*)) FROM (SELECT NULL, 2, 3) AS t(a, b, c)"
+        )
+        self.validate_identity(
+            "SELECT id, STRUCT_PACK(*COLUMNS('m\d')) AS measurements FROM many_measurements",
+            """SELECT id, {'_0': *COLUMNS('m\d')} AS measurements FROM many_measurements""",
+        )
+
     def test_array_index(self):
         with self.assertLogs(helper_logger) as cm:
             self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 1b89ecd1..ab06683c 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -814,6 +814,7 @@ class TestPostgres(Validator):
         cdef.args["kind"].assert_is(exp.DataType)
         self.assertEqual(expr.sql(dialect="postgres"), "CREATE TABLE t (x INTERVAL DAY)")
 
+        self.validate_identity('ALTER INDEX "IX_Ratings_Column1" RENAME TO "IX_Ratings_Column2"')
         self.validate_identity('CREATE TABLE x (a TEXT COLLATE "de_DE")')
         self.validate_identity('CREATE TABLE x (a TEXT COLLATE pg_catalog."default")')
         self.validate_identity("CREATE TABLE t (col INT[3][5])")
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 17333cf2..b82f9928 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -2137,6 +2137,16 @@ SINGLE = TRUE""",
         self.validate_identity("SELECT t.$23:a.b", "SELECT GET_PATH(t.$23, 'a.b')")
         self.validate_identity("SELECT t.$17:a[0].b[0].c", "SELECT GET_PATH(t.$17, 'a[0].b[0].c')")
 
+        self.validate_all(
+            """
+            SELECT col:"customer's department"
+            """,
+            write={
+                "snowflake": """SELECT GET_PATH(col, '["customer\\'s department"]')""",
+                "postgres": "SELECT JSON_EXTRACT_PATH(col, 'customer\\'s department')",
+            },
+        )
+
     def test_alter_set_unset(self):
         self.validate_identity("ALTER TABLE tbl SET DATA_RETENTION_TIME_IN_DAYS=1")
         self.validate_identity("ALTER TABLE tbl SET DEFAULT_DDL_COLLATION='test'")