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-20 11:56:28+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-20 11:56:26+03:00, mhansson@stripped
+44 -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-20 11:56:26+03:00, mhansson@stripped
+33 -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-20 11:56:26+03:00, mhansson@stripped +95 -10
    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-20 11:56:26 +03:00
@@ -51,7 +51,9 @@ 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, TABLE_REF *ref, 
+                                bool is_nullable, uint prefix_len,
+                                Field *field);
 
 /*
   Substitutes constants for some COUNT(), MIN() and MAX() functions.
@@ -206,12 +208,11 @@ 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, &ref, 
+                                        range_fl & NEAR_MIN, prefix_len, 
+                                        item_field->field );
+
 	  if (!error && reckey_in_range(0, &ref, item_field->field, 
 			                conds, range_fl, prefix_len))
 	    error= HA_ERR_KEY_NOT_FOUND;
@@ -739,13 +740,23 @@ 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 key buffer will
+              be passed to the handler along with a flag, NEAR_MIN,
+              that tells it to retrieve the record that is stored
+              after this key buffer. This is handled by function
+              read_next_or_current(). This function is smart enough to
+              backtrack and see if the original key {const, NULL} is
+              present if there is no key after it.
             */
             ref->key_buff[ref->key_length]= 1;
             ref->key_length+= part->store_length;
             *range_fl&= ~NO_MIN_RANGE;
+            /* Exclusive interval, we prefer the value after this key */
             *range_fl|= NEAR_MIN;                // > NULL
           }
           /*
@@ -858,3 +869,77 @@ 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.
+
+  @param table       In.      The table that the key belongs to.
+  @param key_buffer  In/Out.  Complete-field prefix of a key in handler format.
+  @param key_length  In.      Length of the buffer
+  @param exclusive   In.      Used to indicate that either the last
+                              keypart is column containing NULLs, see below,
+                              or that we want the smallest value in an
+                              exclusive interval, i.e. using <.
+  @details
+    This function is used to read the minimum value for a
+    complete-field prefix key. If exclusive is true, The next
+    different tuple will be read, if it exists. If it doesn't, the
+    function will backtrack to see if the n:th keypart is NULL for
+    this column.  In that case the record read by backtracking will be
+    returned. Otherwise the index position is restored and
+    HA_ERR_KEY_NOT_FOUND is returned.
+                         
+  @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, TABLE_REF *ref, bool exclusive,
+                                uint prefix_len, Field *field)
+{
+  int error;
+  int key_length= ref->key_length;
+  byte *key_buffer= ref->key_buff;
+  handler *file= table->file;
+  byte* data_buffer= table->record[0];
+
+  if (!exclusive)
+    /* The interval is inclusive, we want the current record. This
+       usually means it has been determined that there are no NULLs in
+       the column we are searching. It could also mean that we have a
+       >= predicate for the wanted value.
+    */
+    return file->index_read(data_buffer, key_buffer, key_length, 
+                            HA_READ_KEY_OR_NEXT);
+
+  /*
+    The interval is not inclusive. Hence, we are either doing a MIN(c)
+    where c is the minimum column and NULLs are known to be in this
+    column, or we have a predicate c > something. Either way, we want
+    to scan the next record.
+   */
+  error= file->index_read(data_buffer, key_buffer, key_length, 
+                          HA_READ_AFTER_KEY);
+
+  if (error == HA_ERR_KEY_NOT_FOUND || 
+      key_cmp_if_same(table, ref->key_buff, ref->key, prefix_len)) {
+    /*
+      We are either at the end of the index, or we just read a value
+      where the prefix is different. Depending on what we're doing, we
+      should take a step backward, or just keep what we have and exit
+      (even if we have nothing) However, in order to know, we must
+      take that step backards.
+    */
+    error= file->index_read(data_buffer, key_buffer, key_length, 
+                            HA_READ_KEY_OR_PREV);
+    if (!field->is_null())
+      /*
+        So, after taking a step backwards, we find that the sought
+        value for the column is not NULL. This means that we were
+        *not* looking for a MIN() value (because then it would have
+        been NULL. Hence, advance the index position again.
+      */
+      return file->index_read(data_buffer, key_buffer, key_length, 
+                              HA_READ_AFTER_KEY);
+  }
+  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-20 11:56:26 +03:00
@@ -1321,4 +1321,48 @@ 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);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	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 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	a	a	5	const	2	Using where
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b)	MIN(c)
+3	2
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+MIN(a)	MIN(b)
+2	NULL
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+MIN(a)	MIN(b)
+2	NULL
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+MIN(b)	min(c)
+NULL	2
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+MIN(a)	MIN(b)
+1	1
+DROP TABLE t1, t2, t3, t4, t5;
 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-20 11:56:26 +03:00
@@ -817,5 +817,38 @@ 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);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+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 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2),  (2, NULL, 2),  (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2),  (2, NULL, 2),  (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+
+CREATE TABLE t5( a INT, b INT, KEY( a, b) ); 
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+
+
+DROP TABLE t1, t2, t3, t4, t5;
+
 ###
 --echo End of 5.0 tests