Repository: duckdb/duckdb (language: python)

Issue:
Should macros be allowed as custom aggregate window functions?
### What happens?

See https://github.com/duckdb/duckdb-web/issues/3294

### To Reproduce

```sql
CREATE OR REPLACE MACRO my_agg(x) AS SUM(CASE WHEN x THEN 1 END);
select my_agg(range) from range(2)
```
works and returns `1` but 
```sql
select my_agg(range) OVER () from range(2)
```
throws 
```
Catalog Error: my_agg is not an aggregate function
```

### OS:

Linux

### DuckDB Version:

1.1.0

### DuckDB Client:

Python

### Hardware:

i5, AMD64

### Full Name:

Soeren Wolfers

### Affiliation:

G-Research

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/planner/binder/expression/bind_macro_expression.cpp b/src/planner/binder/expression/bind_macro_expression.cpp
index 151eadf9863b..3856ecbf9feb 100644
--- a/src/planner/binder/expression/bind_macro_expression.cpp
+++ b/src/planner/binder/expression/bind_macro_expression.cpp
@@ -3,6 +3,7 @@
 #include "duckdb/function/scalar_macro_function.hpp"
 #include "duckdb/parser/expression/function_expression.hpp"
 #include "duckdb/parser/expression/subquery_expression.hpp"
+#include "duckdb/parser/expression/window_expression.hpp"
 #include "duckdb/parser/parsed_expression_iterator.hpp"
 #include "duckdb/planner/expression_binder.hpp"
 
@@ -126,7 +127,26 @@ void ExpressionBinder::UnfoldMacroExpression(FunctionExpression &function, Scala
 	macro_binding = new_macro_binding.get();
 
 	// replace current expression with stored macro expression
-	expr = macro_def.expression->Copy();
+	// special case: If this is a window function, then we need to return a window expression
+	if (expr->GetExpressionClass() == ExpressionClass::WINDOW) {
+		//	Only allowed if the expression is a function
+		if (macro_def.expression->GetExpressionType() != ExpressionType::FUNCTION) {
+			throw BinderException("Window function macros must be functions");
+		}
+		auto macro_copy = macro_def.expression->Copy();
+		auto &macro_expr = macro_copy->Cast<FunctionExpression>();
+		// Transfer the macro function attributes
+		auto &window_expr = expr->Cast<WindowExpression>();
+		window_expr.catalog = macro_expr.catalog;
+		window_expr.schema = macro_expr.schema;
+		window_expr.function_name = macro_expr.function_name;
+		window_expr.children = std::move(macro_expr.children);
+		window_expr.distinct = macro_expr.distinct;
+		window_expr.filter_expr = std::move(macro_expr.filter);
+		// TODO: transfer order_bys when window functions support them
+	} else {
+		expr = macro_def.expression->Copy();
+	}
 
 	// qualify only the macro parameters with a new empty binder that only knows the macro binding
 	auto dummy_binder = Binder::CreateBinder(context);
diff --git a/src/planner/binder/expression/bind_window_expression.cpp b/src/planner/binder/expression/bind_window_expression.cpp
index 5909df507454..482c4e17d650 100644
--- a/src/planner/binder/expression/bind_window_expression.cpp
+++ b/src/planner/binder/expression/bind_window_expression.cpp
@@ -142,9 +142,20 @@ static LogicalType BindRangeExpression(ClientContext &context, const string &nam
 }
 
 BindResult BaseSelectBinder::BindWindow(WindowExpression &window, idx_t depth) {
+	QueryErrorContext error_context(window.query_location);
+	//	Check for macros pretending to be aggregates
+	auto entry = GetCatalogEntry(CatalogType::SCALAR_FUNCTION_ENTRY, window.catalog, window.schema,
+	                             window.function_name, OnEntryNotFound::RETURN_NULL, error_context);
+	if (window.type == ExpressionType::WINDOW_AGGREGATE && entry && entry->type == CatalogType::MACRO_ENTRY) {
+		auto macro = make_uniq<FunctionExpression>(window.catalog, window.schema, window.function_name,
+		                                           std::move(window.children), std::move(window.filter_expr), nullptr,
+		                                           window.distinct);
+		auto macro_expr = window.Copy();
+		return BindMacro(*macro, entry->Cast<ScalarMacroCatalogEntry>(), depth, macro_expr);
+	}
+
 	auto name = window.GetName();
 
-	QueryErrorContext error_context(window.query_location);
 	if (inside_window) {
 		throw BinderException(error_context, "window function calls cannot be nested");
 	}
@@ -243,8 +254,12 @@ BindResult BaseSelectBinder::BindWindow(WindowExpression &window, idx_t depth) {
 	unique_ptr<FunctionData> bind_info;
 	if (window.type == ExpressionType::WINDOW_AGGREGATE) {
 		//  Look up the aggregate function in the catalog
-		auto &func = Catalog::GetEntry<AggregateFunctionCatalogEntry>(context, window.catalog, window.schema,
-		                                                              window.function_name, error_context);
+		if (!entry || entry->type != CatalogType::AGGREGATE_FUNCTION_ENTRY) {
+			//	Not an aggregate: Look it up to generate error
+			Catalog::GetEntry<AggregateFunctionCatalogEntry>(context, window.catalog, window.schema,
+			                                                 window.function_name, error_context);
+		}
+		auto &func = entry->Cast<AggregateFunctionCatalogEntry>();
 		D_ASSERT(func.type == CatalogType::AGGREGATE_FUNCTION_ENTRY);
 
 		// bind the aggregate


Test patch:
diff --git a/test/sql/catalog/function/test_window_macro.test b/test/sql/catalog/function/test_window_macro.test
new file mode 100644
index 000000000000..8d90bef7795e
--- /dev/null
+++ b/test/sql/catalog/function/test_window_macro.test
@@ -0,0 +1,46 @@
+# name: test/sql/catalog/function/test_window_macro.test
+# description: Test macro expansion for window functions
+# group: [function]
+
+statement ok
+PRAGMA enable_verification
+
+# Windowed aggregate macro
+statement ok
+CREATE OR REPLACE MACRO my_agg(x) AS SUM(CASE WHEN x THEN 1 END);
+
+statement ok
+select my_agg(range) 
+from range(2);
+
+statement ok
+select my_agg(range) OVER () 
+from range(2);
+
+# Windowed non-aggregate function macro
+statement ok
+CREATE OR REPLACE MACRO my_func(x) AS mod(x, 2);
+
+statement ok
+select my_func(range) 
+from range(2);
+
+statement error
+select my_func(range) OVER () 
+from range(2);
+----
+mod is not an aggregate function
+
+# Windowed non-aggregate expression macro
+statement ok
+CREATE OR REPLACE MACRO my_case(x) AS (CASE WHEN x THEN 1 END);
+
+statement ok
+select my_case(range) 
+from range(2);
+
+statement error
+select my_case(range) OVER () 
+from range(2);
+----
+Window function macros must be functions