MySQL Bugs: #37532: Explain command shows incorrect rows, when table is partitioned and innodb.

Bug #37532	Explain command shows incorrect rows, when table is partitioned and innodb.
Submitted:	19 Jun 2008 19:53	Modified:	30 May 2009 5:23
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Linux
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
1) I create database and table with range partitions and Innodb storage engine.
2) I insert some values in to the table.
3) I use explain partitions command to see the details of the table.
The no.of rows highlighted here is incorrect.

SET CHARACTER_SET_SERVER= utf8;
CREATE DATABASE partitions;
USE partitions;
CREATE TABLE `range`(
ID INT NOT NULL,
NAME CHAR(20),
AGE TINYINT,
DOB DATE,
SAL FLOAT,
PRIMARY KEY(DOB)) engine=innodb PARTITION BY RANGE(Year(DOB))(
PARTITION P0 values less than (1970),
PARTITION P1 values less than (1980),
PARTITION P2 values less than (1990),
PARTITION P3 values less than (2000),
PARTITION P4 values less than maxvalue);
SHOW CREATE TABLE `range`;
INSERT INTO `range`VALUES
(10,'aa','20','1988-12-19',2345.89),(11,'bb',31,'1977-10-19',6009.89),
(12,'cc',78,'1945-09-12',3897),(13,'dd',12,'1994-09-18',7892.78),
(14,'ee',45,'1960-12-23',7845.90),(15,'ff',25,'1984-02-07',3500.89);
EXPLAIN PARTITIONS SELECT * FROM `range`\G

How to repeat:
SET CHARACTER_SET_SERVER= utf8;
mysql> CREATE DATABASE partitions;
Query OK, 1 row affected (0.01 sec)

mysql>USE partitions;

mysql>CREATE TABLE `range`(
ID INT NOT NULL,
NAME CHAR(20),
AGE TINYINT,
DOB DATE,
SAL FLOAT,
PRIMARY KEY(DOB)) engine=innodb PARTITION BY RANGE(Year(DOB))(
PARTITION P0 values less than (1970),
PARTITION P1 values less than (1980),
PARTITION P2 values less than (1990),
PARTITION P3 values less than (2000),
PARTITION P4 values less than maxvalue);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `range`VALUES
    -> (10,'aa','20','1988-12-19',2345.89),(11,'bb',31,'1977-10-19',6009.89),
    -> (12,'cc',78,'1945-09-12',3897),(13,'dd',12,'1994-09-18',7892.78),
    -> (14,'ee',45,'1960-12-23',7845.90),(15,'ff',25,'1984-02-07',3500.89);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select count(*) from `range`;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM `range`\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: range
   partitions: P0,P1,P2,P3,P4
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra:
1 row in set (0.00 sec)

If you notice here, the no.of rows shows are incorrect. The rows column should be 6 instead of 7.

Thank you for the report.

Verified as described.

Though I understand what is stated, I feel that these should be properly documented from users perspective.
The sentence within the manual is not clear that the number of rows is  an estimation.

This is not a partitioning bug, since this specific EXPLAIN query can be repeated without partitioning by:
CREATE TABLE t1 (a INT) ENGINE = InnoDB;
EXPLAIN SELECT * FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1	

I.e An empty innodb table/partition does say 1 row in the EXPLAIN, that's why it shows 7 instead of 6 when it is partitioned (since the MAXVALUE partition was empty).

Bug still repeatable with current 5.1 and 6.0 sources.

Innodb returns estimates of rows in explain and not an exact number

Thank you for the feedback.

Reclassifying as doc request.

See also bug #43903 and comment "[27 Mar 11:14] Sveta Smirnova" to bug #43903

Partitioning docs -> should be assigned to me.

See also BUG#44646 for possibly related documentation issue.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.