Bug #53806 Wrong estimates for range query in partitioned MyISAM table
Submitted: 19 May 2010 14:00 Modified: 6 Oct 2010 12:35
Reporter: Øystein Grøvlen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.46, mysql-next-mr OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[19 May 2010 14:00] Øystein Grøvlen
Description:
If I have a test file with just the following query (in addition to
create/insert):

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;

I get the following with seemingly correct row estimate in EXPLAIN:

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows Extra
1    SIMPLE    t1    p0,p1    index    PRIMARY    PRIMARY    4    NULL    3    Using where; Using index

If I add another query before this, I get a different result:

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1,p2,p3,p4,p5,max    range    PRIMARY    PRIMARY    4    NULL    9    Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1    index    PRIMARY    PRIMARY    4    NULL    10    Using where; Using index

And it gets even stranger.  The estimate seem to be dependent on the estimate
of the previous query. E.g,:

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1,p2,p3,p4    index    PRIMARY    PRIMARY    4    NULL    6    Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id    select_type    table    partitions    type    possible_keys    key    key_len    ref rows    Extra
1    SIMPLE    t1    p0,p1    index    PRIMARY    PRIMARY    4    NULL    6    Using where; Using index

These queries are part of the partition_pruning test.  From looking at the
result file, correct estimates are given for '<' operator while '<=', '>', and '>=' give incorrect values.

How to repeat:
-- source include/have_partition.inc

CREATE TABLE t1 (a INT PRIMARY KEY)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION max VALUES LESS THAN MAXVALUE)
;

INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);

EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;

DROP TABLE t1;
[25 May 2010 13:06] Valeriy Kravchuk
Thank you for the problem report. What storage engine is used for the t1 table? Is it InnoDB?
[25 May 2010 13:13] Øystein Grøvlen
Since, I did not specify any storage engine in my script, it must have been MyIsam.
[25 May 2010 13:38] Valeriy Kravchuk
OK, just wanted to clarify as I've got different estimations with InnoDB table. Verified also with 5.1.46:

mysql> CREATE TABLE t1 (a INT PRIMARY KEY) engine=MyISAM
    -> PARTITION BY RANGE (a) (
    -> PARTITION p0 VALUES LESS THAN (1),
    -> PARTITION p1 VALUES LESS THAN (2),
    -> PARTITION p2 VALUES LESS THAN (3),
    -> PARTITION p3 VALUES LESS THAN (4),
    -> PARTITION p4 VALUES LESS THAN (5),
    -> PARTITION p5 VALUES LESS THAN (6),
    -> PARTITION max VALUES LESS THAN MAXVALUE)
    -> ;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |    3 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.02 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
+----+-------------+-------+-----------------------+-------+---------------+----
-----+---------+------+------+--------------------------+
| id | select_type | table | partitions            | type  | possible_keys | key
     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-----------------------+-------+---------------+----
-----+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3,p4,p5,max | range | PRIMARY       | PRI
MARY | 4       | NULL |    9 | Using where; Using index |
+----+-------------+-------+-----------------------+-------+---------------+----
-----+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |   10 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 5;
+----+-------------+-------+----------------+-------+---------------+---------+-
--------+------+------+--------------------------+
| id | select_type | table | partitions     | type  | possible_keys | key     |
key_len | ref  | rows | Extra                    |
+----+-------------+-------+----------------+-------+---------------+---------+-
--------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3,p4 | index | PRIMARY       | PRIMARY |
4       | NULL |   10 | Using where; Using index |
+----+-------------+-------+----------------+-------+---------------+---------+-
--------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |   10 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 2;
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_
len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
|  1 | SIMPLE      | t1    | p0,p1      | index | PRIMARY       | PRIMARY | 4
    | NULL |   10 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+-----
----+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.46-community |
+------------------+
1 row in set (0.00 sec)
[25 Aug 2010 13:44] Mattias Jonsson
Seems like the optimizer is using ::records in a wrong way, this seems to fix it (also fixes bug#46754):
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2010-08-13 08:07:39 +0000
+++ sql/sql_select.cc	2010-08-25 11:23:28 +0000
@@ -16640,7 +16640,15 @@
         if (tab->select && tab->select->quick)
           examined_rows= tab->select->quick->records;
         else if (tab->type == JT_NEXT || tab->type == JT_ALL)
-          examined_rows= tab->limit ? tab->limit : tab->table->file->records();
+        {
+          if (tab->limit)
+            examined_rows= tab->limit;
+          else
+          {
+            tab->table->file->info(HA_STATUS_VARIABLE);
+            examined_rows= tab->table->file->stats.records;
+          }
+        }
         else
           examined_rows=(ha_rows)join->best_positions[i].records_read;
[26 Aug 2010 15:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116908

3489 Mattias Jonsson	2010-08-26
      Bug#53806: Wrong estimates for range query in partitioned MyISAM table
      Bug#46754: 'rows' field doesn't reflect partition pruning
      
      The EXPLAIN's result in 'rows' field
      was evaluated to number of rows when the table was opened
      (not from the table cache) and only the partitions left
      after pruning was updated with its correct number
      of rows.
      
      The evaluation of the 'rows' field was using handler::records()
      which is a potentially expensive call, and ignores the partitioning
      pruning.
      
      The fix was to use the handlers stats.records after updating it
      with ::info(HA_STATUS_VARIABLE) instead.
     @ mysql-test/r/partition_pruning.result
        updated result
     @ mysql-test/t/partition_pruning.test
        Added test.
     @ sql/sql_select.cc
        Use ::info + stats.records instead of ::records().
[27 Aug 2010 8:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116963

3490 Mattias Jonsson	2010-08-27
      Bug#53806: Wrong estimates for range query in partitioned MyISAM table
      Bug#46754: 'rows' field doesn't reflect partition pruning
        
      Update of test results after fixing the above bugs.
      (fix in separate commit).
     @ mysql-test/r/partition.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/r/partition_hash.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/r/partition_innodb.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/r/partition_range.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/suite/parts/r/partition_alter3_innodb.result
        Updated test result after fixing bugs 46754 and 53806
     @ mysql-test/suite/parts/r/partition_alter3_myisam.result
        Updated test result after fixing bugs 46754 and 53806
[28 Aug 2010 11:50] Øystein Grøvlen
Approved.
[14 Sep 2010 9:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/118153

3204 Mattias Jonsson	2010-09-14 [merge]
      post push patch, fixing test result for bug#53806/46754.
[14 Sep 2010 9:26] Mattias Jonsson
pushed into mysql-5.1-bugteam and mysql-5.5-merge
[28 Sep 2010 15:40] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:45] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[30 Sep 2010 8:27] Jon Stephens
Documented bugfix in the 5.5.7 and 5.6.1 changelogs, as follows:

        EXPLAIN PARTITIONS returned bad estimates for range queries on
        partitioned MyISAM tables. In addition, values in the rows
        column of EXPLAIN PARTITIONS output did not take partition
        pruning into account.

Set NM status, waiting for merge to 5.1.
[4 Oct 2010 12:58] Jon Stephens
Returned to NM status, still waiting for 5.1 merge.
[6 Oct 2010 12:35] Jon Stephens
Also documented in the 5.1.52 changelog. Closed.
[1 Nov 2010 19:02] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)