Repository: tobymao/sqlglot

Issue:
DuckDB `SUMMARIZE` incorrectly parsed as `Alias`
```
In [1]: import sqlglot.expressions as sge, sqlglot as sg

In [2]: sg.parse_one("summarize my_table", read="duckdb")
Out[2]:
Alias(
  this=Column(
    this=Identifier(this=summarize, quoted=False)),
  alias=Identifier(this=my_table, quoted=False))

In [3]: sg.parse_one("summarize my_table", read="duckdb").sql("duckdb")
Out[3]: 'summarize AS my_table'
```

`SUMMARIZE` is a valid tabular operation in DuckDB:

```
D summarize (select x from range (5) _ (x));
┌─────────────┬─────────────┬─────────┬─────────┬───────────────┬─────────┬────────────────────┬─────────┬─────────┬─────────┬───────┬─────────────────┐
│ column_name │ column_type │   min   │   max   │ approx_unique │   avg   │        std         │   q25   │   q50   │   q75   │ count │ null_percentage │
│   varchar   │   varchar   │ varchar │ varchar │     int64     │ varchar │      varchar       │ varchar │ varchar │ varchar │ int64 │  decimal(9,2)   │
├─────────────┼─────────────┼─────────┼─────────┼───────────────┼─────────┼────────────────────┼─────────┼─────────┼─────────┼───────┼─────────────────┤
│ x           │ BIGINT      │ 0       │ 4       │             5 │ 2.0     │ 1.5811388300841898 │ 1       │ 2       │ 3       │     5 │            0.00 │
└─────────────┴─────────────┴─────────┴─────────┴───────────────┴─────────┴────────────────────┴─────────┴─────────┴─────────┴───────┴─────────────────┘
```

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 6303d739..9c3df9ae 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -81,6 +81,14 @@ def _build_count_if(args: t.List) -> exp.CountIf | exp.CombinedAggFunc:
     return exp.CombinedAggFunc(this="countIf", expressions=args, parts=("count", "If"))
 
 
+def _build_str_to_date(args: t.List) -> exp.Cast | exp.Anonymous:
+    if len(args) == 3:
+        return exp.Anonymous(this="STR_TO_DATE", expressions=args)
+
+    strtodate = exp.StrToDate.from_arg_list(args)
+    return exp.cast(strtodate, exp.DataType.build(exp.DataType.Type.DATETIME))
+
+
 def _datetime_delta_sql(name: str) -> t.Callable[[Generator, DATEΤΙΜΕ_DELTA], str]:
     def _delta_sql(self: Generator, expression: DATEΤΙΜΕ_DELTA) -> str:
         if not expression.unit:
@@ -181,6 +189,7 @@ class ClickHouse(Dialect):
             "MAP": parser.build_var_map,
             "MATCH": exp.RegexpLike.from_arg_list,
             "RANDCANONICAL": exp.Rand.from_arg_list,
+            "STR_TO_DATE": _build_str_to_date,
             "TUPLE": exp.Struct.from_arg_list,
             "TIMESTAMP_SUB": build_date_delta(exp.TimestampSub, default_unit=None),
             "TIMESTAMPSUB": build_date_delta(exp.TimestampSub, default_unit=None),
@@ -836,6 +845,24 @@ class ClickHouse(Dialect):
             "NAMED COLLECTION",
         }
 
+        def strtodate_sql(self, expression: exp.StrToDate) -> str:
+            strtodate_sql = super().strtodate_sql(expression)
+
+            if not isinstance(expression.parent, exp.Cast):
+                # StrToDate returns DATEs in other dialects (eg. postgres), so
+                # this branch aims to improve the transpilation to clickhouse
+                return f"CAST({strtodate_sql} AS DATE)"
+
+            return strtodate_sql
+
+        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
+            this = expression.this
+
+            if isinstance(this, exp.StrToDate) and expression.to == exp.DataType.build("datetime"):
+                return self.sql(this)
+
+            return super().cast_sql(expression, safe_prefix=safe_prefix)
+
         def _jsonpathsubscript_sql(self, expression: exp.JSONPathSubscript) -> str:
             this = self.json_path_part(expression.this)
             return str(int(this) + 1) if is_int(this) else this
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 076b8958..a7df72f6 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -238,14 +238,15 @@ class DuckDB(Dialect):
             "POSITIONAL": TokenType.POSITIONAL,
             "SIGNED": TokenType.INT,
             "STRING": TokenType.TEXT,
-            "UBIGINT": TokenType.UBIGINT,
-            "UINTEGER": TokenType.UINT,
-            "USMALLINT": TokenType.USMALLINT,
-            "UTINYINT": TokenType.UTINYINT,
+            "SUMMARIZE": TokenType.SUMMARIZE,
             "TIMESTAMP_S": TokenType.TIMESTAMP_S,
             "TIMESTAMP_MS": TokenType.TIMESTAMP_MS,
             "TIMESTAMP_NS": TokenType.TIMESTAMP_NS,
             "TIMESTAMP_US": TokenType.TIMESTAMP,
