Fixed unlikely bug in the range optimzer when using many IN() queries on different key parts. (Bug #4157)

mysql-test/r/range.result:
  Test of range optimizer bug
mysql-test/t/range.test:
  Test of range optimizer bug
sql/opt_range.cc:
  Fixed unlikely bug in the range optimzer when using many IN() queries on
  different key parts. (Bug #4157)

3 files changed, 67 insertions, 9 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index b826dd1b677..028f49a8d16 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -314,3 +314,24 @@ a	b
 15	1
 47	1
 DROP TABLE t1;
+CREATE TABLE t1 (
+id int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,
+line int( 5 ) unsigned NOT NULL default '0',
+columnid int( 3 ) unsigned NOT NULL default '0',
+owner int( 3 ) unsigned NOT NULL default '0',
+ordinal int( 3 ) unsigned NOT NULL default '0',
+showid smallint( 6 ) unsigned NOT NULL default '1',
+tableid int( 1 ) unsigned NOT NULL default '1',
+content int( 5 ) unsigned NOT NULL default '188',
+PRIMARY KEY ( owner, id ) ,
+KEY menu( owner, showid, columnid ) ,
+KEY `COLUMN` ( owner, columnid, line ) ,
+KEY `LINES` ( owner, tableid, content, id ) ,
+KEY recount( owner, line ) 
+) ENGINE = MYISAM;
+INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5);
+SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30;
+id	columnid	tableid	content	showid	line	ordinal
+13	13	1	188	1	5	0
+15	15	1	188	1	1	0
+drop table t1;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 40fb402a457..d67f28a1524 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -264,3 +264,28 @@ WHERE
     (a BETWEEN 19 AND 47)
 );
 DROP TABLE t1;
+
+#
+# Test of problem with IN on many different keyparts. (Bug #4157)
+#
+
+CREATE TABLE t1 (
+id int( 11 ) unsigned NOT NULL AUTO_INCREMENT ,
+line int( 5 ) unsigned NOT NULL default '0',
+columnid int( 3 ) unsigned NOT NULL default '0',
+owner int( 3 ) unsigned NOT NULL default '0',
+ordinal int( 3 ) unsigned NOT NULL default '0',
+showid smallint( 6 ) unsigned NOT NULL default '1',
+tableid int( 1 ) unsigned NOT NULL default '1',
+content int( 5 ) unsigned NOT NULL default '188',
+PRIMARY KEY ( owner, id ) ,
+KEY menu( owner, showid, columnid ) ,
+KEY `COLUMN` ( owner, columnid, line ) ,
+KEY `LINES` ( owner, tableid, content, id ) ,
+KEY recount( owner, line ) 
+) ENGINE = MYISAM;
+
+INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5);
+
+SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30;
+drop table t1;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index dfed08479f9..1bbf967b2bc 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1539,7 +1539,7 @@ key_or(SEL_ARG *key1,SEL_ARG *key2)
     {
       swap(SEL_ARG *,key1,key2);
     }
-    else if (!(key1=key1->clone_tree()))
+    if (key1->use_count > 0 || !(key1=key1->clone_tree()))
       return 0;					// OOM
   }
 
@@ -1608,10 +1608,10 @@ key_or(SEL_ARG *key1,SEL_ARG *key2)
 	  SEL_ARG *next=key2->next;		// Keys are not overlapping
 	  if (key2_shared)
 	  {
-	    SEL_ARG *tmp= new SEL_ARG(*key2);	// Must make copy
-	    if (!tmp)
+	    SEL_ARG *cpy= new SEL_ARG(*key2);	// Must make copy
+	    if (!cpy)
 	      return 0;				// OOM
-	    key1=key1->insert(tmp);
+	    key1=key1->insert(cpy);
 	    key2->increment_use_count(key1->use_count+1);
 	  }
 	  else
@@ -1847,8 +1847,17 @@ SEL_ARG::find_range(SEL_ARG *key)
 
 
 /*
-** Remove a element from the tree
-** This also frees all sub trees that is used by the element
+  Remove a element from the tree
+
+  SYNOPSIS
+    tree_delete()
+    key		Key that is to be deleted from tree (this)
+    
+  NOTE
+    This also frees all sub trees that is used by the element
+
+  RETURN
+    root of new tree (with key deleted)
 */
 
 SEL_ARG *
@@ -1856,7 +1865,10 @@ SEL_ARG::tree_delete(SEL_ARG *key)
 {
   enum leaf_color remove_color;
   SEL_ARG *root,*nod,**par,*fix_par;
-  root=this; this->parent= 0;
+  DBUG_ENTER("tree_delete");
+
+  root=this;
+  this->parent= 0;
 
   /* Unlink from list */
   if (key->prev)
@@ -1903,7 +1915,7 @@ SEL_ARG::tree_delete(SEL_ARG *key)
   }
 
   if (root == &null_element)
-    return 0;					// Maybe root later
+    DBUG_RETURN(0);				// Maybe root later
   if (remove_color == BLACK)
     root=rb_delete_fixup(root,nod,fix_par);
   test_rb_tree(root,root->parent);
@@ -1911,7 +1923,7 @@ SEL_ARG::tree_delete(SEL_ARG *key)
   root->use_count=this->use_count;		// Fix root counters
   root->elements=this->elements-1;
   root->maybe_flag=this->maybe_flag;
-  return root;
+  DBUG_RETURN(root);
 }