bzr commit into mysql-5.0-bugteam branch (sergefp:2717) Bug#40974

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-5.0-look/ based on
revid:gshchepa@stripped

 2717 Sergey Petrunia	2008-12-19
      BUG#40974: Incorrect query results when using clause evaluated using range check
      - QUICK_INDEX_MERGE_SELECT deinitializes its rnd_pos() scan when it reaches EOF, but
we 
        need to make the deinitialization in QUICK_INDEX_MERGE_SELECT destructor also.
This is because
        certain execution strategies can stop scanning without reaching EOF, then then try
to do a full
        table scan on this table. Failure to deinitialize caused the full scan to use
(already empty) 
        table->sort and produce zero records.
modified:
  mysql-test/r/index_merge.result
  mysql-test/t/index_merge.test
  sql/opt_range.cc

per-file messages:
  mysql-test/r/index_merge.result
    BUG#40974: Incorrect query results when using clause evaluated using range check
    - Testcase
  mysql-test/t/index_merge.test
    BUG#40974: Incorrect query results when using clause evaluated using range check
    - Testcase
  sql/opt_range.cc
    BUG#40974: Incorrect query results when using clause evaluated using range check
    - QUICK_INDEX_MERGE_SELECT deinitializes its rnd_pos() scan when it reaches EOF, but
we 
      need to make the deinitialization in QUICK_INDEX_MERGE_SELECT destructor also. This
is because
      certain execution strategies can stop scanning without reaching EOF, then then try
to do a full
      table scan on this table. Failure to deinitialize caused the full scan to use
(already empty) 
      table->sort and produce zero records.
=== modified file 'mysql-test/r/index_merge.result'
--- a/mysql-test/r/index_merge.result	2008-10-01 15:50:55 +0000
+++ b/mysql-test/r/index_merge.result	2008-12-19 13:38:39 +0000
@@ -527,4 +527,32 @@ b	a
 	y
 	z
 DROP TABLE t1;
+#
+# BUG#40974: Incorrect query results when using clause evaluated using range check
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 values (1),(2);
+create table t2(a int, b int);
+insert into t2 values (1,1), (2, 1000);
+create table t3 (a int, b int, filler char(100), key(a), key(b));
+insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
+insert into t3 values (1,1,'data');
+insert into t3 values (1,1,'data');
+The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
+explain select * from t1 
+where exists (select 1 from t2, t3 
+where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	a,b	NULL	NULL	NULL	1002	Range checked for each record (index
map: 0x3)
+select * from t1 
+where exists (select 1 from t2, t3 
+where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+a
+1
+2
+drop table t0, t1, t2, t3;
 End of 5.0 tests

=== modified file 'mysql-test/t/index_merge.test'
--- a/mysql-test/t/index_merge.test	2008-10-01 15:50:55 +0000
+++ b/mysql-test/t/index_merge.test	2008-12-19 13:38:39 +0000
@@ -477,4 +477,30 @@ SELECT b,a from t1 WHERE (b!='c' AND b!=
 
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#40974: Incorrect query results when using clause evaluated using range check
+--echo #
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (a int);
+insert into t1 values (1),(2);
+create table t2(a int, b int);
+insert into t2 values (1,1), (2, 1000);
+create table t3 (a int, b int, filler char(100), key(a), key(b));
+
+insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; 
+insert into t3 values (1,1,'data');
+insert into t3 values (1,1,'data');
+-- echo The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
+explain select * from t1 
+where exists (select 1 from t2, t3 
+              where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+
+select * from t1 
+where exists (select 1 from t2, t3 
+              where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
+
+drop table t0, t1, t2, t3;
+
 --echo End of 5.0 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2008-10-10 10:27:58 +0000
+++ b/sql/opt_range.cc	2008-12-19 13:38:39 +0000
@@ -1109,6 +1109,9 @@ QUICK_INDEX_MERGE_SELECT::~QUICK_INDEX_M
     quick->file= NULL;
   quick_selects.delete_elements();
   delete pk_quick_select;
+  /* It's ok to call the next two even if they are already deinitialized */
+  end_read_record(&read_record);
+  free_io_cache(head);
   free_root(&alloc,MYF(0));
   DBUG_VOID_RETURN;
 }