Repository: tobymao/sqlglot

Issue:
snowflake `copy grants` fails to parse
 I have a SQL query like 
```
alter session set ABORT_DETACHED_QUERY = TRUE;
create or replace view my_view
(
    "USER_ID", 
    "GUEST_IND",
    "FIRST_ORDER_DATE_TIME_UTC"  
) 
copy grants
AS (
SELECT
    USER_ID,
    GUEST_IND,
    FIRST_ORDER_DATE_TIME_UTC
FROM
    (select * from __dbt__cte__user_base LIMIT 0)  
  );
  ```
  This fails to parse with error:
  ```
  sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 9, Col: 1.
  ate or replace view my_view
(
    "USER_ID", 
    "GUEST_IND",
    "FIRST_ORDER_DATE_TIME_UTC"  
) 
copy grants
AS (
SELECT
    USER_ID,
    GUEST_IND,
    FIRST_ORDER_DATE_TIME_UTC
FROM
    (select * fro
    ```
    
If I remove `copy grants` from the query it works. 
Since this is a valid snowflake query, it should be supported.
The problem differs from the original issue reported, I can create a new issue.

_Originally posted by @vedas2 in https://github.com/tobymao/sqlglot/discussions/1787#discussioncomment-6198754_

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index cfa9a7e5..efaf34cd 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -345,7 +345,7 @@ class ClickHouse(Dialect):
                 "CONCAT",
                 *[
                     exp.func("if", e.is_(exp.null()), e, exp.cast(e, "text"))
-                    for e in expression.expressions
+                    for e in t.cast(t.List[exp.Condition], expression.expressions)
                 ],
             )
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 7b09a370..805a09dc 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1844,6 +1844,10 @@ class CollateProperty(Property):
     arg_types = {"this": True}
 
 
+class CopyGrantsProperty(Property):
+    arg_types = {}
+
+
 class DataBlocksizeProperty(Property):
     arg_types = {
         "size": False,
@@ -3221,15 +3225,15 @@ class Star(Expression):
         return self.name
 
 
-class Parameter(Expression):
+class Parameter(Condition):
     arg_types = {"this": True, "wrapped": False}
 
 
-class SessionParameter(Expression):
+class SessionParameter(Condition):
     arg_types = {"this": True, "kind": False}
 
 
-class Placeholder(Expression):
+class Placeholder(Condition):
     arg_types = {"this": False, "kind": False}
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 7f7d5dec..e30480ab 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -56,39 +56,40 @@ class Generator:
         exp.TsOrDsAdd: lambda self, e: self.func(
             "TS_OR_DS_ADD", e.this, e.expression, exp.Literal.string(e.text("unit"))
         ),
-        exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
+        exp.CaseSpecificColumnConstraint: lambda self, e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC",
+        exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
         exp.CharacterSetProperty: lambda self, e: f"{'DEFAULT ' if e.args.get('default') else ''}CHARACTER SET={self.sql(e, 'this')}",
+        exp.CheckColumnConstraint: lambda self, e: f"CHECK ({self.sql(e, 'this')})",
+        exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
+        exp.CopyGrantsProperty: lambda self, e: "COPY GRANTS",
+        exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
+        exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
+        exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
+        exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
         exp.ExecuteAsProperty: lambda self, e: self.naked_property(e),
         exp.ExternalProperty: lambda self, e: "EXTERNAL",
+        exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
         exp.LanguageProperty: lambda self, e: self.naked_property(e),
         exp.LocationProperty: lambda self, e: self.naked_property(e),
         exp.LogProperty: lambda self, e: f"{'NO ' if e.args.get('no') else ''}LOG",
         exp.MaterializedProperty: lambda self, e: "MATERIALIZED",
         exp.NoPrimaryIndexProperty: lambda self, e: "NO PRIMARY INDEX",
         exp.OnCommitProperty: lambda self, e: f"ON COMMIT {'DELETE' if e.args.get('delete') else 'PRESERVE'} ROWS",
+        exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
+        exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
         exp.ReturnsProperty: lambda self, e: self.naked_property(e),
         exp.SetProperty: lambda self, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
         exp.SettingsProperty: lambda self, e: f"SETTINGS{self.seg('')}{(self.expressions(e))}",
         exp.SqlSecurityProperty: lambda self, e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}",
