|
Note: while fixing, please check both InnoDB and MyISAM/Aria. Currently result is incorrect for all of them, but it's somewhat different.
|
Test case
|
CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT);
|
INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3);
|
|
CREATE TABLE t2 (f2 INT);
|
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
|
|
--echo # t1.pk is always IN ( SELECT f2 FROM t2 ),
|
--echo so the IN condition should be true for every row, and thus COUNT(*) should always return 5
|
|
SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
|
|
Result with default semijoin + materialization
|
MariaDB [test]> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
|
+----+------+------+
|
| pk | f1 | sq |
|
+----+------+------+
|
| 1 | 4 | 0 |
|
| 2 | 3 | 0 |
|
| 3 | 3 | 0 |
|
| 4 | 6 | 0 |
|
| 5 | 3 | 0 |
|
+----+------+------+
|
5 rows in set (0.00 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
|
| 2 | DEPENDENT SUBQUERY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
|
4 rows in set, 2 warnings (0.00 sec)
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 |
|
| Note | 1003 | select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` semi join (`test`.`t2`) where (`test`.`t1`.`pk` = `test`.`t2`.`f2`))) AS `sq` from `test`.`t1` |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
Results with semijoin=off
|
MariaDB [test]> SET optimizer_switch = 'semijoin=off';
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
|
+----+------+------+
|
| pk | f1 | sq |
|
+----+------+------+
|
| 1 | 4 | 5 |
|
| 2 | 3 | 5 |
|
| 3 | 3 | 5 |
|
| 4 | 6 | 5 |
|
| 5 | 3 | 5 |
|
+----+------+------+
|
5 rows in set (0.01 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT pk, f1, ( SELECT COUNT(*) FROM t2 WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
|
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
|
| 3 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
3 rows in set, 2 warnings (0.00 sec)
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 |
|
| Note | 1003 | select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,<expr_cache><`test`.`t1`.`pk`>((select count(0) from `test`.`t2` where <expr_cache><`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`pk`,`test`.`t1`.`pk` in ( <materialize> (select `test`.`t2`.`f2` from `test`.`t2` ), <primary_index_lookup>(`test`.`t1`.`pk` in <temporary table> on distinct_key where ((`test`.`t1`.`pk` = `<subquery3>`.`f2`)))))))) AS `sq` from `test`.`t1` |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Reproducible on current 5.5, 10.0, 10.1; I didn't check 5.3.
Not reproducible on MySQL 5.5, 5.6, 5.7.
|