diff options
| -rw-r--r-- | mysql-test/r/range_vs_index_merge.result | 26 | ||||
| -rw-r--r-- | mysql-test/r/range_vs_index_merge_innodb.result | 26 | ||||
| -rwxr-xr-x | mysql-test/t/range_vs_index_merge.test | 70 | ||||
| -rw-r--r-- | sql/opt_range.cc | 39 |
4 files changed, 143 insertions, 18 deletions
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 0563a0e491f..0f9c46f182d 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1360,3 +1360,29 @@ id c1 c2 c3 select * from t1 where (c1=' 100000' or c2=' 2000000') and c3='2'; id c1 c2 c3 drop table t1; +CREATE TABLE t1 ( +a smallint DEFAULT NULL, +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +b varchar(10) DEFAULT NULL, +c varchar(64) DEFAULT NULL, +INDEX idx1 (a), +INDEX idx2 (b), +INDEX idx3 (c) +); +SELECT COUNT(*) FROM t1 IGNORE INDEX (idx2,idx3) +WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR +(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +COUNT(*) +5 +SELECT COUNT(*) FROM t1 +WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR +(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +COUNT(*) +5 +EXPLAIN +SELECT COUNT(*) FROM t1 +WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR +(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,idx1,idx2,idx3 idx3,idx2,PRIMARY,idx1 67,13,4,3 NULL 8 Using sort_union(idx3,idx2,PRIMARY,idx1); Using where +DROP TABLE t1; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index dd97415b1dc..2251119996a 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1361,4 +1361,30 @@ id c1 c2 c3 select * from t1 where (c1=' 100000' or c2=' 2000000') and c3='2'; id c1 c2 c3 drop table t1; +CREATE TABLE t1 ( +a smallint DEFAULT NULL, +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +b varchar(10) DEFAULT NULL, +c varchar(64) DEFAULT NULL, +INDEX idx1 (a), +INDEX idx2 (b), +INDEX idx3 (c) +); +SELECT COUNT(*) FROM t1 IGNORE INDEX (idx2,idx3) +WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR +(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +COUNT(*) +5 +SELECT COUNT(*) FROM t1 +WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR +(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +COUNT(*) +5 +EXPLAIN +SELECT COUNT(*) FROM t1 +WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR +(pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge PRIMARY,idx1,idx2,idx3 idx3,idx2,idx1,PRIMARY 67,13,3,4 NULL 9 Using sort_union(idx3,idx2,idx1,PRIMARY); Using where +DROP TABLE t1; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test index aa5f4620ffe..f57fced6da6 100755 --- a/mysql-test/t/range_vs_index_merge.test +++ b/mysql-test/t/range_vs_index_merge.test @@ -846,7 +846,75 @@ select * from t1 where (c1=' 100000' or c2=' 2000000') and c3='2'; drop table t1; +# +# Bug #637978: invalid index merge access plan causes to wrong results +# +CREATE TABLE t1 ( + a smallint DEFAULT NULL, + pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, + b varchar(10) DEFAULT NULL, + c varchar(64) DEFAULT NULL, + INDEX idx1 (a), + INDEX idx2 (b), + INDEX idx3 (c) +); +--disable_query_log +--disable_result_log +INSERT INTO t1 VALUES +(30371,99001,'dl','e'),(3,99002,'Ohio','t'),(9,99003,'Delaware','xb'), +(0,99004,'Pennsylvan','i'),(-199,99005,'y','d'),(0,99006,'with','Rhode Island'), +(3,99007,'km','qkmiimdxbdljsejtsfrvwlrgacinbmfuosflnenlpomkmvbig'), +(22860,99008,'ovqkmiimdx','uovqkmiimdxbdljsejtsfrvwlrgacinbmfuosflnenlpomkmvbig'), +(212,99009,'f','p'),(NULL,99010,'i','k'),(20426,99011,'Vermont','New York'), +(0,99012,'Oregon','w'),(31831,99013,'s','isrcijpuovqkmiimdxbdljsejtsfrvwl'), +(123,99014,'t','p'),(32767,99015,'q','Maine'), +(NULL,99016,'know','qqqpisrcijpuovqkmiimdxbdljsejtsfrvwlrgacinbmfuosflnenlpo'), +(1,99017,'going','North Carolina'),(-717,99018,'ad','Indiana'), +(32767,99019,'Maryland','aa'),(31280,99020,'Nebraska','Colorado'), +(0,99021,'q','Ohio'), +(5989,99022,'rovaadtqqq','lrovaadtqqqpisrcijpuovqkmiimdxbdljsejtsfrvwlrgacinb'), +(89,99023,'n','Pennsylvania'),(0,99024,'Florida','c'),(97,99025,'Maine','y'), +(149,99026,'xaemnl','Idaho'),(NULL,99027,'h','y'),(26276,99028,'going','New York'), +(242,99029,'bdhxaemnlr','sbdhxaemnlrovaadtqqqpisrcijpuovqkmiimdxb'), +(32767,99030,'if','a'),(26581,99031,'Arizona','q'),(45,99032,'ysazsbdhxa','f'), +(0,99033,'qv','s'),(NULL,99034,'Louisiana','lqvfysazsbdhxaemnlrovaadtqqqpisrc'), +(160,99035,'Connecticu','x'),(23241,99036,'lx','q'),(0,99037,'u','Colorado'), +(-19141,99038,'w','h'),(218,99039,'s','uo'),(4,99040,'Montana','Oklahoma'), +(97,99041,'r','ls'),(32767,99042,'q','v'),(7,99043,'mlsuownlnl','did'), +(NULL,99044,'ui','i'),(2,99045,'to','I\'ll'),(0,99046,'Nevada','g'), +(3251,99047,'y','New York'),(0,99048,'wyttuimlsu','you\'re'), +(7,99049,'he','South Carolina'),(32767,99050,'s','right'), +(172,99051,'Arizona','e'),(0,99052,'x','lxmvwyttuimlsuownlnlxklq'), +(NULL,99053,'f','wfjlxmvwyttuimlsuownlnlxklqvfysazs'),(44,99054,'s','n'), +(-17561,99055,'me','wm'),(88,99056,'y','my'),(7313,99057,'jx','New Hampshire'), +(63,99058,'zl','South Carolina'),(9,99059,'ma','Illinois'), +(6,99060,'lamazljxpg','like'),(17021,99061,'x','v'),(0,99062,'New Mexico','j'), +(179,99427,'fliq','because'), +(107,99063,'Virginia','Mississippi'), +(0,99064,'si','to'),(113,99065,'Illinois','Kansas'),(20808,99066,'tsi','d'), +(-15372,99067,'d','vdftsidjtvulamazljxpgiwmbnmwfjlxmvwyttuimlsuownlnl'), +(0,99068,'y','then'),(2,99069,'all','b'),(NULL,99070,'by','Wisconsin'), +(4,99071,'about','right'),(5,99072,'m','s'),(0,99073,'e','Pennsylvania'), +(-28284,99074,'x','f'),(1,99075,'Rhode Isla','Georgia'),(NULL,99076,'p','was'), +(168,99077,'Tennessee','Minnesota'),(18349,99078,'x','Rhode Island'), +(5,99079,'as','d'),(12217,99080,'c','i'),(0,99081,'rdvdxboydm','s'), +(19132,99082,'her','jerdvdxboydmpefbiesqbyyvdftsidjtvulamazljxpgiwmbn'), +(0,99083,'all','jhjerdvdxboydmpefbiesqbyyvdftsidjtvulamazljx'), +(32767,99084,'s','flj'),(-4947,99085,'something','Vermont'), +(0,99086,'cjfljhjerd','Washington'); +--enable_query_log +--enable_result_log +SELECT COUNT(*) FROM t1 IGNORE INDEX (idx2,idx3) + WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR + (pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +SELECT COUNT(*) FROM t1 + WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR + (pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; +EXPLAIN +SELECT COUNT(*) FROM t1 + WHERE c = 'i' OR b IN ( 'Arkansas' , 'd' , 'pdib' , 'can' ) OR + (pk BETWEEN 120 AND 79 + 255 OR a IN ( 4 , 179 , 1 ) ) AND a > 8 ; - +DROP TABLE t1; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 9fac099855e..1342c1ef331 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -890,10 +890,12 @@ public: int and_sel_tree(RANGE_OPT_PARAM *param, SEL_TREE *tree, SEL_IMERGE *new_imerge); int or_sel_tree_with_checks(RANGE_OPT_PARAM *param, + uint n_init_trees, SEL_TREE *new_tree, bool is_first_check_pass, bool *is_last_check_pass); int or_sel_imerge_with_checks(RANGE_OPT_PARAM *param, + uint n_init_trees, SEL_IMERGE* imerge, bool is_first_check_pass, bool *is_last_check_pass); @@ -1025,7 +1027,8 @@ int SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM *param, SEL_TREE *tree, SYNOPSIS or_sel_tree_with_checks() - param Context info for the operation + param Context info for the operation + n_trees Number of trees in this imerge to check for oring tree SEL_TREE whose range part is to be ored is_first_check_pass <=> the first call of the function for this imerge is_last_check_pass OUT <=> no more calls of the function for this imerge @@ -1075,14 +1078,14 @@ int SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM *param, SEL_TREE *tree, */ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, + uint n_trees, SEL_TREE *tree, bool is_first_check_pass, bool *is_last_check_pass) { - *is_last_check_pass= TRUE; - for (SEL_TREE** or_tree = trees; - or_tree != trees_next; - or_tree++) + *is_last_check_pass= TRUE; + SEL_TREE** or_tree = trees; + for (uint i= 0; i < n_trees; i++, or_tree++) { SEL_TREE *result= 0; key_map result_keys; @@ -1116,7 +1119,7 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, (key1)->test_use_count(key1); } #endif - } + } } } else if(is_first_check_pass) @@ -1152,8 +1155,9 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, Perform OR operation on this imerge and and another imerge SYNOPSIS - or_sel_tree_with_checks() - param Context info for the operation + or_sel_imerge_with_checks() + param Context info for the operation + n_trees Number of trees in this imerge to check for oring imerge The second operand of the operation is_first_check_pass <=> the first call of the function for this imerge is_last_check_pass OUT <=> no more calls of the function for this imerge @@ -1176,18 +1180,19 @@ int SEL_IMERGE::or_sel_tree_with_checks(RANGE_OPT_PARAM *param, */ int SEL_IMERGE::or_sel_imerge_with_checks(RANGE_OPT_PARAM *param, + uint n_trees, SEL_IMERGE* imerge, bool is_first_check_pass, bool *is_last_check_pass) { *is_last_check_pass= TRUE; - for (SEL_TREE** tree= imerge->trees; - tree != imerge->trees_next; - tree++) + SEL_TREE** tree= imerge->trees; + for (uint i= 0; i < n_trees; i++, tree++) { uint rc; - bool is_last; - rc= or_sel_tree_with_checks(param, *tree, is_first_check_pass, &is_last); + bool is_last= TRUE; + rc= or_sel_tree_with_checks(param, n_trees, *tree, + is_first_check_pass, &is_last); if (!is_last) *is_last_check_pass= FALSE; if (rc) @@ -1368,7 +1373,7 @@ int imerge_list_or_list(RANGE_OPT_PARAM *param, im1->empty(); im1->push_back(imerge); - rc= imerge->or_sel_imerge_with_checks(param, im2->head(), + rc= imerge->or_sel_imerge_with_checks(param, elems, im2->head(), TRUE, &is_last_check_pass); if (rc) { @@ -1386,7 +1391,7 @@ int imerge_list_or_list(RANGE_OPT_PARAM *param, if (new_imerge) { is_last_check_pass= TRUE; - rc= new_imerge->or_sel_imerge_with_checks(param, im2->head(), + rc= new_imerge->or_sel_imerge_with_checks(param, elems, im2->head(), FALSE, &is_last_check_pass); if (!rc) im1->push_back(new_imerge); @@ -1463,14 +1468,14 @@ int imerge_list_or_tree(RANGE_OPT_PARAM *param, if (or_tree) { uint elems= imerge->trees_next-imerge->trees; - rc= imerge->or_sel_tree_with_checks(param, or_tree, + rc= imerge->or_sel_tree_with_checks(param, elems, or_tree, TRUE, &is_last_check_pass); if (!is_last_check_pass) { SEL_IMERGE *new_imerge= new SEL_IMERGE(imerge, elems, param); if (new_imerge) { - rc1= new_imerge->or_sel_tree_with_checks(param, or_tree, + rc1= new_imerge->or_sel_tree_with_checks(param, elems, or_tree, FALSE, &is_last_check_pass); if (!rc1) additional_merges.push_back(new_imerge); |