Repository: tobymao/sqlglot

Issue:
Previously correct translation of ArrayJoin is now broken for various dialects
**Fully reproducible code snippet**

```
In [12]: import sqlglot as sg, sqlglot.expressions as sge

In [13]: sg.__version__
Out[13]: '22.3.1'

In [14]: sge.ArrayJoin(this=sg.column("arg"), expression=sg.column("sep")).sql("bigquery")
Out[14]: 'ARRAY_JOIN(arg, sep)'
```

Interestingly, when I parse the result I get an `ArrayJoin` instance and calling `.sql` on the result of `parse_one` generates the correct code, but constructing the object directly generates incorrect code:

```
In [20]: sg.parse_one("select array_to_string(arg, sep)", read="bigquery")
Out[20]:
Select(
  expressions=[
    ArrayJoin(
      this=Column(
        this=Identifier(this=arg, quoted=False)),
      expression=Column(
        this=Identifier(this=sep, quoted=False)))])

In [21]: sg.parse_one("select array_to_string(arg, sep)", read="bigquery").sql("bigquery")
Out[21]: 'SELECT array_to_string(arg, sep)'
```

**Official Documentation**

E.g., BigQuery has no function called `array_join`: https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions


Discussion:

Patch:
diff --git a/sqlglot/dataframe/sql/functions.py b/sqlglot/dataframe/sql/functions.py
index db5201f2..f87b2ffa 100644
--- a/sqlglot/dataframe/sql/functions.py
+++ b/sqlglot/dataframe/sql/functions.py
@@ -971,10 +971,10 @@ def array_join(
 ) -> Column:
     if null_replacement is not None:
         return Column.invoke_expression_over_column(
-            col, expression.ArrayJoin, expression=lit(delimiter), null=lit(null_replacement)
+            col, expression.ArrayToString, expression=lit(delimiter), null=lit(null_replacement)
         )
     return Column.invoke_expression_over_column(
-        col, expression.ArrayJoin, expression=lit(delimiter)
+        col, expression.ArrayToString, expression=lit(delimiter)
     )
 
 
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index bbe6813a..dab07750 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -356,7 +356,6 @@ class DuckDB(Dialect):
                 if e.expressions and e.expressions[0].find(exp.Select)
                 else inline_array_sql(self, e)
             ),
-            exp.ArrayJoin: rename_func("ARRAY_TO_STRING"),
             exp.ArrayFilter: rename_func("LIST_FILTER"),
             exp.ArraySize: rename_func("ARRAY_LENGTH"),
             exp.ArgMax: arg_max_or_min_no_count("ARG_MAX"),
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 391e02b7..089e6aa7 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -473,7 +473,7 @@ class Hive(Dialect):
             exp.ArgMax: arg_max_or_min_no_count("MAX_BY"),
             exp.ArgMin: arg_max_or_min_no_count("MIN_BY"),
             exp.ArrayConcat: rename_func("CONCAT"),
-            exp.ArrayJoin: lambda self, e: self.func("CONCAT_WS", e.expression, e.this),
+            exp.ArrayToString: lambda self, e: self.func("CONCAT_WS", e.expression, e.this),
             exp.ArraySize: rename_func("SIZE"),
             exp.ArraySort: _array_sort_sql,
             exp.With: no_recursive_cte_sql,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 5a7664ae..6099d299 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -324,6 +324,7 @@ class Presto(Dialect):
             exp.ArrayConcat: rename_func("CONCAT"),
             exp.ArrayContains: rename_func("CONTAINS"),
             exp.ArraySize: rename_func("CARDINALITY"),
+            exp.ArrayToString: rename_func("ARRAY_JOIN"),
             exp.ArrayUniqueAgg: rename_func("SET_AGG"),
             exp.AtTimeZone: rename_func("AT_TIMEZONE"),
             exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression),
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 1d63e8b3..804775c3 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -715,7 +715,6 @@ class Snowflake(Dialect):
             exp.Array: inline_array_sql,
             exp.ArrayConcat: rename_func("ARRAY_CAT"),
             exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this),
-            exp.ArrayJoin: rename_func("ARRAY_TO_STRING"),
             exp.AtTimeZone: lambda self, e: self.func(
                 "CONVERT_TIMEZONE", e.args.get("zone"), e.this
             ),
diff --git a/sqlglot/dialects/spark2.py b/sqlglot/dialects/spark2.py
index 63eae6ee..1a582b2d 100644
--- a/sqlglot/dialects/spark2.py
+++ b/sqlglot/dialects/spark2.py
@@ -203,6 +203,7 @@ class Spark2(Hive):
             exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"),
             exp.ArraySum: lambda self,
             e: f"AGGREGATE({self.sql(e, 'this')}, 0, (acc, x) -> acc + x, acc -> acc)",
