bk commit into 5.0 tree (gkodinov:1.2400) BUG#24484

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge 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-02-02 19:29:03+02:00, gkodinov@stripped +5 -0
  Bug #24484: Aggregate function used in column list subquery gives erroneous
              error
  Most of the aggregate functions depend on the number of rows they process, 
  even if their arguments are constants (e.g. SUM(1) cannot be calculated 
  without knowing the number of rows that it is called for).
  There are however two exceptions : MIN and MAX. These depend only on their
  arguments : so if they are constant, them MIN and MAX can be considered
  constants. In that respect MIN and MAX are just as any other scalar SQL 
  function.
  However they were erroneously treated as the other aggregates by the
  optimizer.
  Fixed by introducing in Item_sum_hybrid (the superclass of MIN and MAX)
  the same calculation for used_tables as for Item_func.
  One other flaw was revealed and fixed in the process : references were 
  not calling the recalculation method for used_tables of their targets. 

  mysql-test/r/subselect3.result@stripped, 2007-02-02 19:28:52+02:00, gkodinov@stripped +7
-0
    Bug #24484: Aggregate function used in column list subquery gives erroneous
                error
    - test case

  mysql-test/t/subselect3.test@stripped, 2007-02-02 19:28:53+02:00, gkodinov@stripped +12 -0
    Bug #24484: Aggregate function used in column list subquery gives erroneous
                error
    - test case

  sql/item.h@stripped, 2007-02-02 19:28:54+02:00, gkodinov@stripped +5 -0
    Bug #24484: Aggregate function used in column list subquery gives erroneous
                error
    - Item_ref must update the used tables.

  sql/item_sum.cc@stripped, 2007-02-02 19:28:55+02:00, gkodinov@stripped +17 -1
    Bug #24484: Aggregate function used in column list subquery gives erroneous
                error
    - correct calculation of used_tables for MIN/MAX (Item_sum_hybrid).

  sql/item_sum.h@stripped, 2007-02-02 19:28:55+02:00, gkodinov@stripped +4 -2
    Bug #24484: Aggregate function used in column list subquery gives erroneous
                error
    - correct calculation of used_tables for MIN/MAX (Item_sum_hybrid).

# 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:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B24484-5.0

--- 1.219/sql/item.h	2007-01-26 19:33:16 +02:00
+++ 1.220/sql/item.h	2007-02-02 19:28:54 +02:00
@@ -1881,6 +1881,11 @@ public:
   { 
     return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); 
   }
+  void update_used_tables() 
+  { 
+    if (!depended_from) 
+      (*ref)->update_used_tables(); 
+  }
   table_map not_null_tables() const { return (*ref)->not_null_tables(); }
   void set_result_field(Field *field)	{ result_field= field; }
   bool is_result_field() { return 1; }

