Merge pull request #9658 from Mytherin/issue684

In set operations ORDER BY columns refer to the first set operation in SQL - so the reference is not ambiguous
duckdb · Nov 14, 2023 · c92602b · c92602b
2 parents 8b0d266 + 520443b
commit c92602b
Show file tree

Hide file tree

Showing 6 changed files with 59 additions and 24 deletions.
diff --git a/src/planner/binder/query_node/bind_setop_node.cpp b/src/planner/binder/query_node/bind_setop_node.cpp
@@ -51,17 +51,7 @@ static void GatherAliases(BoundQueryNode &node, case_insensitive_map_t<idx_t> &a
 
 			idx_t index = reorder_idx[i];
 
-			if (entry != aliases.end()) {
-				// the alias already exists
-				// check if there is a conflict
-
-				if (entry->second != index) {
-					// there is a conflict
-					// we place "-1" in the aliases map at this location
-					// "-1" signifies that there is an ambiguous reference
-					aliases[name] = DConstants::INVALID_INDEX;
-				}
-			} else {
+			if (entry == aliases.end()) {
 				// the alias is not in there yet, just assign it
 				aliases[name] = index;
 			}

diff --git a/test/sql/order/test_order_by_exceptions.test b/test/sql/order/test_order_by_exceptions.test
@@ -24,11 +24,13 @@ SELECT a FROM test ORDER BY 'hello', a
 12
 13
 
-# ambiguous reference in union alias
-statement error
+query II
 SELECT a AS k, b FROM test UNION SELECT a, b AS k FROM test ORDER BY k
+----
+11	22
+12	21
+13	22
 
-# but works if not ambiguous
 query II
 SELECT a AS k, b FROM test UNION SELECT a AS k, b FROM test ORDER BY k
 ----

diff --git a/test/sql/setops/ambiguous_order_by.test b/test/sql/setops/ambiguous_order_by.test
@@ -0,0 +1,15 @@
+# name: test/sql/setops/ambiguous_order_by.test
+# description: Ambiguous ORDER BY in set ops
+# group: [setops]
+
+query II
+select * from (values(42, 84)) s1(c1, c2) union all select * from (values(84, 42)) s2(c2, c3) order by c1;
+----
+42	84
+84	42
+
+query II
+select * from (values(42, 84)) s1(c1, c2) union all select * from (values(84, 42)) s2(c2, c3) order by c3;
+----
+84	42
+42	84
diff --git a/test/sql/setops/test_union_all_by_name.test b/test/sql/setops/test_union_all_by_name.test
@@ -69,8 +69,17 @@ statement error
 SELECT x AS a FROM t1 UNION ALL BY NAME SELECT x AS b FROM t1 ORDER BY t1.x;
 
 # Error: Binder Error: Ambiguous name in ORDER BY!
-statement error
-(SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION ALL BY NAME (SELECT z FROM t2 UNION ALL SELECT y FROM t2) ORDER BY y;
+query II
+(SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION ALL BY NAME (SELECT z FROM t2 UNION ALL SELECT y FROM t2) ORDER BY y, z;
+----
+NULL	2
+NULL	2
+NULL	4
+NULL	4
+1	NULL
+1	NULL
+3	NULL
+3	NULL
 
 query II
 (SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION ALL BY NAME (SELECT z FROM t2 UNION ALL SELECT y FROM t2) ORDER BY t1.y;

diff --git a/test/sql/setops/test_union_binding.test b/test/sql/setops/test_union_binding.test
@@ -93,8 +93,17 @@ NULL
 4
 
 # if names are ambiguous, throw an error
-statement error
-SELECT 1, a FROM test UNION SELECT b AS a, 1 FROM test2 ORDER BY a;
+query II
+SELECT 1, a FROM test UNION SELECT b AS a, 1 FROM test2 ORDER BY a, 1;
+----
+1	NULL
+NULL	1
+1	1
+2	1
+3	1
+4	1
+1	2
+1	3
 
 # if expressions are ambiguous as well, throw an error
 statement error
@@ -133,9 +142,16 @@ NULL	NULL
 2	8
 1	9
 
-# ambiguous naming reference should fail
-statement error
+query II
 SELECT a, 10 - a AS b FROM test UNION SELECT b, b + 1 FROM test2 ORDER BY b;
+----
+NULL	NULL
+2	3
+3	4
+4	5
+3	7
+2	8
+1	9
 
 # and by constant references
 query I

diff --git a/test/sql/setops/test_union_by_name.test b/test/sql/setops/test_union_by_name.test
@@ -55,10 +55,13 @@ NULL	5
 statement error
 SELECT x AS a FROM t1 UNION BY NAME SELECT x AS b FROM t1 ORDER BY t1.x;
 
-# Error: Binder Error: Ambiguous name in ORDER BY!
-statement error
-(SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION BY NAME (SELECT z FROM t2 UNION ALL SELECT y FROM t2) ORDER BY y;
-
+query II
+(SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION BY NAME (SELECT z FROM t2 UNION ALL SELECT y FROM t2) ORDER BY y, z;
+----
+NULL	2
+NULL	4
+1	NULL
+3	NULL
 
 ########## test limit