+            exp.ArrayToString: rename_func("ARRAY_JOIN"),
             exp.AtTimeZone: lambda self, e: self.func(
                 "FROM_UTC_TIMESTAMP", e.this, e.args.get("zone")
             ),
@@ -252,7 +253,6 @@ class Spark2(Hive):
                 [transforms.remove_within_group_for_percentiles]
             ),
         }
-        TRANSFORMS.pop(exp.ArrayJoin)
         TRANSFORMS.pop(exp.ArraySort)
         TRANSFORMS.pop(exp.ILike)
         TRANSFORMS.pop(exp.Left)
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 313824d3..45855772 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -761,6 +761,7 @@ class TSQL(Dialect):
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
             exp.AnyValue: any_value_to_max_sql,
+            exp.ArrayToString: rename_func("STRING_AGG"),
             exp.AutoIncrementColumnConstraint: lambda *_: "IDENTITY",
             exp.DateAdd: date_delta_sql("DATEADD"),
             exp.DateDiff: date_delta_sql("DATEDIFF"),
diff --git a/sqlglot/executor/env.py b/sqlglot/executor/env.py
index 901ed3b4..59d0938b 100644
--- a/sqlglot/executor/env.py
+++ b/sqlglot/executor/env.py
@@ -139,7 +139,7 @@ def interval(this, unit):
 
 
 @null_if_any("this", "expression")
-def arrayjoin(this, expression, null=None):
+def arraytostring(this, expression, null=None):
     return expression.join(x for x in (x if x is not None else null for x in this) if x is not None)
 
 
@@ -173,7 +173,7 @@ ENV = {
     "ABS": null_if_any(lambda this: abs(this)),
     "ADD": null_if_any(lambda e, this: e + this),
     "ARRAYANY": null_if_any(lambda arr, func: any(func(e) for e in arr)),
-    "ARRAYJOIN": arrayjoin,
+    "ARRAYTOSTRING": arraytostring,
     "BETWEEN": null_if_any(lambda this, low, high: low <= this and this <= high),
     "BITWISEAND": null_if_any(lambda this, e: this & e),
     "BITWISELEFTSHIFT": null_if_any(lambda this, e: this << e),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index af1d7700..1d2fe971 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4577,9 +4577,9 @@ class ArrayFilter(Func):
     _sql_names = ["FILTER", "ARRAY_FILTER"]
 
 
-class ArrayJoin(Func):
+class ArrayToString(Func):
     arg_types = {"this": True, "expression": True, "null": False}
-    _sql_names = ["ARRAY_JOIN", "ARRAY_TO_STRING"]
+    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
 
 
 class ArrayOverlaps(Binary, Func):


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 0b1dab58..eb2ef3a5 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -60,6 +60,7 @@ class TestBigQuery(Validator):
         select_with_quoted_udf = self.validate_identity("SELECT `p.d.UdF`(data) FROM `p.d.t`")
         self.assertEqual(select_with_quoted_udf.selects[0].name, "p.d.UdF")
 
+        self.validate_identity("SELECT ARRAY_TO_STRING(list, '--') AS text")
         self.validate_identity("SELECT jsondoc['some_key']")
         self.validate_identity("SELECT `p.d.UdF`(data).* FROM `p.d.t`")
         self.validate_identity("SELECT * FROM `my-project.my-dataset.my-table`")
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 35daff09..306496b7 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -26,13 +26,20 @@ class TestDuckDB(Validator):
         self.validate_all(
             "ARRAY_TO_STRING(arr, delim)",
             read={
+                "bigquery": "ARRAY_TO_STRING(arr, delim)",
+                "postgres": "ARRAY_TO_STRING(arr, delim)",
+                "presto": "ARRAY_JOIN(arr, delim)",
                 "snowflake": "ARRAY_TO_STRING(arr, delim)",
                 "spark": "ARRAY_JOIN(arr, delim)",
             },
             write={
+                "bigquery": "ARRAY_TO_STRING(arr, delim)",
                 "duckdb": "ARRAY_TO_STRING(arr, delim)",
+                "postgres": "ARRAY_TO_STRING(arr, delim)",
+                "presto": "ARRAY_JOIN(arr, delim)",
                 "snowflake": "ARRAY_TO_STRING(arr, delim)",
                 "spark": "ARRAY_JOIN(arr, delim)",
+                "tsql": "STRING_AGG(arr, delim)",
             },
         )
         self.validate_all(