From afa8187fb77f72fbc767ae112bc8050c51afc398 Mon Sep 17 00:00:00 2001 From: "evgen@moonbone.local" <> Date: Mon, 19 Sep 2005 04:39:49 +0400 Subject: Fix bug #13218: InnoDB: using a partial-field key prefix in search This is backport from 5.0 of fix for bug #11039 --- sql/opt_sum.cc | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'sql/opt_sum.cc') diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 4ab506cc4e1..cb8e3c2d273 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -661,7 +661,8 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, If key_part2 may be NULL, then we want to find the first row that is not null */ - ref->key_buff[ref->key_length++]= 1; + ref->key_buff[ref->key_length]= 1; + ref->key_length+= part->store_length; *range_fl&= ~NO_MIN_RANGE; *range_fl|= NEAR_MIN; // > NULL } -- cgit v1.2.1 From 633d118d01eedea0d15969cd4b049525ea680f53 Mon Sep 17 00:00:00 2001 From: "timour@mysql.com" <> Date: Wed, 21 Sep 2005 09:49:19 +0300 Subject: Fix for BUG#12882 - min/max inconsistent on empty table. The problem was in that the MIN/MAX optimization in opt_sum_query was replacing MIN/MAX functions with their constant argument without taking into account that a query has no result rows. --- sql/opt_sum.cc | 96 ++++++++++++++++++++++++++++++++++++++++------------------ 1 file changed, 67 insertions(+), 29 deletions(-) (limited to 'sql/opt_sum.cc') diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 4ab506cc4e1..3d88d85af87 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -59,8 +59,8 @@ static int maxmin_in_range(bool max_fl, Field* field, COND *cond); SYNOPSIS opt_sum_query() - tables Tables in query - all_fields All fields to be returned + tables Tables in query + all_fields All fields to be returned conds WHERE clause NOTE: @@ -80,6 +80,8 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) List_iterator_fast it(all_fields); int const_result= 1; bool recalc_const_item= 0; + longlong count= 1; + bool is_exact_count= TRUE; table_map removed_tables= 0, outer_tables= 0, used_tables= 0; table_map where_tables= 0; Item *item; @@ -88,9 +90,13 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) if (conds) where_tables= conds->used_tables(); - /* Don't replace expression on a table that is part of an outer join */ + /* + Analyze outer join dependencies, and, if possible, compute the number + of returned rows. + */ for (TABLE_LIST *tl=tables; tl ; tl= tl->next) { + /* Don't replace expression on a table that is part of an outer join */ if (tl->on_expr) { outer_tables|= tl->table->map; @@ -102,15 +108,31 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) WHERE t2.field IS NULL; */ if (tl->table->map & where_tables) - return 0; + const_result= 0; } else used_tables|= tl->table->map; + + /* + If the storage manager of 'tl' gives exact row count, compute the total + number of rows. If there are no outer table dependencies, this count + may be used as the real count. + */ + if (tl->table->file->table_flags() & HA_NOT_EXACT_COUNT) + is_exact_count= FALSE; + else + { + tl->table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); + count*= tl->table->file->records; + } } + if (!const_result) + return 0; + /* - Iterate through item is select part and replace COUNT(), MIN() and MAX() - with constants (if possible) + Iterate through all items in the SELECT clause and replace + COUNT(), MIN() and MAX() with constants (if possible). */ while ((item= it++)) @@ -122,28 +144,14 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) case Item_sum::COUNT_FUNC: /* If the expr in count(expr) can never be null we can change this - to the number of rows in the tables + to the number of rows in the tables if this number is exact and + there are no outer joins. */ - if (!conds && !((Item_sum_count*) item)->args[0]->maybe_null) + if (!conds && !((Item_sum_count*) item)->args[0]->maybe_null && + !outer_tables && is_exact_count) { - longlong count= 1; - TABLE_LIST *table; - for (table=tables ; table ; table=table->next) - { - if (outer_tables || (table->table->file->table_flags() & - HA_NOT_EXACT_COUNT)) - { - const_result= 0; // Can't optimize left join - break; - } - tables->table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); - count*= table->table->file->records; - } - if (!table) - { ((Item_sum_count*) item)->make_const(count); recalc_const_item= 1; - } } else const_result= 0; @@ -210,12 +218,27 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) } removed_tables|= table->map; } - else if (!expr->const_item()) // This is VERY seldom false + else if (!expr->const_item() || !is_exact_count) { + /* + The optimization is not applicable in both cases: + (a) 'expr' is a non-constant expression. Then we can't + replace 'expr' by a constant. + (b) 'expr' is a costant. According to ANSI, MIN/MAX must return + NULL if the query does not return any rows. Thus, if we are not + able to determine if the query returns any rows, we can't apply + the optimization and replace MIN/MAX with a constant. + */ const_result= 0; break; } - ((Item_sum_min*) item_sum)->reset(); + if (!count) + { + /* If count != 1, then we know that is_exact_count == TRUE. */ + ((Item_sum_min*) item_sum)->clear(); /* Set to NULL. */ + } + else + ((Item_sum_min*) item_sum)->reset(); /* Set to the constant value. */ ((Item_sum_min*) item_sum)->make_const(); recalc_const_item= 1; break; @@ -282,13 +305,28 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) } removed_tables|= table->map; } - else if (!expr->const_item()) // This is VERY seldom false + else if (!expr->const_item() || !is_exact_count) { + /* + The optimization is not applicable in both cases: + (a) 'expr' is a non-constant expression. Then we can't + replace 'expr' by a constant. + (b) 'expr' is a costant. According to ANSI, MIN/MAX must return + NULL if the query does not return any rows. Thus, if we are not + able to determine if the query returns any rows, we can't apply + the optimization and replace MIN/MAX with a constant. + */ const_result= 0; break; } - ((Item_sum_min*) item_sum)->reset(); - ((Item_sum_min*) item_sum)->make_const(); + if (!count) + { + /* If count != 1, then we know that is_exact_count == TRUE. */ + ((Item_sum_max*) item_sum)->clear(); /* Set to NULL. */ + } + else + ((Item_sum_max*) item_sum)->reset(); /* Set to the constant value. */ + ((Item_sum_max*) item_sum)->make_const(); recalc_const_item= 1; break; } -- cgit v1.2.1 From d55eeed8fd85427c2431bd54dfc387b59629f50b Mon Sep 17 00:00:00 2001 From: "msvensson@neptunus.(none)" <> Date: Fri, 23 Sep 2005 14:37:22 +0200 Subject: Merge from 4.1 to 5.0 --- sql/opt_sum.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/opt_sum.cc') diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index d598b8e6402..2f54cce0275 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -167,6 +167,7 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) { ((Item_sum_count*) item)->make_const(count); recalc_const_item= 1; + } } else const_result= 0; -- cgit v1.2.1 From ea6d5f9ec38e63532bf6b7f4b21ff7b227336954 Mon Sep 17 00:00:00 2001 From: "monty@mysql.com" <> Date: Sun, 25 Sep 2005 21:22:23 +0300 Subject: Fixed error found during review of new pushed code --- sql/opt_sum.cc | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'sql/opt_sum.cc') diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 3ac72e5a6f9..cfb5b3695a3 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -108,7 +108,7 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) WHERE t2.field IS NULL; */ if (tl->table->map & where_tables) - const_result= 0; + return 0; } else used_tables|= tl->table->map; @@ -119,7 +119,10 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) may be used as the real count. */ if (tl->table->file->table_flags() & HA_NOT_EXACT_COUNT) + { is_exact_count= FALSE; + count= 1; // ensure count != 0 + } else { tl->table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); @@ -127,9 +130,6 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) } } - if (!const_result) - return 0; - /* Iterate through all items in the SELECT clause and replace COUNT(), MIN() and MAX() with constants (if possible). @@ -150,8 +150,8 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) if (!conds && !((Item_sum_count*) item)->args[0]->maybe_null && !outer_tables && is_exact_count) { - ((Item_sum_count*) item)->make_const(count); - recalc_const_item= 1; + ((Item_sum_count*) item)->make_const(count); + recalc_const_item= 1; } else const_result= 0; @@ -234,7 +234,7 @@ int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) } if (!count) { - /* If count != 1, then we know that is_exact_count == TRUE. */ + /* If count == 0, then we know that is_exact_count == TRUE. */ ((Item_sum_min*) item_sum)->clear(); /* Set to NULL. */ } else -- cgit v1.2.1