Bug #34945 ref_or_null queries that are null_rejecting and have a null value crash mysql

Description:
A query that does a ref_or_null join where the second table has key part that could be null and a value that is null crashes mysql. 

join_read_always_key() can return before calling ha_index_init(). After that join_read_always_key_or_null will try to call safe_index_read() to read the index which crashes because the index was never initialized. The patch moves the index initialization before the null check in join_read_always_key() so the index is initialized in both cases. 

How to repeat:
CREATE TABLE `bar` (
  `a` int(11) default NULL,
  `b` int(11) default NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `bar` VALUES (0,10),(0,11),(0,12);

CREATE TABLE `foo` (
  `a` int(11) default NULL,
  `b` int(11) default NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `foo` VALUES (3,NULL),(3,11),(3,12);

SELECT * FROM foo inner join bar WHERE ( bar.a = 0 OR bar.a IS NULL) AND foo.a = 3 AND foo.b = bar.b ;

Suggested fix:
diff -urN mysql-5.0.51a-orig/sql/sql_select.cc mysql-5.0.51a/sql/sql_select.cc
--- mysql-5.0.51a-orig/sql/sql_select.cc	2008-01-11 06:43:13.000000000 -0800
+++ mysql-5.0.51a/sql/sql_select.cc	2008-02-28 14:33:37.000000000 -0800
@@ -11116,13 +11116,15 @@
   int error;
   TABLE *table= tab->table;
 
+  if (!table->file->inited)
+    table->file->ha_index_init(tab->ref.key);
+
   for (uint i= 0 ; i < tab->ref.key_parts ; i++)
   {
     if ((tab->ref.null_rejecting & 1 << i) && tab->ref.items[i]->is_null())
         return -1;
-  } 
-  if (!table->file->inited)
-    table->file->ha_index_init(tab->ref.key);
+  }
+
   if (cp_buffer_from_ref(tab->join->thd, &tab->ref))
     return -1;
   if ((error=table->file->index_read(table->record[0],

Thank you for the report.

Verified as described.

More details: the crash is caused by failed assertion. In release builds, attempts do do index lookups before initializing the index cause errors like this:

ERROR 126: Incorrect key file for table '%s'; try to repair it

.
The bug was introduced by fix for BUG#12144.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43328

ChangeSet@1.2598, 2008-03-03 20:35:44+03:00, sergefp@mysql.com +3 -0
  BUG#34945: "ref_or_null queries that are null_rejecting and have a null value crash mysql"
  - Apply Eric Bergen's patch: in join_read_always_key(), move ha_index_init() call
    to before the late NULLs filtering code.
  - Backport function comments from 6.0.

The suggested fix is correct. Thanks Eric ! :-)

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Documented bugfix in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        A query that performed a ref_or_null join where the second table used a
        key having one or columns that could be NULL and had a column value that
        was NULL caused the server to crash.