From 6b75e24b73828316dd1715ab32811a614ce74314 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 26 Jul 2006 19:19:30 +0300 Subject: * Bug #20792: Incorrect results from aggregate subquery When processing aggregate functions all tables values are reset to NULLs at the end of each group. When doing that if there are no rows found for a group the const tables must not be reset as they are not recalculated by do_select()/sub_select() for each group. mysql-test/r/subselect2.result: * Bug #20792: Incorrect results from aggregate subquery - test suite for the bug. This is dependent on InnoDB despite the fact that the bug and the fix are not InnoDB specific. This is because of the table flag HA_NOT_EXACT_COUNT. When this flag is off (as in MyISAM) both t2 and t3 become of join type 'system' as they are estimated to have 1 record and and this statistics can be trusted (according to the absence of HA_NOT_EXACT_COUNT). mysql-test/t/subselect2.test: * Bug #20792: Incorrect results from aggregate subquery - test suite for the bug sql/sql_select.cc: * Bug #20792: Incorrect results from aggregate subquery - when clearing results if there are not rows found for group the const tables must not be reset as they are not recalculated for each group. --- mysql-test/r/subselect2.result | 12 ++++++++++++ mysql-test/t/subselect2.test | 18 ++++++++++++++++++ sql/sql_select.cc | 10 ++++++++-- 3 files changed, 38 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 8fcfa06a8ae..db1848105f8 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -130,3 +130,15 @@ id select_type table type possible_keys key key_len ref rows Extra 5 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX PRIMARY 32 func 1 Using index; Using where 6 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 32 func 1 Using index; Using where drop table t1, t2, t3, t4; +CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB; +INSERT INTO t2 VALUES (1); +CREATE TABLE t3 (a int(10), b int(10), c int(10), +PRIMARY KEY (a)) Engine=InnoDB; +INSERT INTO t3 VALUES (1,2,1); +SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a +and t2.a='1' AND t1.a=t3.b) > 0; +a +2 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index b21eda176b6..162bdd0d90a 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -150,3 +150,21 @@ EXPLAIN SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JO drop table t1, t2, t3, t4; # End of 4.1 tests + +# +# Bug #20792: Incorrect results from aggregate subquery +# +CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (a int(10), PRIMARY KEY (a)) Engine=InnoDB; +INSERT INTO t2 VALUES (1); + +CREATE TABLE t3 (a int(10), b int(10), c int(10), + PRIMARY KEY (a)) Engine=InnoDB; +INSERT INTO t3 VALUES (1,2,1); + +SELECT t1.* FROM t1 WHERE (SELECT COUNT(*) FROM t3,t2 WHERE t3.c=t2.a + and t2.a='1' AND t1.a=t3.b) > 0; + +DROP TABLE t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fd8a5149edd..ce573136faf 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4452,10 +4452,16 @@ return_zero_rows(JOIN *join, select_result *result,TABLE_LIST *tables, DBUG_RETURN(0); } - +/* + used only in JOIN::clear +*/ static void clear_tables(JOIN *join) { - for (uint i=0 ; i < join->tables ; i++) + /* + must clear only the non-const tables, as const tables + are not re-calculated. + */ + for (uint i=join->const_tables ; i < join->tables ; i++) mark_as_null_row(join->table[i]); // All fields are NULL } -- cgit v1.2.1 From 1150869c4a6418b9dbcbe6b9b2f6036e1c5379f1 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 26 Jul 2006 21:36:03 +0400 Subject: item_func.h, item_func.cc, sql_select.cc, item.h: Post review changes for bug#19862. sql/sql_select.cc: Post review changes for bug#19862. sql/item_func.h: Post review changes for bug#19862. sql/item_func.cc: Post review changes for bug#19862. sql/item.h: Post review changes for bug#19862. --- sql/item.h | 2 +- sql/item_func.cc | 4 ++-- sql/item_func.h | 5 ++++- sql/sql_select.cc | 5 +---- 4 files changed, 8 insertions(+), 8 deletions(-) diff --git a/sql/item.h b/sql/item.h index c59d84aaeaa..514c31c2d74 100644 --- a/sql/item.h +++ b/sql/item.h @@ -752,7 +752,7 @@ public: virtual bool find_item_in_field_list_processor(byte *arg) { return 0; } virtual bool change_context_processor(byte *context) { return 0; } virtual bool reset_query_id_processor(byte *query_id) { return 0; } - virtual bool func_type_checker_processor(byte *arg) { return 0; } + virtual bool is_expensive_processor(byte *arg) { return 0; } virtual Item *equal_fields_propagator(byte * arg) { return this; } virtual Item *set_no_const_sub(byte *arg) { return this; } diff --git a/sql/item_func.cc b/sql/item_func.cc index c31ac2bf047..cbab9a4de1b 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -399,9 +399,9 @@ Field *Item_func::tmp_table_field(TABLE *t_arg) } -bool Item_func::func_type_checker_processor(byte *arg) +bool Item_func::is_expensive_processor(byte *arg) { - return *((Functype*)arg) == functype(); + return is_expensive(); } diff --git a/sql/item_func.h b/sql/item_func.h index bac4a186867..b33dae092ac 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -189,7 +189,8 @@ public: Item *transform(Item_transformer transformer, byte *arg); void traverse_cond(Cond_traverser traverser, void * arg, traverse_order order); - bool func_type_checker_processor(byte *arg); + bool is_expensive_processor(byte *arg); + virtual bool is_expensive() { return 0; } }; @@ -947,6 +948,7 @@ public: void cleanup(); Item_result result_type () const { return udf.result_type(); } table_map not_null_tables() const { return 0; } + bool is_expensive() { return 1; } }; @@ -1475,6 +1477,7 @@ public: virtual enum Functype functype() const { return FUNC_SP; } bool fix_fields(THD *thd, Item **ref); + bool is_expensive() { return 1; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 36962a233b8..20945117da1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1073,10 +1073,7 @@ JOIN::optimize() for (ORDER *tmp_order= order; tmp_order ; tmp_order=tmp_order->next) { Item *item= *tmp_order->item; - Item_func::Functype type=Item_func::FUNC_SP; - Item_func::Functype type1=Item_func::UDF_FUNC; - if (item->walk(&Item::func_type_checker_processor,(byte*)&type) || - item->walk(&Item::func_type_checker_processor,(byte*)&type1)) + if (item->walk(&Item::is_expensive_processor,(byte*)0)) { /* Force tmp table without sort */ need_tmp=1; simple_order=simple_group=0; -- cgit v1.2.1