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