Repository: duckdb/duckdb (language: python)

Issue:
Query unnesting with grouping sets results in wrong output
### What happens?

The following two queries should return the same result:
```sql
select x, a, b
from
(values (1), (2)) t2(x),
lateral (select count(*), count(a) from (select 1, 2 where 1 = x) t(a, b) group by grouping sets ((), (b), (a, b))) t3(a, b);

select x, a, b
from
(
select 1, count(*), count(a) from (select 1, 2 where 1 = 1) t(a, b) group by grouping sets ((), (b), (a, b))
union all
select 2, count(*), count(a) from (select 1, 2 where 1 = 2) t(a, b) group by grouping sets ((), (b), (a, b))
) t3(x, a, b);
```

The correct result for both queries (also returned by Postgres) is:
```
x,a,b
1,1,1
1,1,1
1,1,1
2,0,0
```

However, for the first query, duckdb returns:
```
x,a,b
1,1,1
1,1,1
1,1,1
```

### To Reproduce

The queries require no database, thus DuckDB CLI can be used to reproduce.
I can also reproduce on DuckDB WASM.

### OS:

Linux

### DuckDB Version:

1.0

### DuckDB Client:

CLI

### Full Name:

Altan Birler

### Affiliation:

TUM

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

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?

- [X] Yes, I have
Query unnesting with grouping sets results in wrong output
### What happens?

The following two queries should return the same result:
```sql
select x, a, b
from
(values (1), (2)) t2(x),
lateral (select count(*), count(a) from (select 1, 2 where 1 = x) t(a, b) group by grouping sets ((), (b), (a, b))) t3(a, b);

select x, a, b
from
(
select 1, count(*), count(a) from (select 1, 2 where 1 = 1) t(a, b) group by grouping sets ((), (b), (a, b))
union all
select 2, count(*), count(a) from (select 1, 2 where 1 = 2) t(a, b) group by grouping sets ((), (b), (a, b))
) t3(x, a, b);
```

The correct result for both queries (also returned by Postgres) is:
```
x,a,b
1,1,1
1,1,1
1,1,1
2,0,0
```

However, for the first query, duckdb returns:
```
x,a,b
1,1,1
1,1,1
1,1,1
```

### To Reproduce

The queries require no database, thus DuckDB CLI can be used to reproduce.
I can also reproduce on DuckDB WASM.

### OS:

Linux

### DuckDB Version:

1.0

### DuckDB Client:

CLI

### Full Name:

Altan Birler

### Affiliation:

TUM

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

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?

- [X] Yes, I have


Discussion / hints:
@hbirler thanks, I could reproduce the issue. We'll take a look.
@hbirler thanks, I could reproduce the issue. We'll take a look.