+        exp.StabilityProperty: lambda self, e: e.name,
         exp.TemporaryProperty: lambda self, e: f"TEMPORARY",
         exp.ToTableProperty: lambda self, e: f"TO {self.sql(e.this)}",
         exp.TransientProperty: lambda self, e: "TRANSIENT",
-        exp.StabilityProperty: lambda self, e: e.name,
+        exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
+        exp.UppercaseColumnConstraint: lambda self, e: f"UPPERCASE",
+        exp.VarMap: lambda self, e: self.func("MAP", e.args["keys"], e.args["values"]),
         exp.VolatileProperty: lambda self, e: "VOLATILE",
         exp.WithJournalTableProperty: lambda self, e: f"WITH JOURNAL TABLE={self.sql(e, 'this')}",
-        exp.CaseSpecificColumnConstraint: lambda self, e: f"{'NOT ' if e.args.get('not_') else ''}CASESPECIFIC",
-        exp.CharacterSetColumnConstraint: lambda self, e: f"CHARACTER SET {self.sql(e, 'this')}",
-        exp.DateFormatColumnConstraint: lambda self, e: f"FORMAT {self.sql(e, 'this')}",
-        exp.OnUpdateColumnConstraint: lambda self, e: f"ON UPDATE {self.sql(e, 'this')}",
-        exp.UppercaseColumnConstraint: lambda self, e: f"UPPERCASE",
-        exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
-        exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
-        exp.CheckColumnConstraint: lambda self, e: f"CHECK ({self.sql(e, 'this')})",
-        exp.CommentColumnConstraint: lambda self, e: f"COMMENT {self.sql(e, 'this')}",
-        exp.CollateColumnConstraint: lambda self, e: f"COLLATE {self.sql(e, 'this')}",
-        exp.EncodeColumnConstraint: lambda self, e: f"ENCODE {self.sql(e, 'this')}",
-        exp.DefaultColumnConstraint: lambda self, e: f"DEFAULT {self.sql(e, 'this')}",
-        exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
     }
 
     # Whether or not null ordering is supported in order by
@@ -185,6 +186,7 @@ class Generator:
         exp.CharacterSetProperty: exp.Properties.Location.POST_SCHEMA,
         exp.ChecksumProperty: exp.Properties.Location.POST_NAME,
         exp.CollateProperty: exp.Properties.Location.POST_SCHEMA,
+        exp.CopyGrantsProperty: exp.Properties.Location.POST_SCHEMA,
         exp.Cluster: exp.Properties.Location.POST_SCHEMA,
         exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
         exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index d6888c77..5cc2e2c1 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -587,6 +587,7 @@ class Parser(metaclass=_Parser):
         "CLUSTER": lambda self: self._parse_cluster(),
         "COLLATE": lambda self: self._parse_property_assignment(exp.CollateProperty),
         "COMMENT": lambda self: self._parse_property_assignment(exp.SchemaCommentProperty),
