From: Date: February 6 2007 3:29pm Subject: bk commit into 5.0 tree (gkodinov:1.2400) BUG#24484 List-Archive: http://lists.mysql.com/commits/19398 X-Bug: 24484 Message-Id: <20070206142924.1718ECF99FB@macbook.gmz> 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;