Repository: duckdb/duckdb (language: python)

Issue:
`LIST_CONCAT()` and `||` don't propagate nulls
### What happens?

Using the concatenation operator `||` on strings does NULL propagation, ie `'foo' || NULL` results in `NULL`. I think this is good behavior. I expected that for lists, the `||` operator (and the alias function, `LIST_CONCAT()`), to work the same. But instead, they treat NULL as an empty list, so that `NULL || [1,2,3]` results in `[1,2,3]`, where I would expect `NULL`.

### To Reproduce

```sql
SELECT
    a,
    LIST_CONCAT(a, [3, 4]),
    a || [3, 4],
FROM (
    SELECT [1, 2] AS "a"
    UNION ALL
    SELECT [] AS "a"
    UNION ALL
    SELECT NULL AS "a"
);
```

```
┌─────────┬───────────────────────────────────────┬──────────────────────────────┐
│    a    │ list_concat(a, main.list_value(3, 4)) │ (a || main.list_value(3, 4)) │
│ int32[] │                int32[]                │           int32[]            │
├─────────┼───────────────────────────────────────┼──────────────────────────────┤
│ [1, 2]  │ [1, 2, 3, 4]                          │ [1, 2, 3, 4]                 │
│ []      │ [3, 4]                                │ [3, 4]                       │
│ NULL    │ [3, 4]                                │ [3, 4]                       │
└─────────┴───────────────────────────────────────┴──────────────────────────────┘
```

In addition, there is separate issue related to parsing, but may be related so I wanted to bring it up. The way duckdb interprets the types from using `||` vs `LIST_CONCAT` are different. Note the int32 vs the int32[]. This datatype different leads to different values getting produced as well.

```sql
SELECT NULL || [1, 2, 3] as pipes, LIST_CONCAT(NULL, [1, 2, 3]) as list_concat;
```

```
┌───────┬─────────────┐
│ pipes │ list_concat │
│ int32 │   int32[]   │
├───────┼─────────────┤
│  NULL │ [1, 2, 3]   │
└───────┴─────────────┘
```

### OS:

shell.duckdb.org

### DuckDB Version:

1.1.2

### DuckDB Client:

https://shell.duckdb.org/

### Hardware:

_No response_

### Full Name:

Nick Crews

### Affiliation:

Ship Creek Group

### 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?

No - Other reason (please specify in the issue body)

### 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/function/scalar/string/concat.cpp b/src/function/scalar/string/concat.cpp
index be879f0176bd..a6a495a95a6b 100644
--- a/src/function/scalar/string/concat.cpp
+++ b/src/function/scalar/string/concat.cpp
@@ -155,14 +155,14 @@ struct ListConcatInputData {
 	const list_entry_t *input_entries = nullptr;
 };
 
-static void ListConcatFunction(DataChunk &args, ExpressionState &state, Vector &result) {
+static void ListConcatFunction(DataChunk &args, ExpressionState &state, Vector &result, bool is_operator) {
 	auto count = args.size();
 
 	auto result_entries = FlatVector::GetData<list_entry_t>(result);
 	vector<ListConcatInputData> input_data;
 	for (auto &input : args.data) {
-		if (input.GetType().id() == LogicalTypeId::SQLNULL) {
-			// ignore NULL values
+		if (!is_operator && input.GetType().id() == LogicalTypeId::SQLNULL) {
+			// LIST_CONCAT ignores NULL values
 			continue;
 		}
 
@@ -178,6 +178,7 @@ static void ListConcatFunction(DataChunk &args, ExpressionState &state, Vector &
 		input_data.push_back(std::move(data));
 	}
 
+	auto &result_validity = FlatVector::Validity(result);
 	idx_t offset = 0;
 	for (idx_t i = 0; i < count; i++) {
 		auto &result_entry = result_entries[i];
@@ -186,6 +187,10 @@ static void ListConcatFunction(DataChunk &args, ExpressionState &state, Vector &
 		for (auto &data : input_data) {
 			auto list_index = data.vdata.sel->get_index(i);
 			if (!data.vdata.validity.RowIsValid(list_index)) {
+				// LIST_CONCAT ignores NULL values, but || does not
+				if (is_operator) {
+					result_validity.SetInvalid(i);
+				}
 				continue;
 			}
 			const auto &list_entry = data.input_entries[list_index];
@@ -206,7 +211,7 @@ static void ConcatFunction(DataChunk &args, ExpressionState &state, Vector &resu
 	auto &func_expr = state.expr.Cast<BoundFunctionExpression>();
 	auto &info = func_expr.bind_info->Cast<ConcatFunctionData>();
 	if (info.return_type.id() == LogicalTypeId::LIST) {
-		return ListConcatFunction(args, state, result);
+		return ListConcatFunction(args, state, result, info.is_operator);
 	} else if (info.is_operator) {
 		return ConcatOperator(args, state, result);
 	}


Test patch:
diff --git a/test/sql/function/list/list_concat.test b/test/sql/function/list/list_concat.test
index ce170468d49d..a7a73702c61f 100644
--- a/test/sql/function/list/list_concat.test
+++ b/test/sql/function/list/list_concat.test
@@ -187,6 +187,22 @@ SELECT array_push_front(NULL, 1);
 ----
 [1]
 
+# concat operator
+query T
+SELECT [1, 2] || NULL
+----
+NULL
+
+query T
+SELECT [1, 2] || b FROM (VALUES (NULL::INT[])) t(b)
+----
+NULL
+
+query T
+SELECT a || b FROM (VALUES ([1,2,3], NULL::INT[])) t(a,b)
+----
+NULL
+
 # type mismatch
 statement error
 SELECT concat([42], [84], 'str')