3752 Mattias Jonsson 2012-03-14 [merge]
merge of bug#1364811 into mysql-5.5
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
3751 Tor Didriksen 2012-03-14
Bug#13832772 ASSERTION `THD->IS_ERROR() || KILL_ERRNO'
We are trying to sort a lot of text/blob fields,
so the buffer is indeed too small.
Memory available = thd->variables.sortbuff_size = 262144
min_sort_memory = param.sort_length*MERGEBUFF2 = 292245
So the decision to abort the query is correct.
filesort() calls my_error(), the error is reported.
But, since we have DELETE IGNORE ... the error is converted to a warning by
THD::raise_condition
filesort currently expects an error to be recorded in the THD diagnostics
area.
If we lift this restriction (remove the assert) we end up in the familiar
void Protocol::end_statement()
default:
DBUG_ASSERT(0);
The solution seems to be to call my_error(ME_FATALERROR) in filesort,
so that the error is propagated as an error rather than a warning.
@ mysql-test/r/filesort_debug.result
New test case.
@ mysql-test/t/filesort_debug.test
New test case.
modified:
mysql-test/r/filesort_debug.result
mysql-test/t/filesort_debug.test
sql/filesort.cc
=== modified file 'mysql-test/r/partition_innodb.result'
--- a/mysql-test/r/partition_innodb.result revid:tor.didriksen@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#56287: crash when using Partition datetime in sub in query
#
CREATE TABLE t1
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result revid:tor.didriksen@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
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 where; 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
@@ -2866,18 +2866,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 Using where
+1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 Using where
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 Using where
+1 SIMPLE t2 ref b b 5 const 96 100.00 Using where
Warnings:
Note 1003 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 Using where
+1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 96 Using where
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 `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);
@@ -2885,7 +2885,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 `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);
@@ -2893,7 +2893,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 `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);
@@ -2901,7 +2901,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 `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;
@@ -2909,7 +2909,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 `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;
@@ -2917,15 +2917,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 `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 `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;
@@ -2959,10 +2959,10 @@ 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
flush status;
delete from t2 where b > 5;
show status like 'Handler_read_rnd_next';
=== modified file 'mysql-test/t/partition_innodb.test'
--- a/mysql-test/t/partition_innodb.test revid:tor.didriksen@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#56287: crash when using Partition datetime in sub in query
--echo #
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc revid:tor.didriksen@stripped
+++ b/sql/ha_partition.cc revid:mattias.jonsson@stripped
@@ -285,6 +285,7 @@ void ha_partition::init_handler_variable
m_is_sub_partitioned= 0;
m_is_clone_of= NULL;
m_clone_mem_root= NULL;
+ m_part_ids_sorted_by_num_of_records= NULL;
#ifdef DONT_HAVE_TO_BE_INITALIZED
m_start_key.flag= 0;
@@ -320,6 +321,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;
@@ -2680,6 +2682,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))
@@ -5277,6 +5289,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
@@ -5521,6 +5551,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);
@@ -6276,145 +6315,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 10
-/*
- 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->used_partitions));
- *num_used_parts= bitmap_bits_set(&(m_part_info->used_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");
- partitions_optimizer_call_preparations(&first, &num_used_parts, &check_min_num);
- for (part_id= first; partitions_called < num_used_parts ; part_id++)
+ tot_used_partitions= bitmap_bits_set(&m_part_info->used_partitions);
+ DBUG_ASSERT(tot_used_partitions);
+
+ /*
+ 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->used_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::records_in_range");
- 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->used_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->used_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->used_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->used_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:tor.didriksen@stripped
+++ b/sql/ha_partition.h revid:mattias.jonsson@stripped
@@ -177,6 +177,12 @@ private:
ha_rows m_bulk_inserted_rows;
/** used for prediction of start_bulk_insert rows */
enum_monotonicity_info m_part_func_monotonicity_info;
+ /** 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:
handler *clone(const char *name, MEM_ROOT *mem_root);
virtual void set_part_info(partition_info *part_info)
@@ -584,15 +590,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).
| Thread |
|---|
| • bzr push into mysql-5.5 branch (mattias.jonsson:3751 to 3752) Bug#1364811 | Mattias Jonsson | 15 Mar |
