diff options
Diffstat (limited to 'mysql-test/r/partition_innodb.result')
| -rw-r--r-- | mysql-test/r/partition_innodb.result | 43 |
1 files changed, 36 insertions, 7 deletions
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index da2f59f375e..dd403f64b5c 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -2,6 +2,35 @@ set global default_storage_engine='innodb'; set session default_storage_engine='innodb'; 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 @@ -148,13 +177,13 @@ insert INTO t1 VALUES (110); ERROR HY000: Table has no partition for value 110 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where @@ -163,16 +192,16 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # Bug#50104: Partitioned table with just 1 partion works with fk |