Repository: tobymao/sqlglot

Issue:
Failure to parse nested ANY_VALUE HAVING MIN [Dialect: BigQuery]
sqlglot 10.1.3 fails to parse this query:

`sqlglot.parse_one('SELECT ANY_VALUE(fruit HAVING MAX sold) AS a_highest_selling_fruit FROM (( SELECT 20 AS sold, "apples" AS fruit UNION ALL SELECT 30 AS sold, "pears" AS fruit UNION ALL SELECT 30 AS sold, "bananas" AS fruit UNION ALL SELECT 10 AS sold, "oranges" AS fruit))')`

It expect a closing ) after HAVING.

**Error**
sqlglot.errors.ParseError: Expecting ). Line 1, Col: 29.
  SELECT ANY_VALUE(fruit HAVING MAX sold) AS a_highest_selling_fruit FROM (( SELECT 20 AS sold, "apples" AS fruit UNION ALL SELECT

**Official Documentation**
BigQuery docs for [ANY_VALUE HAVING MIN](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#any_value)

Discussion:

Patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index e01cc1ad..cdb93db0 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3922,7 +3922,7 @@ class Avg(AggFunc):
 
 
 class AnyValue(AggFunc):
-    pass
+    arg_types = {"this": True, "having": False, "max": False}
 
 
 class Case(Func):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 5d8a4ca5..a41af127 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2423,6 +2423,15 @@ class Generator:
         buckets = self.sql(expression, "buckets")
         return f"CLUSTERED BY ({expressions}){sorted_by} INTO {buckets} BUCKETS"
 
+    def anyvalue_sql(self, expression: exp.AnyValue) -> str:
+        this = self.sql(expression, "this")
+        having = self.sql(expression, "having")
+
+        if having:
+            this = f"{this} HAVING {'MAX' if expression.args.get('max') else 'MIN'} {having}"
+
+        return self.func("ANY_VALUE", this)
+
 
 def cached_generator(
     cache: t.Optional[t.Dict[int, str]] = None
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index e5bd4aed..85649a78 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -717,6 +717,7 @@ class Parser(metaclass=_Parser):
     FUNCTIONS_WITH_ALIASED_ARGS = {"STRUCT"}
 
     FUNCTION_PARSERS: t.Dict[str, t.Callable] = {
+        "ANY_VALUE": lambda self: self._parse_any_value(),
         "CAST": lambda self: self._parse_cast(self.STRICT_CAST),
         "CONCAT": lambda self: self._parse_concat(),
         "CONVERT": lambda self: self._parse_convert(self.STRICT_CAST),
@@ -3678,6 +3679,18 @@ class Parser(metaclass=_Parser):
 
         return self.expression(exp.Extract, this=this, expression=self._parse_bitwise())
 
+    def _parse_any_value(self) -> exp.AnyValue:
+        this = self._parse_lambda()
+        is_max = None
+        having = None
+
+        if self._match(TokenType.HAVING):
+            self._match_texts(("MAX", "MIN"))
+            is_max = self._prev.text == "MAX"
+            having = self._parse_column()
+
+        return self.expression(exp.AnyValue, this=this, having=having, max=is_max)
+
     def _parse_cast(self, strict: bool) -> exp.Expression:
         this = self._parse_conjunction()
 
diff --git a/sqlglot/schema.py b/sqlglot/schema.py
index b8560a16..b796f75e 100644
--- a/sqlglot/schema.py
+++ b/sqlglot/schema.py
@@ -226,9 +226,8 @@ class MappingSchema(AbstractMappingSchema[t.Dict[str, str]], Schema):
             column_mapping: a column mapping that describes the structure of the table.
             dialect: the SQL dialect that will be used to parse `table` if it's a string.
         """
-        normalized_table = self._normalize_table(
-            self._ensure_table(table, dialect=dialect), dialect=dialect
-        )
+        normalized_table = self._normalize_table(table, dialect=dialect)
+
         normalized_column_mapping = {
             self._normalize_name(key, dialect=dialect): value
             for key, value in ensure_column_mapping(column_mapping).items()
@@ -249,9 +248,7 @@ class MappingSchema(AbstractMappingSchema[t.Dict[str, str]], Schema):
         only_visible: bool = False,
         dialect: DialectType = None,
     ) -> t.List[str]:
-        normalized_table = self._normalize_table(
-            self._ensure_table(table, dialect=dialect), dialect=dialect
-        )
+        normalized_table = self._normalize_table(table, dialect=dialect)
 
         schema = self.find(normalized_table)
         if schema is None:
@@ -269,9 +266,8 @@ class MappingSchema(AbstractMappingSchema[t.Dict[str, str]], Schema):
         column: exp.Column,
         dialect: DialectType = None,
     ) -> exp.DataType:
-        normalized_table = self._normalize_table(
-            self._ensure_table(table, dialect=dialect), dialect=dialect
-        )
+        normalized_table = self._normalize_table(table, dialect=dialect)
+
         normalized_column_name = self._normalize_name(
             column if isinstance(column, str) else column.this, dialect=dialect
         )
@@ -316,8 +312,8 @@ class MappingSchema(AbstractMappingSchema[t.Dict[str, str]], Schema):
 
         return normalized_mapping
 
-    def _normalize_table(self, table: exp.Table, dialect: DialectType = None) -> exp.Table:
-        normalized_table = table.copy()
+    def _normalize_table(self, table: exp.Table | str, dialect: DialectType = None) -> exp.Table:
+        normalized_table = exp.maybe_parse(table, into=exp.Table, dialect=dialect or self.dialect, copy=True)
 
         for arg in TABLE_ARGS:
             value = normalized_table.args.get(arg)
@@ -351,9 +347,6 @@ class MappingSchema(AbstractMappingSchema[t.Dict[str, str]], Schema):
         # The columns themselves are a mapping, but we don't want to include those
         return super()._depth() - 1
 
-    def _ensure_table(self, table: exp.Table | str, dialect: DialectType = None) -> exp.Table:
-        return exp.maybe_parse(table, into=exp.Table, dialect=dialect or self.dialect)
-
     def _to_data_type(self, schema_type: str, dialect: DialectType = None) -> exp.DataType:
         """
         Convert a type represented as a string to the corresponding `sqlglot.exp.DataType` object.


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index e95ff3e0..e3fc4950 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -29,6 +29,8 @@ class TestBigQuery(Validator):
         with self.assertRaises(ParseError):
             transpile("SELECT * FROM UNNEST(x) AS x(y)", read="bigquery")
 
+        self.validate_identity("SELECT ANY_VALUE(fruit HAVING MAX sold) FROM fruits")
+        self.validate_identity("SELECT ANY_VALUE(fruit HAVING MIN sold) FROM fruits")
         self.validate_identity("SELECT `project-id`.udfs.func(call.dir)")
         self.validate_identity("SELECT CAST(CURRENT_DATE AS STRING FORMAT 'DAY') AS current_day")
         self.validate_identity("SAFE_CAST(encrypted_value AS STRING FORMAT 'BASE64')")