Repository: tobymao/sqlglot

Issue:
Array of chars is truncated to one element
When updating the SQLGlot from version `25.19.0` to version `25.20.1`, I encountered the following problem. I have an array of chars and it is truncated to one element.

```python
from sqlglot import parse_one, Dialects

sql = 'SELECT toString(char(104.1, 101, 108.9, 108.9, 111, 32, 119, 111, 114, 108, 100))'
print(parse_one(sql, read=Dialects.CLICKHOUSE).sql(dialect=Dialects.CLICKHOUSE))
```

❌ Wrong output with the new version SQLGlot v25.20.1
```
SELECT toString(char(104.1))
```

✅ Correct output with SQLGlot v25.19.0
```
SELECT toString(char(104.1, 101, 108.9, 108.9, 111, 32, 119, 111, 114, 108, 100))
```

Discussion:

Solution patch:
diff --git a/sqlglot/__init__.py b/sqlglot/__init__.py
index e272b9f1c5..19fe93ba58 100644
--- a/sqlglot/__init__.py
+++ b/sqlglot/__init__.py
@@ -30,8 +30,10 @@
     except_ as except_,
     from_ as from_,
     func as func,
+    insert as insert,
     intersect as intersect,
     maybe_parse as maybe_parse,
+    merge as merge,
     not_ as not_,
     or_ as or_,
     select as select,
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 7796e84d70..978d446b6b 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -888,7 +888,7 @@ class Generator(generator.Generator):
             exp.Variance: rename_func("varSamp"),
             exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
             exp.Stddev: rename_func("stddevSamp"),
