summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorunknown <timour@mysql.com>2005-09-21 09:53:42 +0300
committerunknown <timour@mysql.com>2005-09-21 09:53:42 +0300
commita4243d3c6b6b24871213a704fd9d1f371c532339 (patch)
tree5b108169560cc59bd2a619cf95326a3b306fb466
parent458a4234e4ce764fdc40de8c46bee2a198151fb7 (diff)
parent8e7217e43bed6db3bffd97a39b85c65e1a72579a (diff)
downloadmariadb-git-a4243d3c6b6b24871213a704fd9d1f371c532339.tar.gz
Merge mysql.com:/home/timka/mysql/src/4.1-virgin
into mysql.com:/home/timka/mysql/src/4.1-dbg sql/opt_sum.cc: Auto merged
-rw-r--r--mysql-test/r/func_group.result136
-rw-r--r--mysql-test/t/func_group.test69
-rw-r--r--sql/item_sum.cc2
-rw-r--r--sql/opt_sum.cc96
4 files changed, 273 insertions, 30 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 2fb18ca92c7..67966b999d4 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -780,3 +780,139 @@ SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
MAX(id)
NULL
DROP TABLE t1;
+create table t1m (a int) engine=myisam;
+create table t1i (a int) engine=innodb;
+create table t2m (a int) engine=myisam;
+create table t2i (a int) engine=innodb;
+insert into t2m values (5);
+insert into t2i values (5);
+select min(a) from t1m;
+min(a)
+NULL
+select min(7) from t1m;
+min(7)
+NULL
+select min(7) from DUAL;
+min(7)
+NULL
+explain select min(7) from t2m join t1m;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+select min(7) from t2m join t1m;
+min(7)
+NULL
+select max(a) from t1m;
+max(a)
+NULL
+select max(7) from t1m;
+max(7)
+NULL
+select max(7) from DUAL;
+max(7)
+NULL
+explain select max(7) from t2m join t1m;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+select max(7) from t2m join t1m;
+max(7)
+NULL
+select 1, min(a) from t1m where a=99;
+1 min(a)
+1 NULL
+select 1, min(a) from t1m where 1=99;
+1 min(a)
+1 NULL
+select 1, min(1) from t1m where a=99;
+1 min(1)
+select 1, min(1) from t1m where 1=99;
+1 min(1)
+1 NULL
+select 1, max(a) from t1m where a=99;
+1 max(a)
+1 NULL
+select 1, max(a) from t1m where 1=99;
+1 max(a)
+1 NULL
+select 1, max(1) from t1m where a=99;
+1 max(1)
+select 1, max(1) from t1m where 1=99;
+1 max(1)
+1 NULL
+select min(a) from t1i;
+min(a)
+NULL
+select min(7) from t1i;
+min(7)
+NULL
+select min(7) from DUAL;
+min(7)
+NULL
+explain select min(7) from t2i join t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2i ALL NULL NULL NULL NULL 1
+1 SIMPLE t1i ALL NULL NULL NULL NULL 1
+select min(7) from t2i join t1i;
+min(7)
+NULL
+select max(a) from t1i;
+max(a)
+NULL
+select max(7) from t1i;
+max(7)
+NULL
+select max(7) from DUAL;
+max(7)
+NULL
+explain select max(7) from t2i join t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2i ALL NULL NULL NULL NULL 1
+1 SIMPLE t1i ALL NULL NULL NULL NULL 1
+select max(7) from t2i join t1i;
+max(7)
+NULL
+select 1, min(a) from t1i where a=99;
+1 min(a)
+1 NULL
+select 1, min(a) from t1i where 1=99;
+1 min(a)
+1 NULL
+select 1, min(1) from t1i where a=99;
+1 min(1)
+1 NULL
+select 1, min(1) from t1i where 1=99;
+1 min(1)
+1 NULL
+select 1, max(a) from t1i where a=99;
+1 max(a)
+1 NULL
+select 1, max(a) from t1i where 1=99;
+1 max(a)
+1 NULL
+select 1, max(1) from t1i where a=99;
+1 max(1)
+1 NULL
+select 1, max(1) from t1i where 1=99;
+1 max(1)
+1 NULL
+explain select count(*), min(7), max(7) from t1m, t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
+1 SIMPLE t1i ALL NULL NULL NULL NULL 1
+select count(*), min(7), max(7) from t1m, t1i;
+count(*) min(7) max(7)
+0 NULL NULL
+explain select count(*), min(7), max(7) from t1m, t2i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
+1 SIMPLE t2i ALL NULL NULL NULL NULL 1
+select count(*), min(7), max(7) from t1m, t2i;
+count(*) min(7) max(7)
+0 NULL NULL
+explain select count(*), min(7), max(7) from t2m, t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2m system NULL NULL NULL NULL 1
+1 SIMPLE t1i ALL NULL NULL NULL NULL 1
+select count(*), min(7), max(7) from t2m, t1i;
+count(*) min(7) max(7)
+0 NULL NULL
+drop table t1m, t1i, t2m, t2i;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 4dc8d66c4f1..f833f482f73 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -527,4 +527,73 @@ INSERT INTO t1 VALUES
SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
DROP TABLE t1;
+#
+# Bug #12882 min/max inconsistent on empty table
+#
+
+create table t1m (a int) engine=myisam;
+create table t1i (a int) engine=innodb;
+create table t2m (a int) engine=myisam;
+create table t2i (a int) engine=innodb;
+insert into t2m values (5);
+insert into t2i values (5);
+
+# test with MyISAM
+select min(a) from t1m;
+select min(7) from t1m;
+select min(7) from DUAL;
+explain select min(7) from t2m join t1m;
+select min(7) from t2m join t1m;
+
+select max(a) from t1m;
+select max(7) from t1m;
+select max(7) from DUAL;
+explain select max(7) from t2m join t1m;
+select max(7) from t2m join t1m;
+
+select 1, min(a) from t1m where a=99;
+select 1, min(a) from t1m where 1=99;
+select 1, min(1) from t1m where a=99;
+select 1, min(1) from t1m where 1=99;
+
+select 1, max(a) from t1m where a=99;
+select 1, max(a) from t1m where 1=99;
+select 1, max(1) from t1m where a=99;
+select 1, max(1) from t1m where 1=99;
+
+# test with InnoDB
+select min(a) from t1i;
+select min(7) from t1i;
+select min(7) from DUAL;
+explain select min(7) from t2i join t1i;
+select min(7) from t2i join t1i;
+
+select max(a) from t1i;
+select max(7) from t1i;
+select max(7) from DUAL;
+explain select max(7) from t2i join t1i;
+select max(7) from t2i join t1i;
+
+select 1, min(a) from t1i where a=99;
+select 1, min(a) from t1i where 1=99;
+select 1, min(1) from t1i where a=99;
+select 1, min(1) from t1i where 1=99;
+
+select 1, max(a) from t1i where a=99;
+select 1, max(a) from t1i where 1=99;
+select 1, max(1) from t1i where a=99;
+select 1, max(1) from t1i where 1=99;
+
+# mixed MyISAM/InnoDB test
+explain select count(*), min(7), max(7) from t1m, t1i;
+select count(*), min(7), max(7) from t1m, t1i;
+
+explain select count(*), min(7), max(7) from t1m, t2i;
+select count(*), min(7), max(7) from t1m, t2i;
+
+explain select count(*), min(7), max(7) from t2m, t1i;
+select count(*), min(7), max(7) from t2m, t1i;
+
+drop table t1m, t1i, t2m, t2i;
+
# End of 4.1 tests
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 6ae782db557..43468adea1a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -552,8 +552,8 @@ void Item_sum_hybrid::cleanup()
void Item_sum_hybrid::no_rows_in_result()
{
- Item_sum::no_rows_in_result();
was_values= FALSE;
+ clear();
}
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index cb8e3c2d273..3ac72e5a6f9 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<Item> &all_fields,COND *conds)
List_iterator_fast<Item> 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<Item> &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<Item> &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<Item> &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<Item> &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<Item> &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;
}