bk commit into 5.0 tree (mhansson:1.2432) BUG#27573

[mhansson]

Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-04-11 14:33:09+03:00, mhansson@stripped +3 -0
  Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results 
  to NULL
  
  We have the following case:
  
  SELECT MIN(keypart2),... FROM t1 WHERE keypart1 = constant;
  
  There is an optimization which will substitute a constant for
  MIN(keypart2), inserting the 1) minimum non-null value where the 
  WHERE clause still matches, or 2) null if there are only NULL 
  values for keypart2. If no row matches, the result will be 3) a row
  of NULLs.
  
  The previous implementation skipped step 2, and went straight to
  a row of NULLs.

  mysql-test/r/func_group.result@stripped, 2007-04-11 14:33:03+03:00, mhansson@stripped
+11 -0
    Bug #27573: MIN() on an indexed column which is always NULL sets
     _other_ results to NULL
    
    Correct result

  mysql-test/t/func_group.test@stripped, 2007-04-11 14:33:03+03:00, mhansson@stripped
+14 -0
    Bug #27573: MIN() on an indexed column which is always NULL sets
     _other_ results to NULL
    
    test case

  sql/opt_sum.cc@stripped, 2007-04-11 14:33:04+03:00, mhansson@stripped +60 -9
    Bug #27573: MIN() on an indexed column which is always NULL sets
     _other_ results to NULL
    
    Added function read_next_or_current that will try to read the
    first non-null value for a given complete-field prefix, second
    choice is to read the null, and lastly return an error if no row
    is found.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	mhansson
# Host:	linux-st28.site
# Root:	/home/martin/mysql/src/5.0o-bug27573

--- 1.61/sql/opt_sum.cc	2007-01-30 23:09:25 +02:00
+++ 1.62/sql/opt_sum.cc	2007-04-11 14:33:04 +03:00
@@ -51,6 +51,8 @@ static bool find_key_for_maxmin(bool max
 static int reckey_in_range(bool max_fl, TABLE_REF *ref, Field* field,
                             COND *cond, uint range_fl, uint prefix_len);
 static int maxmin_in_range(bool max_fl, Field* field, COND *cond);
+static int read_next_or_current(TABLE *table, byte *key_buffer,
+                                int key_length, bool is_nullable);
 
 
 /*
@@ -206,12 +208,10 @@ int opt_sum_query(TABLE_LIST *tables, Li
 
           if (!ref.key_length)
             error= table->file->index_first(table->record[0]);
-          else
-	    error= table->file->index_read(table->record[0],key_buff,
-					   ref.key_length,
-					   range_fl & NEAR_MIN ?
-					   HA_READ_AFTER_KEY :
-					   HA_READ_KEY_OR_NEXT);
+          else 
+            error= read_next_or_current(table, key_buff, ref.key_length, 
+                                        range_fl & NEAR_MIN);
+
 	  if (!error && reckey_in_range(0, &ref, item_field->field, 
 			                conds, range_fl, prefix_len))
 	    error= HA_ERR_KEY_NOT_FOUND;
@@ -294,6 +294,7 @@ int opt_sum_query(TABLE_LIST *tables, Li
           if (!ref.key_length)
             error= table->file->index_last(table->record[0]);
           else
+            // This can never happen
 	    error= table->file->index_read(table->record[0], key_buff,
 					   ref.key_length,
 					   range_fl & NEAR_MAX ?
@@ -739,9 +740,12 @@ static bool find_key_for_maxmin(bool max
           if (!max_fl && key_part_used == key_part_to_use &&
part->null_bit)
           {
             /*
-              SELECT MIN(key_part2) FROM t1 WHERE key_part1=const
-              If key_part2 may be NULL, then we want to find the first row
-              that is not null
+              SELECT MIN(key_part2) FROM t1 WHERE key_part1=const If
+              key_part2 may be NULL, then we want to find the first
+              row that is not null. So we set the first byte that
+              comes after key_part1 to 1. This means that the key
+              buffer now contains {const, NULL}. This will be used for
+              index lookup later.
             */
             ref->key_buff[ref->key_length]= 1;
             ref->key_length+= part->store_length;
@@ -858,3 +862,50 @@ static int maxmin_in_range(bool max_fl, 
   return 0;
 }
 
+/*
+  Try to read the next key from the the table's index, or the given
+  key if none exists.
+
+  SYNOPSIS
+    read_next_or_current()
+    table         in     The table that the key belongs to.
+    key_buffer    in/out Complete-field prefix of a key in handler format.
+    key_length    in     Length of the buffer
+    is_nullable   in     Used to indicate that the last keypart is a nullable
+                         field, see below.
+  DESCRIPTION
+    This function is used to read the minimum value for a complete-field prefix
+    key. If the n:th keypart is indicated nullable, an attempt will be made to 
+    read the first non-null field matching the prefix before actually reading a 
+    NULL value.
+                         
+  RETURN
+    0      A record was successfully read from the index.
+    other  An error from the handler, see handler::index_read.
+ */
+static int read_next_or_current(TABLE *table, byte *key_buffer,
+                                int key_length, bool is_nullable)
+{
+  int error;
+  handler *file= table->file;
+  /*
+    We are looking for the first record in the index that is
+    after the index record { c, NULL } if one
+    exists. Otherwise we want the record { c, NULL }.
+  */
+  enum ha_rkey_function read_function=
+    is_nullable ? HA_READ_AFTER_KEY : HA_READ_KEY_OR_NEXT;
+  error= 
+    file->index_read(table->record[0], key_buffer, key_length, read_function);
+
+  if (error == HA_ERR_KEY_NOT_FOUND)
+    /* 
+       Our search for the record after the sought one came up with
+       nothing so we try again by taking a step back in the index, in
+       case key_field2 is null for every row. If that is the case,
+       we'll find it now.
+    */              
+    error= file->index_read(table->record[0], key_buffer, key_length,
+                            HA_READ_PREFIX_LAST_OR_PREV );
+  return error;
+}

--- 1.59/mysql-test/r/func_group.result	2007-02-27 09:01:57 +02:00
+++ 1.60/mysql-test/r/func_group.result	2007-04-11 14:33:03 +03:00
@@ -1321,4 +1321,15 @@ SELECT a,AVG(DISTINCT b) AS average FROM
 a	average
 1	32768.5000
 DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+SELECT MIN(a), MIN(b) FROM t1;
+MIN(a)	MIN(b)
+NULL	1
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 );
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b)	MIN(c)
+3	2
+DROP TABLE t1, t2;
 End of 5.0 tests

--- 1.54/mysql-test/t/func_group.test	2007-02-27 09:01:57 +02:00
+++ 1.55/mysql-test/t/func_group.test	2007-04-11 14:33:03 +03:00
@@ -817,5 +817,19 @@ SELECT a,AVG(DISTINCT b) AS average FROM
 
 DROP TABLE t1;
 
+#
+# Bug #27573: MIN() on an indexed column which is always NULL sets _other_ 
+# results to NULL
+#
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+SELECT MIN(a), MIN(b) FROM t1;
+
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 );
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+DROP TABLE t1, t2;
+
 ###
 --echo End of 5.0 tests