bzr commit into mysql-trunk-innodb branch (marko.makela:3417) Bug#59259

[marko.makela]

#At file:///home/marko/innobase/dev/mysql2a/5.6-innodb/ based on revid:marko.makela@strippedi2cly5l7r6gy

 3417 Marko Mäkelä	2011-01-12
      Bug #59259 Incorrect rows returned for a correlated subquery when ICP is on
      
      row_search_for_mysql(): After a successful adaptive hash index lookup,
      test the index condition pushdown condition if there is one.

    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
      storage/innobase/row/row0sel.c
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc	revid:marko.makela@stripped
+++ b/mysql-test/include/icp_tests.inc	revid:[e-mail]-20110112134710-k1e2q8fowpfgxyb3
@@ -653,3 +653,25 @@ create table t1 (a char(2) charset utf8,
 insert into t1 values ('',1);
 select 1 from t1 where b <= 1 and a <> '';
 drop table t1;
+
+--echo #
+--echo # Bug#59259 "Incorrect rows returned for a correlated subquery
+--echo #            when ICP is on"
+--echo #
+
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (11,0);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (15,0);
+
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (12,2);
+INSERT INTO t2 VALUES (15,4);
+
+SELECT * FROM t1
+WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result	revid:marko.makela@stripped10110134032-o6wgi2cly5l7r6gy
+++ b/mysql-test/r/innodb_icp.result	revid:marko.makela@stripped2q8fowpfgxyb3
@@ -598,5 +598,22 @@ insert into t1 values ('',1);
 select 1 from t1 where b <= 1 and a <> '';
 1
 drop table t1;
+#
+# Bug#59259 "Incorrect rows returned for a correlated subquery
+#            when ICP is on"
+#
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (11,0);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (15,0);
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (12,2);
+INSERT INTO t2 VALUES (15,4);
+SELECT * FROM t1
+WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
+pk	i
+12	5
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result	revid:marko.makela@stripped134032-o6wgi2cly5l7r6gy
+++ b/mysql-test/r/innodb_icp_none.result	revid:marko.makela@stripped2q8fowpfgxyb3
@@ -597,5 +597,22 @@ insert into t1 values ('',1);
 select 1 from t1 where b <= 1 and a <> '';
 1
 drop table t1;
+#
+# Bug#59259 "Incorrect rows returned for a correlated subquery
+#            when ICP is on"
+#
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (11,0);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (15,0);
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (12,2);
+INSERT INTO t2 VALUES (15,4);
+SELECT * FROM t1
+WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
+pk	i
+12	5
+DROP TABLE t1, t2;
 set default_storage_engine= @save_storage_engine;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result	revid:marko.makela@strippedgi2cly5l7r6gy
+++ b/mysql-test/r/myisam_icp.result	revid:marko.makela@stripped
@@ -596,4 +596,21 @@ insert into t1 values ('',1);
 select 1 from t1 where b <= 1 and a <> '';
 1
 drop table t1;
+#
+# Bug#59259 "Incorrect rows returned for a correlated subquery
+#            when ICP is on"
+#
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (11,0);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (15,0);
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (12,2);
+INSERT INTO t2 VALUES (15,4);
+SELECT * FROM t1
+WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
+pk	i
+12	5
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result	revid:marko.makela@strippedwgi2cly5l7r6gy
+++ b/mysql-test/r/myisam_icp_none.result	revid:marko.makela@strippedxyb3
@@ -595,4 +595,21 @@ insert into t1 values ('',1);
 select 1 from t1 where b <= 1 and a <> '';
 1
 drop table t1;
+#
+# Bug#59259 "Incorrect rows returned for a correlated subquery
+#            when ICP is on"
+#
+CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (11,0);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (15,0);
+CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER NOT NULL) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (11,1);
+INSERT INTO t2 VALUES (12,2);
+INSERT INTO t2 VALUES (15,4);
+SELECT * FROM t1
+WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE t1.i);
+pk	i
+12	5
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'storage/innobase/row/row0sel.c'
--- a/storage/innobase/row/row0sel.c	revid:marko.makela@strippedcly5l7r6gy
+++ b/storage/innobase/row/row0sel.c	revid:marko.makela@stripped
@@ -3797,6 +3797,18 @@ row_search_for_mysql(
 				mtr_commit(&mtr). */
 				ut_ad(!rec_get_deleted_flag(rec, comp));
 
+				if (prebuilt->idx_cond) {
+					switch (row_search_idx_cond_check(
+							buf, prebuilt,
+							rec, offsets)) {
+					case ICP_NO_MATCH:
+					case ICP_OUT_OF_RANGE:
+						goto shortcut_mismatch;
+					case ICP_MATCH:
+						goto shortcut_match;
+					}
+				}
+
 				if (!row_sel_store_mysql_rec(
 					    buf, prebuilt,
 					    rec, FALSE, index, offsets)) {
@@ -3815,6 +3827,7 @@ row_search_for_mysql(
 					break;
 				}
 
+			shortcut_match:
 				mtr_commit(&mtr);
 
 				/* ut_print_name(stderr, index->name);
@@ -3826,6 +3839,7 @@ row_search_for_mysql(
 				goto release_search_latch_if_needed;
 
 			case SEL_EXHAUSTED:
+			shortcut_mismatch:
 				mtr_commit(&mtr);
 
 				/* ut_print_name(stderr, index->name);

Attachment: [text/bzr-bundle] bzr/[e-mail]