Repository: duckdb/duckdb (language: python)

Issue:
Literal in SELECT DISTINCT ON clause raises ORDER BY non-integer literal has no effect.
### What happens?

After upgrading to DuckDB 1.1.1, I encountered an issue where DuckDB raises `Binder Error: ORDER BY non-integer literal has no effect` when having literal in `SELECT DISTINCT ON (..)` clause.

Turning order_by_non_integer_literal=true resolves the issue, allowing literal in DISTINCT ON.

The error message is really confusing as the initial cause does not come from the ORDER BY clause.

I would rather accept literal in SELECT DISTINCT clause by default or rework the error message to point out the correct SQL clause. 

### To Reproduce

```
con = duckdb.connect(":memory:")
con.execute('CREATE TABLE my_table AS SELECT 42 AS "column_name";')
con.execute("SET order_by_non_integer_literal = true;")
con.execute(
    "SELECT DISTINCT ON (my_table.column_name, 'co2') my_table.column_name FROM my_table"
).fetchone()

```

### OS:

iOS/Linux

### DuckDB Version:

1.1.1

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Jonathan Wadin

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### Did you include all relevant data sets for reproducing the issue?

Yes

### 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?

- [ ] Yes, I have
Literal in SELECT DISTINCT ON clause raises ORDER BY non-integer literal has no effect.
### What happens?

After upgrading to DuckDB 1.1.1, I encountered an issue where DuckDB raises `Binder Error: ORDER BY non-integer literal has no effect` when having literal in `SELECT DISTINCT ON (..)` clause.

Turning order_by_non_integer_literal=true resolves the issue, allowing literal in DISTINCT ON.

The error message is really confusing as the initial cause does not come from the ORDER BY clause.

I would rather accept literal in SELECT DISTINCT clause by default or rework the error message to point out the correct SQL clause. 

### To Reproduce

```
con = duckdb.connect(":memory:")
con.execute('CREATE TABLE my_table AS SELECT 42 AS "column_name";')
con.execute("SET order_by_non_integer_literal = true;")
con.execute(
    "SELECT DISTINCT ON (my_table.column_name, 'co2') my_table.column_name FROM my_table"
).fetchone()

```

### OS:

iOS/Linux

### DuckDB Version:

1.1.1

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Jonathan Wadin

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### Did you include all relevant data sets for reproducing the issue?

Yes

### 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?

- [ ] Yes, I have


Discussion / hints:
Hi @wadinj, thanks for opening this issue. What's the error message that you're getting? For me, this works fine in the CLI client:

```sql
CREATE TABLE my_table AS SELECT 42 AS "column_name";
SELECT * FROM my_table ORDER BY "column_name";
```
Hi @szarnyasg, here's a reproduction: 
```
con = duckdb.connect(":memory:")
con.execute('CREATE TABLE my_table AS SELECT 42 AS "column_name";')
con.execute(
    "SELECT DISTINCT ON (my_table.column_name, 'any_string') my_table.column_name FROM my_table"
).fetchone()
```
Raising: 
```
duckdb.duckdb.BinderException: Binder Error: ORDER BY non-integer literal has no effect.
* SET order_by_non_integer_literal=true to allow this behavior.
LINE 1: ...ECT DISTINCT ON (my_table.column_name, 'any_string') my_table.column_name FROM...
```
My initial analysis was wrong. 

The issue come from the `DISTINCT ON (...)` having literal. I would rather change the error message to specify `DISTINCT ON` vs `ORDER BY` or allow literal in `DISTINCT ON (...)` by default (if there's no other restrictions I'm not aware).


Hi @wadinj, thanks for opening this issue. What's the error message that you're getting? For me, this works fine in the CLI client:

```sql
CREATE TABLE my_table AS SELECT 42 AS "column_name";
SELECT * FROM my_table ORDER BY "column_name";
```
Hi @szarnyasg, here's a reproduction: 
```
con = duckdb.connect(":memory:")
con.execute('CREATE TABLE my_table AS SELECT 42 AS "column_name";')
con.execute(
    "SELECT DISTINCT ON (my_table.column_name, 'any_string') my_table.column_name FROM my_table"
).fetchone()
```
Raising: 
```
duckdb.duckdb.BinderException: Binder Error: ORDER BY non-integer literal has no effect.
* SET order_by_non_integer_literal=true to allow this behavior.
LINE 1: ...ECT DISTINCT ON (my_table.column_name, 'any_string') my_table.column_name FROM...
```
My initial analysis was wrong. 

The issue come from the `DISTINCT ON (...)` having literal. I would rather change the error message to specify `DISTINCT ON` vs `ORDER BY` or allow literal in `DISTINCT ON (...)` by default (if there's no other restrictions I'm not aware).



