Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.3
Description
With the following tables:
create table t1 (a int); |
insert into t1 values (3), (1), (7), (3), (2), (7), (4); |
create table t2 (a int); |
insert into t2 values (4), (5), (9), (1), (8), (9); |
create table t3 (a int); |
insert into t3 values (8), (1), (8), (2), (3), (7), (2); |
the query
select * from t1 where a > 4 |
union all |
select * from t2 where a < 5 |
intersect
|
select * from t3 where a < 5 |
returns a wrong result set in 10.4:
MariaDB [test]> select * from t1 where a > 4
|
-> union all
|
-> select * from t2 where a < 5
|
-> intersect
|
-> select * from t3 where a < 5
|
-> ;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
| 1 |
|
+------+
|
2 rows in set
|
The expected result is returned when UNION ALL follows INTERSECT:
MariaDB [test]> select * from t2 where a < 5
|
-> intersect
|
-> select * from t3 where a < 5
|
-> union all
|
-> select * from t1 where a > 4;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
| 1 |
|
| 7 |
|
+------+
|
3 rows in set
|
10.3 is free of this bug:
MariaDB [test]> select version();
|
+-----------------------+
|
| version() |
|
+-----------------------+
|
| 10.3.14-MariaDB-debug |
|
+-----------------------+
|
MariaDB [test]> select * from t1 where a > 4
|
-> union all
|
-> select * from t2 where a < 5
|
-> intersect
|
-> select * from t3 where a < 5;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
| 7 |
|
| 1 |
|
+------+
|
3 rows in set
|