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