| 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: | |
| 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: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".
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)