-            exp.Chr: lambda self, e: self.func("char", e.this),
+            exp.Chr: rename_func("CHAR"),
             exp.Lag: lambda self, e: self.func(
                 "lagInFrame", e.this, e.args.get("offset"), e.args.get("default")
             ),
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 47a518b18e..2508596283 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -329,7 +329,11 @@ class Parser(parser.Parser):
 
         FUNCTION_PARSERS = {
             **parser.Parser.FUNCTION_PARSERS,
-            "CHAR": lambda self: self._parse_chr(),
+            "CHAR": lambda self: self.expression(
+                exp.Chr,
+                expressions=self._parse_csv(self._parse_assignment),
+                charset=self._match(TokenType.USING) and self._parse_var(),
+            ),
             "GROUP_CONCAT": lambda self: self._parse_group_concat(),
             # https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
             "VALUES": lambda self: self.expression(
@@ -620,18 +624,6 @@ def _parse_type(
                 parse_interval=parse_interval, fallback_to_identifier=fallback_to_identifier
             )
 
-        def _parse_chr(self) -> t.Optional[exp.Expression]:
-            expressions = self._parse_csv(self._parse_assignment)
-            kwargs: t.Dict[str, t.Any] = {"this": seq_get(expressions, 0)}
-
-            if len(expressions) > 1:
-                kwargs["expressions"] = expressions[1:]
-
-            if self._match(TokenType.USING):
-                kwargs["charset"] = self._parse_var()
-
-            return self.expression(exp.Chr, **kwargs)
-
         def _parse_group_concat(self) -> t.Optional[exp.Expression]:
             def concat_exprs(
                 node: t.Optional[exp.Expression], exprs: t.List[exp.Expression]
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index ac48541c15..45c9096fd9 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5172,7 +5172,7 @@ class Coalesce(Func):
 
 
 class Chr(Func):
-    arg_types = {"this": True, "charset": False, "expressions": False}
+    arg_types = {"expressions": True, "charset": False}
     is_var_len_args = True
     _sql_names = ["CHR", "CHAR"]
 
@@ -6279,6 +6279,7 @@ class Merge(Expression):
         "on": True,
         "expressions": True,
         "with": False,
+        "returning": False,
     }
 
 
@@ -6885,6 +6886,49 @@ def insert(
     return insert
 
 
+def merge(
+    *when_exprs: ExpOrStr,
+    into: ExpOrStr,
+    using: ExpOrStr,
+    on: ExpOrStr,
+    dialect: DialectType = None,
+    copy: bool = True,
+    **opts,
+) -> Merge:
+    """
+    Builds a MERGE statement.
+
+    Example:
+        >>> merge("WHEN MATCHED THEN UPDATE SET col1 = source_table.col1",
+        ...       "WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)",
+        ...       into="my_table",
+        ...       using="source_table",
+        ...       on="my_table.id = source_table.id").sql()
+        'MERGE INTO my_table USING source_table ON my_table.id = source_table.id WHEN MATCHED THEN UPDATE SET col1 = source_table.col1 WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)'
+
+    Args:
+        *when_exprs: The WHEN clauses specifying actions for matched and unmatched rows.
+        into: The target table to merge data into.
+        using: The source table to merge data from.
+        on: The join condition for the merge.
+        dialect: The dialect used to parse the input expressions.
+        copy: Whether to copy the expression.
+        **opts: Other options to use to parse the input expressions.
+
+    Returns:
+        Merge: The syntax tree for the MERGE statement.
+    """
+    return Merge(
+        this=maybe_parse(into, dialect=dialect, copy=copy, **opts),
+        using=maybe_parse(using, dialect=dialect, copy=copy, **opts),
+        on=maybe_parse(on, dialect=dialect, copy=copy, **opts),
+        expressions=[
+            maybe_parse(when_expr, dialect=dialect, copy=copy, into=When, **opts)
+            for when_expr in when_exprs
+        ],
+    )
+
+
 def condition(
     expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
 ) -> Condition:
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 9a346b24d1..6e11d68c41 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3587,9 +3587,12 @@ def merge_sql(self, expression: exp.Merge) -> str:
         on = f"ON {self.sql(expression, 'on')}"
         expressions = self.expressions(expression, sep=" ", indent=False)
         sep = self.sep()
+        returning = self.expressions(expression, key="returning", indent=False)
+        returning = f"RETURNING {returning}" if returning else ""
 
         return self.prepend_ctes(
-            expression, f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{expressions}"
+            expression,
+            f"MERGE INTO {this}{table_alias}{sep}{using}{sep}{on}{sep}{expressions}{sep}{returning}",
         )
 
     def tochar_sql(self, expression: exp.ToChar) -> str:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index a15dd8155e..8fb3c14d51 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -187,6 +187,8 @@ class Parser(metaclass=_Parser):
         "ARRAY_AGG": lambda args, dialect: exp.ArrayAgg(
             this=seq_get(args, 0), nulls_excluded=dialect.ARRAY_AGG_INCLUDES_NULLS is None or None
         ),
+        "CHAR": lambda args: exp.Chr(expressions=args),
+        "CHR": lambda args: exp.Chr(expressions=args),
         "COUNT": lambda args: exp.Count(this=seq_get(args, 0), expressions=args[1:], big_int=True),
         "CONCAT": lambda args, dialect: exp.Concat(
             expressions=args,
@@ -6825,6 +6827,7 @@ def _parse_merge(self) -> exp.Merge:
             using=using,
             on=on,
             expressions=self._parse_when_matched(),
+            returning=self._match(TokenType.RETURNING) and self._parse_csv(self._parse_bitwise),
         )
 
     def _parse_when_matched(self) -> t.List[exp.When]:
@@ -6865,7 +6868,7 @@ def _parse_when_matched(self) -> t.List[exp.When]:
             elif self._match(TokenType.DELETE):
                 then = self.expression(exp.Var, this=self._prev.text)
             else:
-                then = None
+                then = self._parse_var_from_options(self.CONFLICT_ACTIONS)
 
             whens.append(
                 self.expression(


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index ea6064a6be..34d2b4d889 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -28,6 +28,7 @@ def test_clickhouse(self):
         self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
         self.assertIsNone(expr._meta)
 
+        self.validate_identity("SELECT toString(CHAR(104.1, 101, 108.9, 108.9, 111, 32))")
         self.validate_identity("@macro").assert_is(exp.Parameter).this.assert_is(exp.Var)
         self.validate_identity("SELECT toFloat(like)")
         self.validate_identity("SELECT like")
@@ -160,10 +161,10 @@ def test_clickhouse(self):
         )
 
         self.validate_all(
-            "char(67) || char(65) || char(84)",
+            "CHAR(67) || CHAR(65) || CHAR(84)",
             read={
-                "clickhouse": "char(67) || char(65) || char(84)",
-                "oracle": "chr(67) || chr(65) || chr(84)",
+                "clickhouse": "CHAR(67) || CHAR(65) || CHAR(84)",
+                "oracle": "CHR(67) || CHR(65) || CHR(84)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index f3f21a9b56..1b89ecd1d5 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -794,6 +794,9 @@ def test_postgres(self):
         self.validate_identity(
             'SELECT js, js IS JSON ARRAY WITH UNIQUE KEYS AS "array w. UK?", js IS JSON ARRAY WITHOUT UNIQUE KEYS AS "array w/o UK?", js IS JSON ARRAY UNIQUE KEYS AS "array w UK 2?" FROM t'
         )
+        self.validate_identity(
+            "MERGE INTO target_table USING source_table AS source ON target.id = source.id WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN DO NOTHING RETURNING MERGE_ACTION(), *"
+        )
 
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier
diff --git a/tests/test_build.py b/tests/test_build.py
index e074fea119..d169530461 100644
--- a/tests/test_build.py
+++ b/tests/test_build.py
@@ -731,6 +731,36 @@ def test_build(self):
                 lambda: exp.rename_column("table1", "c1", "c2"),
                 "ALTER TABLE table1 RENAME COLUMN c1 TO c2",
             ),
+            (
+                lambda: exp.merge(
+                    "WHEN MATCHED THEN UPDATE SET col1 = source.col1",
+                    "WHEN NOT MATCHED THEN INSERT (col1) VALUES (source.col1)",
+                    into="target_table",
+                    using="source_table",
+                    on="target_table.id = source_table.id",
+                ),
+                "MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED THEN UPDATE SET col1 = source.col1 WHEN NOT MATCHED THEN INSERT (col1) VALUES (source.col1)",
+            ),
+            (
+                lambda: exp.merge(
+                    "WHEN MATCHED AND source.is_deleted = 1 THEN DELETE",
+                    "WHEN MATCHED THEN UPDATE SET val = source.val",
+                    "WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)",
+                    into="target_table",
+                    using="source_table",
+                    on="target_table.id = source_table.id",
+                ),
+                "MERGE INTO target_table USING source_table ON target_table.id = source_table.id WHEN MATCHED AND source.is_deleted = 1 THEN DELETE WHEN MATCHED THEN UPDATE SET val = source.val WHEN NOT MATCHED THEN INSERT (id, val) VALUES (source.id, source.val)",
+            ),
+            (
+                lambda: exp.merge(
+                    "WHEN MATCHED THEN UPDATE SET target.name = source.name",
+                    into=exp.table_("target_table").as_("target"),
+                    using=exp.table_("source_table").as_("source"),
+                    on="target.id = source.id",
+                ),
+                "MERGE INTO target_table AS target USING source_table AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.name = source.name",
+            ),
         ]:
             with self.subTest(sql):
                 self.assertEqual(expression().sql(dialect[0] if dialect else None), sql)