Bug #82313 Wrong result for EXPLAIN SELECT COUNT(*) FROM TABLE;

Submitted: 21 Jul 2016 23:44 Modified: 7 Mar 2017 18:09
Reporter: Justin Swanhart Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.7.13,5.7.14,8 DMR OS:Any
Assigned to: CPU Architecture:Any

[21 Jul 2016 23:44] Justin Swanhart
Description:
The source code from the git repo currently returns wrong results (table optimized away) for SELECT COUNT(*) FROM TABLE;

How to repeat:
MySQL [test]> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `c2` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `NULL` binary(0) DEFAULT NULL,
  PRIMARY KEY (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=2097153 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (c2)
PARTITIONS 7 */
1 row in set (0.00 sec)

MySQL [test]> explain partitions select count(*) from t3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 2 warnings (0.00 sec)

MySQL [test]> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release. |
| Note    | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t3`       |
+---------+------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

MySQL [test]> select count(*) from t3;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+
1 row in set (0.24 sec)

Suggested fix:
expected same plan as following query:
MySQL [test]> explain partitions select count(*) from t3 where c1 is not null ;
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions           | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t3    | p0,p1,p2,p3,p4,p5,p6 | ALL  | NULL          | NULL | NULL    | NULL | 2088752 |    90.00 | Using where |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
[21 Jul 2016 23:50] Justin Swanhart
I'm building 5.7.13 from source tarball to see if it is similarly affected.
[22 Jul 2016 0:03] Justin Swanhart
Confirmed in 5.7.13 source tarball:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.13 Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test]> explain partitions select count(*) from t3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 2 warnings (0.00 sec)
[22 Jul 2016 7:31] MySQL Verification Team
Hello Justin,

Thank you for the report.

Thanks,
Umesh
[22 Jul 2016 7:31] MySQL Verification Team
##

-- 5.7.13

mysql>  CREATE TABLE `t3` (
    ->   `c2` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `c1` int(11) DEFAULT NULL,
    ->   `NULL` binary(0) DEFAULT NULL,
/*!50100 PARTITION BY KEY (c2)
PARTITIONS 7 */;

    ->   PRIMARY KEY (`c2`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2097153 DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY KEY (c2)
    -> PARTITIONS 7 */;

Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  insert into `t3` values
    ->   (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'');
 insert into `t3`(`c2`,`c1`,`NULL`)
   select  @id:=@id+1,@id:=@id+1,''  from
    `t3` k1, `t3` k2, `t3` k3, `t3` k4,`t3` k5,`t3` k6, `t3` k7, `t3` k8, `t3` k9,
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql>  insert into `t3`(`c2`,`c1`,`NULL`)
    ->    select  @id:=@id+1,@id:=@id+1,''  from
    ->     `t3` k1, `t3` k2, `t3` k3, `t3` k4,`t3` k5,`t3` k6, `t3` k7, `t3` k8, `t3` k9,
    -> `t3` k0,`t3` ka, `t3` kb, `t3` kc, `t3` kd limit  1000000;
Query OK, 1000000 rows affected (9.89 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select count(*) from t3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release. |
| Note    | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t3`       |
+---------+------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  explain partitions select count(*) from t3 where c1 is not null ;
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions           | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t3    | p0,p1,p2,p3,p4,p5,p6 | ALL  | NULL          | NULL | NULL    | NULL | 1000265 |    90.00 | Using where |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select count(*) from t3;
+----------+
| count(*) |
+----------+
|  1000004 |
+----------+
1 row in set (0.21 sec)

mysql> \q

-- 5.6.31

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.31: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.31-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

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> use test
Database changed
mysql>  CREATE TABLE `t3` (
    ->   `c2` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=2097153 DEFAULT CHARSET=latin1
    ->   `NULL` binary(0) DEFAULT NULL,

set @id:=0;

    ->   PRIMARY KEY (`c2`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2097153 DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY KEY (c2)
    -> PARTITIONS 7 */;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  insert into `t3` values
    ->   (@id:=@id+1,@id:=@id+1,'')
, (@id:=@id+1,@id:=@id+1,'')
, (@id:=@id+1,@id:=@id+1,'');
    -> , (@id:=@id+1,@id:=@id+1,'')
 insert into `t3`(`c2`,`c1`,`NULL`)
   select  @id:=@id+1,@id:=@id+1,''  from
    `t3` k1    -> , `t3`, (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql>  insert into `t3`(`c2`,`c1`,`NULL`)
    ->    select  @id:=@id+1,@id:=@id+1,''  from
    ->     `t3` k1, `t3` k2, `t3` k3, `t3` k4,`t3` k5,`t3` k6, `t3` k7, `t3` k8, `t3` k9,
    -> `t3` k0,`t3` ka, `t3` kb, `t3` kc, `t3` kd limit  1000000;
Query OK, 1000000 rows affected (5.89 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select count(*) from t3 ;
+----+-------------+-------+----------------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | partitions           | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+----------------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | t3    | p0,p1,p2,p3,p4,p5,p6 | index | NULL          | PRIMARY | 8       | NULL | 1000004 | Using index |
+----+-------------+-------+----------------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select count(*) from t3 where c1 is not null ;
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | partitions           | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t3    | p0,p1,p2,p3,p4,p5,p6 | ALL  | NULL          | NULL | NULL    | NULL | 1000314 | Using where |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from t3;
+----------+
| count(*) |
+----------+
|  1000004 |
+----------+
1 row in set (0.21 sec)
[22 Jul 2016 7:34] MySQL Verification Team
-- 5.7.9

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>  CREATE TABLE `t3` (
    ->   `c2` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `c1` int(11) DEFAULT NULL,
    ->   `NULL` binary(0) DEFAULT NULL,
PARTITIONS 7 */;

    ->   PRIMARY KEY (`c2`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=2097153 DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY KEY (c2)
    -> PARTITIONS 7 */;
set @id:=0;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  insert into `t3` values
, (@id:=@id+1,@id:=@id+1,'')
    ->   (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'')
    -> , (@id:=@id+1,@id:=@id+1,'');
 insert into `t3`(`c2`,`c1`,`NULL`)
   select  @id:=@id+1,@id:=@id+1,''  from
    `t3` k1, `t3` k2, `t3` k3, `t3` k4,`t3` k5,`t3` k6, `t3` k7, `t3` k8, `t3` k9,
`t3` k0,`t3` ka, `t3` kb, `t3` kc, `t3` kd limit  1000000;  Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql>  insert into `t3`(`c2`,`c1`,`NULL`)
    ->    select  @id:=@id+1,@id:=@id+1,''  from
    ->     `t3` k1, `t3` k2, `t3` k3, `t3` k4,`t3` k5,`t3` k6, `t3` k7, `t3` k8, `t3` k9,
    -> `t3` k0,`t3` ka, `t3` kb, `t3` kc, `t3` kd limit  1000000;
Query OK, 1000000 rows affected (10.04 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select count(*) from t3 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release. |
| Note    | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`t3`       |
+---------+------+---------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> explain partitions select count(*) from t3 where c1 is not null ;
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions           | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t3    | p0,p1,p2,p3,p4,p5,p6 | ALL  | NULL          | NULL | NULL    | NULL | 1000265 |    90.00 | Using where |
+----+-------------+-------+----------------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql>
[30 Jul 2016 3:01] Justin Swanhart
Appears in 5.7.14 as well.
[4 Oct 2016 13:59] Justin Swanhart
Also present in MySQL 8 DMR
[7 Mar 2017 18:09] Paul DuBois
Posted by developer:
 
Noted in 5.7.18 changelog.

EXPLAIN SELECT COUNT(*) FROM tbl_name could incorrectly report an
Extra value of "Select tables optimized away" due to a modification
introduced in MySQL 5.7.2 that caused InnoDB to count rows by
traversing the clustered index instead of a smaller secondary index.
The Extra value now displays "Count Rows".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.