--- 1.194/sql/item_sum.cc	2006-12-27 07:28:20 +02:00
+++ 1.195/sql/item_sum.cc	2007-02-02 19:28:55 +02:00
@@ -484,7 +484,8 @@ Item_sum_num::fix_fields(THD *thd, Item 
 Item_sum_hybrid::Item_sum_hybrid(THD *thd, Item_sum_hybrid *item)
   :Item_sum(thd, item), value(item->value), hybrid_type(item->hybrid_type),
   hybrid_field_type(item->hybrid_field_type), cmp_sign(item->cmp_sign),
-  used_table_cache(item->used_table_cache), was_values(item->was_values)
+  used_table_cache(item->used_table_cache), forced_const (item->forced_const),
+  was_values(item->was_values)
 {
   /* copy results from old value */
   switch (hybrid_type) {
@@ -1554,6 +1555,7 @@ void Item_sum_hybrid::cleanup()
   DBUG_ENTER("Item_sum_hybrid::cleanup");
   Item_sum::cleanup();
   used_table_cache= ~(table_map) 0;
+  forced_const= FALSE;
 
   /*
     by default it is TRUE to avoid TRUE reporting by
@@ -2157,6 +2159,20 @@ Item_sum_hybrid::min_max_update_decimal_
   else if (result_field->is_null(0))
     result_field->set_null();
   result_field->store_decimal(old_nr);
+}
+
+
+void Item_sum_hybrid::update_used_tables()
+{
+  if (!forced_const)
+  {
+    used_table_cache=0;
+    for (uint i=0 ; i < arg_count ; i++)
+    {
+      args[i]->update_used_tables();
+      used_table_cache|=args[i]->used_tables();
+    }
+  }
 }
 
 

--- 1.110/sql/item_sum.h	2007-01-11 16:40:02 +02:00
+++ 1.111/sql/item_sum.h	2007-02-02 19:28:55 +02:00
@@ -806,6 +806,7 @@ protected:
   enum_field_types hybrid_field_type;
   int cmp_sign;
   table_map used_table_cache;
+  bool forced_const;
   bool was_values;  // Set if we have found at least one row (for max/min only)
 
   public:
@@ -813,11 +814,12 @@ protected:
     :Item_sum(item_par), sum(0.0), sum_int(0),
     hybrid_type(INT_RESULT), hybrid_field_type(FIELD_TYPE_LONGLONG),
     cmp_sign(sign), used_table_cache(~(table_map) 0),
-    was_values(TRUE)
+    forced_const(FALSE), was_values(TRUE)
   { collation.set(&my_charset_bin); }
   Item_sum_hybrid(THD *thd, Item_sum_hybrid *item);
   bool fix_fields(THD *, Item **);
   table_map used_tables() const { return used_table_cache; }
+  void update_used_tables();
   bool const_item() const { return !used_table_cache; }
 
   void clear();
@@ -826,7 +828,7 @@ protected:
   my_decimal *val_decimal(my_decimal *);
   void reset_field();
   String *val_str(String *);
-  void make_const() { used_table_cache=0; }
+  void make_const() { used_table_cache=0; forced_const= TRUE; }
   bool keep_field_type(void) const { return 1; }
   enum Item_result result_type () const { return hybrid_type; }
   enum enum_field_types field_type() const { return hybrid_field_type; }

--- 1.4/mysql-test/r/subselect3.result	2007-01-27 03:10:42 +02:00
+++ 1.5/mysql-test/r/subselect3.result	2007-02-02 19:28:52 +02:00
@@ -645,3 +645,10 @@ a	b	Z
 2	2	0
 3	3	1
 drop table t1,t2;
+CREATE TABLE t_1 (a int, b INT, c INT, PRIMARY KEY (a, b));
+INSERT INTO t_1 VALUES (1,1,1), (1,2,2);
+SELECT a, MAX(b), (SELECT t.c FROM t_1 AS t WHERE t_1.a=t.a AND t.b=MAX(t_1.b))
+as test FROM t_1 GROUP BY a;
+a	MAX(b)	test
+1	2	2
+DROP TABLE t_1;

--- 1.5/mysql-test/t/subselect3.test	2007-01-27 03:10:42 +02:00
+++ 1.6/mysql-test/t/subselect3.test	2007-02-02 19:28:53 +02:00
@@ -489,3 +489,15 @@ select a, b, (a,b) in (select a, min(b) 
 
 drop table t1,t2;
 
+#
+# Bug #24484: Aggregate function used in column list subquery gives erroneous 
+# error
+#
+CREATE TABLE t_1 (a int, b INT, c INT, PRIMARY KEY (a, b));
+INSERT INTO t_1 VALUES (1,1,1), (1,2,2);
+
+
+SELECT a, MAX(b), (SELECT t.c FROM t_1 AS t WHERE t_1.a=t.a AND t.b=MAX(t_1.b))
+  as test FROM t_1 GROUP BY a;
+
+DROP TABLE t_1;