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-09-20
      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.
      
      
      The subselect_single_select_engine::exec method has been
      modified to substitute a read_record engine handler with
      its clone for further table scan.
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/item_subselect.cc
  sql/sql_select.h

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/item_subselect.cc
    The subselect_single_select_engine::exec method has been
    modified to substitute a read_record engine handler with
    its clone for further table scan.
  sql/sql_select.h
    The JOIN_TAB::save_file field has been added to save
    substituted read_record.table->file field value.
=== 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-09-19 21:16:05 +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-09-19 21:16:05 +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/item_subselect.cc'
--- a/sql/item_subselect.cc	2008-03-28 11:31:52 +0000
+++ b/sql/item_subselect.cc	2008-09-19 21:16:05 +0000
@@ -1858,6 +1858,18 @@ int subselect_single_select_engine::exec
             bool *cond_guard= tab->ref.cond_guards[i];
             if (cond_guard && !*cond_guard)
             {
+              if (tab->table->file->inited != handler::NONE)
+              {
+                DBUG_ASSERT(!tab->save_file);
+                tab->save_file= tab->read_record.file;
+                if (!(tab->read_record.file=
tab->table->file->clone(thd->mem_root)))
+                {
+                  thd->net.report_error= 1;
+                  DBUG_RETURN(1);
+                }
+                if (tab->read_record.file->ha_external_lock(thd, F_RDLCK))
+                  DBUG_RETURN(1);
+              }
               /* Change the access method to full table scan */
               tab->save_read_first_record= tab->read_first_record;
               tab->save_read_record= tab->read_record.read_record;
@@ -1879,6 +1891,14 @@ int subselect_single_select_engine::exec
     for (JOIN_TAB **ptab= changed_tabs; ptab != last_changed_tab; ptab++)
     {
       JOIN_TAB *tab= *ptab;
+      if (tab->save_file)
+      {
+        if (tab->read_record.file->ha_external_lock(thd, F_UNLCK) ||
+            tab->read_record.file->close())
+          DBUG_RETURN(1);
+        tab->read_record.file= tab->save_file;
+        tab->save_file= 0;
+      }
       tab->read_record.record= 0;
       tab->read_record.ref_length= 0;
       tab->read_first_record= tab->save_read_first_record; 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2007-11-07 16:02:12 +0000
+++ b/sql/sql_select.h	2008-09-19 21:16:05 +0000
@@ -166,6 +166,7 @@ typedef struct st_join_table {
   */  
   Read_record_func save_read_first_record;/* to save read_first_record */ 
   int (*save_read_record) (READ_RECORD *);/* to save read_record.read_record */
+  handler       *save_file;               /* to save read_record.table->file */
   double	worst_seeks;
   key_map	const_keys;			/* Keys with constant part */
   key_map	checked_keys;			/* Keys checked in find_best */