Repository: tobymao/sqlglot
Pull request: feat(databricks): GROUP_CONCAT to LISTAGG
Fixes #5281 

**DOCS**
[Databricks LISTAGG](https://docs.databricks.com/aws/en/sql/language-manual/functions/listagg)

Problem statement:
Use STRING_AGG insted of GROUP_CONCAT in databricks
sqlglot==26.25.3

`
SELECT
  test,
  STRING_AGG(email, '') AS Email
FROM organizations
GROUP BY
test
`

The above query gets converted to
`
SELECT
  test,
  GROUP_CONCAT(email, '') AS Email
FROM organizations
GROUP BY
test
`

When passed through the below code,
`
expression_tree = parse_one(query, read='databricks')
`

But GROUP_CONCAT is not a valid DATABRICKS, but STRING_AGG is.

Can this be fixed?

Hints from issue discussion:

Commit message: fix validate identity

Patch to review:
diff --git a/sqlglot/dialects/databricks.py b/sqlglot/dialects/databricks.py
index f13b4ca447..b93b0529ca 100644
--- a/sqlglot/dialects/databricks.py
+++ b/sqlglot/dialects/databricks.py
@@ -9,6 +9,7 @@
     build_date_delta,
     timestamptrunc_sql,
     build_formatted_time,
+    groupconcat_sql,
 )
 from sqlglot.dialects.spark import Spark
 from sqlglot.tokens import TokenType
@@ -87,6 +88,7 @@ class Generator(Spark.Generator):
                 e.this,
             ),
             exp.DatetimeTrunc: timestamptrunc_sql(),
+            exp.GroupConcat: lambda self, e: groupconcat_sql(self, e),
             exp.Select: transforms.preprocess(
                 [
                     transforms.eliminate_distinct_on,
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index d99c518beb..e4e9842106 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -171,6 +171,9 @@ def test_databricks(self):
                 self.validate_identity(
                     f"CREATE TABLE t1 (foo BIGINT NOT NULL CONSTRAINT foo_c FOREIGN KEY REFERENCES t2{option})"
                 )
+        self.validate_identity(
+            "SELECT test, LISTAGG(email, '') AS Email FROM organizations GROUP BY test",
+        )
 
     # https://docs.databricks.com/sql/language-manual/functions/colonsign.html
     def test_json(self):
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 3fe14e56c4..1bd90bb988 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -933,6 +933,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(DISTINCT x ORDER BY y DESC SEPARATOR ',')",
                 "sqlite": "GROUP_CONCAT(DISTINCT x)",
                 "tsql": "STRING_AGG(x, ',') WITHIN GROUP (ORDER BY y DESC)",
+                "databricks": "LISTAGG(DISTINCT x, ',') WITHIN GROUP (ORDER BY y DESC)",
                 "postgres": "STRING_AGG(DISTINCT x, ',' ORDER BY y DESC NULLS LAST)",
             },
         )
@@ -942,6 +943,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(x ORDER BY y SEPARATOR z)",
                 "sqlite": "GROUP_CONCAT(x, z)",
                 "tsql": "STRING_AGG(x, z) WITHIN GROUP (ORDER BY y)",
+                "databricks": "LISTAGG(x, z) WITHIN GROUP (ORDER BY y)",
                 "postgres": "STRING_AGG(x, z ORDER BY y NULLS FIRST)",
             },
         )
@@ -951,6 +953,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(DISTINCT x ORDER BY y DESC SEPARATOR '')",
                 "sqlite": "GROUP_CONCAT(DISTINCT x, '')",
                 "tsql": "STRING_AGG(x, '') WITHIN GROUP (ORDER BY y DESC)",
+                "databricks": "LISTAGG(DISTINCT x, '') WITHIN GROUP (ORDER BY y DESC)",
                 "postgres": "STRING_AGG(DISTINCT x, '' ORDER BY y DESC NULLS LAST)",
             },
         )
@@ -961,6 +964,7 @@ def test_mysql(self):
                 "sqlite": "GROUP_CONCAT(a || b || c, ',')",
                 "tsql": "STRING_AGG(CONCAT(a, b, c), ',')",
                 "postgres": "STRING_AGG(CONCAT(a, b, c), ',')",
+                "databricks": "LISTAGG(CONCAT(a, b, c), ',')",
                 "presto": "ARRAY_JOIN(ARRAY_AGG(CONCAT(CAST(a AS VARCHAR), CAST(b AS VARCHAR), CAST(c AS VARCHAR))), ',')",
             },
         )
@@ -970,6 +974,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(CONCAT(a, b, c) SEPARATOR '')",
                 "sqlite": "GROUP_CONCAT(a || b || c, '')",
                 "tsql": "STRING_AGG(CONCAT(a, b, c), '')",
+                "databricks": "LISTAGG(CONCAT(a, b, c), '')",
                 "postgres": "STRING_AGG(CONCAT(a, b, c), '')",
             },
         )
@@ -979,6 +984,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(DISTINCT CONCAT(a, b, c) SEPARATOR '')",
                 "sqlite": "GROUP_CONCAT(DISTINCT a || b || c, '')",
                 "tsql": "STRING_AGG(CONCAT(a, b, c), '')",
+                "databricks": "LISTAGG(DISTINCT CONCAT(a, b, c), '')",
                 "postgres": "STRING_AGG(DISTINCT CONCAT(a, b, c), '')",
             },
         )
@@ -988,6 +994,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(CONCAT(a, b, c) ORDER BY d SEPARATOR '')",
                 "sqlite": "GROUP_CONCAT(a || b || c, '')",
                 "tsql": "STRING_AGG(CONCAT(a, b, c), '') WITHIN GROUP (ORDER BY d)",
+                "databricks": "LISTAGG(CONCAT(a, b, c), '') WITHIN GROUP (ORDER BY d)",
                 "postgres": "STRING_AGG(CONCAT(a, b, c), '' ORDER BY d NULLS FIRST)",
             },
         )
@@ -997,6 +1004,7 @@ def test_mysql(self):
                 "mysql": "GROUP_CONCAT(DISTINCT CONCAT(a, b, c) ORDER BY d SEPARATOR '')",
                 "sqlite": "GROUP_CONCAT(DISTINCT a || b || c, '')",
                 "tsql": "STRING_AGG(CONCAT(a, b, c), '') WITHIN GROUP (ORDER BY d)",
+                "databricks": "LISTAGG(DISTINCT CONCAT(a, b, c), '') WITHIN GROUP (ORDER BY d)",
                 "postgres": "STRING_AGG(DISTINCT CONCAT(a, b, c), '' ORDER BY d NULLS FIRST)",
             },
         )


Code review comments:
diff_hunk: @@ -87,6 +88,7 @@ class Generator(Spark.Generator):
                 e.this,
             ),
             exp.DatetimeTrunc: timestamptrunc_sql(),
+            exp.GroupConcat: lambda self, e: groupconcat_sql(self, e),
path: sqlglot/dialects/databricks.py
text: @user1:
```suggestion
            exp.GroupConcat: groupconcat_sql,
```

@author:
right