Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.0, 10.5, 10.6, 10.11, 11.4, 11.8
-
git rev-parse HEAD
11a6c1b30a12c448ddfe05e1b818a6a228e90e43
Description
Description:
When executing a query with a subquery in the WHERE IN clause, MariaDB returns incorrect results for specific queries that involve union operations. The expected behavior is that the union of two queries should return the same result as the first query, but the query returns an empty set.
Steps to Reproduce:
DROP TABLE IF EXISTS `t0`;
|
CREATE TABLE `t0` (
|
`c0` double |
) ;
|
|
|
INSERT INTO `t0` VALUES (1); |
|
|
DROP TABLE IF EXISTS `t1`;
|
CREATE TABLE `t1` (
|
`c1` double DEFAULT NULL |
) ;
|
|
|
INSERT INTO `t1` VALUES (0); |
query1:
select distinct
|
ref_0.c1 as c_0
|
from
|
t1 as ref_0
|
output:
+------+
|
| c_0 |
|
+------+
|
| 0 | |
+------+
|
1 row in set (0.00 sec) |
query2:
select distinct
|
ref_0.c1 as c_0
|
from
|
t1 as ref_0
|
where (select c0 from t0 order by t0.c0 limit 1) |
in (select
|
ref_1.c0 as c_0
|
from
|
t0 as ref_1
|
where (ref_0.c1) > (98.32 ) |
union
|
select
|
0 as c_0) |
output:
Empty set (0.00 sec) |
query3:
select distinct
|
ref_0.c1 as c_0
|
from
|
t1 as ref_0
|
where (not (select c0 from t0 order by t0.c0 limit 1) |
in (select
|
ref_1.c0 as c_0
|
from
|
t0 as ref_1
|
where (ref_0.c1) > (98.32 ) |
union
|
select
|
0 as c_0)) |
or ( (select c0 from t0 order by t0.c0 limit 1) |
in (select
|
ref_1.c0 as c_0
|
from
|
t0 as ref_1
|
where (ref_0.c1) > (98.32 ) |
union
|
select
|
0 as c_0) is null) |
output:
Empty set (0.00 sec) |
Expected Behavior:
The union of the result of the second and third queries should be the same as the first query.
Actual Behavior:
However, both return an empty set, which is incorrect.