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-06 16:29:05+02:00, gkodinov@stripped +6 -0
  Bug #24484:
  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.
  Also used_tables() for other aggregates now excludes the special
  "non-table" bits (like OUTER_REF_TABLE_BIT for example).

  mysql-test/r/subselect3.result@stripped, 2007-02-06 16:28:55+02:00, gkodinov@stripped +11
-0
    Bug #24484: test case

  mysql-test/t/subselect3.test@stripped, 2007-02-06 16:28:55+02:00, gkodinov@stripped +15 -0
    Bug #24484: test case

  sql/item.h@stripped, 2007-02-06 16:28:56+02:00, gkodinov@stripped +5 -0
    Bug #24484: Item_ref must update the used tables.

  sql/item_sum.cc@stripped, 2007-02-06 16:28:57+02:00, gkodinov@stripped +17 -1
    Bug #24484: correct calculation of used_tables for MIN/MAX (Item_sum_hybrid).

  sql/item_sum.h@stripped, 2007-02-06 16:28:57+02:00, gkodinov@stripped +6 -4
    Bug #24484: correct calculation of used_tables for MIN/MAX (Item_sum_hybrid).

  sql/sql_select.cc@stripped, 2007-02-06 16:28:58+02:00, gkodinov@stripped +2 -1
    Bug #24484: correct check for aggregates

# 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-06 16:28:56 +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-06 16:28:57 +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-06 16:28:57 +02:00
@@ -319,7 +319,7 @@ public:
   virtual const char *func_name() const= 0;
   virtual Item *result_item(Field *field)
     { return new Item_field(field); }
-  table_map used_tables() const { return ~(table_map) 0; } /* Not used */
+  table_map used_tables() const { return ~(table_map) PSEUDO_TABLE_BITS; }
   bool const_item() const { return 0; }
   bool is_null() { return null_value; }
   void update_used_tables() { }
@@ -806,18 +806,20 @@ 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:
   Item_sum_hybrid(Item *item_par,int sign)
     :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)
+    cmp_sign(sign), used_table_cache(~(table_map) PSEUDO_TABLE_BITS),
+    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.488/sql/sql_select.cc	2007-02-01 10:50:33 +02:00
+++ 1.489/sql/sql_select.cc	2007-02-06 16:28:58 +02:00
@@ -6376,7 +6376,8 @@ static void update_depend_map(JOIN *join
     order->item[0]->update_used_tables();
     order->depend_map=depend_map=order->item[0]->used_tables();
     // Not item_sum(), RAND() and no reference to table outside of sub select
-    if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT)))
+    if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT))
+        && !order->item[0]->with_sum_func)
     {
       for (JOIN_TAB **tab=join->map2table;
 	   depend_map ;

--- 1.4/mysql-test/r/subselect3.result	2007-01-27 03:10:42 +02:00
+++ 1.5/mysql-test/r/subselect3.result	2007-02-06 16:28:55 +02:00
@@ -645,3 +645,14 @@ a	b	Z
 2	2	0
 3	3	1
 drop table t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
+(1,9,'m');
+SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
+as test FROM t1 GROUP BY a;
+a	MAX(b)	test
+1	9	m
+2	3	h
+3	4	i
+DROP TABLE t1;

--- 1.5/mysql-test/t/subselect3.test	2007-01-27 03:10:42 +02:00
+++ 1.6/mysql-test/t/subselect3.test	2007-02-06 16:28:55 +02:00
@@ -489,3 +489,18 @@ 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 t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+  (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
+  (1,9,'m');
+
+# Gives error, but should work since it is (a, b) is the PK so only one 
+# given match possible
+SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
+  as test FROM t1 GROUP BY a;
+
+DROP TABLE t1;