Fix patch:
diff --git a/src/include/duckdb/planner/subquery/flatten_dependent_join.hpp b/src/include/duckdb/planner/subquery/flatten_dependent_join.hpp
index 991e084c42ab..9b7ad035389e 100644
--- a/src/include/duckdb/planner/subquery/flatten_dependent_join.hpp
+++ b/src/include/duckdb/planner/subquery/flatten_dependent_join.hpp
@@ -29,7 +29,8 @@ struct FlattenDependentJoins {
 	bool MarkSubtreeCorrelated(LogicalOperator &op);
 
 	//! Push the dependent join down a LogicalOperator
-	unique_ptr<LogicalOperator> PushDownDependentJoin(unique_ptr<LogicalOperator> plan);
+	unique_ptr<LogicalOperator> PushDownDependentJoin(unique_ptr<LogicalOperator> plan,
+	                                                  bool propagates_null_values = true);
 
 	Binder &binder;
 	ColumnBinding base_binding;
diff --git a/src/planner/binder/query_node/plan_subquery.cpp b/src/planner/binder/query_node/plan_subquery.cpp
index af70d7e033e0..860b17914aca 100644
--- a/src/planner/binder/query_node/plan_subquery.cpp
+++ b/src/planner/binder/query_node/plan_subquery.cpp
@@ -429,7 +429,7 @@ unique_ptr<LogicalOperator> Binder::PlanLateralJoin(unique_ptr<LogicalOperator>
 	// first we check which logical operators have correlated expressions in the first place
 	flatten.DetectCorrelatedExpressions(*right, true);
 	// now we push the dependent join down
-	auto dependent_join = flatten.PushDownDependentJoin(std::move(right));
+	auto dependent_join = flatten.PushDownDependentJoin(std::move(right), join_type != JoinType::INNER);
 
 	// now the dependent join is fully eliminated
 	// we only need to create the join conditions between the LHS and the RHS
diff --git a/src/planner/subquery/flatten_dependent_join.cpp b/src/planner/subquery/flatten_dependent_join.cpp
index 4c60d299d779..ac36e9eaef4c 100644
--- a/src/planner/subquery/flatten_dependent_join.cpp
+++ b/src/planner/subquery/flatten_dependent_join.cpp
@@ -87,8 +87,8 @@ bool FlattenDependentJoins::MarkSubtreeCorrelated(LogicalOperator &op) {
 	return has_correlation;
 }
 
-unique_ptr<LogicalOperator> FlattenDependentJoins::PushDownDependentJoin(unique_ptr<LogicalOperator> plan) {
-	bool propagate_null_values = true;
+unique_ptr<LogicalOperator> FlattenDependentJoins::PushDownDependentJoin(unique_ptr<LogicalOperator> plan,
+                                                                         bool propagate_null_values) {
 	auto result = PushDownDependentJoinInternal(std::move(plan), propagate_null_values, 0);
 	if (!replacement_map.empty()) {
 		// check if we have to replace any COUNT aggregates into "CASE WHEN X IS NULL THEN 0 ELSE COUNT END"
@@ -255,18 +255,32 @@ unique_ptr<LogicalOperator> FlattenDependentJoins::PushDownDependentJoinInternal
 			delim_column_offset = aggr.groups.size() - correlated_columns.size();
 			delim_data_offset = aggr.groups.size();
 		}
-		if (aggr.groups.size() == new_group_count) {
-			// we have to perform a LEFT OUTER JOIN between the result of this aggregate and the delim scan
-			// FIXME: this does not always have to be a LEFT OUTER JOIN, depending on whether aggr.expressions return
+		bool ungrouped_join = false;
+		if (aggr.grouping_sets.empty()) {
+			ungrouped_join = aggr.groups.size() == new_group_count;
+		} else {
+			for (auto &grouping_set : aggr.grouping_sets) {
+				if (grouping_set.size() == new_group_count) {
+					ungrouped_join = true;
+				}
+			}
+		}
+		if (ungrouped_join) {
+			// we have to perform an INNER or LEFT OUTER JOIN between the result of this aggregate and the delim scan
+			// this does not always have to be a LEFT OUTER JOIN, depending on whether aggr.expressions return
 			// NULL or a value
-			unique_ptr<LogicalComparisonJoin> join = make_uniq<LogicalComparisonJoin>(JoinType::INNER);
+			JoinType join_type = JoinType::INNER;
+			if (any_join || !parent_propagate_null_values) {
+				join_type = JoinType::LEFT;
+			}
 			for (auto &aggr_exp : aggr.expressions) {
 				auto &b_aggr_exp = aggr_exp->Cast<BoundAggregateExpression>();
-				if (!b_aggr_exp.PropagatesNullValues() || any_join || !parent_propagate_null_values) {
-					join = make_uniq<LogicalComparisonJoin>(JoinType::LEFT);
+				if (!b_aggr_exp.PropagatesNullValues()) {
+					join_type = JoinType::LEFT;
 					break;
 				}
 			}
+			unique_ptr<LogicalComparisonJoin> join = make_uniq<LogicalComparisonJoin>(join_type);
 			auto left_index = binder.GenerateTableIndex();
 			delim_scan = make_uniq<LogicalDelimGet>(left_index, delim_types);
 			join->children.push_back(std::move(delim_scan));


Test patch:
diff --git a/test/sql/subquery/lateral/lateral_grouping_sets.test b/test/sql/subquery/lateral/lateral_grouping_sets.test
new file mode 100644
index 000000000000..4c730aa61fe8
--- /dev/null
+++ b/test/sql/subquery/lateral/lateral_grouping_sets.test
@@ -0,0 +1,48 @@
+# name: test/sql/subquery/lateral/lateral_grouping_sets.test
+# description: Test LATERAL join with empty grouping sets
+# group: [lateral]
+
+statement ok
+PRAGMA enable_verification
+
+query III
+select x, a, b
+from
+(values (1), (2)) t2(x),
+lateral (select count(*), count(a) from (select 1, 2 where 1 = x) t(a, b) group by grouping sets ((), (b), (a, b))) t3(a, b)
+order by all;
+----
+1	1	1
+1	1	1
+1	1	1
+2	0	0
+
+query II
+select x, a
+from
+(values (1), (2)) t2(x),
+lateral (select sum(a) from (select 1, 2 where 1 = x) t(a, b) group by grouping sets ((), (b), (a, b))) t3(a)
+order by all;
+----
+1	1
+1	1
+1	1
+2	NULL
+
+query II
+select *
+  from
+  (values (1), (2)) t2(x), lateral (select sum(a) from (select 42 a) where x=1)
+order by all
+----
+1	42
+2	NULL
+
+query II
+select *
+  from
+  (values (1), (2)) t2(x) left join (select sum(a) from (select 42 a) where x=1) on (1=1)
+order by all
+----
+1	42
+2	NULL