+            "UBIGINT": TokenType.UBIGINT,
+            "UINTEGER": TokenType.UINT,
+            "USMALLINT": TokenType.USMALLINT,
+            "UTINYINT": TokenType.UTINYINT,
             "VARCHAR": TokenType.TEXT,
         }
         KEYWORDS.pop("/*+")
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index d661a64a..f9cac6b5 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1439,6 +1439,11 @@ class Describe(Expression):
     arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
 
 
+# https://duckdb.org/docs/guides/meta/summarize.html
+class Summarize(Expression):
+    arg_types = {"this": True, "table": False}
+
+
 class Kill(Expression):
     arg_types = {"this": True, "kind": False}
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 9849dbe7..df46f8e9 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -4067,3 +4067,7 @@ class Generator(metaclass=_Generator):
             fill_pattern = "' '"
 
         return self.func(f"{prefix}PAD", expression.this, expression.expression, fill_pattern)
+
+    def summarize_sql(self, expression: exp.Summarize) -> str:
+        table = " TABLE" if expression.args.get("table") else ""
+        return f"SUMMARIZE{table} {self.sql(expression.this)}"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a429f101..30e1a9b3 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2825,12 +2825,14 @@ class Parser(metaclass=_Parser):
             this = self._parse_derived_table_values()
         elif from_:
             this = exp.select("*").from_(from_.this, copy=False)
+        elif self._match(TokenType.SUMMARIZE):
+            table = self._match(TokenType.TABLE)
+            this = self._parse_select() or self._parse_string() or self._parse_table()
+            return self.expression(exp.Summarize, this=this, table=table)
         else:
             this = None
 
-        if parse_set_operation:
-            return self._parse_set_operations(this)
-        return this
+        return self._parse_set_operations(this) if parse_set_operation else this
 
     def _parse_with(self, skip_with_token: bool = False) -> t.Optional[exp.With]:
         if not skip_with_token and not self._match(TokenType.WITH):
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index e3843946..cf3f1443 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -364,6 +364,7 @@ class TokenType(AutoName):
     STORAGE_INTEGRATION = auto()
     STRAIGHT_JOIN = auto()
     STRUCT = auto()
+    SUMMARIZE = auto()
     TABLE_SAMPLE = auto()
     TAG = auto()
     TEMPORARY = auto()


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 92fbd4bf..2356ad00 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -28,6 +28,8 @@ class TestClickhouse(Validator):
         self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
         self.assertIsNone(expr._meta)
 
+        self.validate_identity("SELECT STR_TO_DATE(str, fmt, tz)")
+        self.validate_identity("SELECT STR_TO_DATE('05 12 2000', '%d %m %Y')")
         self.validate_identity("SELECT EXTRACT(YEAR FROM toDateTime('2023-02-01'))")
         self.validate_identity("extract(haystack, pattern)")
         self.validate_identity("SELECT * FROM x LIMIT 1 UNION ALL SELECT * FROM y")
@@ -153,6 +155,17 @@ class TestClickhouse(Validator):
             "SELECT SUM(1) AS impressions FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities) WHERE arrayJoin(cities) IN ('Istanbul', 'Berlin')",
         )
 
+        self.validate_all(
+            "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+            read={
+                "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+                "postgres": "SELECT TO_DATE('05 12 2000', 'DD MM YYYY')",
+            },
+            write={
+                "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+                "postgres": "SELECT CAST(CAST(TO_DATE('05 12 2000', 'DD MM YYYY') AS TIMESTAMP) AS DATE)",
+            },
+        )
         self.validate_all(
             "SELECT * FROM x PREWHERE y = 1 WHERE z = 2",
             write={
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index d092d3bb..88aef7b0 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -293,6 +293,12 @@ class TestDuckDB(Validator):
         self.validate_identity("x -> '$.family'")
         self.validate_identity("CREATE TABLE color (name ENUM('RED', 'GREEN', 'BLUE'))")
         self.validate_identity("SELECT * FROM foo WHERE bar > $baz AND bla = $bob")
+        self.validate_identity("SUMMARIZE tbl").assert_is(exp.Summarize)
+        self.validate_identity("SUMMARIZE SELECT * FROM tbl").assert_is(exp.Summarize)
+        self.validate_identity("CREATE TABLE tbl_summary AS SELECT * FROM (SUMMARIZE tbl)")
+        self.validate_identity(
+            "SUMMARIZE TABLE 'https://blobs.duckdb.org/data/Star_Trek-Season_1.csv'"
+        ).assert_is(exp.Summarize)
         self.validate_identity(
             "SELECT * FROM x LEFT JOIN UNNEST(y)", "SELECT * FROM x LEFT JOIN UNNEST(y) ON TRUE"
         )