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