Repository: tobymao/sqlglot

Issue:
DuckDB Min Arg's not supported
```python
import sqlglot

print(sqlglot.parse_one("select min_by(item, a, 2) from test", dialect="duckdb").sql("duckdb"))
```
gives:
```
Argument 'count' is not supported for expression 'ArgMin' when targeting DuckDB.
SELECT ARG_MIN(item, a) FROM test
```

Duckdb does support this however: https://duckdb.org/docs/sql/functions/aggregates.html#min_byarg-val-n

Discussion:
@VaggelisD: Hey @aersam, thank you for raising this issue as well as fixing it!


Looks like [earlier in 2024](https://web.archive.org/web/20240714192150/https://duckdb.org/docs/sql/functions/aggregates.html#approximate-aggregates) it wasn't supported and support from our end was initially added in 2023 by https://github.com/tobymao/sqlglot/pull/2454, so this should be a very recent DuckDB feature.

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 9dd7a365dd..9c757d677c 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -9,7 +9,6 @@
     JSON_EXTRACT_TYPE,
     NormalizationStrategy,
     approx_count_distinct_sql,
-    arg_max_or_min_no_count,
     arrow_json_extract_sql,
     binary_from_function,
     bool_xor_sql,
@@ -516,8 +515,6 @@ class Generator(generator.Generator):
             exp.ApproxDistinct: approx_count_distinct_sql,
             exp.Array: inline_array_unless_query,
             exp.ArrayFilter: rename_func("LIST_FILTER"),
-            exp.ArgMax: arg_max_or_min_no_count("ARG_MAX"),
-            exp.ArgMin: arg_max_or_min_no_count("ARG_MIN"),
             exp.ArraySort: _array_sort_sql,
             exp.ArraySum: rename_func("LIST_SUM"),
             exp.BitwiseXor: rename_func("XOR"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 1c40602e2f..41ac50bcbe 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1476,6 +1476,7 @@ class Describe(Expression):
         "kind": False,
         "expressions": False,
         "partition": False,
+        "format": False,
     }
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 90a42df42a..f8c82102ed 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1193,7 +1193,10 @@ def describe_sql(self, expression: exp.Describe) -> str:
         style = f" {style}" if style else ""
         partition = self.sql(expression, "partition")
         partition = f" {partition}" if partition else ""
-        return f"DESCRIBE{style} {self.sql(expression, 'this')}{partition}"
+        format = self.sql(expression, "format")
+        format = f" {format}" if format else ""
+
+        return f"DESCRIBE{style}{format} {self.sql(expression, 'this')}{partition}"
 
     def heredoc_sql(self, expression: exp.Heredoc) -> str:
         tag = self.sql(expression, "tag")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 5fa7d1ef13..ac7f23a573 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2609,7 +2609,14 @@ def _parse_describe(self) -> exp.Describe:
         if self._match(TokenType.DOT):
             style = None
             self._retreat(self._index - 2)
-        this = self._parse_table(schema=True)
+
+        format = self._parse_property() if self._match(TokenType.FORMAT, advance=False) else None
+
+        if self._match_set(self.STATEMENT_PARSERS, advance=False):
+            this = self._parse_statement()
+        else:
+            this = self._parse_table(schema=True)
+
         properties = self._parse_properties()
         expressions = properties.expressions if properties else None
         partition = self._parse_partition()
@@ -2620,6 +2627,7 @@ def _parse_describe(self) -> exp.Describe:
             kind=kind,
             expressions=expressions,
             partition=partition,
+            format=format,
         )
 
     def _parse_multitable_inserts(self, comments: t.Optional[t.List[str]]) -> exp.MultitableInserts:
@@ -5109,9 +5117,8 @@ def _parse_column_ops(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.
             else:
                 field = self._parse_field(any_token=True, anonymous_func=True)
 
-            if isinstance(field, exp.Func) and this:
-                # bigquery allows function calls like x.y.count(...)
-                # SAFE.SUBSTR(...)
+            if isinstance(field, (exp.Func, exp.Window)) and this:
+                # BQ & snowflake allow function calls like x.y.count(...), SAFE.SUBSTR(...) etc
                 # https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference#function_call_rules
                 this = exp.replace_tree(
                     this,
@@ -5135,6 +5142,11 @@ def _parse_column_ops(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.
                     db=this.args.get("table"),
                     catalog=this.args.get("db"),
                 )
+            elif isinstance(field, exp.Window):
+                # Move the exp.Dot's to the window's function
+                window_func = self.expression(exp.Dot, this=this, expression=field.this)
+                field.set("this", window_func)
+                this = field
             else:
                 this = self.expression(exp.Dot, this=this, expression=field)
 


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index d7aef4f8d9..fe21b01516 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1319,3 +1319,6 @@ def test_explain(self):
         expression = self.parse_one("EXPLAIN ANALYZE SELECT * FROM t")
         self.assertIsInstance(expression, exp.Describe)
         self.assertEqual(expression.text("style"), "ANALYZE")
+
+        for format in ("JSON", "TRADITIONAL", "TREE"):
+            self.validate_identity(f"DESCRIBE FORMAT={format} UPDATE test SET test_col = 'abc'")
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 31a078c17c..74da50d6e0 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -722,7 +722,7 @@ def test_presto(self):
             "SELECT MIN_BY(a.id, a.timestamp, 3) FROM a",
             write={
                 "clickhouse": "SELECT argMin(a.id, a.timestamp) FROM a",
-                "duckdb": "SELECT ARG_MIN(a.id, a.timestamp) FROM a",
+                "duckdb": "SELECT ARG_MIN(a.id, a.timestamp, 3) FROM a",
                 "presto": "SELECT MIN_BY(a.id, a.timestamp, 3) FROM a",
                 "snowflake": "SELECT MIN_BY(a.id, a.timestamp, 3) FROM a",
                 "spark": "SELECT MIN_BY(a.id, a.timestamp) FROM a",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 515a07c4f4..157947df46 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -2250,3 +2250,13 @@ def test_grant(self):
         self.validate_identity(
             "GRANT ALL PRIVILEGES ON FUNCTION mydb.myschema.ADD5(number) TO ROLE analyst"
         )
+
+    def test_window_function_arg(self):
+        query = "SELECT * FROM TABLE(db.schema.FUNC(a) OVER ())"
+
+        ast = self.parse_one(query)
+        window = ast.find(exp.Window)
+
+        self.assertEqual(ast.sql("snowflake"), query)
+        self.assertEqual(len(list(ast.find_all(exp.Column))), 1)
+        self.assertEqual(window.this.sql("snowflake"), "db.schema.FUNC(a)")