+        "COPY": lambda self: self._parse_copy_property(),
         "DATABLOCKSIZE": lambda self, **kwargs: self._parse_datablocksize(**kwargs),
         "DEFINER": lambda self: self._parse_definer(),
         "DETERMINISTIC": lambda self: self.expression(
@@ -1429,6 +1430,13 @@ class Parser(metaclass=_Parser):
 
         return self.expression(exp.Cluster, expressions=self._parse_csv(self._parse_ordered))
 
+    def _parse_copy_property(self) -> t.Optional[exp.CopyGrantsProperty]:
+        if not self._match_text_seq("GRANTS"):
+            self._retreat(self._index - 1)
+            return None
+
+        return self.expression(exp.CopyGrantsProperty)
+
     def _parse_freespace(self) -> exp.FreespaceProperty:
         self._match(TokenType.EQ)
         return self.expression(
@@ -3655,7 +3663,11 @@ class Parser(metaclass=_Parser):
     def _parse_concat(self) -> t.Optional[exp.Expression]:
         args = self._parse_csv(self._parse_conjunction)
         if self.CONCAT_NULL_OUTPUTS_STRING:
-            args = [exp.func("COALESCE", arg, exp.Literal.string("")) for arg in args]
+            args = [
+                exp.func("COALESCE", exp.cast(arg, "text"), exp.Literal.string(""))
+                for arg in args
+                if arg
+            ]
 
         # Some dialects (e.g. Trino) don't allow a single-argument CONCAT call, so when
         # we find such a call we replace it with its argument.


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 7584c674..b0df4df4 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -53,7 +53,7 @@ class TestClickhouse(Validator):
         )
 
         self.validate_all(
-            "CONCAT(CASE WHEN COALESCE(a, '') IS NULL THEN COALESCE(a, '') ELSE CAST(COALESCE(a, '') AS TEXT) END, CASE WHEN COALESCE(b, '') IS NULL THEN COALESCE(b, '') ELSE CAST(COALESCE(b, '') AS TEXT) END)",
+            "CONCAT(CASE WHEN COALESCE(CAST(a AS TEXT), '') IS NULL THEN COALESCE(CAST(a AS TEXT), '') ELSE CAST(COALESCE(CAST(a AS TEXT), '') AS TEXT) END, CASE WHEN COALESCE(CAST(b AS TEXT), '') IS NULL THEN COALESCE(CAST(b AS TEXT), '') ELSE CAST(COALESCE(CAST(b AS TEXT), '') AS TEXT) END)",
             read={"postgres": "CONCAT(a, b)"},
         )
         self.validate_all(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index f801dc04..07ff4e47 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1192,7 +1192,7 @@ class TestDialect(Validator):
             },
         )
         self.validate_all(
-            "COALESCE(a, '')",
+            "COALESCE(CAST(a AS TEXT), '')",
             read={
                 "drill": "CONCAT(a)",
                 "duckdb": "CONCAT(a)",
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 4e57b363..c3910522 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -577,13 +577,19 @@ class TestPostgres(Validator):
         )
 
     def test_string_concat(self):
+        self.validate_all(
+            "SELECT CONCAT('abcde', 2, NULL, 22)",
+            write={
+                "postgres": "SELECT CONCAT(COALESCE(CAST('abcde' AS TEXT), ''), COALESCE(CAST(2 AS TEXT), ''), COALESCE(CAST(NULL AS TEXT), ''), COALESCE(CAST(22 AS TEXT), ''))",
+            },
+        )
         self.validate_all(
             "CONCAT(a, b)",
             write={
-                "": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
-                "duckdb": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
-                "postgres": "CONCAT(COALESCE(a, ''), COALESCE(b, ''))",
-                "presto": "CONCAT(CAST(COALESCE(a, '') AS VARCHAR), CAST(COALESCE(b, '') AS VARCHAR))",
+                "": "CONCAT(COALESCE(CAST(a AS TEXT), ''), COALESCE(CAST(b AS TEXT), ''))",
+                "duckdb": "CONCAT(COALESCE(CAST(a AS TEXT), ''), COALESCE(CAST(b AS TEXT), ''))",
+                "postgres": "CONCAT(COALESCE(CAST(a AS TEXT), ''), COALESCE(CAST(b AS TEXT), ''))",
+                "presto": "CONCAT(CAST(COALESCE(CAST(a AS VARCHAR), '') AS VARCHAR), CAST(COALESCE(CAST(b AS VARCHAR), '') AS VARCHAR))",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 426e188c..0514149d 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -575,6 +575,7 @@ class TestSnowflake(Validator):
         )
 
     def test_ddl(self):
+        self.validate_identity("CREATE OR REPLACE VIEW foo (uid) COPY GRANTS AS (SELECT 1)")
         self.validate_identity("CREATE TABLE geospatial_table (id INT, g GEOGRAPHY)")
         self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
         self.validate_identity("CREATE DATABASE mytestdb_clone CLONE mytestdb")