Merge pull request #11315 from Mytherin/issue11294

Fix #11294 - avoid applying Filter Pushdown optimization for UNION/EXCEPT without ALL
duckdb · Mar 23, 2024 · f339e3c · f339e3c
2 parents 54e26a0 + 609339e
commit f339e3c
Show file tree

Hide file tree

Showing 3 changed files with 35 additions and 7 deletions.
diff --git a/src/optimizer/pushdown/pushdown_set_operation.cpp b/src/optimizer/pushdown/pushdown_set_operation.cpp
@@ -69,7 +69,7 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownSetOperation(unique_ptr<Logi
 		// both empty: return empty result
 		return make_uniq<LogicalEmptyResult>(std::move(op));
 	}
-	if (left_empty) {
+	if (left_empty && setop.setop_all) {
 		// left child is empty result
 		switch (op->type) {
 		case LogicalOperatorType::LOGICAL_UNION:
@@ -88,7 +88,7 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownSetOperation(unique_ptr<Logi
 		default:
 			throw InternalException("Unsupported set operation");
 		}
-	} else if (right_empty) {
+	} else if (right_empty && setop.setop_all) {
 		// right child is empty result
 		switch (op->type) {
 		case LogicalOperatorType::LOGICAL_UNION:

diff --git a/test/optimizer/pushdown_set_op.test b/test/optimizer/pushdown_set_op.test
@@ -30,7 +30,7 @@ logical_opt	<!REGEX>:.*EXCEPT.*
 
 # if RHS is empty we can optimize away the except
 query II
-explain select 42 except select 42 where 1=0;
+explain select 42 except all select 42 where 1=0;
 ----
 logical_opt	<!REGEX>:.*EXCEPT.*
 
@@ -41,12 +41,12 @@ explain select * from (select 42 intersect select 42) tbl(i) where i=42;
 logical_opt	<REGEX>:.*INTERSECT.*
 
 query II
-explain select * from (select 42 intersect select 43) tbl(i) where i=42;
+explain select * from (select 42 intersect all select 43) tbl(i) where i=42;
 ----
 logical_opt	<!REGEX>:.*INTERSECT.*
 
 query II
-explain select * from (select 43 intersect select 42) tbl(i) where i=42;
+explain select * from (select 43 intersect all select 42) tbl(i) where i=42;
 ----
 logical_opt	<!REGEX>:.*INTERSECT.*
 
@@ -56,12 +56,12 @@ explain select * from (select 42 except select 42) tbl(i) where i=42;
 logical_opt	<REGEX>:.*EXCEPT.*
 
 query II
-explain select * from (select 42 except select 43) tbl(i) where i=42;
+explain select * from (select 42 except all select 43) tbl(i) where i=42;
 ----
 logical_opt	<!REGEX>:.*EXCEPT.*
 
 query II
-explain select * from (select 43 except select 42) tbl(i) where i=42;
+explain select * from (select 43 except all select 42) tbl(i) where i=42;
 ----
 logical_opt	<!REGEX>:.*EXCEPT.*
 

diff --git a/test/sql/setops/test_union_except_empty.test b/test/sql/setops/test_union_except_empty.test
@@ -0,0 +1,28 @@
+# name: test/sql/setops/test_union_except_empty.test
+# description: Test order of UNION statements
+# group: [setops]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+create table t (i int);
+
+statement ok
+insert into t values (1),(2),(3),(4),(4);
+
+query I
+select i from t union select 1 where false order by 1;
+----
+1
+2
+3
+4
+
+query I
+select i from t except select 1 where false order by 1;
+----
+1
+2
+3
+4