bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:3512) Bug#50402

[Sergey Glukhov]

#At file:///home/gluh/MySQL/mysql-5.1-bugteam/ based on revid:magne.mahre@stripped

 3512 Sergey Glukhov	2010-09-16
      Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB 
      Subselect executes twice, at JOIN::optimize stage
      and at JOIN::execute stage. At optimize stage
      Innodb prebuilt struct which is used for the
      retrieval of column values is initialized in.
      ha_innobase::index_read(), prebuilt->sql_stat_start is true.
      After QUICK_ROR_INTERSECT_SELECT finished his job it
      restores read_set/write_set bitmaps with initial values
      and deactivates one of the handlers used by
      QUICK_ROR_INTERSECT_SELECT in JOIN::cleanup
      (it's the case when we reuse original handler as one of
       handlers required by QUICK_ROR_INTERSECT_SELECT object).
      On second subselect execution inactive handler is activated
      in  QUICK_RANGE_SELECT::reset, file->ha_index_init().
      In ha_index_init Innodb prebuilt struct is reinitialized
      with inappropriate read_set/write_set bitmaps. Further
      reinitialization in ha_innobase::index_read() does not
      happen as prebuilt->sql_stat_start is false.
      It leads to partial retrieval of required field values
      and we get a mix of field values from different records
      in the record buffer.
      The fix is to reset
      read_set/write_set bitmaps as these values
      are required for proper intialization of
      internal InnoDB struct which is used for
      the retrieval of column values
      (see build_template(), ha_innodb.cc)
     @ mysql-test/include/index_merge_ror_cpk.inc
        test case
     @ mysql-test/r/index_merge_innodb.result
        test case
     @ mysql-test/r/index_merge_myisam.result
        test case
     @ sql/opt_range.cc
        if ROR merge scan is used we need to reset
        read_set/write_set bitmaps as these values
        are required for proper intialization of
        internal InnoDB struct which is used for
        the retrieval of column values
        (see build_template(), ha_innodb.cc)

    modified:
      mysql-test/include/index_merge_ror_cpk.inc
      mysql-test/r/index_merge_innodb.result
      mysql-test/r/index_merge_myisam.result
      sql/opt_range.cc
=== modified file 'mysql-test/include/index_merge_ror_cpk.inc'
--- a/mysql-test/include/index_merge_ror_cpk.inc	2006-08-16 12:58:49 +0000
+++ b/mysql-test/include/index_merge_ror_cpk.inc	2010-09-16 12:13:53 +0000
@@ -126,3 +126,19 @@ WHERE
 
 drop table t1;
 
+--echo #
+--echo # Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
+--echo #
+CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
+                 PRIMARY KEY (f1), KEY (f2), KEY (f3) );
+INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
+
+SELECT t1.f1 FROM t1
+WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
+
+EXPLAIN SELECT t1.f1 FROM t1
+WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
+
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result	2006-09-18 14:55:56 +0000
+++ b/mysql-test/r/index_merge_innodb.result	2010-09-16 12:13:53 +0000
@@ -581,3 +581,21 @@ WHERE
 `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
 `TESTID`='' AND `UCCHECK`='';
 drop table t1;
+#
+# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
+#
+CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
+PRIMARY KEY (f1), KEY (f2), KEY (f3) );
+INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
+SELECT t1.f1 FROM t1
+WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
+f1
+2
+EXPLAIN SELECT t1.f1 FROM t1
+WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+2	DEPENDENT SUBQUERY	t2	index_merge	f2,f3	f3,f2	2,5	NULL	1	Using intersect(f3,f2); Using where; Using index
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result	2009-06-15 14:36:51 +0000
+++ b/mysql-test/r/index_merge_myisam.result	2010-09-16 12:13:53 +0000
@@ -1416,6 +1416,24 @@ WHERE
 `TESTID`='' AND `UCCHECK`='';
 drop table t1;
 #
+# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
+#
+CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
+PRIMARY KEY (f1), KEY (f2), KEY (f3) );
+INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
+SELECT t1.f1 FROM t1
+WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
+f1
+2
+EXPLAIN SELECT t1.f1 FROM t1
+WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ref	f2,f3	f2	5		1	Using where
+DROP TABLE t1,t2;
+#
 # Generic @@optimizer_switch tests (move those into a separate file if
 #  we get another @@optimizer_switch user)
 #

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-08-24 15:51:32 +0000
+++ b/sql/opt_range.cc	2010-09-16 12:13:53 +0000
@@ -8451,9 +8451,14 @@ int QUICK_RANGE_SELECT::reset()
   in_range= FALSE;
   cur_range= (QUICK_RANGE**) ranges.buffer;
 
-  if (file->inited == handler::NONE && (error= file->ha_index_init(index,1)))
-    DBUG_RETURN(error);
- 
+  if (file->inited == handler::NONE)
+  {
+    if (in_ror_merged_scan)
+      head->column_bitmaps_set_no_signal(&column_bitmap, &column_bitmap);
+    if ((error= file->ha_index_init(index,1)))
+        DBUG_RETURN(error);
+  }
+
   /* Do not allocate the buffers twice. */
   if (multi_range_length)
   {


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