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