From 5c2d49dbedd4aff65d3459ae6648b21a3d1727b3 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 5 Mar 2007 11:35:04 +0400 Subject: bug #25492 (Invalid deallocation in mysql_stmt_fetch()) Additional patch. mysql_flush_use_result() fixed. libmysqld/lib_sql.cc: now emb_flush_use_result() uses emb_free_rows() duplicating code removed --- libmysqld/lib_sql.cc | 22 ++++++++-------------- 1 file changed, 8 insertions(+), 14 deletions(-) diff --git a/libmysqld/lib_sql.cc b/libmysqld/lib_sql.cc index 8992bea943b..9ee8d48eec4 100644 --- a/libmysqld/lib_sql.cc +++ b/libmysqld/lib_sql.cc @@ -69,10 +69,14 @@ void embedded_get_error(MYSQL *mysql) static void emb_free_rows(THD *thd) { + if (!thd->data) + return; + if (thd->current_stmt) free_root(&thd->data->alloc,MYF(0)); else free_rows(thd->data); + thd->data= NULL; } @@ -86,11 +90,8 @@ emb_advanced_command(MYSQL *mysql, enum enum_server_command command, THD *thd=(THD *) mysql->thd; NET *net= &mysql->net; - if (thd->data) - { - emb_free_rows(thd); - thd->data= 0; - } + emb_free_rows(thd); + /* Check that we are calling the client functions in right order */ if (mysql->status != MYSQL_STATUS_READY) { @@ -143,13 +144,7 @@ emb_advanced_command(MYSQL *mysql, enum enum_server_command command, static void emb_flush_use_result(MYSQL *mysql) { - MYSQL_DATA *data= ((THD*)(mysql->thd))->data; - - if (data) - { - free_rows(data); - ((THD*)(mysql->thd))->data= NULL; - } + emb_free_rows((THD*) (mysql->thd)); } static MYSQL_DATA * @@ -304,8 +299,7 @@ int emb_unbuffered_fetch(MYSQL *mysql, char **row) static void emb_free_embedded_thd(MYSQL *mysql) { THD *thd= (THD*)mysql->thd; - if (thd->data) - emb_free_rows(thd); + emb_free_rows(thd); thread_count--; delete thd; mysql->thd=0; -- cgit v1.2.1 From 9c89dd654e90d85fefc2459711063b680ed10f24 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 20 Mar 2007 19:46:02 +0200 Subject: Bug #24484: To correctly decide which predicates can be evaluated with a given table the optimizer must know the exact set of tables that a predicate depends on. If that mask is too wide (refer to non-existing tables) the optimizer can erroneously skip a predicate. One such case of wrong table usage mask were the aggregate functions. The have a all-1 mask (meaning depend on all tables, including non-existent ones). Fixed by making a real used_tables mask for the aggregates. The mask is constructed in the following way : 1. OR the table dependency masks of all the arguments of the aggregate. 2. If all the arguments of the function are from the local name resolution context and it is evaluated in the same name resolution context where it is referenced all the tables from that name resolution context are OR-ed to the dependency mask. This is to denote that an aggregate function depends on the number of rows it processes. 3. Handle correctly the case of an aggregate function optimization (such that the aggregate function can be pre-calculated and made a constant). Made sure that an aggregate function is never a constant (unless subject of a specific optimization and pre-calculation). 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: Bug #24484: test case mysql-test/t/subselect3.test: Bug #24484: test case sql/item.h: Bug #24484: Item_ref must update the used tables. sql/item_sum.cc: Bug #24484: correct calculation of used_tables for aggregates. sql/item_sum.h: Bug #24484: correct calculation of used_tables for aggregates. sql/opt_range.cc: Bug #24484: fixed ref resolution in loose index scan sql/sql_base.cc: Bug #24484: moved counting of leaf tables inside setup_tables_and_check_access. sql/sql_class.h: Bug #24484: changed table count to more narrow type. sql/sql_insert.cc: Bug #24484: moved counting of leaf tables inside setup_tables_and_check_access. Substract the first table (and its subtables) of an INSERT statement from leaf_count. sql/sql_select.cc: Bug #24484: correct check for aggregates --- mysql-test/r/subselect3.result | 53 +++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect3.test | 41 +++++++++++++++++++++++++++++ sql/item.h | 5 ++++ sql/item_sum.cc | 36 +++++++++++++++++++++----- sql/item_sum.h | 59 ++++++++++++++++++++++++++---------------- sql/opt_range.cc | 6 +++-- sql/sql_base.cc | 2 ++ sql/sql_class.h | 3 +++ sql/sql_insert.cc | 6 +++-- sql/sql_select.cc | 23 ++++++++-------- 10 files changed, 189 insertions(+), 45 deletions(-) diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 29143b9e504..1320bc76222 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -645,3 +645,56 @@ 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'); +CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t2 SELECT * FROM t1; +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 +SELECT * FROM t1 GROUP by t1.a +HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c +HAVING MAX(t2.b+t1.a) < 10)); +a b c +SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) +AS test FROM t1 GROUP BY a; +a AVG(b) test +1 4.0000 NULL +2 2.0000 k +3 2.5000 NULL +SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; +a b c +1 3 c +2 3 h +3 3 j +1 4 d +3 4 i +1 9 m +SELECT a, MAX(b), +(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) +LIMIT 1) +as cnt, +(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) +as t_b, +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) +as t_b, +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) +as t_b +FROM t1 GROUP BY a; +a MAX(b) cnt t_b t_b t_b +1 9 1 9 m m +2 3 1 3 h h +3 4 1 4 i i +SELECT a, MAX(b), +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test +FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index ed8480ba464..e3703c0da16 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -489,3 +489,44 @@ select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1; 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'); +CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b)); +INSERT INTO t2 SELECT * FROM t1; + +# 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; +SELECT * FROM t1 GROUP by t1.a + HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c + HAVING MAX(t2.b+t1.a) < 10)); +SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b)) + AS test FROM t1 GROUP BY a; + +SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c; + +SELECT a, MAX(b), + (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) + LIMIT 1) + as cnt, + (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) + as t_b, + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) + as t_b, + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1) + as t_b + FROM t1 GROUP BY a; + +SELECT a, MAX(b), + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test + FROM t1 GROUP BY a; + + +DROP TABLE t1, t2; diff --git a/sql/item.h b/sql/item.h index 6c41aa09f80..1e724e2aa93 100644 --- a/sql/item.h +++ b/sql/item.h @@ -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; } diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 8bfac058936..0b0fb065bb3 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -61,6 +61,7 @@ bool Item_sum::init_sum_func_check(THD *thd) /* Save a pointer to object to be used in items for nested set functions */ thd->lex->in_sum_func= this; nest_level= thd->lex->current_select->nest_level; + nest_level_tables_count= thd->lex->current_select->join->tables; ref_by= 0; aggr_level= -1; max_arg_level= -1; @@ -176,6 +177,7 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref) */ set_if_bigger(in_sum_func->max_sum_func_level, aggr_level); } + update_used_tables(); thd->lex->in_sum_func= in_sum_func; return FALSE; } @@ -271,8 +273,8 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref) } -Item_sum::Item_sum(List &list) - :arg_count(list.elements) +Item_sum::Item_sum(List &list) :arg_count(list.elements), + forced_const(FALSE) { if ((args=(Item**) sql_alloc(sizeof(Item*)*arg_count))) { @@ -296,7 +298,10 @@ Item_sum::Item_sum(List &list) Item_sum::Item_sum(THD *thd, Item_sum *item): Item_result_field(thd, item), arg_count(item->arg_count), - quick_group(item->quick_group) + nest_level(item->nest_level), aggr_level(item->aggr_level), + quick_group(item->quick_group), used_tables_cache(item->used_tables_cache), + forced_const(item->forced_const), + nest_level_tables_count(item->nest_level_tables_count) { if (arg_count <= 2) args=tmp_args; @@ -425,6 +430,26 @@ case DECIMAL_RESULT: } +void Item_sum::update_used_tables () +{ + if (!forced_const) + { + used_tables_cache= 0; + for (uint i=0 ; i < arg_count ; i++) + { + args[i]->update_used_tables(); + used_tables_cache|= args[i]->used_tables(); + } + + used_tables_cache&= PSEUDO_TABLE_BITS; + + /* the aggregate function is aggregated into its local context */ + if (aggr_level == nest_level) + used_tables_cache |= (1 << nest_level_tables_count) - 1; + } +} + + String * Item_sum_num::val_str(String *str) { @@ -484,7 +509,7 @@ Item_sum_num::fix_fields(THD *thd, Item **ref) 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) + was_values(item->was_values) { /* copy results from old value */ switch (hybrid_type) { @@ -1072,7 +1097,6 @@ void Item_sum_count::cleanup() DBUG_ENTER("Item_sum_count::cleanup"); count= 0; Item_sum_int::cleanup(); - used_table_cache= ~(table_map) 0; DBUG_VOID_RETURN; } @@ -1553,7 +1577,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 diff --git a/sql/item_sum.h b/sql/item_sum.h index f011f105453..9ddda94a8ee 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -215,7 +215,9 @@ TODO: to catch queries where the limit is exceeded to make the code clean here. -*/ +*/ + +class st_select_lex; class Item_sum :public Item_result_field { @@ -237,19 +239,26 @@ public: int8 max_sum_func_level;/* max level of aggregation for embedded functions */ bool quick_group; /* If incremental update of fields */ +protected: + table_map used_tables_cache; + bool forced_const; + byte nest_level_tables_count; + +public: + void mark_as_sum_func(); - Item_sum() :arg_count(0), quick_group(1) + Item_sum() :arg_count(0), quick_group(1), forced_const(FALSE) { mark_as_sum_func(); } - Item_sum(Item *a) - :args(tmp_args), arg_count(1), quick_group(1) + Item_sum(Item *a) :args(tmp_args), arg_count(1), quick_group(1), + forced_const(FALSE) { args[0]=a; mark_as_sum_func(); } - Item_sum( Item *a, Item *b ) - :args(tmp_args), arg_count(2), quick_group(1) + Item_sum( Item *a, Item *b ) :args(tmp_args), arg_count(2), quick_group(1), + forced_const(FALSE) { args[0]=a; args[1]=b; mark_as_sum_func(); @@ -319,10 +328,20 @@ 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 */ - bool const_item() const { return 0; } + table_map used_tables() const { return used_tables_cache; } + void update_used_tables (); + void cleanup() + { + Item::cleanup(); + forced_const= FALSE; + } bool is_null() { return null_value; } - void update_used_tables() { } + void make_const () + { + used_tables_cache= 0; + forced_const= TRUE; + } + virtual bool const_item() const { return forced_const; } void make_field(Send_field *field); void print(String *str); void fix_num_length_and_dec(); @@ -509,23 +528,23 @@ public: class Item_sum_count :public Item_sum_int { longlong count; - table_map used_table_cache; public: Item_sum_count(Item *item_par) - :Item_sum_int(item_par),count(0),used_table_cache(~(table_map) 0) + :Item_sum_int(item_par),count(0) {} Item_sum_count(THD *thd, Item_sum_count *item) - :Item_sum_int(thd, item), count(item->count), - used_table_cache(item->used_table_cache) + :Item_sum_int(thd, item), count(item->count) {} - table_map used_tables() const { return used_table_cache; } - bool const_item() const { return !used_table_cache; } enum Sumfunctype sum_func () const { return COUNT_FUNC; } void clear(); void no_rows_in_result() { count=0; } bool add(); - void make_const(longlong count_arg) { count=count_arg; used_table_cache=0; } + void make_const(longlong count_arg) + { + count=count_arg; + Item_sum::make_const(); + } longlong val_int(); void reset_field(); void cleanup(); @@ -805,28 +824,22 @@ protected: Item_result hybrid_type; enum_field_types hybrid_field_type; int cmp_sign; - table_map used_table_cache; 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), 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; } - bool const_item() const { return !used_table_cache; } - void clear(); double val_real(); longlong val_int(); my_decimal *val_decimal(my_decimal *); void reset_field(); String *val_str(String *); - void make_const() { used_table_cache=0; } 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; } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index f0af4b7db2a..8985561dd30 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -7516,7 +7516,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree) else DBUG_RETURN(NULL); - Item *expr= min_max_item->args[0]; /* The argument of MIN/MAX. */ + /* The argument of MIN/MAX. */ + Item *expr= min_max_item->args[0]->real_item(); if (expr->type() == Item::FIELD_ITEM) /* Is it an attribute? */ { if (! min_max_arg_item) @@ -7894,6 +7895,7 @@ check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item, DBUG_ENTER("check_group_min_max_predicates"); DBUG_ASSERT(cond && min_max_arg_item); + cond= cond->real_item(); Item::Type cond_type= cond->type(); if (cond_type == Item::COND_ITEM) /* 'AND' or 'OR' */ { @@ -7931,7 +7933,7 @@ check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item, DBUG_PRINT("info", ("Analyzing: %s", pred->func_name())); for (uint arg_idx= 0; arg_idx < pred->argument_count (); arg_idx++) { - cur_arg= arguments[arg_idx]; + cur_arg= arguments[arg_idx]->real_item(); DBUG_PRINT("info", ("cur_arg: %s", cur_arg->full_name())); if (cur_arg->type() == Item::FIELD_ITEM) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index ad9cd5985d1..0f9ba371ce1 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4656,6 +4656,7 @@ bool setup_tables_and_check_access(THD *thd, TABLE_LIST *leaves_tmp = NULL; bool first_table= true; + thd->leaf_count= 0; if (setup_tables (thd, context, from_clause, tables, conds, &leaves_tmp, select_insert)) return TRUE; @@ -4673,6 +4674,7 @@ bool setup_tables_and_check_access(THD *thd, return TRUE; } first_table= false; + thd->leaf_count++; } return FALSE; } diff --git a/sql/sql_class.h b/sql/sql_class.h index 05034ebd573..3421f506ca5 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1432,6 +1432,9 @@ public: query_id_t first_query_id; } binlog_evt_union; + /* pass up the count of "leaf" tables in a JOIN out of setup_tables() */ + byte leaf_count; + THD(); ~THD(); diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index fb59aeea8e7..c369f03d978 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -2301,12 +2301,14 @@ bool mysql_insert_select_prepare(THD *thd) DBUG_ASSERT(select_lex->leaf_tables != 0); lex->leaf_tables_insert= select_lex->leaf_tables; /* skip all leaf tables belonged to view where we are insert */ - for (first_select_leaf_table= select_lex->leaf_tables->next_leaf; + for (first_select_leaf_table= select_lex->leaf_tables->next_leaf, + thd->leaf_count --; first_select_leaf_table && first_select_leaf_table->belong_to_view && first_select_leaf_table->belong_to_view == lex->leaf_tables_insert->belong_to_view; - first_select_leaf_table= first_select_leaf_table->next_leaf) + first_select_leaf_table= first_select_leaf_table->next_leaf, + thd->leaf_count --) {} select_lex->leaf_tables= first_select_leaf_table; DBUG_RETURN(FALSE); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 05ee0d77c1f..12bab33cc13 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -342,12 +342,15 @@ JOIN::prepare(Item ***rref_pointer_array, /* Check that all tables, fields, conds and order are ok */ - if ((!(select_options & OPTION_SETUP_TABLES_DONE) && - setup_tables_and_check_access(thd, &select_lex->context, join_list, - tables_list, &conds, - &select_lex->leaf_tables, FALSE, - SELECT_ACL, SELECT_ACL)) || - setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) || + if (!(select_options & OPTION_SETUP_TABLES_DONE) && + setup_tables_and_check_access(thd, &select_lex->context, join_list, + tables_list, &conds, + &select_lex->leaf_tables, FALSE, + SELECT_ACL, SELECT_ACL)) + DBUG_RETURN(-1); + tables= thd->leaf_count; + + if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) || select_lex->setup_ref_array(thd, og_num) || setup_fields(thd, (*rref_pointer_array), fields_list, 1, &all_fields, 1) || @@ -437,11 +440,6 @@ JOIN::prepare(Item ***rref_pointer_array, DBUG_RETURN(-1); } } - TABLE_LIST *table_ptr; - for (table_ptr= select_lex->leaf_tables; - table_ptr; - table_ptr= table_ptr->next_leaf) - tables++; } { /* Caclulate the number of groups */ @@ -6376,7 +6374,8 @@ static void update_depend_map(JOIN *join, ORDER *order) 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 ; -- cgit v1.2.1 From 91f7f3181670523aa657420ae47d1d20fcd07aea Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 20 Mar 2007 11:51:09 -0700 Subject: Fixed bug #27257: queries containing subqueries with COUNT(*) aggregated in outer context returned wrong results. This happened only if the subquery did not contain any references to outer fields. As there were no references to outer fields the subquery erroneously was taken for non-correlated one. Now any set function aggregated in outer context makes the subquery correlated. mysql-test/r/subselect.result: Added a test case for bug #27257. mysql-test/t/subselect.test: Added a test case for bug #27257. --- mysql-test/r/subselect.result | 25 +++++++++++++++++++++++++ mysql-test/t/subselect.test | 21 +++++++++++++++++++++ sql/item_sum.cc | 1 + 3 files changed, 47 insertions(+) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f2d41bd44ae..72bde001e87 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3880,3 +3880,28 @@ this is a test. 3 this is a test. 1 this is a test. 2 DROP table t1; +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); +SELECT COUNT(*), a, +(SELECT m FROM t2 WHERE m = count(*) LIMIT 1) +FROM t1 GROUP BY a; +COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1) +2 2 2 +3 3 3 +1 4 1 +SELECT COUNT(*), a, +(SELECT MIN(m) FROM t2 WHERE m = count(*)) +FROM t1 GROUP BY a; +COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*)) +2 2 2 +3 3 3 +1 4 1 +SELECT COUNT(*), a +FROM t1 GROUP BY a +HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; +COUNT(*) a +2 2 +3 3 +DROP TABLE t1,t2; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 1655422c51e..a238c8f070b 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2741,4 +2741,25 @@ SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t; DROP table t1; +# +# Bug #27257: COUNT(*) aggregated in outer query +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (m int, n int); +INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4); +INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44); +SELECT COUNT(*), a, + (SELECT m FROM t2 WHERE m = count(*) LIMIT 1) + FROM t1 GROUP BY a; + +SELECT COUNT(*), a, + (SELECT MIN(m) FROM t2 WHERE m = count(*)) + FROM t1 GROUP BY a; + +SELECT COUNT(*), a + FROM t1 GROUP BY a + HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1; + +DROP TABLE t1,t2; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 41f0dd6496b..a8ffa200102 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -267,6 +267,7 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref) sl= sl->master_unit()->outer_select() ) sl->master_unit()->item->with_sum_func= 1; } + thd->lex->current_select->mark_as_dependent(aggr_sl); return FALSE; } -- cgit v1.2.1 From 16404523645b8f396ebaea9da5dfa4c453056d06 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 21 Mar 2007 21:54:38 +0300 Subject: Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. INTO clause can be specified only for the last select of a UNION and it receives the result of the whole query. But it was wrongly allowed in non-last selects of a UNION which leads to a confusing query result. Now INTO allowed only in the last select of a UNION. mysql-test/t/union.test: Added a test case for the bug#23345: Wrongly allowed INTO in a non-last select of a UNION. mysql-test/r/union.result: Added a test case for the bug#23345: Wrongly allowed INTO in a non-last select of a UNION. sql/sql_yacc.yy: Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. Now INTO allowed only in the last select of a UNION. --- mysql-test/r/union.result | 8 ++++++++ mysql-test/t/union.test | 9 +++++++++ sql/sql_yacc.yy | 2 +- 3 files changed, 18 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 9861b1bffeb..8219d68a681 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -1381,4 +1381,12 @@ a SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; ERROR 42S22: Unknown column 'c' in 'order clause' DROP TABLE t1; +(select 1 into @var) union (select 1); +ERROR HY000: Incorrect usage of UNION and INTO +(select 1) union (select 1 into @var); +select @var; +@var +1 +(select 2) union (select 1 into @var); +ERROR 42000: Result consisted of more than one row End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 29a9ee36481..22f09466b1c 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -868,4 +868,13 @@ SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test; DROP TABLE t1; +# +# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION. +# +--error 1221 +(select 1 into @var) union (select 1); +(select 1) union (select 1 into @var); +select @var; +--error 1172 +(select 2) union (select 1 into @var); --echo End of 5.0 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 258283d113e..a2e7100a6f5 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9307,7 +9307,7 @@ union_list: UNION_SYM union_option { LEX *lex=Lex; - if (lex->exchange) + if (lex->result) { /* Only the last SELECT can have INTO...... */ my_error(ER_WRONG_USAGE, MYF(0), "UNION", "INTO"); -- cgit v1.2.1 From a4a23fb907ce7b2c3e998735e074da647b3e9ffc Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Mar 2007 00:05:36 -0700 Subject: Fixed bug #27362: crash at evaluation of IN predicate when one of its argument happened to be a decimal expression returning the NULL value. The crash was due to the fact the function in_decimal::set did not take into account that val_decimal() could return 0 if the decimal expression had been evaluated to NULL. mysql-test/r/func_in.result: Added a test case for bug #27362. mysql-test/t/func_in.test: Added a test case for bug #27362. --- mysql-test/r/func_in.result | 5 +++++ mysql-test/t/func_in.test | 11 +++++++++++ sql/item_cmpfunc.cc | 3 ++- 3 files changed, 18 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index fad9a7157e1..87855091699 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -470,4 +470,9 @@ a Warnings: Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); +id +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index f9749662ec1..77592d015eb 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -360,4 +360,15 @@ SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); DROP TABLE t1,t2,t3,t4; +# +# BUG#27362: IN with a decimal expression that may return NULL +# + +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); + +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); + +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index e032974fdea..44472bf0803 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2423,7 +2423,8 @@ void in_decimal::set(uint pos, Item *item) dec->len= DECIMAL_BUFF_LENGTH; dec->fix_buffer_pointer(); my_decimal *res= item->val_decimal(dec); - if (res != dec) + /* if item->val_decimal() is evaluated to NULL then res == 0 */ + if (!item->null_value && res != dec) my_decimal2decimal(res, dec); } -- cgit v1.2.1 From 9f9e2f2df02ba7cbeb075ea345f979eb244f97a6 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Mar 2007 12:19:41 +0400 Subject: merging --- sql/sql_base.cc | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index d4316f11491..a3c03fdaf12 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5771,11 +5771,13 @@ bool setup_tables_and_check_access(THD *thd, TABLE_LIST *leaves_tmp= NULL; bool first_table= true; + thd->leaf_count= 0; if (setup_tables(thd, context, from_clause, tables, &leaves_tmp, select_insert)) return TRUE; - *leaves= leaves_tmp; + if (leaves) + *leaves= leaves_tmp; for (; leaves_tmp; leaves_tmp= leaves_tmp->next_leaf) { @@ -5787,6 +5789,7 @@ bool setup_tables_and_check_access(THD *thd, return TRUE; } first_table= 0; + thd->leaf_count++; } return FALSE; } -- cgit v1.2.1 From 50b5064ccd0af957080d9c9efd022e33c7c9c060 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Mar 2007 12:24:56 +0400 Subject: bug #16546 (DATETIME + 0 not always coerced in the same way) fix for cast( AS DATETIME) + 0 operation. I just implemented Item_datetime_typecast::val() method as it is usually done in other classes. Should be fixed more radically in 5.0 mysql-test/r/type_datetime.result: result added mysql-test/t/type_datetime.test: testcase sql/item_timefunc.h: added double conversion to Item_datetime_typecast --- mysql-test/r/type_datetime.result | 3 +++ mysql-test/t/type_datetime.test | 6 ++++++ sql/item_timefunc.h | 7 +++++++ 3 files changed, 16 insertions(+) diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index f313a6b934b..64337bd2c2f 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -166,3 +166,6 @@ dt 0000-00-00 00:00:00 0000-00-00 00:00:00 drop table t1; +select cast('2006-12-05 22:10:10' as datetime) + 0; +cast('2006-12-05 22:10:10' as datetime) + 0 +20061205221010.000000 diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 4b6741b4242..87b86b55fc9 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -113,4 +113,10 @@ insert into t1 values ("00-00-00"), ("00-00-00 00:00:00"); select * from t1; drop table t1; +# +# Bug #16546 DATETIME+0 not always coerced the same way +# +select cast('2006-12-05 22:10:10' as datetime) + 0; + + # End of 4.1 tests diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 45cad627c05..2383b4f86ac 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -751,12 +751,19 @@ public: String *val_str(String *str); const char *cast_type() const { return "datetime"; } enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; } + void fix_length_and_dec() + { + Item_typecast_maybe_null::fix_length_and_dec(); + decimals= DATETIME_DEC; + } + Field *tmp_table_field(TABLE *t_arg) { return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val() { return (double) val_int(); } }; class Item_func_makedate :public Item_str_func -- cgit v1.2.1 From 2b983572a7763914abeb61b8084e2f54d0de915d Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 22 Mar 2007 12:44:38 +0400 Subject: bug #16546 (DATETIME+0 not always coerced the same way) fix for cast( AS DATETIME)+0 in 5.0 and above versions. val_real now works using val_decimal for DATETIME Items Superfluous val_real() methods deleted sql/item_timefunc.h: val_real() for datetime functions implemented as { return val_real_from_decimal(); } It's not a fastest possible way, but code is simple and less error-prone, what i belive is more important here as this part works unfrequently. --- sql/item_timefunc.h | 19 ++++++++----------- 1 file changed, 8 insertions(+), 11 deletions(-) diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index acc5e6a80cc..14ceb8dcb28 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -330,7 +330,7 @@ public: enum_field_types field_type() const { return MYSQL_TYPE_DATE; } String *val_str(String *str); longlong val_int(); - double val_real() { DBUG_ASSERT(fixed == 1); return (double) val_int(); } + double val_real() { return val_real_from_decimal(); } const char *func_name() const { return "date"; } void fix_length_and_dec() { @@ -368,6 +368,7 @@ public: return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } bool result_as_longlong() { return TRUE; } + double val_real() { return (double) val_int(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -390,13 +391,14 @@ public: enum_field_types field_type() const { return MYSQL_TYPE_TIME; } void fix_length_and_dec() { - decimals=0; + decimals= DATETIME_DEC; max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; } Field *tmp_table_field(TABLE *t_arg) { return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); } + double val_real() { return val_real_from_decimal(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -504,7 +506,6 @@ public: Item_func_now() :Item_date_func() {} Item_func_now(Item *a) :Item_date_func(a) {} enum Item_result result_type () const { return STRING_RESULT; } - double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; } longlong val_int() { DBUG_ASSERT(fixed == 1); return value; } int save_in_field(Field *to, bool no_conversions); String *val_str(String *str); @@ -592,11 +593,6 @@ class Item_func_from_unixtime :public Item_date_func THD *thd; public: Item_func_from_unixtime(Item *a) :Item_date_func(a) {} - double val_real() - { - DBUG_ASSERT(fixed == 1); - return (double) Item_func_from_unixtime::val_int(); - } longlong val_int(); String *val_str(String *str); const char *func_name() const { return "from_unixtime"; } @@ -635,7 +631,6 @@ class Item_func_convert_tz :public Item_date_func Item_func_convert_tz(Item *a, Item *b, Item *c): Item_date_func(a, b, c), from_tz_cached(0), to_tz_cached(0) {} longlong val_int(); - double val_real() { return (double) val_int(); } String *val_str(String *str); const char *func_name() const { return "convert_tz"; } bool fix_fields(THD *, Item **); @@ -661,7 +656,6 @@ public: Item_str_timefunc::fix_length_and_dec(); collation.set(&my_charset_bin); maybe_null=1; - decimals= DATETIME_DEC; } const char *func_name() const { return "sec_to_time"; } bool result_as_longlong() { return TRUE; } @@ -699,7 +693,6 @@ public: const char *func_name() const { return "date_add_interval"; } void fix_length_and_dec(); enum_field_types field_type() const { return cached_field_type; } - double val_real() { DBUG_ASSERT(fixed == 1); return (double) val_int(); } longlong val_int(); bool get_date(TIME *res, uint fuzzy_date); bool eq(const Item *item, bool binary_cmp) const; @@ -800,6 +793,7 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val_real() { return (double) val_int(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -827,6 +821,7 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val_real() { return val_real_from_decimal(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); @@ -859,6 +854,7 @@ public: } bool result_as_longlong() { return TRUE; } longlong val_int(); + double val_real() { return val_real_from_decimal(); } double val() { return (double) val_int(); } my_decimal *val_decimal(my_decimal *decimal_value) { @@ -928,6 +924,7 @@ public: } void print(String *str); const char *func_name() const { return "add_time"; } + double val_real() { return val_real_from_decimal(); } my_decimal *val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); -- cgit v1.2.1