Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.1, 10.2, 10.3, 10.4
-
None
Description
CREATE TABLE t1 (a INT, b INT); |
INSERT INTO t1 VALUES (2,9),(0,0); |
|
CREATE TABLE t2 (c INT PRIMARY KEY); |
CREATE VIEW v2 AS SELECT * FROM t2; |
|
SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 ); |
SELECT STRAIGHT_JOIN * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 ); |
10.4 c400a73d |
MariaDB [test]> SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 ); |
Empty set (0.001 sec) |
|
MariaDB [test]> SELECT STRAIGHT_JOIN * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 ); |
+------+------+ |
| a | b |
|
+------+------+ |
| 0 | 0 |
|
+------+------+ |
1 row in set (0.001 sec) |
The empty result appears to be correct, because the subquery should return NULL, 0, not 0,0.
Reproducible on 5.5-10.5 with at least MyISAM and InnoDB.
Not reproducible on MySQL 5.6, 5.7, I didn't try other versions.
Execution plans:
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 ); |
+------+--------------+-------------+--------+---------------+--------------+---------+---------------------+------+----------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------+-------------+--------+---------------+--------------+---------+---------------------+------+----------+-------------+ |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 12 | test.t1.a,test.t1.b | 1 | 100.00 | Using where | |
| 2 | MATERIALIZED | t2 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index | |
+------+--------------+-------------+--------+---------------+--------------+---------+---------------------+------+----------+-------------+ |
3 rows in set, 1 warning (0.003 sec) |
|
MariaDB [test]> show warnings;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select `test`.`t2`.`c`,count(0) from `test`.`t2`) join `test`.`t1` where `<subquery2>`.`c` = `test`.`t1`.`a` and `test`.`t1`.`b` = `<subquery2>`.`COUNT(*)` | |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * FROM t1 WHERE ( a, b ) IN ( SELECT c, COUNT(*) FROM v2 ); |
+------+--------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | |
| 2 | MATERIALIZED | t2 | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using index | |
+------+--------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+ |
2 rows in set, 1 warning (0.001 sec) |
|
MariaDB [test]> show warnings;
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Note | 1003 | /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`c`,count(0) from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`c` and `test`.`t1`.`b` = `<subquery2>`.`COUNT(*)`)))) | |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.000 sec) |
Given the combination of oddities in the scenario, I'm setting it to Minor.