3910 Olav Sandstaa 2012-06-27
Fix for Bug#14185642 - WL#6082: FAILURES ON TRUNK
Test failures occurring when running the tests with the non-default 4K
page size for InnoDB. The tests fail due to changes in the "disk sweep
cost model" for reading data from disk introduced in WL#6082. The disk
sweep cost is influenced by the number of blocks used for storing the
table which can be different when using 4K page size compared to 16K
page size.
opt_trace.bugs_no_prot_all:
===========================
Cause: this test fails due to the cost estimate in the optimizer trace
is slightly different due to change in "file size" when using
4K blocks instead of 16K blocks.
Fix: This is an optimizer trace test. Fixes this problem by only running
it with the default 16K block size for InnoDB.
innodb_explain_json_non_select_all, innodb_explain_non_select_none,
innodb_explain_json_non_select_none, nnodb_explain_non_select_all:
==================================================================
Cause: These tests fail due to change in execution plan between 16K
and 4K InnoDB page sizes. For 16 page size the failing query is done
as a table scan while for 4K page size it is done as an index-merge.
Before WL#6082 it would use a table scan in both cases. The reason for
the change to do index-merge for the 4K case is that the size of the
table is smaller when using 4K pages compared to using 16K pages and
thus the cost for doing index merge is now lower.
Fix: We need to have the same query plan for both 4K and 16K pages
(since the test has the same result file). For the same tests when run
with MyISAM the queries are done using index-merge. So instead of
"forcing" the 4K InnoDB test to run as a "table scan", we change the
default/16K InnoDB test to run using index-merge by adding more
records to the table. This increases the cost of doing a full table
scan and makes index-merge cheaper than a table scan in both 16K and
4K test case.
@ mysql-test/collections/default.experimental
Remove tests from being experimental after fixing Bug#14185642.
@ mysql-test/include/explain_non_select.inc
Bug#14185642: Add more records to the test table for test case 35 and 45
in order to make the query use index-merge for InnoDB when running the
test with both 16K and 4K page size. Before this change it would
use a full table scan when running with 16K page size.
@ mysql-test/r/innodb_explain_json_non_select_all.result
Bug#14185642: Plan changes after having incresed the size of the table.
The query will now be run using index merge instead of table scan also
when using 16 page size.
@ mysql-test/r/innodb_explain_json_non_select_none.result
Bug#14185642: Plan changes after having incresed the size of the table.
The query will now be run using index merge instead of table scan also
when using 16 page size.
@ mysql-test/r/innodb_explain_non_select_all.result
Bug#14185642: Plan changes after having incresed the size of the table.
The query will now be run using index merge instead of table scan also
when using 16 page size.
@ mysql-test/r/innodb_explain_non_select_none.result
Bug#14185642: Plan changes after having incresed the size of the table.
The query will now be run using index merge instead of table scan also
when using 16 page size.
@ mysql-test/r/myisam_explain_json_non_select_all.result
Bug#14185642: Change in result file after adding more records in the
test table.
@ mysql-test/r/myisam_explain_json_non_select_none.result
Bug#14185642: Change in result file after adding more records in the
test table.
@ mysql-test/r/myisam_explain_non_select_all.result
Bug#14185642: Change in result file after adding more records in the
test table.
@ mysql-test/r/myisam_explain_non_select_none.result
Bug#14185642: Change in result file after adding more records in the
test table.
@ mysql-test/suite/opt_trace/include/bugs.inc
Bug#14185642: Only run this test when using default page size for
InnoDB. With non-default page size cost numbers in the optimizer trace
will be different.
modified:
mysql-test/collections/default.experimental
mysql-test/include/explain_non_select.inc
mysql-test/r/innodb_explain_json_non_select_all.result
mysql-test/r/innodb_explain_json_non_select_none.result
mysql-test/r/innodb_explain_non_select_all.result
mysql-test/r/innodb_explain_non_select_none.result
mysql-test/r/myisam_explain_json_non_select_all.result
mysql-test/r/myisam_explain_json_non_select_none.result
mysql-test/r/myisam_explain_non_select_all.result
mysql-test/r/myisam_explain_non_select_none.result
mysql-test/suite/opt_trace/include/bugs.inc
3909 Jorgen Loland 2012-06-26
BUG#11752097: SELECT ... WHERE (col1, col2) IN ((const, const))
is optimized, non-SELECT not
Post-push fix: record one more result file.
modified:
mysql-test/suite/funcs_1/r/myisam_views-big.result
=== modified file 'mysql-test/collections/default.experimental'
=== modified file 'mysql-test/collections/default.experimental'
--- a/mysql-test/collections/default.experimental 2012-06-12 13:06:39 +0000
+++ b/mysql-test/collections/default.experimental 2012-06-27 10:56:34 +0000
@@ -22,12 +22,6 @@
main.sp-prelocking @solaris # Bug#11753919 2012-05-22 Hemant Several test cases fail on Solaris with error Thread stack overrun
main.sp-fib @solaris # Bug#11753919 2012-05-22 Hemant Several test cases fail on Solaris with error Thread stack overrun
-main.innodb_explain_json_non_select_all # Bug#14185642 2012-06-12 Hemant Failing after Olav push for WL#6082
-main.innodb_explain_non_select_none # Bug#14185642 2012-06-12 Hemant Failing after Olav push for WL#6082
-main.innodb_explain_json_non_select_none # Bug#14185642 2012-06-12 Hemant Failing after Olav push for WL#6082
-main.innodb_explain_non_select_all # Bug#14185642 2012-06-12 Hemant Failing after Olav push for WL#6082
-opt_trace.bugs_no_prot_all # Bug#14185642 2012-06-12 Hemant Failing after Olav push for WL#6082
-
innodb.innodb_monitor # Bug#12320827 2011-08-04 Occasional failure in PB2
rpl.rpl_delayed_slave # BUG#11764654 rpl_delayed_slave fails sporadically in pb
=== modified file 'mysql-test/include/explain_non_select.inc'
--- a/mysql-test/include/explain_non_select.inc 2012-05-14 16:08:00 +0000
+++ b/mysql-test/include/explain_non_select.inc 2012-06-27 10:56:34 +0000
@@ -361,7 +361,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
- (30),(31),(32),(33),(34),(35);
+ (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+ (40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
--let $query = DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1
@@ -487,7 +488,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
- (30),(31),(32),(33),(34),(35);
+ (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+ (40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
--let $query = UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1
=== modified file 'mysql-test/r/innodb_explain_json_non_select_all.result'
--- a/mysql-test/r/innodb_explain_json_non_select_all.result 2012-05-29 19:18:45 +0000
+++ b/mysql-test/r/innodb_explain_json_non_select_all.result 2012-06-27 10:56:34 +0000
@@ -3299,7 +3299,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -3308,12 +3309,12 @@
#
EXPLAIN DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
EXPLAIN FORMAT=JSON DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;;
@@ -3326,12 +3327,14 @@
"table": {
"delete": true,
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -3342,7 +3345,7 @@
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
@@ -3356,12 +3359,14 @@
"using_filesort": true,
"table": {
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -3372,20 +3377,19 @@
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_delete 4
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#36
@@ -4206,7 +4210,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -4215,12 +4220,12 @@
#
EXPLAIN UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
EXPLAIN FORMAT=JSON UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;;
@@ -4233,12 +4238,14 @@
"table": {
"update": true,
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -4249,7 +4256,7 @@
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
@@ -4263,12 +4270,14 @@
"using_filesort": true,
"table": {
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -4279,20 +4288,19 @@
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
Handler_update 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#46
=== modified file 'mysql-test/r/innodb_explain_json_non_select_none.result'
--- a/mysql-test/r/innodb_explain_json_non_select_none.result 2012-05-29 19:18:45 +0000
+++ b/mysql-test/r/innodb_explain_json_non_select_none.result 2012-06-27 10:56:34 +0000
@@ -3316,7 +3316,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -3325,12 +3326,12 @@
#
EXPLAIN DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
EXPLAIN FORMAT=JSON DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;;
@@ -3343,12 +3344,14 @@
"table": {
"delete": true,
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -3359,7 +3362,7 @@
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
@@ -3373,12 +3376,14 @@
"using_filesort": true,
"table": {
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -3389,20 +3394,19 @@
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_delete 4
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#36
@@ -4223,7 +4227,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -4232,12 +4237,12 @@
#
EXPLAIN UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
EXPLAIN FORMAT=JSON UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;;
@@ -4250,12 +4255,14 @@
"table": {
"update": true,
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -4266,7 +4273,7 @@
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
@@ -4280,12 +4287,14 @@
"using_filesort": true,
"table": {
"table_name": "t2",
- "access_type": "ALL",
+ "access_type": "index_merge",
"possible_keys": [
"key1",
"key2"
] /* possible_keys */,
- "rows": 26,
+ "key": "sort_union(key1,key2)",
+ "key_length": "5,5",
+ "rows": 5,
"filtered": 100,
"attached_condition": "((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14))"
} /* table */
@@ -4296,20 +4305,19 @@
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
Handler_update 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#46
=== modified file 'mysql-test/r/innodb_explain_non_select_all.result'
--- a/mysql-test/r/innodb_explain_non_select_all.result 2012-05-22 13:35:37 +0000
+++ b/mysql-test/r/innodb_explain_non_select_all.result 2012-06-27 10:56:34 +0000
@@ -1541,7 +1541,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -1550,39 +1551,38 @@
#
EXPLAIN DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_delete 4
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#36
@@ -2009,7 +2009,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -2018,39 +2019,38 @@
#
EXPLAIN UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
Handler_update 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#46
=== modified file 'mysql-test/r/innodb_explain_non_select_none.result'
--- a/mysql-test/r/innodb_explain_non_select_none.result 2012-05-22 13:35:37 +0000
+++ b/mysql-test/r/innodb_explain_non_select_none.result 2012-06-27 10:56:34 +0000
@@ -1543,7 +1543,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -1552,39 +1553,38 @@
#
EXPLAIN DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
Handler_delete 4
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#36
@@ -2011,7 +2011,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -2020,39 +2021,38 @@
#
EXPLAIN UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 Using sort_union(key1,key2); Using where; Using filesort
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
# Status of EXPLAIN EXTENDED query
Variable_name Value
FLUSH STATUS;
FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL key1,key2 NULL NULL NULL 26 100.00 Using where; Using filesort
+1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 /* select#1 */ select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 1
-Handler_read_rnd_next 27
+Handler_read_key 6
+Handler_read_next 7
+Handler_read_rnd 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
# Status of testing query execution:
Variable_name Value
-Handler_read_first 1
-Handler_read_key 5
-Handler_read_rnd 4
-Handler_read_rnd_next 27
+Handler_read_key 10
+Handler_read_next 7
+Handler_read_rnd 8
Handler_update 4
+Sort_range 1
Sort_rows 4
-Sort_scan 1
DROP TABLE t1, t2;
#46
=== modified file 'mysql-test/r/myisam_explain_json_non_select_all.result'
--- a/mysql-test/r/myisam_explain_json_non_select_all.result 2012-05-22 13:35:37 +0000
+++ b/mysql-test/r/myisam_explain_json_non_select_all.result 2012-06-27 10:56:34 +0000
@@ -3187,7 +3187,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -4076,7 +4077,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
=== modified file 'mysql-test/r/myisam_explain_json_non_select_none.result'
--- a/mysql-test/r/myisam_explain_json_non_select_none.result 2012-05-22 13:35:37 +0000
+++ b/mysql-test/r/myisam_explain_json_non_select_none.result 2012-06-27 10:56:34 +0000
@@ -3202,7 +3202,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -4091,7 +4092,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
=== modified file 'mysql-test/r/myisam_explain_non_select_all.result'
--- a/mysql-test/r/myisam_explain_non_select_all.result 2012-05-22 13:35:37 +0000
+++ b/mysql-test/r/myisam_explain_non_select_all.result 2012-06-27 10:56:34 +0000
@@ -1442,7 +1442,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -1888,7 +1889,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
=== modified file 'mysql-test/r/myisam_explain_non_select_none.result'
--- a/mysql-test/r/myisam_explain_non_select_none.result 2012-05-22 13:35:37 +0000
+++ b/mysql-test/r/myisam_explain_non_select_none.result 2012-06-27 10:56:34 +0000
@@ -1442,7 +1442,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
@@ -1888,7 +1889,8 @@
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
-(30),(31),(32),(33),(34),(35);
+(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
+(40),(41),(42);
CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2));
INSERT INTO t2 (key1, key2) SELECT i, i FROM t1;
#
=== modified file 'mysql-test/suite/opt_trace/include/bugs.inc'
--- a/mysql-test/suite/opt_trace/include/bugs.inc 2012-02-29 11:17:52 +0000
+++ b/mysql-test/suite/opt_trace/include/bugs.inc 2012-06-27 10:56:34 +0000
@@ -1,6 +1,8 @@
# Regressiontest for statements that failed with optimizer tracing enabled.
--source include/have_optimizer_trace.inc
+# InnoDB page size influences cost numbers
+--source include/have_innodb_16k.inc
let $DEFAULT_TRACE_MEM_SIZE=1048576; # 1MB
eval set optimizer_trace_max_mem_size=$DEFAULT_TRACE_MEM_SIZE;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (olav.sandstaa:3909 to 3910) Bug#14185642WL#6082 | Olav Sandstaa | 27 Jun |
