Merging upstream version 23.16.0.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 15 insertions, 6 deletions

diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 3d2fb3d..47fb0ce 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -156,7 +156,7 @@ def _build_date(args: t.List) -> exp.Date | exp.DateFromParts:
 def _build_to_hex(args: t.List) -> exp.Hex | exp.MD5:
     # TO_HEX(MD5(..)) is common in BigQuery, so it's parsed into MD5 to simplify its transpilation
     arg = seq_get(args, 0)
-    return exp.MD5(this=arg.this) if isinstance(arg, exp.MD5Digest) else exp.Hex(this=arg)
+    return exp.MD5(this=arg.this) if isinstance(arg, exp.MD5Digest) else exp.LowerHex(this=arg)
 
 
 def _array_contains_sql(self: BigQuery.Generator, expression: exp.ArrayContains) -> str:
@@ -212,6 +212,7 @@ class BigQuery(Dialect):
     SUPPORTS_USER_DEFINED_TYPES = False
     SUPPORTS_SEMI_ANTI_JOIN = False
     LOG_BASE_FIRST = False
+    HEX_LOWERCASE = True
 
     # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#case_sensitivity
     NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
@@ -568,6 +569,8 @@ class BigQuery(Dialect):
         CAN_IMPLEMENT_ARRAY_ANY = True
         SUPPORTS_TO_NUMBER = False
         NAMED_PLACEHOLDER_TOKEN = "@"
+        HEX_FUNC = "TO_HEX"
+        WITH_PROPERTIES_PREFIX = "OPTIONS"
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -603,13 +606,12 @@ class BigQuery(Dialect):
             ),
             exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
             exp.GroupConcat: rename_func("STRING_AGG"),
-            exp.Hex: rename_func("TO_HEX"),
+            exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
             exp.If: if_sql(false_value="NULL"),
             exp.ILike: no_ilike_sql,
             exp.IntDiv: rename_func("DIV"),
             exp.JSONFormat: rename_func("TO_JSON_STRING"),
             exp.Max: max_or_greatest,
-            exp.Mod: rename_func("MOD"),
             exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
             exp.MD5Digest: rename_func("MD5"),
             exp.Min: min_or_least,
@@ -634,6 +636,7 @@ class BigQuery(Dialect):
                     transforms.eliminate_semi_and_anti_joins,
                 ]
             ),
+            exp.SHA: rename_func("SHA1"),
             exp.SHA2: lambda self, e: self.func(
                 "SHA256" if e.text("length") == "256" else "SHA512", e.this
             ),
@@ -801,6 +804,15 @@ class BigQuery(Dialect):
             "within",
         }
 
+        def mod_sql(self, expression: exp.Mod) -> str:
+            this = expression.this
+            expr = expression.expression
+            return self.func(
+                "MOD",
+                this.unnest() if isinstance(this, exp.Paren) else this,
+                expr.unnest() if isinstance(expr, exp.Paren) else expr,
+            )
+
         def column_parts(self, expression: exp.Column) -> str:
             if expression.meta.get("quoted_column"):
                 # If a column reference is of the form `dataset.table`.name, we need
@@ -896,9 +908,6 @@ class BigQuery(Dialect):
                 self.unsupported("INTERSECT without DISTINCT is not supported in BigQuery")
             return f"INTERSECT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
 
-        def with_properties(self, properties: exp.Properties) -> str:
-            return self.properties(properties, prefix=self.seg("OPTIONS"))
-
         def version_sql(self, expression: exp.Version) -> str:
             if expression.name == "TIMESTAMP":
                 expression.set("this", "SYSTEM_TIME")