From: Jorgen Loland Date: May 8 2012 6:08am Subject: bzr push into mysql-trunk branch (jorgen.loland:3798 to 3799) Bug#13970015 List-Archive: http://lists.mysql.com/commits/143773 X-Bug: 13970015 Message-Id: <20120508060810.0E4F8634@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3799 Jorgen Loland 2012-05-08 Bug#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN HANDLER::MULTI_RANGE_READ_INFO_CONST When key_or() finds that two SEL_ARG trees form a full range (no upper or lower bound in the range; is always true), NULL is returned. In most cases the range optimizer then forgets about these trees, but when the trees belong to two different SEL_TREEs used in an index merge the always true range is only removed from one of the SEL_TREEs. Index merge is a disjunct operation, meaning that the rows read from the different indexes shall be ORed. If the predicate for one of the indexes is always true then index merge range access will effectively fetch all rows from the table. Range access in this case is not a good idea. modified: mysql-test/include/index_merge2.inc mysql-test/r/index_merge_myisam.result sql/opt_range.cc 3798 Jorgen Loland 2012-05-08 [merge] Merge from feature tree to trunk: * refactoring of Field::store() return type * cleanup of server setup/teardown for large server unit tests added: unittest/gunit/field_date-t.cc unittest/gunit/field_datetime-t.cc unittest/gunit/field_long-t.cc unittest/gunit/field_newdecimal-t.cc unittest/gunit/field_temporal_utils.h unittest/gunit/gunit_test_main_server.cc modified: mysql-test/extra/rpl_tests/rpl_extra_col_master.test mysql-test/r/ctype_ucs.result mysql-test/r/type_bit.result mysql-test/suite/rpl/r/rpl_extra_col_master_innodb.result mysql-test/suite/rpl/r/rpl_extra_col_master_myisam.result mysql-test/t/ctype_ucs.test mysql-test/t/type_bit.test sql-common/my_time.c sql/field.cc sql/field.h sql/field_conv.cc sql/item.cc sql/item.h sql/item_cmpfunc.cc sql/item_func.cc sql/item_func.h sql/item_strfunc.h sql/item_timefunc.cc sql/item_timefunc.h sql/opt_range.cc sql/sql_base.cc sql/sql_select.h sql/unireg.cc unittest/gunit/CMakeLists.txt unittest/gunit/copy_info-t.cc unittest/gunit/create_field-t.cc unittest/gunit/decimal-t.cc unittest/gunit/fake_table.h unittest/gunit/field-t.cc unittest/gunit/field_timestamp-t.cc unittest/gunit/get_diagnostics-t.cc unittest/gunit/item-t.cc unittest/gunit/item_func_now_local-t.cc unittest/gunit/join_tab_sort-t.cc unittest/gunit/my_decimal-t.cc unittest/gunit/opt_range-t.cc unittest/gunit/segfault-t.cc unittest/gunit/sql_table-t.cc unittest/gunit/test_utils.cc unittest/gunit/test_utils.h === modified file 'mysql-test/include/index_merge2.inc' --- a/mysql-test/include/index_merge2.inc 2011-11-03 07:01:49 +0000 +++ b/mysql-test/include/index_merge2.inc 2012-05-08 06:07:50 +0000 @@ -463,3 +463,34 @@ DROP TABLE t1, t2; #a b #1 b #DROP TABLE t1, t2; + +--echo # +--echo # BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN +--echo # HANDLER::MULTI_RANGE_READ_INFO_CONST +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL, + col_int_key INT NOT NULL, + col_varchar_key VARCHAR(1) NOT NULL, + PRIMARY KEY (pk), + KEY col_int_key (col_int_key), + KEY col_varchar_key (col_varchar_key,col_int_key) +); + +INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b'); + +EXPLAIN +SELECT col_int_key +FROM t1 +WHERE col_varchar_key >= 'l' OR + (((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') + AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); + +SELECT col_int_key +FROM t1 +WHERE col_varchar_key >= 'l' OR + (((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') + AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); + +DROP TABLE t1; === modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2012-02-29 11:17:52 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2012-05-08 06:07:50 +0000 @@ -1241,6 +1241,34 @@ SELECT * FROM t2; a b 1 b DROP TABLE t1, t2; +# +# BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN +# HANDLER::MULTI_RANGE_READ_INFO_CONST +# +CREATE TABLE t1 ( +pk INT NOT NULL, +col_int_key INT NOT NULL, +col_varchar_key VARCHAR(1) NOT NULL, +PRIMARY KEY (pk), +KEY col_int_key (col_int_key), +KEY col_varchar_key (col_varchar_key,col_int_key) +); +INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b'); +EXPLAIN +SELECT col_int_key +FROM t1 +WHERE col_varchar_key >= 'l' OR +(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') +AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY,col_int_key,col_varchar_key NULL NULL NULL 2 Using where +SELECT col_int_key +FROM t1 +WHERE col_varchar_key >= 'l' OR +(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') +AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); +col_int_key +DROP TABLE t1; #---------------- 2-sweeps read Index merge test 2 ------------------------------- SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; drop table if exists t1; === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2012-05-07 08:29:18 +0000 +++ b/sql/opt_range.cc 2012-05-08 06:07:50 +0000 @@ -394,7 +394,17 @@ public: */ SEL_ARG *next_key_part; enum leaf_color { BLACK,RED } color; - enum Type { IMPOSSIBLE, MAYBE, MAYBE_KEY, KEY_RANGE } type; + + /** + Starting an effort to document this field: + + IMPOSSIBLE: if the range predicate for this index is always false. + + ALWAYS: if the range predicate for this index is always true. + + KEY_RANGE: if there is a range predicate that can be used on this index. + */ + enum Type { IMPOSSIBLE, ALWAYS, MAYBE, MAYBE_KEY, KEY_RANGE } type; enum { MAX_SEL_ARGS = 16000 }; @@ -699,10 +709,28 @@ class SEL_IMERGE; class SEL_TREE :public Sql_alloc { public: - /* + /** Starting an effort to document this field: - (for some i, keys[i]->type == SEL_ARG::IMPOSSIBLE) => - (type == SEL_TREE::IMPOSSIBLE) + + IMPOSSIBLE: if keys[i]->type == SEL_ARG::IMPOSSIBLE for some i, + then type == SEL_TREE::IMPOSSIBLE. Rationale: if the predicate for + one of the indexes is always false, then the full predicate is also + always false. + + ALWAYS: if either (keys[i]->type == SEL_ARG::ALWAYS) or + (keys[i] == NULL) for all i, then type == SEL_TREE::ALWAYS. + Rationale: the range access method will not be able to filter + out any rows when there are no range predicates that can be used + to filter on any index. + + KEY: There are range predicates that can be used on at least one + index. + + KEY_SMALLER: There are range predicates that can be used on at + least one index. In addition, there are predicates that cannot + be directly utilized by range access on key parts in the same + index. These unused predicates makes it probable that the row + estimate for range access on this index is too pessimistic. */ enum Type { IMPOSSIBLE, ALWAYS, MAYBE, KEY, KEY_SMALLER } type; SEL_TREE(enum Type type_arg) :type(type_arg) {} @@ -7538,6 +7566,8 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG * // cur_key2 is full range: [-inf <= cur_key2 <= +inf] key1->free_tree(); key2->free_tree(); + key1->type= SEL_ARG::ALWAYS; + key2->type= SEL_ARG::ALWAYS; if (key1->maybe_flag) return new SEL_ARG(SEL_ARG::MAYBE_KEY); return 0; @@ -7746,6 +7776,8 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG * if (full_range) { // Full range key1->free_tree(); + key1->type= SEL_ARG::ALWAYS; + key2->type= SEL_ARG::ALWAYS; for (; cur_key2 ; cur_key2= cur_key2->next) cur_key2->increment_use_count(-1); // Free not used tree if (key1->maybe_flag) No bundle (reason: useless for push emails).