#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#59186 | Olav Sandstaa | 28 Jan |
