Repository: tobymao/sqlglot

Issue:
Clickhouse dialect .sql output has incorrect syntax for Materialized Views
Using `.sql(dialect="clickhouse")` on a materialized view query produces invalid syntax. Example, this python script

```python
from sqlglot import parse_one

original_query = """
CREATE MATERIALIZED VIEW test.mv_ad_advertiser TO test.ad_advertiser
(
    `id` Int32,
    `name` String,
    `type` String,
)
AS SELECT *
FROM test.s3_ad_advertiser
"""

query_ast = parse_one(original_query, dialect="clickhouse")
output_query = query_ast.sql(dialect="clickhouse", pretty=True)
print(output_query)
```

Produces this output
```
CREATE MATERIALIZED VIEW test.mv_ad_advertiser (
  "id" Int32,
  "name" String,
  "type" String
)
TO test.ad_advertiser AS
SELECT
  *
FROM test.s3_ad_advertiser
```

Which when run in `clickhouse` produces this error

```
2b11a5cc8ee1 :) CREATE MATERIALIZED VIEW test.mv_ad_advertiser (
  "id" Int32,
  "name" String,
  "type" String
)
TO test.ad_advertiser AS
SELECT
  *
FROM test.s3_ad_advertiser;

Exception on client:
Code: 62. DB::Exception: When creating a materialized view you can't declare both 'ENGINE' and 'TO [db].[table]'. (SYNTAX_ERROR)
```

Proper syntax should be: 
```
CREATE MATERIALIZED VIEW [db.]table_name TO [db.]name] 
AS SELECT ...
```

Versions used

```
# clickhouse --version
ClickHouse local version 24.8.4.13 (official build).

# pip show sqlglot
Name: sqlglot
Version: 25.22.0
```


**Official Documentation**
 - [Materialized Views](https://clickhouse.com/docs/en/sql-reference/statements/create/view#materialized-view)

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index c8671f690f..b682fa3fb3 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -918,9 +918,10 @@ class Generator(generator.Generator):
 
         PROPERTIES_LOCATION = {
             **generator.Generator.PROPERTIES_LOCATION,
-            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
-            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
             exp.OnCluster: exp.Properties.Location.POST_NAME,
+            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
+            exp.ToTableProperty: exp.Properties.Location.POST_NAME,
+            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
         }
 
         # There's no list in docs, but it can be found in Clickhouse code
@@ -1089,7 +1090,9 @@ def createable_sql(self, expression: exp.Create, locations: t.DefaultDict) -> st
                     [self.sql(prop) for prop in locations[exp.Properties.Location.POST_NAME]]
                 )
                 this_schema = self.schema_columns_sql(expression.this)
-                return f"{this_name}{self.sep()}{this_properties}{self.sep()}{this_schema}"
+                this_schema = f"{self.sep()}{this_schema}" if this_schema else ""
+
+                return f"{this_name}{self.sep()}{this_properties}{this_schema}"
 
             return super().createable_sql(expression, locations)
 


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index aa6c4f1b65..bc5a4a4340 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -139,10 +139,10 @@ def test_clickhouse(self):
             "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 (id UInt8) ENGINE=AggregatingMergeTree() ORDER BY tuple() AS SELECT * FROM test_data"
         )
         self.validate_identity(
-            "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 (id UInt8) TO table1 AS SELECT * FROM test_data"
+            "CREATE MATERIALIZED VIEW test_view ON CLUSTER cl1 TO table1 AS SELECT * FROM test_data"
         )
         self.validate_identity(
-            "CREATE MATERIALIZED VIEW test_view (id UInt8) TO db.table1 AS SELECT * FROM test_data"
+            "CREATE MATERIALIZED VIEW test_view TO db.table1 (id UInt8) AS SELECT * FROM test_data"
         )
         self.validate_identity(
             "CREATE TABLE t (foo String CODEC(LZ4HC(9), ZSTD, DELTA), size String ALIAS formatReadableSize(size_bytes), INDEX idx1 a TYPE bloom_filter(0.001) GRANULARITY 1, INDEX idx2 a TYPE set(100) GRANULARITY 2, INDEX idx3 a TYPE minmax GRANULARITY 3)"
@@ -678,6 +678,7 @@ def test_ddl(self):
             "CREATE TABLE foo ENGINE=Memory AS (SELECT * FROM db.other_table) COMMENT 'foo'",
         )
 
+        self.validate_identity("CREATE MATERIALIZED VIEW a.b TO a.c (c Int32) AS SELECT * FROM a.d")
         self.validate_identity("""CREATE TABLE ip_data (ip4 IPv4, ip6 IPv6) ENGINE=TinyLog()""")
         self.validate_identity("""CREATE TABLE dates (dt1 Date32) ENGINE=TinyLog()""")
         self.validate_identity("CREATE TABLE named_tuples (a Tuple(select String, i Int64))")