Repository: duckdb/duckdb (language: python)

Issue:
PostgreSQL incompatibility and inconsistency: Floating point version of `greatest` doesn't ignore `NULL`s
### What happens?

In PostgreSQL, `greatest(var1, ..., varn)` and `least(var1, ..., varn)` return the greatest and least non-`NULL` arguments, respectively, for all data-types.

In DuckDB, this is true with the exception of only the `greatest` function on floating point datatypes, which returns `NULL` when any argument is `NULL`. 

(FWIW, [the recent SQL standards](https://modern-sql.com/caniuse/greatest-least#null) specify that `greatest` and `least` should always propagate `NULL`s, but I think this can be safely ignored since they did this after a lot of dialects already had made their choices, it doesn't seem like any other system changed in response to this, and certainly DuckDB doesn't seem to adhere to this anywhere else except for the floating point version of `greatest`.) 

PS: This is part two - report bug - of my dissection of https://github.com/duckdb/duckdb/issues/14239, which I closed for being too chaotic.

### To Reproduce

```sql
SELECT 
greatest(0::FLOAT, NULL), -- DuckDB returns NULL; PostgreSQL returns 0 
least(0::FLOAT, NULL), -- both DuckDB and PostgreSQL return 0
greatest(0::INT, NULL), -- both DuckDB and PostgreSQL return 0
least(0::INT, NULL) -- both DuckDB and PostgreSQL return 0
```

### OS:

Ubuntu 20.04, x86_64

### DuckDB Version:

1.1.3

### DuckDB Client:

Python

### Hardware:

_No response_

### 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
PostgreSQL incompatibility and inconsistency: Floating point version of `greatest` doesn't ignore `NULL`s
### What happens?

In PostgreSQL, `greatest(var1, ..., varn)` and `least(var1, ..., varn)` return the greatest and least non-`NULL` arguments, respectively, for all data-types.

In DuckDB, this is true with the exception of only the `greatest` function on floating point datatypes, which returns `NULL` when any argument is `NULL`. 

(FWIW, [the recent SQL standards](https://modern-sql.com/caniuse/greatest-least#null) specify that `greatest` and `least` should always propagate `NULL`s, but I think this can be safely ignored since they did this after a lot of dialects already had made their choices, it doesn't seem like any other system changed in response to this, and certainly DuckDB doesn't seem to adhere to this anywhere else except for the floating point version of `greatest`.) 

PS: This is part two - report bug - of my dissection of https://github.com/duckdb/duckdb/issues/14239, which I closed for being too chaotic.

### To Reproduce

```sql
SELECT 
greatest(0::FLOAT, NULL), -- DuckDB returns NULL; PostgreSQL returns 0 
least(0::FLOAT, NULL), -- both DuckDB and PostgreSQL return 0
greatest(0::INT, NULL), -- both DuckDB and PostgreSQL return 0
least(0::INT, NULL) -- both DuckDB and PostgreSQL return 0
```

### OS:

Ubuntu 20.04, x86_64

### DuckDB Version:

1.1.3

### DuckDB Client:

Python

### Hardware:

_No response_

### 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/extension/core_functions/scalar/generic/least.cpp b/extension/core_functions/scalar/generic/least.cpp
index 8dcb64344f5e..e9df9872829c 100644
--- a/extension/core_functions/scalar/generic/least.cpp
+++ b/extension/core_functions/scalar/generic/least.cpp
@@ -5,6 +5,22 @@
 
 namespace duckdb {
 
+struct LeastOp {
+	using OP = LessThan;
+
+	static OrderByNullType NullOrdering() {
+		return OrderByNullType::NULLS_LAST;
+	}
+};
+
+struct GreaterOp {
+	using OP = GreaterThan;
+
+	static OrderByNullType NullOrdering() {
+		return OrderByNullType::NULLS_FIRST;
+	}
+};
+
 template <class OP>
 struct LeastOperator {
 	template <class T>
@@ -14,8 +30,8 @@ struct LeastOperator {
 };
 
 struct LeastGreatestSortKeyState : public FunctionLocalState {
-	explicit LeastGreatestSortKeyState(idx_t column_count)
-	    : intermediate(LogicalType::BLOB), modifiers(OrderType::ASCENDING, OrderByNullType::NULLS_LAST) {
+	explicit LeastGreatestSortKeyState(idx_t column_count, OrderByNullType null_ordering)
+	    : intermediate(LogicalType::BLOB), modifiers(OrderType::ASCENDING, null_ordering) {
 		vector<LogicalType> types;
 		// initialize sort key chunk
 		for (idx_t i = 0; i < column_count; i++) {
@@ -29,9 +45,10 @@ struct LeastGreatestSortKeyState : public FunctionLocalState {
 	OrderModifiers modifiers;
 };
 
+template <class OP>
 unique_ptr<FunctionLocalState> LeastGreatestSortKeyInit(ExpressionState &state, const BoundFunctionExpression &expr,
                                                         FunctionData *bind_data) {
-	return make_uniq<LeastGreatestSortKeyState>(expr.children.size());
+	return make_uniq<LeastGreatestSortKeyState>(expr.children.size(), OP::NullOrdering());
 }
 
 template <bool STRING>
@@ -155,7 +172,7 @@ static void LeastGreatestFunction(DataChunk &args, ExpressionState &state, Vecto
 	result.SetVectorType(result_type);
 }
 
-template <class OP>
+template <class LEAST_GREATER_OP>
 unique_ptr<FunctionData> BindLeastGreatest(ClientContext &context, ScalarFunction &bound_function,
                                            vector<unique_ptr<Expression>> &arguments) {
 	LogicalType child_type = ExpressionBinder::GetExpressionReturnType(*arguments[0]);
@@ -179,7 +196,9 @@ unique_ptr<FunctionData> BindLeastGreatest(ClientContext &context, ScalarFunctio
 	default:
 		break;
 	}
+	using OP = typename LEAST_GREATER_OP::OP;
 	switch (child_type.InternalType()) {
+#ifndef DUCKDB_SMALLER_BINARY
 	case PhysicalType::BOOL:
 	case PhysicalType::INT8:
 		bound_function.function = LeastGreatestFunction<int8_t, OP>;
@@ -202,10 +221,11 @@ unique_ptr<FunctionData> BindLeastGreatest(ClientContext &context, ScalarFunctio
 	case PhysicalType::VARCHAR:
 		bound_function.function = LeastGreatestFunction<string_t, OP, StandardLeastGreatest<true>>;
 		break;
+#endif
 	default:
 		// fallback with sort keys
 		bound_function.function = LeastGreatestFunction<string_t, OP, SortKeyLeastGreatest>;
-		bound_function.init_local_state = LeastGreatestSortKeyInit;
+		bound_function.init_local_state = LeastGreatestSortKeyInit<LEAST_GREATER_OP>;
 		break;
 	}
 	bound_function.arguments[0] = child_type;
@@ -229,11 +249,11 @@ static ScalarFunctionSet GetLeastGreatestFunctions() {
 }
 
 ScalarFunctionSet LeastFun::GetFunctions() {
-	return GetLeastGreatestFunctions<LessThan>();
+	return GetLeastGreatestFunctions<LeastOp>();
 }
 
 ScalarFunctionSet GreatestFun::GetFunctions() {
-	return GetLeastGreatestFunctions<GreaterThan>();
+	return GetLeastGreatestFunctions<GreaterOp>();
 }
 
 } // namespace duckdb
diff --git a/src/common/types/column/column_data_collection_segment.cpp b/src/common/types/column/column_data_collection_segment.cpp
index f8b0c1583dd9..1ec0f6f45900 100644
--- a/src/common/types/column/column_data_collection_segment.cpp
+++ b/src/common/types/column/column_data_collection_segment.cpp
@@ -216,7 +216,6 @@ idx_t ColumnDataCollectionSegment::ReadVector(ChunkManagementState &state, Vecto
 		auto &child_vector = ListVector::GetEntry(result);
 		auto child_count = ReadVector(state, GetChildIndex(vdata.child_index), child_vector);
 		ListVector::SetListSize(result, child_count);
-
 	} else if (internal_type == PhysicalType::ARRAY) {
 		auto &child_vector = ArrayVector::GetEntry(result);
 		auto child_count = ReadVector(state, GetChildIndex(vdata.child_index), child_vector);


Test patch:
diff --git a/test/sql/function/generic/test_least_greatest.test b/test/sql/function/generic/test_least_greatest.test
index e743f1888f81..22edc46a8e7c 100644
--- a/test/sql/function/generic/test_least_greatest.test
+++ b/test/sql/function/generic/test_least_greatest.test
@@ -41,6 +41,11 @@ SELECT LEAST(NULL, 3, 0, 2, 7, 8, 10, 11, -100, 30, 1)
 ----
 -100
 
+query I
+SELECT GREATEST(NULL, 1.0::FLOAT)
+----
+1
+
 # double
 query R
 SELECT LEAST(1.0, 10.0)