List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:January 28 2011 11:18am
Subject:bzr commit into mysql-trunk branch (olav.sandstaa:3565) Bug#59186
View as plain text  
#At file:///home/olav/mysql/develop/trunk-tmp/ based on revid:olav.sandstaa@stripped

 3565 Olav Sandstaa	2011-01-28
      Fix for Bug#59186 Wrong results of join when ICP is enabled
      
      When index condition pushdown was used an extra row appeared in the
      result set due to parts of the select condition for the second table
      in the JOIN was not evaluated. 
      
      This was caused when computing the "remainder" for the second table's
      select condition after pushing down parts of it to the storage
      engine. If parts of the select condition was common for both tables in
      the JOIN and the common part was pushed down for the first table the
      common part could have the marker field set to
      ICP_COND_USES_INDEX_ONLY during evaluation of make_cond_for_index()
      for the first table. If the common part of the select condition was
      not pushed down for the second table it would still be marked with
      ICP_COND_USES_INDEX_ONLY when computing the remainder for the select
      condition for the second table. This would cause that this part of the
      select condition neither was pushed down to the storage engine nor
      included in the select condition to be evaluated by the server.
      
      The fix for this is to extend make_cond_for_index() so that it clears
      the marker field for the parts of the item tree that it decides should
      not be pushed down to the storage engine. This will prevent that
      common items in select conditions for different tables that have been
      marked with ICP_COND_USES_INDEX_ONLY when evaluating one table does
      not keep this value when computing the "remainder" for a following
      table in a JOIN.
      
      The patch also contains some re-write and clean-up code to 
      make_cond_for_index():
      -remove unnessary cast for return of NULL.
      -ensure that make_cond_for_index() is always called with a cond value.
     @ mysql-test/include/icp_tests.inc
        Test case for Bug#59186 Wrong results of join when ICP is enabled.
     @ mysql-test/r/innodb_icp.result
        Result file for the test for Bug#59186 Wrong results of join when 
        ICP is enabled.
     @ mysql-test/r/innodb_icp_none.result
        Result file for the test for Bug#59186 Wrong results of join when 
        ICP is enabled.
     @ mysql-test/r/myisam_icp.result
        Result file for the test for Bug#59186 Wrong results of join when 
        ICP is enabled.
     @ mysql-test/r/myisam_icp_none.result
        Result file for the test for Bug#59186 Wrong results of join when 
        ICP is enabled.
     @ sql/sql_select.cc
        Reset the condition's marker field in make_cond_for_index() when it has
        determined that this part of the select condition should not be
        included in the condition to be pushed down to the storage engine. 
        
        The reason this must be cleared is that if the condition
        is a common part of for the select condition of two tables in
        a JOIN operation then the marker field might have gotten the value
        set to ICP_COND_USES_INDEX_ONLY when evaluating the select
        condition for the first table. If this is the case we need
        to reset it to avoid that this part of the select condition
        is wrongly concluded to not be needed in the select condition
        to be evaluated by the server after pushing parts of the
        condition down to the storage engine.

    modified:
      mysql-test/include/icp_tests.inc
      mysql-test/r/innodb_icp.result
      mysql-test/r/innodb_icp_none.result
      mysql-test/r/myisam_icp.result
      mysql-test/r/myisam_icp_none.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	2011-01-28 10:21:56 +0000
+++ b/mysql-test/include/icp_tests.inc	2011-01-28 11:18:49 +0000
@@ -726,6 +726,37 @@ WHERE NOT EXISTS
 DROP TABLE t1,t2;
 
 --echo #
+--echo # Bug#59186 Wrong results of join when ICP is enabled
+--echo #
+
+CREATE TABLE t1 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(3) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1 VALUES (1,'y'),(0,'or');
+
+CREATE TABLE t2 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(3) NOT NULL,
+  c2 VARCHAR(6) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
+
+let query=
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+      (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo #
 --echo # Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 
 --echo #            ICP is enabled"
 --echo # 

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	2011-01-28 10:21:56 +0000
+++ b/mysql-test/r/innodb_icp.result	2011-01-28 11:18:49 +0000
@@ -673,6 +673,33 @@ id	select_type	table	type	possible_keys
 
 DROP TABLE t1,t2;
 #
+# Bug#59186 Wrong results of join when ICP is enabled
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'y'),(0,'or');
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+c2 VARCHAR(6) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
+EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using index condition
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using join buffer (BNL, incremental buffers)
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+c2
+DROP TABLE t1, t2;
+#
 # Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 
 #            ICP is enabled"
 # 

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	2011-01-28 10:21:56 +0000
+++ b/mysql-test/r/innodb_icp_none.result	2011-01-28 11:18:49 +0000
@@ -672,6 +672,33 @@ id	select_type	table	type	possible_keys
 
 DROP TABLE t1,t2;
 #
+# Bug#59186 Wrong results of join when ICP is enabled
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'y'),(0,'or');
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+c2 VARCHAR(6) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
+EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	1	Using where
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+c2
+DROP TABLE t1, t2;
+#
 # Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 
 #            ICP is enabled"
 # 

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	2011-01-28 10:21:56 +0000
+++ b/mysql-test/r/myisam_icp.result	2011-01-28 11:18:49 +0000
@@ -671,6 +671,33 @@ id	select_type	table	type	possible_keys
 
 DROP TABLE t1,t2;
 #
