bzr commit into mysql-5.0-bugteam branch (gshchepa:2662) Bug#37894

[Gleb Shchepa]

#At file:///work/bzr/5.0-bugteam-37894/

 2662 Gleb Shchepa	2008-10-06
      Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
      
      Select with a "NULL NOT IN" condition containing complex
      subselect from the same table as in the outer select failed
      with an assertion.
      
      
      The failure was caused by a concatenation of circumstances:
      1) an inner select was optimized by make_join_statistics to use
         the QUICK_RANGE_SELECT access method (that implies an index
         scan of the table);
      2) a subselect was independent (constant) from the outer select;
      3) a condition was pushed down into inner select.
      
      During the evaluation of a constant IN expression an optimizer
      temporary changed the access method from index scan to table
      scan, but an engine handler was already initialized for index
      access by make_join_statistics. That caused an assertion.
      
      
      Unnecessary index initialization has been removed from
      the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
      reinvokes this initialization).
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/opt_range.cc

per-file messages:
  mysql-test/r/subselect3.result
    Added test case for bug #37894.
  mysql-test/t/subselect3.test
    Added test case for bug #37894.
  sql/opt_range.cc
    Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
    
    Unnecessary index initialization has been removed from
    the QUICK_RANGE_SELECT::init method (QUICK_RANGE_SELECT::reset
    reinvokes this initialization).
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-04-22 21:27:23 +0000
+++ b/mysql-test/r/subselect3.result	2008-10-06 11:25:36 +0000
@@ -779,4 +779,20 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN (SELE
 1
 1
 DROP TABLE t1, t2;
+CREATE TABLE t1 (
+pk INT PRIMARY KEY,
+int_key INT,
+varchar_key VARCHAR(5) UNIQUE,
+varchar_nokey VARCHAR(5)
+);
+INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p');
+SELECT varchar_nokey
+FROM t1
+WHERE NULL NOT IN (
+SELECT INNR.pk FROM t1 AS INNR2
+LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key )
+WHERE INNR.varchar_key > 'n{'
+);
+varchar_nokey
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-04-22 21:27:23 +0000
+++ b/mysql-test/t/subselect3.test	2008-10-06 11:25:36 +0000
@@ -618,4 +618,26 @@ SELECT 1 FROM t1 WHERE t1.a NOT IN (SELE
 
 DROP TABLE t1, t2;
 
+#
+# Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
+#
+
+CREATE TABLE t1 (
+  pk INT PRIMARY KEY,
+  int_key INT,
+  varchar_key VARCHAR(5) UNIQUE,
+  varchar_nokey VARCHAR(5)
+);
+INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p');
+
+SELECT varchar_nokey
+FROM t1
+WHERE NULL NOT IN (
+ SELECT INNR.pk FROM t1 AS INNR2
+   LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key )
+   WHERE INNR.varchar_key > 'n{'
+);
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2008-07-23 11:25:00 +0000
+++ b/sql/opt_range.cc	2008-10-06 11:25:36 +0000
@@ -945,7 +945,7 @@ int QUICK_RANGE_SELECT::init()
 
   if (file->inited != handler::NONE)
     file->ha_index_or_rnd_end();
-  DBUG_RETURN(error= file->ha_index_init(index));
+  DBUG_RETURN(FALSE);
 }