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.
Thank you for the report!
I repeated as described on 10.5-11.8 with InnoDb engine, Myisam returned the expected results.
MariaDB [test]> select t.c0 from t0 t;+------+| c0 |+------+| 1 |+------+1 row in set (0,002 sec)MariaDB [test]> SELECT c1 FROM t1 WHERE NOT ((select t.c0 from t0 t) IN (SELECT c0 FROM t0 WHERE c1 > 5 UNION SELECT 0 )) ;Empty set (0,006 sec)MariaDB [test]> SELECT c1 FROM t1 WHERE NOT ((1) IN (SELECT c0 FROM t0 WHERE c1 > 5 UNION SELECT 0 )) ;+------+| c1 |+------+| 0 |+------+1 row in set (0,005 sec)MariaDB [test]> explain extended SELECT c1 FROM t1 WHERE NOT ((select t.c0 from t0 t) IN (SELECT c0 FROM t0 WHERE c1 > 5 UNION SELECT 0 )) ;+------+--------------------+------------+------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+--------------------+------------+------+---------------+------+---------+------+------+----------+----------------+| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where || 3 | DEPENDENT SUBQUERY | t0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where || 4 | UNCACHEABLE UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | || 2 | SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |+------+--------------------+------------+------+---------------+------+---------+------+------+----------+----------------+5 rows in set, 2 warnings (0,005 sec)Note (Code 1276): Field or reference 'test.t1.c1' of SELECT #3 was resolved in SELECT #1Note (Code 1003): /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><(/* select#2 */ select `test`.`t`.`c0` from `test`.`t0` `t`),`test`.`t1`.`c1`>(<in_optimizer>((/* select#2 */ select `test`.`t`.`c0` from `test`.`t0` `t`),<exists>(/* select#3 */ select `test`.`t0`.`c0` from `test`.`t0` where `test`.`t1`.`c1` > 5 and trigcond(<cache>((/* select#2 */ select `test`.`t`.`c0` from `test`.`t0` `t`)) = `test`.`t0`.`c0` or `test`.`t0`.`c0` is null) having trigcond(`test`.`t0`.`c0` is null) union /* select#4 */ select 0 having trigcond(<cache>((/* select#2 */ select `test`.`t`.`c0` from `test`.`t0` `t`)) = <ref_null_helper>(0)))))MariaDB [test]> alter table t0 engine=myisam;Query OK, 1 row affected (0,039 sec)Records: 1 Duplicates: 0 Warnings: 0MariaDB [test]> SELECT c1 FROM t1 WHERE NOT ((select t.c0 from t0 t) IN (SELECT c0 FROM t0 WHERE c1 > 5 UNION SELECT 0 )) ;+------+| c1 |+------+| 0 |+------+1 row in set (0,006 sec)MariaDB [test]> explain extended SELECT c1 FROM t1 WHERE NOT ((select t.c0 from t0 t) IN (SELECT c0 FROM t0 WHERE c1 > 5 UNION SELECT 0 )) ;+------+--------------------+------------+--------+---------------+------+---------+------+------+----------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+--------------------+------------+--------+---------------+------+---------+------+------+----------+----------------+| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where || 3 | DEPENDENT SUBQUERY | t0 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | || 4 | UNCACHEABLE UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used || NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | || 2 | SUBQUERY | t | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |+------+--------------------+------------+--------+---------------+------+---------+------+------+----------+----------------+5 rows in set, 2 warnings (0,005 sec)Note (Code 1276): Field or reference 'test.t1.c1' of SELECT #3 was resolved in SELECT #1Note (Code 1003): /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><(/* select#2 */ select 1 from dual),`test`.`t1`.`c1`>(<in_optimizer>((/* select#2 */ select 1 from dual),<exists>(/* select#3 */ select 1 from dual where `test`.`t1`.`c1` > 5 and trigcond(1 = 1 or 1 is null) having trigcond(1 is null) union /* select#4 */ select 0 having trigcond(1 = <ref_null_helper>(0)))))