Repository: tobymao/sqlglot

Issue:
tablesample with alias is incorrect when going from duckdb to pyspark
```
In [17]: import sqlglot.expressions as sge, sqlglot as sg

In [18]: sql = "select t1.* from (select * from t) t1 tablesample (30 rows)"

In [19]: sg.parse_one(sql, read="duckdb").sql("spark")
Out[19]: 'SELECT t1.* FROM (SELECT * FROM t) AS t1 TABLESAMPLE (30 ROWS)'
```

The `t1` alias must be placed after the `TABLESAMPLE` or else PySpark throws a parse error. This appears to be a PySpark-only thing.

Discussion:

Patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 22752810..a0a70126 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -892,6 +892,7 @@ class ClickHouse(Dialect):
         # There's no list in docs, but it can be found in Clickhouse code
         # see `ClickHouse/src/Parsers/ParserCreate*.cpp`
         ON_CLUSTER_TARGETS = {
+            "SCHEMA",  # Transpiled CREATE SCHEMA may have OnCluster property set
             "DATABASE",
             "TABLE",
             "VIEW",
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 04818f52..e5dd4b8c 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2447,6 +2447,13 @@ class Generator(metaclass=_Generator):
     def subquery_sql(self, expression: exp.Subquery, sep: str = " AS ") -> str:
         alias = self.sql(expression, "alias")
         alias = f"{sep}{alias}" if alias else ""
+        sample = self.sql(expression, "sample")
+        if self.dialect.ALIAS_POST_TABLESAMPLE and sample:
+            alias = f"{sample}{alias}"
+
+            # Set to None so it's not generated again by self.query_modifiers()
+            expression.set("sample", None)
+
         pivots = self.expressions(expression, key="pivots", sep="", flat=True)
         sql = self.query_modifiers(expression, self.wrap(expression), alias, pivots)
         return self.prepend_ctes(expression, sql)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f50e5ce2..3584ab94 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2986,6 +2986,7 @@ class Parser(metaclass=_Parser):
             this=this,
             pivots=self._parse_pivots(),
             alias=self._parse_table_alias() if parse_alias else None,
+            sample=self._parse_table_sample(),
         )
 
     def _implicit_unnests_to_explicit(self, this: E) -> E:


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 51d840f7..b4ba09e0 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -622,6 +622,14 @@ class TestClickhouse(Validator):
         )
         self.assertEqual(create_with_cluster.sql("clickhouse"), "CREATE DATABASE foo ON CLUSTER c")
 
+        # Transpiled CREATE SCHEMA may have OnCluster property set
+        create_with_cluster = exp.Create(
+            this=db_table_expr,
+            kind="SCHEMA",
+            properties=exp.Properties(expressions=[exp.OnCluster(this=exp.to_identifier("c"))]),
+        )
+        self.assertEqual(create_with_cluster.sql("clickhouse"), "CREATE DATABASE foo ON CLUSTER c")
+
         ctas_with_comment = exp.Create(
             this=exp.table_("foo"),
             kind="TABLE",
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 85f50ae8..5d2d044f 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -1008,6 +1008,13 @@ class TestDuckDB(Validator):
                 "duckdb": "SELECT * FROM example TABLESAMPLE RESERVOIR (3 ROWS) REPEATABLE (82)",
             },
         )
+        self.validate_all(
+            "SELECT * FROM (SELECT * FROM t) AS t1 TABLESAMPLE (1 ROWS), (SELECT * FROM t) AS t2 TABLESAMPLE (2 ROWS)",
+            write={
+                "duckdb": "SELECT * FROM (SELECT * FROM t) AS t1 TABLESAMPLE RESERVOIR (1 ROWS), (SELECT * FROM t) AS t2 TABLESAMPLE RESERVOIR (2 ROWS)",
+                "spark": "SELECT * FROM (SELECT * FROM t) TABLESAMPLE (1 ROWS) AS t1, (SELECT * FROM t) TABLESAMPLE (2 ROWS) AS t2",
+            },
+        )
 
     def test_array(self):
         self.validate_identity("ARRAY(SELECT id FROM t)")