Repository: duckdb/duckdb (language: python)

Issue:
`split_part` does not return `null` when first input is `null`
### What happens?

[`split_part`](https://duckdb.org/docs/sql/functions/char.html#split_partstring-separator-index) takes three arguments. The first is the string to split. The documentation states that index outside of range behavior for this function is designed to mimic Postgres behavior (and it does). However, Postgres returns `null` when the first input is `null` but DuckDB returns the empty string. This can cause hard-to-diagnose downstream effects, especially when the result of a `split_part` is used as a join key (empty strings match with other empty strings, but `null`s do not match with other `null`s).

### To Reproduce

Simple example:
```sql
.nullvalue NULL
create table foo (a varchar);
insert into foo values ('hello$there'), ('apples'), (''), (null);
select * from foo;
```
```
┌─────────────┐
│      a      │
│   varchar   │
├─────────────┤
│ hello$there │
│ apples      │
│             │
│ NULL        │
└─────────────┘
```
```sql
select count(*) from foo where a is not null;
```
```
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            3 │
└──────────────┘
```
```sql
select split_part(a, '$', -1) as s from foo;
```
```
┌─────────┐
│    s    │
│ varchar │
├─────────┤
│ there   │
│ apples  │
│         │
│         │
└─────────┘
```
```sql
select count(*) from foo where split_part(a, '$', -1) is not null;
```
```
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            4 │
└──────────────┘
```

Note that since the result of the last query was 4, the function value on `null` input was the empty string, not `null`. This differs from Postgres behavior as shown below:

```sql
postgres: create table foo (a varchar);
CREATE TABLE
postgres: insert into foo values ('hello$there'), ('apples'), (''), (null);
INSERT 0 4
postgres: select count(*) from foo where a is not null;
 count
-------
     3
(1 row)

postgres: select split_part(a, '$', -1) as s from foo;
   s
--------
 there
 apples


(4 rows)

postgres: select count(*) from foo where split_part(a, '$', -1) is not null;
 count
-------
     3
(1 row)
```

### OS:

Manjaro Linus x86_64

### DuckDB Version:

v1.1.1 af39bd0dcf

### DuckDB Client:

CLI

### Hardware:

_No response_

### Full Name:

Doeke Buursma

### Affiliation:

UDisc, LLC

### 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
`split_part` does not return `null` when first input is `null`
### What happens?

[`split_part`](https://duckdb.org/docs/sql/functions/char.html#split_partstring-separator-index) takes three arguments. The first is the string to split. The documentation states that index outside of range behavior for this function is designed to mimic Postgres behavior (and it does). However, Postgres returns `null` when the first input is `null` but DuckDB returns the empty string. This can cause hard-to-diagnose downstream effects, especially when the result of a `split_part` is used as a join key (empty strings match with other empty strings, but `null`s do not match with other `null`s).

### To Reproduce

Simple example:
```sql
.nullvalue NULL
create table foo (a varchar);
insert into foo values ('hello$there'), ('apples'), (''), (null);
select * from foo;
```
```
┌─────────────┐
│      a      │
│   varchar   │
├─────────────┤
│ hello$there │
│ apples      │
│             │
│ NULL        │
└─────────────┘
```
```sql
select count(*) from foo where a is not null;
```
```
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            3 │
└──────────────┘
```
```sql
select split_part(a, '$', -1) as s from foo;
```
```
┌─────────┐
│    s    │
│ varchar │
├─────────┤
│ there   │
│ apples  │
│         │
│         │
└─────────┘
```
```sql
select count(*) from foo where split_part(a, '$', -1) is not null;
```
```
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│            4 │
└──────────────┘
```

Note that since the result of the last query was 4, the function value on `null` input was the empty string, not `null`. This differs from Postgres behavior as shown below:

```sql
postgres: create table foo (a varchar);
CREATE TABLE
postgres: insert into foo values ('hello$there'), ('apples'), (''), (null);
INSERT 0 4
postgres: select count(*) from foo where a is not null;
 count
-------
     3
(1 row)

postgres: select split_part(a, '$', -1) as s from foo;
   s
--------
 there
 apples


(4 rows)

postgres: select count(*) from foo where split_part(a, '$', -1) is not null;
 count
-------
     3
(1 row)
```

### OS:

Manjaro Linus x86_64

### DuckDB Version:

v1.1.1 af39bd0dcf

### DuckDB Client:

CLI

### Hardware:

_No response_

### Full Name:

Doeke Buursma

### Affiliation:

UDisc, LLC

### 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:
Hi @Doekeb, thanks for opening this issue. Indeed we strive to achieve Postgres-compatibility, so this should be fixed. We'll take a look.
Hi @Doekeb, thanks for opening this issue. Indeed we strive to achieve Postgres-compatibility, so this should be fixed. We'll take a look.

Fix patch:
diff --git a/src/catalog/default/default_functions.cpp b/src/catalog/default/default_functions.cpp
index 2479983635fb..59e27632e7b4 100644
--- a/src/catalog/default/default_functions.cpp
+++ b/src/catalog/default/default_functions.cpp
@@ -63,7 +63,7 @@ static const DefaultMacro internal_macros[] = {
 	{"pg_catalog", "pg_get_expr", {"pg_node_tree", "relation_oid", nullptr}, {{nullptr, nullptr}}, "pg_node_tree"},
 	{"pg_catalog", "format_pg_type", {"logical_type", "type_name", nullptr}, {{nullptr, nullptr}}, "case upper(logical_type) when 'FLOAT' then 'float4' when 'DOUBLE' then 'float8' when 'DECIMAL' then 'numeric' when 'ENUM' then lower(type_name) when 'VARCHAR' then 'varchar' when 'BLOB' then 'bytea' when 'TIMESTAMP' then 'timestamp' when 'TIME' then 'time' when 'TIMESTAMP WITH TIME ZONE' then 'timestamptz' when 'TIME WITH TIME ZONE' then 'timetz' when 'SMALLINT' then 'int2' when 'INTEGER' then 'int4' when 'BIGINT' then 'int8' when 'BOOLEAN' then 'bool' else lower(logical_type) end"},
 	{"pg_catalog", "format_type", {"type_oid", "typemod", nullptr}, {{nullptr, nullptr}}, "(select format_pg_type(logical_type, type_name) from duckdb_types() t where t.type_oid=type_oid) || case when typemod>0 then concat('(', typemod//1000, ',', typemod%1000, ')') else '' end"},
-	{"pg_catalog", "map_to_pg_oid", {"type_name", nullptr}, {{nullptr, nullptr}}, "case type_name when 'bool' then 16 when 'int16' then 21 when 'int' then 23 when 'bigint' then 20 when 'date' then 1082 when 'time' then 1083 when 'datetime' then 1114 when 'dec' then 1700 when 'float' then 700 when 'double' then 701 when 'bpchar' then 1043 when 'binary' then 17 when 'interval' then 1186 when 'timestamptz' then 1184 when 'timetz' then 1266 when 'bit' then 1560 when 'guid' then 2950 else null end"}, // map duckdb_oid to pg_oid. If no corresponding type, return null 
+	{"pg_catalog", "map_to_pg_oid", {"type_name", nullptr}, {{nullptr, nullptr}}, "case type_name when 'bool' then 16 when 'int16' then 21 when 'int' then 23 when 'bigint' then 20 when 'date' then 1082 when 'time' then 1083 when 'datetime' then 1114 when 'dec' then 1700 when 'float' then 700 when 'double' then 701 when 'bpchar' then 1043 when 'binary' then 17 when 'interval' then 1186 when 'timestamptz' then 1184 when 'timetz' then 1266 when 'bit' then 1560 when 'guid' then 2950 else null end"}, // map duckdb_oid to pg_oid. If no corresponding type, return null
 
 	{"pg_catalog", "pg_has_role", {"user", "role", "privilege", nullptr}, {{nullptr, nullptr}}, "true"},  //boolean  //does user have privilege for role
 	{"pg_catalog", "pg_has_role", {"role", "privilege", nullptr}, {{nullptr, nullptr}}, "true"},  //boolean  //does current user have privilege for role
@@ -102,12 +102,12 @@ static const DefaultMacro internal_macros[] = {
 	{DEFAULT_SCHEMA, "array_to_string", {"arr", "sep", nullptr}, {{nullptr, nullptr}}, "list_aggr(arr::varchar[], 'string_agg', sep)"},
 	// Test default parameters
 	{DEFAULT_SCHEMA, "array_to_string_comma_default", {"arr", nullptr}, {{"sep", "','"}, {nullptr, nullptr}}, "list_aggr(arr::varchar[], 'string_agg', sep)"},
-	
+
 	{DEFAULT_SCHEMA, "generate_subscripts", {"arr", "dim", nullptr}, {{nullptr, nullptr}}, "unnest(generate_series(1, array_length(arr, dim)))"},
 	{DEFAULT_SCHEMA, "fdiv", {"x", "y", nullptr}, {{nullptr, nullptr}}, "floor(x/y)"},
 	{DEFAULT_SCHEMA, "fmod", {"x", "y", nullptr}, {{nullptr, nullptr}}, "(x-y*floor(x/y))"},
 	{DEFAULT_SCHEMA, "count_if", {"l", nullptr}, {{nullptr, nullptr}}, "sum(if(l, 1, 0))"},
-	{DEFAULT_SCHEMA, "split_part", {"string", "delimiter", "position", nullptr}, {{nullptr, nullptr}}, "coalesce(string_split(string, delimiter)[position],'')"},
+	{DEFAULT_SCHEMA, "split_part", {"string", "delimiter", "position", nullptr}, {{nullptr, nullptr}}, "if(string IS NOT NULL AND delimiter IS NOT NULL AND position IS NOT NULL, coalesce(string_split(string, delimiter)[position],''), NULL)"},
 	{DEFAULT_SCHEMA, "geomean", {"x", nullptr}, {{nullptr, nullptr}}, "exp(avg(ln(x)))"},
 	{DEFAULT_SCHEMA, "geometric_mean", {"x", nullptr}, {{nullptr, nullptr}}, "geomean(x)"},
 


Test patch:
diff --git a/test/sql/function/string/test_split_part.test b/test/sql/function/string/test_split_part.test
index 2e3b8a595d73..e2acb1a6edec 100644
--- a/test/sql/function/string/test_split_part.test
+++ b/test/sql/function/string/test_split_part.test
@@ -73,17 +73,22 @@ select split_part('',',',1)
 query T
 select split_part(NULL,NULL,1)
 ----
-(empty)
+NULL
 
 query T
 select split_part('a,b,c',NULL,1)
 ----
-a,b,c
+NULL
 
 query T
 select split_part(NULL,',',1)
 ----
-(empty)
+NULL
+
+query T
+select split_part('a,b,c', ',',  NULL)
+----
+NULL
 
 # test incorrect usage
 statement error