Repository: tobymao/sqlglot

Issue:
BigQuery "normalization mode" constants are not recognized
The BigQuery function `NORMALIZE` takes an optional second argument to specify the normalization mode ([bigquery docs for this function](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#normalize)). The "normalization_mode" constants for this second argument (one of `nfkc`, `nfc`, `nfkd`, `nfd`) are interpreted as column names by `sqlglot.optimizer.optimize`.

## sample code
This code raises an `OptimizeError`:
```
import sqlglot, sqlglot.optimizer
parsed_query = 'select normalize("hello", nfkc)'
parsed = sqlglot.parse_one(parsed_query, read='bigquery')
sqlglot.optimizer.optimize(parsed)
# => sqlglot.errors.OptimizeError: Column 'nfkc' could not be resolved
```
This code does not raise an error, but it incorrectly parses the SQL as if `nfkc` were a field from the table:
```
import sqlglot, sqlglot.optimizer
parsed_query = 'select normalize("hello", nfkc) from foo'
parsed = sqlglot.parse_one(parsed_query, read='bigquery')
results = sqlglot.optimizer.optimize(parsed) # does not raise an error
print(results.sql())
# => SELECT NORMALIZE('hello', "foo"."nfkc") AS "_col_0" FROM "foo" AS "foo"
```
If a schema is supplied for the table, then the optimizer again raises an `OptimizeError` error:
```
import sqlglot, sqlglot.optimizer
parsed_query = 'select normalize("hello", nfkc) from foo'
parsed = sqlglot.parse_one(parsed_query, read='bigquery')
sqlglot.optimizer.optimize(parsed, read='bigquery', schema={'foo': {'id': 'INT'}})
# => sqlglot.errors.OptimizeError: Column 'nfkc' could not be resolved
```

Discussion:

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 8222bb5d32..69395f81b6 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5851,6 +5851,10 @@ class Nvl2(Func):
     arg_types = {"this": True, "true": True, "false": False}
 
 
+class Normalize(Func):
+    arg_types = {"this": True, "form": False}
+
+
 # https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
 class Predict(Func):
     arg_types = {"this": True, "expression": True, "params_struct": False}
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index b0fc9d1311..0d22c72523 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1096,6 +1096,7 @@ class Parser(metaclass=_Parser):
         "JSON_OBJECTAGG": lambda self: self._parse_json_object(agg=True),
         "JSON_TABLE": lambda self: self._parse_json_table(),
         "MATCH": lambda self: self._parse_match_against(),
+        "NORMALIZE": lambda self: self._parse_normalize(),
         "OPENJSON": lambda self: self._parse_open_json(),
         "POSITION": lambda self: self._parse_position(),
         "PREDICT": lambda self: self._parse_predict(),
@@ -7315,3 +7316,10 @@ def _parse_copy(self) -> exp.Copy | exp.Command:
             files=files,
             params=params,
         )
+
+    def _parse_normalize(self) -> exp.Normalize:
+        return self.expression(
+            exp.Normalize,
+            this=self._parse_bitwise(),
+            form=self._match(TokenType.COMMA) and self._parse_var(),
+        )


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 7d2eb56fcc..9ed8bc4caf 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -2760,3 +2760,22 @@ def test_set_operation_specifiers(self):
                 "tsql": UnsupportedError,
             },
         )
+
+    def test_normalize(self):
+        for form in ("", ", nfkc"):
+            with self.subTest(f"Testing NORMALIZE('str'{form}) roundtrip"):
+                self.validate_all(
+                    f"SELECT NORMALIZE('str'{form})",
+                    read={
+                        "presto": f"SELECT NORMALIZE('str'{form})",
+                        "trino": f"SELECT NORMALIZE('str'{form})",
+                        "bigquery": f"SELECT NORMALIZE('str'{form})",
+                    },
+                    write={
+                        "presto": f"SELECT NORMALIZE('str'{form})",
+                        "trino": f"SELECT NORMALIZE('str'{form})",
+                        "bigquery": f"SELECT NORMALIZE('str'{form})",
+                    },
+                )
+
+        self.assertIsInstance(parse_one("NORMALIZE('str', NFD)").args.get("form"), exp.Var)