Description:
Rows are missing from the results when using a correlated subquery with count(*).
Possibly related to:
https://bugs.mysql.com/bug.php?id=69581
https://bugs.mysql.com/bug.php?id=70703
https://bugs.mysql.com/bug.php?id=81031
How to repeat:
#> mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@optimizer_switch;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database bugtest;
Query OK, 1 row affected (0.00 sec)
mysql> use bugtest;
Database changed
mysql> DROP TABLE IF EXISTS T2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE `T1` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO `T1` (`id`) VALUES
-> (1),
-> (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE `T2` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `t1_id` int(10) unsigned DEFAULT NULL,
-> `locale` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `ndx_t2_t1_id` (`t1_id`),
-> KEY `ndx_t2_locale` (`locale`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO `T2` (`id`, `t1_id`, `locale`, `title`) VALUES
-> (1, 1, 'en', 'e1'),
-> (2, 1, 'fr', 'f1'),
-> (3, 2, 'en', 'e2'),
-> (4, 2, 'fr', 'f2');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from `T1`
-> where
-> (select count(*)
-> from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en') >= 1
-> and `T1`.`id` = 2;
Empty set (0.01 sec)
mysql> explain select * from `T1` where (select count(*) from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en') >= 1 and `T1`.`id` = 2;
+----+--------------------+-------+------------+-------------+----------------------------+----------------------------+---------+-------+------+----------+-----------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-------------+----------------------------+----------------------------+---------+-------+------+----------+-----------------------------------------------------------------------+
| 1 | PRIMARY | T1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | T2 | NULL | index_merge | ndx_t2_t1_id,ndx_t2_locale | ndx_t2_t1_id,ndx_t2_locale | 5,12 | NULL | 1 | 100.00 | Using intersect(ndx_t2_t1_id,ndx_t2_locale); Using where; Using index |
+----+--------------------+-------+------------+-------------+----------------------------+----------------------------+---------+-------+------+----------+-----------------------------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'bugtest.T1.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select '2' AS `id` from `bugtest`.`t1` where (((/* select#2 */ select count(0) from `bugtest`.`t2` where ((`bugtest`.`t2`.`t1_id` = '2') and (`bugtest`.`t2`.`locale` = 'en'))) >= 1)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> set session optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from `T1` where (select count(*) from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en') >= 1 and `T1`.`id` = 2; +----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
mysql> explain select * from `T1` where (select count(*) from `T2` where `T2`.`t1_id` = `T1`.`id` and `locale` = 'en') >= 1 and `T1`.`id` = 2; +----+--------------------+-------+------------+-------+----------------------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-------+----------------------------+--------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | T1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | T2 | NULL | ref | ndx_t2_t1_id,ndx_t2_locale | ndx_t2_t1_id | 5 | const | 2 | 50.00 | Using where |
+----+--------------------+-------+------------+-------+----------------------------+--------------+---------+-------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'bugtest.T1.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select '2' AS `id` from `bugtest`.`t1` where (((/* select#2 */ select count(0) from `bugtest`.`t2` where ((`bugtest`.`t2`.`t1_id` = '2') and (`bugtest`.`t2`.`locale` = 'en'))) >= 1)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
