Repository: tobymao/sqlglot

Issue:
ClickHouse: Unknown function ARRAY_CONCAT.
**Is your feature request related to a problem? Please describe.**

In ClickHouse (version 24.8.5.115) the following SQL statement producing error:

```
SELECT ARRAY_CONCAT([1,2], [3,4])
```

Error:

`SQL Error [46] [07000]: Code: 46. DB::Exception: Unknown function ARRAY_CONCAT.
    Maybe you meant: ['arrayConcat','arrayCount']: Or unknown aggregate function ARRAY_CONCAT.
    Maybe you meant: ['group_concat','array_concat_agg']: While processing ARRAY_CONCAT([1, 2], [3, 4]).
    (UNKNOWN_FUNCTION) (version 24.8.5.115 (official build))`

In ClickHouse this function has different name: `arrayConcat`

[ClickHouse Official Documentation](https://clickhouse.com/docs/en/sql-reference/functions/array-functions#arrayconcat)

**Describe the solution you'd like**

This can be fixed using already existing code, overriding transformation in ClickHouse dialect generator by providing the name of the function.

```
exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="arrayConcat"),
```

Test:

```
self.validate_all(
    "SELECT ARRAY_CONCAT([1, 2], [3, 4])",
    write={
        "clickhouse": "SELECT arrayConcat([1, 2], [3, 4])",
    }
)
```

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 7237813ae8..e13bc15b63 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -989,6 +989,7 @@ class Generator(generator.Generator):
             **generator.Generator.TRANSFORMS,
             exp.AnyValue: rename_func("any"),
             exp.ApproxDistinct: rename_func("uniq"),
+            exp.ArrayConcat: rename_func("arrayConcat"),
             exp.ArrayFilter: lambda self, e: self.func("arrayFilter", e.expression, e.this),
             exp.ArraySum: rename_func("arraySum"),
             exp.ArgMax: arg_max_or_min_no_count("argMax"),
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index fef779951c..65cf870a72 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -689,6 +689,7 @@ class Generator(generator.Generator):
             exp.DataType.Type.CHAR: "TEXT",
             exp.DataType.Type.DATETIME: "TIMESTAMP",
             exp.DataType.Type.FLOAT: "REAL",
+            exp.DataType.Type.JSONB: "JSON",
             exp.DataType.Type.NCHAR: "TEXT",
             exp.DataType.Type.NVARCHAR: "TEXT",
             exp.DataType.Type.UINT: "UINTEGER",


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 44221c48af..1f541df949 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -571,6 +571,7 @@ def test_clickhouse(self):
         self.validate_identity(
             "SELECT COUNT(1) FROM table SETTINGS additional_table_filters = {'a': 'b', 'c': 'd'}"
         )
+        self.validate_identity("SELECT arrayConcat([1, 2], [3, 4])")
 
     def test_clickhouse_values(self):
         values = exp.select("*").from_(
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a9b32004a2..6c3bc6bbcd 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -410,6 +410,13 @@ def test_duckdb(self):
                 "tsql": "SELECT FORMAT(CAST('2020-01-01' AS DATETIME2), CONCAT('yyyy', 'MM'))",
             },
         )
+        self.validate_all(
+            """SELECT CAST('{"x": 1}' AS JSON)""",
+            read={
+                "duckdb": """SELECT '{"x": 1}'::JSON""",
+                "postgres": """SELECT '{"x": 1}'::JSONB""",
+            },
+        )
         self.validate_all(
             "SELECT * FROM produce PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2'))",
             read={