Repository: tobymao/sqlglot

Issue:
ClickHouse WITH statement
According to ClickHouse [docs](https://clickhouse.com/docs/en/sql-reference/statements/select/with/), `WITH` statement looks like `WITH <expression> AS <identifier>`. `sqlglot` expects that identifier precedes expression. So if I'll try to parse SQL like:
```sql
WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound
```
I have exception:
```python
sqlglot.errors.ParseError: Expected CTE to have alias. Line 2, Col: 6.
```
One more problem is SQL generation. If I try to generate something like:
```sql
WITH trim('   A   ') AS val SELECT val
```
I expect to write something like:
```python
select("val").with_("val", as_="trim('   a   ')").sql(dialect=Dialects.CLICKHOUSE)
```
but receive this SQL, which is invalid in ClickHouse:
```sql
WITH val AS (TRIM('   a   ')) SELECT val
```
If I switch arguments of `with_` method, I'll almost achieve expected:
```python
select("val").with_(parse_one("trim('   a   ')"), as_="val", dialect=Dialects.CLICKHOUSE).sql(dialect=Dialects.CLICKHOUSE)
```
But resulting SQL is also invalid due to parentheses around `val`:
```sql
WITH TRIM('   a   ') AS (val) SELECT val
```
I guess ClickHouse dialect should have specified behaviour for `expressions.CTE` parsing/generation.

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 72995c61..1c173a40 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -4,6 +4,7 @@ import typing as t
 
 from sqlglot import exp, generator, parser, tokens
 from sqlglot.dialects.dialect import Dialect, inline_array_sql, var_map_sql
+from sqlglot.errors import ParseError
 from sqlglot.parser import parse_var_map
 from sqlglot.tokens import TokenType
 
@@ -80,6 +81,22 @@ class ClickHouse(Dialect):
             this.args["substr"] = substr
             return this
 
+        # https://clickhouse.com/docs/en/sql-reference/statements/select/with/
+        def _parse_cte(self) -> exp.Expression:
+            index = self._index
+            try:
+                # WITH <identifier> AS <subquery expression>
+                return super()._parse_cte()
+            except ParseError:
+                # WITH <expression> AS <identifier>
+                self._retreat(index)
+                statement = self._parse_statement()
+
+                if statement and isinstance(statement.this, exp.Alias):
+                    self.raise_error("Expected CTE to have alias")
+
+                return self.expression(exp.CTE, this=statement, alias=statement and statement.this)
+
     class Generator(generator.Generator):
         STRUCT_DELIMITER = ("(", ")")
 
@@ -118,3 +135,9 @@ class ClickHouse(Dialect):
             params = self.format_args(self.expressions(expression, params_name))
             args = self.format_args(self.expressions(expression, args_name))
             return f"({params})({args})"
+
+        def cte_sql(self, expression: exp.CTE) -> str:
+            if isinstance(expression.this, exp.Alias):
+                return self.sql(expression, "this")
+
+            return super().cte_sql(expression)


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 711e5cb2..2827dd40 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -48,3 +48,9 @@ class TestClickhouse(Validator):
                 "clickhouse": "SELECT quantileIf(0.5)(a, TRUE)",
             },
         )
+
+    def test_cte(self):
+        self.validate_identity("WITH 'x' AS foo SELECT foo")
+        self.validate_identity("WITH SUM(bytes) AS foo SELECT foo FROM system.parts")
+        self.validate_identity("WITH (SELECT foo) AS bar SELECT bar + 5")
+        self.validate_identity("WITH test1 AS (SELECT i + 1, j + 1 FROM test1) SELECT * FROM test1")