From: Mattias Jonsson Date: March 14 2012 10:38pm Subject: bzr push into mysql-trunk branch (mattias.jonsson:3772 to 3773) Bug#13694811 List-Archive: http://lists.mysql.com/commits/143200 X-Bug: 13694811 Message-Id: <201203142238.q2EMcKTV021531@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3773 Mattias Jonsson 2012-03-14 [merge] merge of bug#13694811 into mysql-trunk modified: mysql-test/r/partition_innodb.result mysql-test/r/partition_pruning.result mysql-test/t/partition_innodb.test sql/ha_partition.cc sql/ha_partition.h 3772 Alfranio Correia 2012-03-14 BUG#13538891 - REP. TABLES SLAVE_MASTER_INFO AND SLAVE_WORKER_INFO MUST BE INNODB BY DEFAULT To provide a crash-safe slave, we had to change the engine used by the tables slave_master_info, slave_relay_log_info and slave_worker_info from MyISAM to Innodb. However, this additional step was causing confusion as simply storing replication meta-information into tables was supposed to be safe. We had decided to use MyISAM to create the aforementioned tables to keep compatibility with system tables which also use MyISAM. To avoid any sort of confusion, we use now Innodb to create the tables: slave_master_info, slave_relay_log_info and slave_worker_info. modified: mysql-test/r/mysqlcheck.result mysql-test/suite/funcs_1/r/is_tables_mysql.result mysql-test/suite/innodb/r/innodb-system-table-view.result mysql-test/suite/innodb/r/innodb_16k.result mysql-test/suite/innodb/r/innodb_bug60049.result mysql-test/suite/innodb/t/innodb-system-table-view.test mysql-test/suite/perfschema/r/pfs_upgrade.result mysql-test/suite/perfschema/t/no_threads-master.opt mysql-test/suite/rpl/r/rpl_alter_repository.result mysql-test/suite/rpl/r/rpl_mixed_mts_crash_safe.result mysql-test/suite/rpl/r/rpl_mixed_mts_rec_crash_safe.result mysql-test/suite/rpl/r/rpl_mixed_mts_rec_crash_safe_checksum.result mysql-test/suite/rpl/r/rpl_row_crash_safe.result mysql-test/suite/rpl/r/rpl_row_mts_crash_safe.result mysql-test/suite/rpl/r/rpl_row_mts_rec_crash_safe.result mysql-test/suite/rpl/r/rpl_stm_mixed_crash_safe.result mysql-test/suite/rpl/r/rpl_stm_mts_crash_safe.result mysql-test/suite/rpl/r/rpl_stm_mts_rec_crash_safe.result mysql-test/t/bootstrap-master.opt mysql-test/t/innodb_ignore_builtin-master.opt mysql-test/t/myisam-blob-master.opt mysql-test/t/mysql_embedded-master.opt mysql-test/t/warnings-master.opt scripts/mysql_system_tables.sql === modified file 'mysql-test/r/partition_innodb.result' --- a/mysql-test/r/partition_innodb.result revid:alfranio.correia@stripped +++ b/mysql-test/r/partition_innodb.result revid:mattias.jonsson@stripped @@ -1,5 +1,34 @@ drop table if exists t1, t2; # +# Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +# INNODB PARTITION STATISTICS +# +CREATE TABLE t1 +(a INT, +b varchar(64), +PRIMARY KEY (a), +KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), +PARTITION p0 VALUES LESS THAN (1000), +PARTITION pMAX VALUES LESS THAN MAXVALUE); +# Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY,b b 67 NULL 18 Using where; Using index +DROP TABLE t1; +# # Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY # and PARTITIONING # === modified file 'mysql-test/r/partition_pruning.result' --- a/mysql-test/r/partition_pruning.result revid:alfranio.correia@stripped +++ b/mysql-test/r/partition_pruning.result revid:mattias.jonsson@stripped @@ -18,7 +18,7 @@ id select_type table partitions type pos # # # # # # # # # 3 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -# # # # # # # # # 9 # +# # # # # # # # # 10 # EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra # # # # # # # # # 3 # @@ -105,7 +105,7 @@ a 6 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 +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -168,7 +168,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; 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 +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a <= 7; a -1 @@ -182,7 +182,7 @@ a 7 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 +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -424,7 +424,7 @@ a 5 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 1; a -1 @@ -474,7 +474,7 @@ a 5 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,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a <= 6; a -1 @@ -487,7 +487,7 @@ a 6 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0,p1,p2,p3,p4,max range PRIMARY PRIMARY 4 NULL 8 Using where; Using index +1 SIMPLE t1 p0,p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index SELECT * FROM t1 WHERE a = 1; a 1 @@ -744,13 +744,13 @@ a 1001-01-01 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 range a a 4 NULL 3 Using where; Using index @@ -759,26 +759,26 @@ id select_type table partitions type pos 1 SIMPLE t1 p1001-01-01 system a NULL NULL NULL 1 NULL EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL ref a a 4 const 1 Using index # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p2001-01-01 index a a 4 NULL 4 Using where; Using index @@ -790,16 +790,16 @@ id select_type table partitions type pos 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 index a a 4 NULL 5 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 range a a 4 NULL 3 Using where; Using index +1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 index a a 4 NULL 6 Using where; Using index # test without index ALTER TABLE t1 DROP KEY a; SELECT * FROM t1 WHERE a < '1001-01-01'; @@ -1076,7 +1076,7 @@ id select_type table partitions type pos 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1104,10 +1104,10 @@ id select_type table partitions type pos # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1119,10 +1119,10 @@ id select_type table partitions type pos 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -1405,7 +1405,7 @@ id select_type table partitions type pos 1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 range a a 4 NULL 4 Using where; Using index @@ -1433,10 +1433,10 @@ id select_type table partitions type pos # Disabling warnings for the invalid date EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2001-01-01,pNULL index a a 4 NULL 4 Using where; Using index @@ -1448,10 +1448,10 @@ id select_type table partitions type pos 1 SIMPLE t1 pNULL ref a a 4 const 1 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 5 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01'; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 4 Using where; Using index +1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 range a a 4 NULL 3 Using where; Using index EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00'; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pNULL,p1001-01-01 index a a 4 NULL 4 Using where; Using index @@ -2950,18 +2950,18 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where explain partitions select * from t2 where b = 4; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 NULL +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 NULL explain extended select * from t2 where b = 6; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ref b b 5 const 76 100.00 NULL +1 SIMPLE t2 ref b b 5 const 96 100.00 NULL Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` = 6) explain partitions select * from t2 where b = 6; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76 NULL +1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 NULL explain extended select * from t2 where b in (1,3,5); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 40.66 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 51.65 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (1,3,5)) explain partitions select * from t2 where b in (1,3,5); @@ -2969,7 +2969,7 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (2,4,6); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 25.05 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 31.65 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (2,4,6)) explain partitions select * from t2 where b in (2,4,6); @@ -2977,7 +2977,7 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b in (7,8,9); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 36.70 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 19.12 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` in (7,8,9)) explain partitions select * from t2 where b in (7,8,9); @@ -2985,7 +2985,7 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 44.84 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 29.23 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`b` > 5) explain partitions select * from t2 where b > 5; @@ -2993,7 +2993,7 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 8; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 22.09 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 28.13 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 8)) explain partitions select * from t2 where b > 5 and b < 8; @@ -3001,15 +3001,15 @@ id select_type table partitions type pos 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where explain extended select * from t2 where b > 5 and b < 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range b b 5 NULL 76 100.00 Using where +1 SIMPLE t2 range b b 5 NULL 96 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 5) and (`test`.`t2`.`b` < 7)) explain partitions select * from t2 where b > 5 and b < 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 76 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 96 Using where explain extended select * from t2 where b > 0 and b < 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL b NULL NULL NULL 910 41.65 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 53.19 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b` > 0) and (`test`.`t2`.`b` < 5)) explain partitions select * from t2 where b > 0 and b < 5; @@ -3042,7 +3042,7 @@ Variable_name Value Handler_read_key 0 explain partitions update t2 set a = 222 where b = 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 102 Using where; Using temporary +1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 4 Using where; Using temporary flush status; update t2 set a = 222 where b = 7; show status like 'Handler_read_rnd_next'; @@ -3053,18 +3053,18 @@ Variable_name Value Handler_read_key 5 explain extended delete from t2 where b = 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range b b 5 NULL 139 100.00 Using where +1 SIMPLE t2 ALL b NULL NULL NULL 910 100.00 Using where explain partitions delete from t2 where b = 7; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 p0,p1,p2,p3,p4 range b b 5 NULL 139 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 910 Using where flush status; delete from t2 where b = 7; show status like 'Handler_read_rnd_next'; Variable_name Value -Handler_read_rnd_next 0 +Handler_read_rnd_next 1215 show status like 'Handler_read_key'; Variable_name Value -Handler_read_key 5 +Handler_read_key 0 explain partitions delete from t2 where b > 5; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p0,p1,p2,p3,p4 ALL b NULL NULL NULL 810 Using where === modified file 'mysql-test/t/partition_innodb.test' --- a/mysql-test/t/partition_innodb.test revid:alfranio.correia@stripped +++ b/mysql-test/t/partition_innodb.test revid:mattias.jonsson@stripped @@ -9,6 +9,35 @@ drop table if exists t1, t2; let $MYSQLD_DATADIR= `SELECT @@datadir`; --echo # +--echo # Bug#13694811: THE OPTIMIZER WRONGLY USES THE FIRST +--echo # INNODB PARTITION STATISTICS +--echo # + +CREATE TABLE t1 +(a INT, + b varchar(64), + PRIMARY KEY (a), + KEY (b)) +ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) SUBPARTITIONS 10 +(PARTITION pNeg VALUES LESS THAN (0), + PARTITION p0 VALUES LESS THAN (1000), + PARTITION pMAX VALUES LESS THAN MAXVALUE); + +--echo # Only one row in the first 10 subpartitions +INSERT INTO t1 VALUES (-1, 'Only negative pk value'); + +INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), +(10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), +(20, '0'), (21, '1'), (22, '2'), (23, '3'), +(4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9'); +INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0; +ANALYZE TABLE t1; +EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100; +DROP TABLE t1; + +--echo # --echo # Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY --echo # and PARTITIONING --echo # === modified file 'sql/ha_partition.cc' --- a/sql/ha_partition.cc revid:alfranio.correia@stripped +++ b/sql/ha_partition.cc revid:mattias.jonsson@stripped @@ -340,6 +340,7 @@ void ha_partition::init_handler_variable m_clone_mem_root= NULL; part_share= NULL; m_new_partitions_share_refs.empty(); + m_part_ids_sorted_by_num_of_records= NULL; #ifdef DONT_HAVE_TO_BE_INITALIZED m_start_key.flag= 0; @@ -377,6 +378,7 @@ ha_partition::~ha_partition() delete m_file[i]; } my_free(m_ordered_rec_buffer); + my_free(m_part_ids_sorted_by_num_of_records); clear_handler_file(); DBUG_VOID_RETURN; @@ -2986,6 +2988,16 @@ int ha_partition::open(const char *name, m_start_key.key= (const uchar*)ptr; } } + if (!m_part_ids_sorted_by_num_of_records) + { + if (!(m_part_ids_sorted_by_num_of_records= + (uint32*) my_malloc(m_tot_parts * sizeof(uint32), MYF(MY_WME)))) + DBUG_RETURN(error); + uint32 i; + /* Initialize it with all partition ids. */ + for (i= 0; i < m_tot_parts; i++) + m_part_ids_sorted_by_num_of_records[i]= i; + } /* Initialize the bitmap we use to minimize ha_start_bulk_insert calls */ if (bitmap_init(&m_bulk_insert_started, NULL, m_tot_parts + 1, FALSE)) @@ -5615,6 +5627,24 @@ int ha_partition::handle_ordered_prev(uc and read_time calls */ +/** + Helper function for sorting according to number of rows in descending order. +*/ + +int ha_partition::compare_number_of_records(ha_partition *me, + const uint32 *a, + const uint32 *b) +{ + handler **file= me->m_file; + /* Note: sorting in descending order! */ + if (file[*a]->stats.records > file[*b]->stats.records) + return -1; + if (file[*a]->stats.records < file[*b]->stats.records) + return 1; + return 0; +} + + /* General method to gather info from handler @@ -5868,6 +5898,15 @@ int ha_partition::info(uint flag) } i++; } while (*(++file_array)); + /* + Sort the array of part_ids by number of records in + in descending order. + */ + my_qsort2((void*) m_part_ids_sorted_by_num_of_records, + m_tot_parts, + sizeof(uint32), + (qsort2_cmp) compare_number_of_records, + this); file= m_file[handler_instance]; file->info(HA_STATUS_CONST | no_lock_flag); @@ -6638,147 +6677,189 @@ const key_map *ha_partition::keys_to_use DBUG_RETURN(m_file[0]->keys_to_use_for_scanning()); } -#define MAX_PARTS_FOR_OPTIMIZER_CALLS 10U -/* - Prepare start variables for estimating optimizer costs. - - @param[out] num_used_parts Number of partitions after pruning. - @param[out] check_min_num Number of partitions to call. - @param[out] first first used partition. -*/ -void ha_partition::partitions_optimizer_call_preparations(uint *first, - uint *num_used_parts, - uint *check_min_num) -{ - *first= bitmap_get_first_set(&(m_part_info->read_partitions)); - *num_used_parts= bitmap_bits_set(&(m_part_info->read_partitions)); - *check_min_num= min(MAX_PARTS_FOR_OPTIMIZER_CALLS, - *num_used_parts); -} - -/* - Return time for a scan of the table - - SYNOPSIS - scan_time() - - RETURN VALUE - time for scan +/** + Minimum number of rows to base optimizer estimate on. */ -double ha_partition::scan_time() +ha_rows ha_partition::min_rows_for_estimate() { - double scan_time= 0.0; - uint first, part_id, num_used_parts, check_min_num, partitions_called= 0; - DBUG_ENTER("ha_partition::scan_time"); + uint i, max_used_partitions, tot_used_partitions; + DBUG_ENTER("ha_partition::min_rows_for_estimate"); + + tot_used_partitions= bitmap_bits_set(&m_part_info->read_partitions); + DBUG_ASSERT(tot_used_partitions); - partitions_optimizer_call_preparations(&first, &num_used_parts, - &check_min_num); - for (part_id= first; partitions_called < num_used_parts ; part_id++) + /* + Allow O(log2(tot_partitions)) increase in number of used partitions. + This gives O(tot_rows/log2(tot_partitions)) rows to base the estimate on. + I.e when the total number of partitions doubles, allow one more + partition to be checked. + */ + i= 2; + max_used_partitions= 1; + while (i < m_tot_parts) { - if (!bitmap_is_set(&(m_part_info->read_partitions), part_id)) - continue; - scan_time+= m_file[part_id]->scan_time(); - partitions_called++; - if (partitions_called >= check_min_num && scan_time != 0.0) - { - DBUG_RETURN(scan_time * - (double) num_used_parts / (double) partitions_called); - } + max_used_partitions++; + i= i << 1; } - DBUG_RETURN(scan_time); + if (max_used_partitions > tot_used_partitions) + max_used_partitions= tot_used_partitions; + + /* stats.records is already updated by the info(HA_STATUS_VARIABLE) call. */ + DBUG_PRINT("info", ("max_used_partitions: %u tot_rows: %lu", + max_used_partitions, + (ulong) stats.records)); + DBUG_PRINT("info", ("tot_used_partitions: %u min_rows_to_check: %lu", + tot_used_partitions, + (ulong) stats.records * max_used_partitions + / tot_used_partitions)); + DBUG_RETURN(stats.records * max_used_partitions / tot_used_partitions); } -/* - Estimate rows for records_in_range or estimate_rows_upper_bound. +/** + Get the biggest used partition. + + Starting at the N:th biggest partition and skips all non used + partitions, returning the biggest used partition found - @param is_records_in_range call records_in_range instead of - estimate_rows_upper_bound. - @param inx (only for records_in_range) index to use. - @param min_key (only for records_in_range) start of range. - @param max_key (only for records_in_range) end of range. + @param[in,out] part_index Skip the *part_index biggest partitions - @return Number of rows or HA_POS_ERROR. + @return The biggest used partition with index not lower than *part_index. + @retval NO_CURRENT_PART_ID No more partition used. + @retval != NO_CURRENT_PART_ID partition id of biggest used partition with + index >= *part_index supplied. Note that + *part_index will be updated to the next + partition index to use. */ -ha_rows ha_partition::estimate_rows(bool is_records_in_range, uint inx, - key_range *min_key, key_range *max_key) -{ - ha_rows rows, estimated_rows= 0; - uint first, part_id, num_used_parts, check_min_num, partitions_called= 0; - DBUG_ENTER("ha_partition::estimate_rows"); - partitions_optimizer_call_preparations(&first, &num_used_parts, &check_min_num); - for (part_id= first; partitions_called < num_used_parts ; part_id++) +uint ha_partition::get_biggest_used_partition(uint *part_index) +{ + uint part_id; + while ((*part_index) < m_tot_parts) { - if (!bitmap_is_set(&(m_part_info->read_partitions), part_id)) - continue; - if (is_records_in_range) - rows= m_file[part_id]->records_in_range(inx, min_key, max_key); - else - rows= m_file[part_id]->estimate_rows_upper_bound(); - if (rows == HA_POS_ERROR) - DBUG_RETURN(HA_POS_ERROR); - estimated_rows+= rows; - partitions_called++; - if (partitions_called >= check_min_num && estimated_rows) - { - DBUG_RETURN(estimated_rows * num_used_parts / partitions_called); - } + part_id= m_part_ids_sorted_by_num_of_records[(*part_index)++]; + if (bitmap_is_set(&m_part_info->read_partitions, part_id)) + return part_id; } - DBUG_RETURN(estimated_rows); + return NO_CURRENT_PART_ID; } /* - Find number of records in a range + Return time for a scan of the table SYNOPSIS - records_in_range() - inx Index number - min_key Start of range - max_key End of range + scan_time() RETURN VALUE - Number of rows in range + time for scan +*/ - DESCRIPTION - Given a starting key, and an ending key estimate the number of rows that - will exist between the two. end_key may be empty which in case determine - if start_key matches any rows. +double ha_partition::scan_time() +{ + double scan_time= 0; + handler **file; + DBUG_ENTER("ha_partition::scan_time"); + + for (file= m_file; *file; file++) + if (bitmap_is_set(&(m_part_info->read_partitions), (file - m_file))) + scan_time+= (*file)->scan_time(); + DBUG_RETURN(scan_time); +} - Called from opt_range.cc by check_quick_keys(). - monty: MUST be called for each range and added. - Note that MySQL will assume that if this returns 0 there is no - matching rows for the range! +/** + Find number of records in a range. + @param inx Index number + @param min_key Start of range + @param max_key End of range + + @return Number of rows in range. + + Given a starting key, and an ending key estimate the number of rows that + will exist between the two. max_key may be empty which in case determine + if start_key matches any rows. */ ha_rows ha_partition::records_in_range(uint inx, key_range *min_key, key_range *max_key) { + ha_rows min_rows_to_check, rows, estimated_rows=0, checked_rows= 0; + uint partition_index= 0, part_id; DBUG_ENTER("ha_partition::records_in_range"); - DBUG_RETURN(estimate_rows(TRUE, inx, min_key, max_key)); -} + min_rows_to_check= min_rows_for_estimate(); + while ((part_id= get_biggest_used_partition(&partition_index)) + != NO_CURRENT_PART_ID) + { + rows= m_file[part_id]->records_in_range(inx, min_key, max_key); + + DBUG_PRINT("info", ("part %u match %lu rows of %lu", part_id, (ulong) rows, + (ulong) m_file[part_id]->stats.records)); -/* - Estimate upper bound of number of rows + if (rows == HA_POS_ERROR) + DBUG_RETURN(HA_POS_ERROR); + estimated_rows+= rows; + checked_rows+= m_file[part_id]->stats.records; + /* + Returning 0 means no rows can be found, so we must continue + this loop as long as we have estimated_rows == 0. + Also many engines return 1 to indicate that there may exist + a matching row, we do not normalize this by dividing by number of + used partitions, but leave it to be returned as a sum, which will + reflect that we will need to scan each partition's index. + + Note that this statistics may not always be correct, so we must + continue even if the current partition has 0 rows, since we might have + deleted rows from the current partition, or inserted to the next + partition. + */ + if (estimated_rows && checked_rows && + checked_rows >= min_rows_to_check) + { + DBUG_PRINT("info", + ("records_in_range(inx %u): %lu (%lu * %lu / %lu)", + inx, + (ulong) (estimated_rows * stats.records / checked_rows), + (ulong) estimated_rows, + (ulong) stats.records, + (ulong) checked_rows)); + DBUG_RETURN(estimated_rows * stats.records / checked_rows); + } + } + DBUG_PRINT("info", ("records_in_range(inx %u): %lu", + inx, + (ulong) estimated_rows)); + DBUG_RETURN(estimated_rows); +} - SYNOPSIS - estimate_rows_upper_bound() - RETURN VALUE - Number of rows +/** + Estimate upper bound of number of rows. + + @return Number of rows. */ ha_rows ha_partition::estimate_rows_upper_bound() { + ha_rows rows, tot_rows= 0; + handler **file= m_file; DBUG_ENTER("ha_partition::estimate_rows_upper_bound"); - DBUG_RETURN(estimate_rows(FALSE, 0, NULL, NULL)); + do + { + if (bitmap_is_set(&(m_part_info->read_partitions), (file - m_file))) + { + rows= (*file)->estimate_rows_upper_bound(); + if (rows == HA_POS_ERROR) + DBUG_RETURN(HA_POS_ERROR); + tot_rows+= rows; + } + } while (*(++file)); + DBUG_RETURN(tot_rows); } === modified file 'sql/ha_partition.h' --- a/sql/ha_partition.h revid:alfranio.correia@stripped +++ b/sql/ha_partition.h revid:mattias.jonsson@stripped @@ -265,6 +265,12 @@ private: Partition_share *part_share; /** Temporary storage for new partitions Handler_shares during ALTER */ List m_new_partitions_share_refs; + /** Sorted array of partition ids in descending order of number of rows. */ + uint32 *m_part_ids_sorted_by_num_of_records; + /* Compare function for my_qsort2, for reversed order. */ + static int compare_number_of_records(ha_partition *me, + const uint32 *a, + const uint32 *b); public: Partition_share *get_part_share() { return part_share; } handler *clone(const char *name, MEM_ROOT *mem_root); @@ -681,15 +687,9 @@ public: */ private: - /* - Helper function to get the minimum number of partitions to use for - the optimizer hints/cost calls. - */ - void partitions_optimizer_call_preparations(uint *num_used_parts, - uint *check_min_num, - uint *first); - ha_rows estimate_rows(bool is_records_in_range, uint inx, - key_range *min_key, key_range *max_key); + /* Helper functions for optimizer hints. */ + ha_rows min_rows_for_estimate(); + uint get_biggest_used_partition(uint *part_index); public: /* No bundle (reason: useless for push emails).