[Commits] Rev 2926: Fixed bug #637978. in file:///home/igor/maria/maria-5.1-wl24/

igor at askmonty.org igor at askmonty.org
Wed Sep 15 23:06:49 EEST 2010


At file:///home/igor/maria/maria-5.1-wl24/

------------------------------------------------------------
revno: 2926
revision-id: igor at askmonty.org-20100915200642-04uemy2m6lzy8yd2
parent: igor at askmonty.org-20100912065651-o6nidxscldhuooo2
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.1-wl24
timestamp: Wed 2010-09-15 13:06:42 -0700
message:
  Fixed bug #637978.
  Fixed a bug in the new code for WL#24 that caused generation of
  an invalid index-merge access plan.
-------------- next part --------------
=== modified file 'mysql-test/r/range_vs_index_merge.result'
--- a/mysql-test/r/range_vs_index_merge.result	2009-10-29 18:49:58 +0000
+++ b/mysql-test/r/range_vs_index_merge.result	2010-09-15 20:06:42 +0000
@@ -1360,3 +1360,29 @@
 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;

=== modified file 'mysql-test/r/range_vs_index_merge_innodb.result'
--- a/mysql-test/r/range_vs_index_merge_innodb.result	2010-09-01 04:15:20 +0000
+++ b/mysql-test/r/range_vs_index_merge_innodb.result	2010-09-15 20:06:42 +0000
@@ -1361,4 +1361,30 @@
 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;

=== modified file 'mysql-test/t/range_vs_index_merge.test'
--- a/mysql-test/t/range_vs_index_merge.test	2009-10-29 18:49:58 +0000
+++ b/mysql-test/t/range_vs_index_merge.test	2010-09-15 20:06:42 +0000
@@ -846,7 +846,75 @@
 
 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;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-09-12 06:56:51 +0000
+++ b/sql/opt_range.cc	2010-09-15 20:06:42 +0000
@@ -890,10 +890,12 @@
   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 @@
 
   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::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 @@
               (key1)->test_use_count(key1);
             }
 #endif
-          }
+          }       
         }
       }
       else if(is_first_check_pass) 
@@ -1152,8 +1155,9 @@
   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_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 @@
   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 @@
     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 @@
     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);



More information about the commits mailing list