fix for the bug #2419: order by ignores rows.

null_ref_key moved to TABLE_REF.
new null range created if necessary.


mysql-test/r/order_by.result:
  fix for the bug #2419: order by ignores rows
mysql-test/t/order_by.test:
  fix for the bug #2419: order by ignores rows
sql/item_subselect.cc:
  fix for the bug #2419: order by ignores rows
sql/opt_range.cc:
  fix for the bug #2419: order by ignores rows
sql/sql_select.cc:
  fix for the bug #2419: order by ignores rows
sql/sql_select.h:
  fix for the bug #2419: order by ignores rows

4 files changed, 32 insertions, 12 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 518b712ad18..5b3cc326679 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1110,7 +1110,7 @@ int subselect_indexsubquery_engine::exec()
   if (check_null)
   {
     /* We need to check for NULL if there wasn't a matching value */
-    *tab->null_ref_key= 0;			// Search first for not null
+    *tab->ref.null_ref_key= 0;			// Search first for not null
     ((Item_in_subselect *) item)->was_null= 0;
   }
 
@@ -1155,7 +1155,7 @@ int subselect_indexsubquery_engine::exec()
 	{
 	  if (!check_null || null_finding)
 	    break;			/* We don't need to check nulls */
-	  *tab->null_ref_key= 1;
+	  *tab->ref.null_ref_key= 1;
 	  null_finding= 1;
 	  /* Check if there exists a row with a null value in the index */
 	  if ((error= (safe_index_read(tab) == 1)))
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index acc23924f75..9f56064cfb1 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2513,8 +2513,25 @@ QUICK_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, TABLE_REF *ref)
       key_part->part_length+=HA_KEY_BLOB_LENGTH;
     key_part->null_bit=     key_info->key_part[part].null_bit;
   }
-  if (!quick->ranges.push_back(range))
-    return quick;
+  if (quick->ranges.push_back(range))
+    goto err;
+
+  if (ref->null_ref_key)
+  {
+    QUICK_RANGE *null_range;
+
+    *ref->null_ref_key= 1;		// Set null byte then create a range
+    if (!(null_range= new QUICK_RANGE(ref->key_buff, ref->key_length,
+				      ref->key_buff, ref->key_length,
+				      EQ_RANGE)))
+      goto err;
+    *ref->null_ref_key= 0;		// Clear null byte
+    /* Do we need to do something with key_parts here? Looks like we don't */
+    if (quick->ranges.push_back(null_range))
+      goto err;
+  }
+
+  return quick;
 
 err:
   delete quick;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 874a3b16312..5e2445be845 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -860,8 +860,10 @@ JOIN::optimize()
     as in other cases the join is done before the sort.
   */
   if (const_tables != tables &&
-      (order || group_list) && join_tab[const_tables].type != JT_ALL &&
+      (order || group_list) && 
+      join_tab[const_tables].type != JT_ALL &&
       join_tab[const_tables].type != JT_FT &&
+      join_tab[const_tables].type != JT_REF_OR_NULL &&
       (order && simple_order || group_list && simple_group))
   {
     if (add_ref_to_table_cond(thd,&join_tab[const_tables]))
@@ -3257,7 +3259,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   {
     /* Must read with repeat */
     j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
-    j->null_ref_key= null_ref_key;
+    j->ref.null_ref_key= null_ref_key;
   }
   else if (ref_key == j->ref.key_copy)
   {
@@ -6208,12 +6210,12 @@ join_read_always_key_or_null(JOIN_TAB *tab)
   int res;
 
   /* First read according to key which is NOT NULL */
-  *tab->null_ref_key=0;
+  *tab->ref.null_ref_key= 0;			// Clear null byte
   if ((res= join_read_always_key(tab)) >= 0)
     return res;
 
   /* Then read key with null value */
-  *tab->null_ref_key= 1;
+  *tab->ref.null_ref_key= 1;			// Set null byte
   return safe_index_read(tab);
 }
 
@@ -6227,10 +6229,10 @@ join_read_next_same_or_null(READ_RECORD *info)
   JOIN_TAB *tab= info->table->reginfo.join_tab;
 
   /* Test if we have already done a read after null key */
-  if (*tab->null_ref_key)
+  if (*tab->ref.null_ref_key)
     return -1;					// All keys read
-  *tab->null_ref_key= 1;			// Read null key
-  return safe_index_read(tab);
+  *tab->ref.null_ref_key= 1;			// Set null byte
+  return safe_index_read(tab);			// then read null keys
 }
 
 
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 7cc71117914..ed650c450c0 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -46,6 +46,8 @@ typedef struct st_table_ref
   store_key     **key_copy;               //
   Item          **items;                  // val()'s for each keypart
   table_map	depend_map;		  // Table depends on these tables.
+  byte          *null_ref_key;		  // null byte position in the key_buf.
+  					  // used for REF_OR_NULL optimization.
 } TABLE_REF;
 
 /*
@@ -88,7 +90,6 @@ typedef struct st_join_table {
   QUICK_SELECT	*quick;
   Item		*on_expr;
   const char	*info;
-  byte		*null_ref_key;
   int		(*read_first_record)(struct st_join_table *tab);
   int		(*next_select)(JOIN *,struct st_join_table *,bool);
   READ_RECORD	read_record;