Repository: duckdb/duckdb (language: python)

Issue:
Wrong number of results when calling a MACRO that calls a scalar function
### What happens?

```sql
create or replace function rnv(a,b) as (select a + b * pi());
select rnv(0, 1) from unnest( range(0,2) );
```
```
┌───────────────────┐
│     rnv(0, 1)     │
│      double       │
├───────────────────┤
│ 3.141592653589793 │
│ 3.141592653589793 │
│ 3.141592653589793 │
│ 3.141592653589793 │
│ 3.141592653589793 │
│ 3.141592653589793 │
│ 3.141592653589793 │
│ 3.141592653589793 │
└───────────────────┘
```

Note: this is similar to one of the problems reported at #13639, but here random() is not called at all.

### To Reproduce

```
create or replace function rnv(a,b) as (select a + b * pi());

select rnv(0, 1) from unnest( range(0,2) );
```

### OS:

macOS

### DuckDB Version:

v1.0.1-dev5058 (August 30)

### DuckDB Client:

CLI

### Full Name:

Peter Koppstein

### Affiliation:

Princeton University

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

### 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
Using random() in an uncorrelated subquery replicates the result
### What happens?

For the sake of clarity, first consider the case where a MACRO only invokes random() once. (*)
```
D create or replace function r() as random();

D select r() from unnest(range(0,4));
┌─────────────────────┐
│         r()         │
│       double        │
├─────────────────────┤
│ 0.38555585057474673 │
│  0.7014810184482485 │
│  0.6464361036196351 │
│ 0.05984393716789782 │
└─────────────────────┘
D
```
All as expected.

But now consider:
```
D create or replace function rnv(mean, sd) as 
  (select (( sqrt(-2 * ln(random())) * cos(2 * pi() * random())) * sd) + mean);

D select rnv(0,1) from unnest(range(0,4));
┌────────────────────┐
│     rnv(0, 1)      │
│       double       │
├────────────────────┤
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
│ 1.0384863850924373 │
├────────────────────┤
│      16 rows       │
└────────────────────┘
D
```

This is bizarre for two separate reasons:

(1) Why are all the values the same?

(2) Shouldn't there be just 4 rows as in the case of r()?

Note that calling rnv() in isolation produces results in accordance with expectations:
```
D select rnv(0,1);
┌──────────────────────┐
│      rnv(0, 1)       │
│        double        │
├──────────────────────┤
│ 0.006052431330421223 │
└──────────────────────┘

D select rnv(0,1);
┌────────────────────┐
│     rnv(0, 1)      │
│       double       │
├────────────────────┤
│ 0.7537127145896633 │
└────────────────────┘
D
```

p.s. Shouldn't it be possible to set the "has_side_effects" field in the table shown duckdb_functions() 
For reference:
```
D select has_side_effects is null from duckdb_functions() where function_name = 'r';
┌────────────────────────────┐
│ (has_side_effects IS NULL) │
│          boolean           │
├────────────────────────────┤
│ true                       │
└────────────────────────────┘
```
(*) All results shown are based on v1.0.1-dev4911 b41679dfd2

### To Reproduce

```
create or replace function rnv(mean, sd) as 
  (select (( sqrt(-2 * ln(random())) * cos(2 * pi() * random())) * sd) + mean);

select rnv(0,1) from unnest(range(0,4));
```

### OS:

MacOS

### DuckDB Version:

v1.0.1-dev4911 b41679dfd2

### DuckDB Client:

CLI

### Full Name:

Peter Koppstein

### Affiliation:

Princeton University

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

I have tested with a source build

### 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:

@pkoppstein thanks for the detailed issue. This seems to be a bug.

> This is bizarre for two separate reasons:
>
> (1) Why are all the values the same?

This is likely because the `has_side_effects` is not set to `true`. The [Python function API](https://duckdb.org/docs/api/python/function) has a way of specifying this but macros don't.

> (2) Shouldn't there be just 4 rows as in the case of r()?

This is a regression from v1.0.0:

```
v1.0.0 1f98600c2c
D create or replace function rnv(mean, sd) as
    (select (( sqrt(-2 * ln(random())) * cos(2 * pi() * random())) * sd) + mean);
D select rnv(0,1) from unnest(range(0,4));
┌─────────────────────┐
│      rnv(0, 1)      │
│       double        │
├─────────────────────┤
│ -1.5192343078784654 │
│ -1.5192343078784654 │
│ -1.5192343078784654 │
│ -1.5192343078784654 │
└─────────────────────┘
```


Fix patch:
diff --git a/src/execution/operator/scan/physical_table_scan.cpp b/src/execution/operator/scan/physical_table_scan.cpp
index 58aa410e6f5c..27ba3982a698 100644
--- a/src/execution/operator/scan/physical_table_scan.cpp
+++ b/src/execution/operator/scan/physical_table_scan.cpp
@@ -213,4 +213,13 @@ bool PhysicalTableScan::Equals(const PhysicalOperator &other_p) const {
 	return true;
 }
 
+bool PhysicalTableScan::ParallelSource() const {
+	if (!function.function) {
+		// table in-out functions cannot be executed in parallel as part of a PhysicalTableScan
+		// since they have only a single input row
+		return false;
+	}
+	return true;
+}
+
 } // namespace duckdb
diff --git a/src/include/duckdb/execution/operator/scan/physical_table_scan.hpp b/src/include/duckdb/execution/operator/scan/physical_table_scan.hpp
index 13d6a233ecdb..621282e2dc8b 100644
--- a/src/include/duckdb/execution/operator/scan/physical_table_scan.hpp
+++ b/src/include/duckdb/execution/operator/scan/physical_table_scan.hpp
@@ -66,9 +66,7 @@ class PhysicalTableScan : public PhysicalOperator {
 	bool IsSource() const override {
 		return true;
 	}
-	bool ParallelSource() const override {
-		return true;
-	}
+	bool ParallelSource() const override;
 
 	bool SupportsBatchIndex() const override {
 		return function.get_batch_index != nullptr;


Test patch:
diff --git a/test/sql/types/list/unnest_aggregate.test b/test/sql/types/list/unnest_aggregate.test
new file mode 100644
index 000000000000..082c44b368a0
--- /dev/null
+++ b/test/sql/types/list/unnest_aggregate.test
@@ -0,0 +1,20 @@
+# name: test/sql/types/list/unnest_aggregate.test
+# description: Test unnest semantics with NULL and empty lists
+# group: [list]
+
+statement ok
+PRAGMA enable_verification
+
+query I
+SELECT SUM(a) FROM UNNEST(RANGE(1, 11)) t(a);
+----
+55
+
+statement ok
+create or replace function rnv(a,b) as (select a + b * pi());
+
+query I
+select rnv(0, 1) from unnest( range(0,2) );
+----
+3.141592653589793
+3.141592653589793