Repository: tobymao/sqlglot

Issue:
Clickhouse ARRAY JOIN parsing
Hi,

I would like to request parsing for Clickhouse **ARRAY JOIN** expressions.

The following is a valid SQL in Clickhouse, but SQLGlot expects a table name after the **JOIN**

```sql
SELECT s, arr_external1, arr_external2,arr_external3
FROM (SELECT 's' as s)
ARRAY JOIN 
    [1, 2, 3] AS arr_external1,
    ['a', 'b', 'c'] AS arr_external2,
    splitByString(',', 'asd,qwerty,zxc') AS arr_external3
```

Here is the ParseError code for reference:

```python
ParseError: Expected table name but got <Token token_type: <TokenType.L_BRACKET: 'L_BRACKET'>, text: '[', line: 5, col: 2, start: 87, end: 87, comments: []>. Line 5, Col: 2.
  
SELECT s, arr_external1, arr_external2,arr_external3
FROM arrays_test
ARRAY JOIN 
    [1, 2, 3] AS arr_external1,
    ['a', 'b', 'c'] AS arr_external2,
    splitByString(',', 'asd,qwerty,
```

**Docs**: https://clickhouse.com/docs/en/sql-reference/statements/select/array-join

Thanks

Discussion:
@brunorpinho: Wow @VaggelisD , this is problaby the fastest merge I've experienced in an open source project. Are you all ok if I add a couple more issues with clickhouse in order to improve coverage?@VaggelisD: @brunorpinho Thank you! Of course, well documented issues help us a lot. Generally though our bandwidth is limited so it’s not sure that we’ll address everything now, we mostly prioritize core bugs & missing features.

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index ff8a6113a9..8884635f1f 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2400,6 +2400,7 @@ class Join(Expression):
         "global": False,
         "hint": False,
         "match_condition": False,  # Snowflake
+        "expressions": False,
     }
 
     @property
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index cdd580afd0..abfbca58ad 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2144,6 +2144,10 @@ def join_sql(self, expression: exp.Join) -> str:
         this = expression.this
         this_sql = self.sql(this)
 
+        exprs = self.expressions(expression)
+        if exprs:
+            this_sql = f"{this_sql},{self.seg(exprs)}"
+
         if on_sql:
             on_sql = self.indent(on_sql, skip_first=True)
             space = self.seg(" " * self.pad) if self.pretty else " "
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 45d2cf2845..7b15f214a0 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3400,6 +3400,10 @@ def _parse_join(
             return None
 
         kwargs: t.Dict[str, t.Any] = {"this": self._parse_table(parse_bracket=parse_bracket)}
+        if kind and kind.token_type == TokenType.ARRAY and self._match(TokenType.COMMA):
+            kwargs["expressions"] = self._parse_csv(
+                lambda: self._parse_table(parse_bracket=parse_bracket)
+            )
 
         if method:
             kwargs["method"] = method.text
@@ -3420,7 +3424,7 @@ def _parse_join(
         elif (
             not (outer_apply or cross_apply)
             and not isinstance(kwargs["this"], exp.Unnest)
-            and not (kind and kind.token_type == TokenType.CROSS)
+            and not (kind and kind.token_type in (TokenType.CROSS, TokenType.ARRAY))
         ):
             index = self._index
             joins: t.Optional[list] = list(self._parse_joins())


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index b4fc58731e..072d81aabe 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -425,6 +425,9 @@ def test_clickhouse(self):
         self.validate_identity(
             "SELECT s, arr_external FROM arrays_test ARRAY JOIN [1, 2, 3] AS arr_external"
         )
+        self.validate_identity(
+            "SELECT * FROM tbl ARRAY JOIN [1, 2, 3] AS arr_external1, ['a', 'b', 'c'] AS arr_external2, splitByString(',', 'asd,qwerty,zxc') AS arr_external3"
+        )
         self.validate_all(
             "SELECT quantile(0.5)(a)",
             read={"duckdb": "SELECT quantile(a, 0.5)"},