bk commit into 4.1 tree (igor:1.2485) BUG#18206

[igor]

Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor 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
  1.2485 06/06/02 14:14:57 igor@stripped +5 -0
  Fixed bug #18206.
  The bug report revealed two problems related to min/max optimization:
  1. If the length of a constant key used in a SARGable condition for
  for the MIN/MAX fields is greater than the length of the field an 
  unwanted warning on key truncation is issued;
  2. If MIN/MAX optimization is applied to a partial index, like INDEX(b(4))
  than can lead to returning a wrong result set.

  sql/sql_select.h
    1.80 06/06/02 14:14:52 igor@stripped +1 -1
    Fixed bug #18206.
    Added a parameter for the function store_val_in_field allowing to
    control setting warnings about data truncation in the function.

  sql/sql_select.cc
    1.455 06/06/02 14:14:52 igor@stripped +3 -3
    Fixed bug #18206.
    Added a parameter for the function store_val_in_field allowing to
    control setting warnings about data truncation in the function.

  sql/opt_sum.cc
    1.46 06/06/02 14:14:52 igor@stripped +14 -3
    Fixed bug #18206.
    Suppressed the warning about data truncation when store_val_in_field
    was used to store keys for the field used in MIN/MAX optimization.
    Blocked MIN/MAX optimization for partial keys, such as in INDEX(b(4)).

  mysql-test/t/func_group.test
    1.33 06/06/02 14:14:52 igor@stripped +19 -0
    Added test cases for bug #18206.

  mysql-test/r/func_group.result
    1.40 06/06/02 14:14:52 igor@stripped +24 -0
    Added test cases for bug #18206.

# 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:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/mysql-4.1-opt

--- 1.45/sql/opt_sum.cc	2005-09-25 11:22:20 -07:00
+++ 1.46/sql/opt_sum.cc	2006-06-02 14:14:52 -07:00
@@ -543,6 +543,10 @@
       break;                        // Found a part od the key for the field
   }
 
+#if 0
+  if (part->length != (((Item_field*) args[0])->field)->field_length)
+    return 0;
+#endif
   bool is_field_part= part == field_part;
   if (!(is_field_part || eq_type))
     return 0;
@@ -582,7 +586,8 @@
     }
     else
     {
-      store_val_in_field(part->field, args[between && max_fl ? 2 : 1]);
+      store_val_in_field(part->field, args[between && max_fl ? 2 : 1],
+                         CHECK_FIELD_IGNORE);
       if (part->null_bit) 
         *key_ptr++= (byte) test(part->field->is_null());
       part->field->get_key_image((char*) key_ptr, part->length,
@@ -638,6 +643,8 @@
         field BETWEEN const1 AND const2
      3. all references to the columns from the same table as column field
         occur only in conjucts mentioned above.
+     4. each of k first components the index is not partial, i.e. is not
+        defined on a fixed length proper prefix of the field.
 
      If such an index exists the function through the ref parameter
      returns the key value to find max/min for the field using the index,
@@ -647,8 +654,8 @@
       of the whole search key)
 
   NOTE
-   This function may set table->key_read to 1, which must be reset after
-   index is used! (This can only happen when function returns 1)
+    This function may set table->key_read to 1, which must be reset after
+    index is used! (This can only happen when function returns 1)
 
   RETURN
     0   Index can not be used to optimize MIN(field)/MAX(field)
@@ -681,6 +688,10 @@
     {
       if (!(table->file->index_flags(idx, jdx, 0) & HA_READ_ORDER))
         return 0;
+
+        /* Check whether the index component is partial */
+      if (part->length < table->field[part->fieldnr-1]->pack_length())
+        break;
 
       if (field->eq(part->field))
       {

--- 1.454/sql/sql_select.cc	2006-05-06 23:48:09 -07:00
+++ 1.455/sql/sql_select.cc	2006-06-02 14:14:52 -07:00
@@ -3434,7 +3434,7 @@
 */
 
 bool
-store_val_in_field(Field *field,Item *item)
+store_val_in_field(Field *field, Item *item, enum_check_fields check_flag)
 {
   bool error;
   THD *thd=current_thd;
@@ -3445,7 +3445,7 @@
     with select_insert, which make count_cuted_fields= 1
    */
   enum_check_fields old_count_cuted_fields= thd->count_cuted_fields;
-  thd->count_cuted_fields= CHECK_FIELD_WARN;
+  thd->count_cuted_fields= check_flag;
   error= item->save_in_field(field, 1);
   thd->count_cuted_fields= old_count_cuted_fields;
   return error || cuted_fields != thd->cuted_fields;
@@ -7097,7 +7097,7 @@
 	    field->real_type() != FIELD_TYPE_VAR_STRING &&
 	    (field->type() != FIELD_TYPE_FLOAT || field->decimals() == 0))
 	{
-	  return !store_val_in_field(field,right_item);
+	  return !store_val_in_field(field, right_item, CHECK_FIELD_WARN);
 	}
       }
     }

--- 1.79/sql/sql_select.h	2006-01-18 03:48:54 -08:00
+++ 1.80/sql/sql_select.h	2006-06-02 14:14:52 -07:00
@@ -335,7 +335,7 @@
 void TEST_join(JOIN *join);
 
 /* Extern functions in sql_select.cc */
-bool store_val_in_field(Field *field,Item *val);
+bool store_val_in_field(Field *field, Item *val, enum_check_fields check_flag);
 TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 			ORDER *group, bool distinct, bool save_sum_fields,
 			ulong select_options, ha_rows rows_limit,

--- 1.39/mysql-test/r/func_group.result	2005-09-20 23:49:16 -07:00
+++ 1.40/mysql-test/r/func_group.result	2006-06-02 14:14:52 -07:00
@@ -916,3 +916,27 @@
 count(*)	min(7)	max(7)
 0	NULL	NULL
 drop table t1m, t1i, t2m, t2i;
+CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
+INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
+SELECT * FROM t1;
+id	b
+1	xx
+2	aa
+SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
+MAX(b)
+aa
+SHOW WARNINGS;
+Level	Code	Message
+SELECT MAX(b) FROM t1 WHERE b < 'pp';
+MAX(b)
+aa
+DROP TABLE t1;
+CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
+INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
+SELECT MAX(b) FROM t1;
+MAX(b)
+xxxxbbbb
+EXPLAIN SELECT MAX(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	
+DROP TABLE t1;

--- 1.32/mysql-test/t/func_group.test	2005-10-08 16:30:23 -07:00
+++ 1.33/mysql-test/t/func_group.test	2006-06-02 14:14:52 -07:00
@@ -598,4 +598,23 @@
 
 drop table t1m, t1i, t2m, t2i;
 
+#
+# Bug #18206: min/max optimization cannot be applied to partial index
+#
+
+CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));
+INSERT INTO t1 VALUES (1,'xx'), (2,'aa');
+SELECT * FROM t1;
+
+SELECT MAX(b) FROM t1 WHERE b < 'ppppp';
+SHOW WARNINGS;
+SELECT MAX(b) FROM t1 WHERE b < 'pp';
+DROP TABLE t1;
+
+CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));
+INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');
+SELECT MAX(b) FROM t1;
+EXPLAIN SELECT MAX(b) FROM t1;
+DROP TABLE t1;
+
 # End of 4.1 tests