+# Bug#59186 Wrong results of join when ICP is enabled
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'y'),(0,'or');
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+c2 VARCHAR(6) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
+EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using where; Using join buffer (BNL, incremental buffers)
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+c2
+DROP TABLE t1, t2;
+#
 # Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 
 #            ICP is enabled"
 # 

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	2011-01-28 10:21:56 +0000
+++ b/mysql-test/r/myisam_icp_none.result	2011-01-28 11:18:49 +0000
@@ -670,6 +670,33 @@ id	select_type	table	type	possible_keys
 
 DROP TABLE t1,t2;
 #
+# Bug#59186 Wrong results of join when ICP is enabled
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'y'),(0,'or');
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(3) NOT NULL,
+c2 VARCHAR(6) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES (6,'y','RPOYT'),(10,'m','JINQE');
+EXPLAIN SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1
+WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR
+(t1.pk > 1 AND t2.pk BETWEEN 6 AND 6);
+c2
+DROP TABLE t1, t2;
+#
 # Bug#58838 "Wrong results with HAVING + LIMIT without GROUP BY when 
 #            ICP is enabled"
 # 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-28 10:21:56 +0000
+++ b/sql/sql_select.cc	2011-01-28 11:18:49 +0000
@@ -10036,8 +10036,8 @@ static bool uses_index_fields_only(Item
 Item *make_cond_for_index(Item *cond, TABLE *table, uint keyno,
                           bool other_tbls_ok)
 {
-  if (!cond)
-    return NULL;
+  DBUG_ASSERT(cond != NULL);
+
   if (cond->type() == Item::COND_ITEM)
   {
     uint n_marked= 0;
@@ -10046,7 +10046,7 @@ Item *make_cond_for_index(Item *cond, TA
       table_map used_tables= 0;
       Item_cond_and *new_cond=new Item_cond_and;
       if (!new_cond)
-	return (Item*) 0;
+	return NULL;
       List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
       Item *item;
       while ((item=li++))
@@ -10063,7 +10063,7 @@ Item *make_cond_for_index(Item *cond, TA
         cond->marker= ICP_COND_USES_INDEX_ONLY;
       switch (new_cond->argument_list()->elements) {
       case 0:
-	return (Item*) 0;
+	return NULL;
       case 1:
         new_cond->used_tables_cache= used_tables;
 	return new_cond->argument_list()->head();
@@ -10077,14 +10077,14 @@ Item *make_cond_for_index(Item *cond, TA
     {
       Item_cond_or *new_cond=new Item_cond_or;
       if (!new_cond)
-	return (Item*) 0;
+	return NULL;
       List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
       Item *item;
       while ((item=li++))
       {
 	Item *fix= make_cond_for_index(item, table, keyno, other_tbls_ok);
 	if (!fix)
-	  return (Item*) 0;
+	  return NULL;
 	new_cond->argument_list()->push_back(fix);
         n_marked += test(item->marker == ICP_COND_USES_INDEX_ONLY);
       }
@@ -10098,7 +10098,15 @@ Item *make_cond_for_index(Item *cond, TA
   }
 
   if (!uses_index_fields_only(cond, table, keyno, other_tbls_ok))
-    return (Item*) 0;
+  {
+    /* 
+      Reset marker since it might have the value
+      ICP_COND_USES_INDEX_ONLY if this condition is part of the select
+      condition for multiple tables.
+    */
+    cond->marker= 0;
+    return NULL;
+  }
   cond->marker= ICP_COND_USES_INDEX_ONLY;
   return cond;
 }
@@ -10182,11 +10190,11 @@ Item *make_cond_remainder(Item *cond, bo
 static void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok)
 {
   DBUG_ENTER("push_index_cond");
-  Item *idx_cond;
 
   /*
     We will only attempt to push down an index condition when the
     following criteria are true:
+    0. The table has a select condition
     1. The storage engine supports ICP.
     2. The system variable for enabling ICP is ON.
     3. The query is not a multi-table update or delete statement. The reason
@@ -10196,7 +10204,8 @@ static void push_index_cond(JOIN_TAB *ta
        when doing the update part and result in either not finding
        the record to update or updating the wrong record.
   */
-  if (tab->table->file->index_flags(keyno, 0, 1) &
+  if (tab->select_cond &&
+      tab->table->file->index_flags(keyno, 0, 1) &
       HA_DO_INDEX_COND_PUSHDOWN &&
       tab->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN) &&
       tab->join->thd->lex->sql_command != SQLCOM_UPDATE_MULTI &&
@@ -10204,8 +10213,8 @@ static void push_index_cond(JOIN_TAB *ta
   {
     DBUG_EXECUTE("where", print_where(tab->select_cond, "full cond",
                  QT_ORDINARY););
-    idx_cond= make_cond_for_index(tab->select_cond, tab->table, keyno,
-                                  other_tbls_ok);
+    Item *idx_cond= make_cond_for_index(tab->select_cond, tab->table, keyno,
+                                        other_tbls_ok);
     DBUG_EXECUTE("where", print_where(idx_cond, "idx cond", QT_ORDINARY););
     if (idx_cond)
     {


Attachment: [text/bzr-bundle] bzr/olav.sandstaa@oracle.com-20110128111849-09ukiz10ng0dkv6n.bundle
Thread
bzr commit into mysql-trunk branch (olav.sandstaa:3565) Bug#59186Olav Sandstaa28 Jan