Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
None
Description
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-