Fix patch:
diff --git a/src/include/duckdb/planner/expression_binder/order_binder.hpp b/src/include/duckdb/planner/expression_binder/order_binder.hpp
index df8365cc7001..b2838c19100e 100644
--- a/src/include/duckdb/planner/expression_binder/order_binder.hpp
+++ b/src/include/duckdb/planner/expression_binder/order_binder.hpp
@@ -37,6 +37,9 @@ class OrderBinder {
 
 	unique_ptr<Expression> CreateExtraReference(unique_ptr<ParsedExpression> expr);
 
+	//! Sets the query component, for error messages
+	void SetQueryComponent(string component = string());
+
 private:
 	unique_ptr<Expression> CreateProjectionReference(ParsedExpression &expr, const idx_t index);
 	unique_ptr<Expression> BindConstant(ParsedExpression &expr);
@@ -46,6 +49,7 @@ class OrderBinder {
 	vector<reference<Binder>> binders;
 	optional_ptr<vector<unique_ptr<ParsedExpression>>> extra_list;
 	SelectBindState &bind_state;
+	string query_component = "ORDER BY";
 };
 
 } // namespace duckdb
diff --git a/src/planner/binder/query_node/bind_select_node.cpp b/src/planner/binder/query_node/bind_select_node.cpp
index f1bad4c335da..d58b3868efbe 100644
--- a/src/planner/binder/query_node/bind_select_node.cpp
+++ b/src/planner/binder/query_node/bind_select_node.cpp
@@ -140,6 +140,7 @@ void Binder::PrepareModifiers(OrderBinder &order_binder, QueryNode &statement, B
 					    make_uniq<ConstantExpression>(Value::INTEGER(UnsafeNumericCast<int32_t>(1 + i))));
 				}
 			}
+			order_binder.SetQueryComponent("DISTINCT ON");
 			for (auto &distinct_on_target : distinct.distinct_on_targets) {
 				auto expr = BindOrderExpression(order_binder, std::move(distinct_on_target));
 				if (!expr) {
@@ -147,10 +148,13 @@ void Binder::PrepareModifiers(OrderBinder &order_binder, QueryNode &statement, B
 				}
 				bound_distinct->target_distincts.push_back(std::move(expr));
 			}
+			order_binder.SetQueryComponent();
+
 			bound_modifier = std::move(bound_distinct);
 			break;
 		}
 		case ResultModifierType::ORDER_MODIFIER: {
+
 			auto &order = mod->Cast<OrderModifier>();
 			auto bound_order = make_uniq<BoundOrderModifier>();
 			auto &config = DBConfig::GetConfig(context);
diff --git a/src/planner/expression_binder/order_binder.cpp b/src/planner/expression_binder/order_binder.cpp
index 4516df444475..ff864bbbd269 100644
--- a/src/planner/expression_binder/order_binder.cpp
+++ b/src/planner/expression_binder/order_binder.cpp
@@ -61,8 +61,10 @@ optional_idx OrderBinder::TryGetProjectionReference(ParsedExpression &expr) cons
 			// this is disabled by default (matching Postgres) - but we can control this with a setting
 			auto &config = ClientConfig::GetConfig(binders[0].get().context);
 			if (!config.order_by_non_integer_literal) {
-				throw BinderException(expr, "ORDER BY non-integer literal has no effect.\n* SET "
-				                            "order_by_non_integer_literal=true to allow this behavior.");
+				throw BinderException(expr,
+				                      "%s non-integer literal has no effect.\n* SET "
+				                      "order_by_non_integer_literal=true to allow this behavior.",
+				                      query_component);
 			}
 			break;
 		}
@@ -94,6 +96,14 @@ optional_idx OrderBinder::TryGetProjectionReference(ParsedExpression &expr) cons
 	return optional_idx();
 }
 
+void OrderBinder::SetQueryComponent(string component) {
+	if (component.empty()) {
+		query_component = "ORDER BY";
+	} else {
+		query_component = std::move(component);
+	}
+}
+
 unique_ptr<Expression> OrderBinder::BindConstant(ParsedExpression &expr) {
 	auto index = TryGetProjectionReference(expr);
 	if (!index.IsValid()) {
@@ -130,7 +140,7 @@ unique_ptr<Expression> OrderBinder::Bind(unique_ptr<ParsedExpression> expr) {
 		break;
 	}
 	case ExpressionClass::PARAMETER: {
-		throw ParameterNotAllowedException("Parameter not supported in ORDER BY clause");
+		throw ParameterNotAllowedException("Parameter not supported in %s clause", query_component);
 	}
 	case ExpressionClass::COLLATE: {
 		auto &collation = expr->Cast<CollateExpression>();


Test patch:
diff --git a/test/sql/aggregate/distinct/test_distinct_on.test b/test/sql/aggregate/distinct/test_distinct_on.test
index f5b78ca70443..074e8212f4e2 100644
--- a/test/sql/aggregate/distinct/test_distinct_on.test
+++ b/test/sql/aggregate/distinct/test_distinct_on.test
@@ -131,3 +131,23 @@ statement error
 SELECT DISTINCT ON (2) i FROM integers
 ----
 Binder Error: ORDER term out of range - should be between 1 and 1
+
+# DISTINCT ON constant returns an error
+statement error
+SELECT DISTINCT ON(i, 'literal') i FROM integers
+----
+DISTINCT ON non-integer literal has no effect
+
+statement ok
+SET order_by_non_integer_literal=true
+
+query I
+SELECT DISTINCT ON(i, 'literal') i FROM integers ORDER BY ALL
+----
+2
+4
+
+statement error
+PREPARE v1 AS select distinct on (?) 42;
+----
+Parameter not supported in DISTINCT ON clause