Repository: tobymao/sqlglot

Issue:
MySQL's numeric `format` function is not properly translated to DuckDB
https://dev.mysql.com/doc/refman/8.4/en/string-functions.html#function_format

```sh
$ # sqlglot-25.32.0
$ python3
Python 3.9.6 (default, Feb  3 2024, 15:58:27)
[Clang 15.0.0 (clang-1500.3.9.4)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlglot
>>> sqlglot.transpile("SELECT format(123456.789, 3)", read="mysql", write="duckdb")[0]
'SELECT FORMAT(123456.789, 3)'
>>>
```

It appears that this function could be translated to duckdb's numeric `format` function: https://duckdb.org/docs/sql/functions/char#print-numbers-with-thousand-separators

Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index f3ef144a..946410f5 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -1021,3 +1021,12 @@ class DuckDB(Dialect):
             return self.func(
                 "REGEXP_EXTRACT", expression.this, expression.expression, group, params
             )
+
+        @unsupported_args("culture")
+        def numbertostr_sql(self, expression: exp.NumberToStr) -> str:
+            fmt = expression.args.get("format")
+            if fmt and fmt.is_int:
+                return self.func("FORMAT", f"'{{:,.{fmt.name}f}}'", expression.this)
+
+            self.unsupported("Only integer formats are supported by NumberToStr")
+            return self.function_fallback_sql(expression)
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 8cadd4ba..4dd35180 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -307,6 +307,7 @@ class MySQL(Dialect):
             "DAYOFMONTH": lambda args: exp.DayOfMonth(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
             "DAYOFWEEK": lambda args: exp.DayOfWeek(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
             "DAYOFYEAR": lambda args: exp.DayOfYear(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
+            "FORMAT": exp.NumberToStr.from_arg_list,
             "FROM_UNIXTIME": build_formatted_time(exp.UnixToTime, "mysql"),
             "ISNULL": isnull_to_is_null,
             "LOCATE": locate_to_strposition,
@@ -735,6 +736,7 @@ class MySQL(Dialect):
             exp.Month: _remove_ts_or_ds_to_date(),
             exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
             exp.NullSafeNEQ: lambda self, e: f"NOT {self.binary(e, '<=>')}",
+            exp.NumberToStr: rename_func("FORMAT"),
             exp.Pivot: no_pivot_sql,
             exp.Select: transforms.preprocess(
                 [


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 18cd3740..465c2317 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1,7 +1,7 @@
 import unittest
 import sys
 
-from sqlglot import expressions as exp
+from sqlglot import UnsupportedError, expressions as exp
 from sqlglot.dialects.mysql import MySQL
 from tests.dialects.test_dialect import Validator
 
@@ -1344,3 +1344,33 @@ COMMENT='客户账户表'"""
 
         for format in ("JSON", "TRADITIONAL", "TREE"):
             self.validate_identity(f"DESCRIBE FORMAT={format} UPDATE test SET test_col = 'abc'")
+
+    def test_number_format(self):
+        self.validate_all(
+            "SELECT FORMAT(12332.123456, 4)",
+            write={
+                "duckdb": "SELECT FORMAT('{:,.4f}', 12332.123456)",
+                "mysql": "SELECT FORMAT(12332.123456, 4)",
+            },
+        )
+        self.validate_all(
+            "SELECT FORMAT(12332.1, 4)",
+            write={
+                "duckdb": "SELECT FORMAT('{:,.4f}', 12332.1)",
+                "mysql": "SELECT FORMAT(12332.1, 4)",
+            },
+        )
+        self.validate_all(
+            "SELECT FORMAT(12332.2, 0)",
+            write={
+                "duckdb": "SELECT FORMAT('{:,.0f}', 12332.2)",
+                "mysql": "SELECT FORMAT(12332.2, 0)",
+            },
+        )
+        self.validate_all(
+            "SELECT FORMAT(12332.2, 2, 'de_DE')",
+            write={
+                "duckdb": UnsupportedError,
+                "mysql": "SELECT FORMAT(12332.2, 2, 'de_DE')",
+            },
+        )