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.


mysql-test/r/func_group.result:
  Test for BUG#12882.
mysql-test/t/func_group.test:
  Test for BUG#12882.
sql/item_sum.cc:
  If it is known that a query has no result rows, do not call add()
  via the call to Item_sum::no_rows_in_result() which calls reset().
  Instead directly call clear() so that the MIN and MAX functions
  produce NULL when there are no result rows.
sql/opt_sum.cc:
  * Do not apply MIN/MAX optimization when the operand of MIN/MAX is
    a constant if it can't be determined whether the query has any
    result rows. The reason is that if the query has result rows,
    then the result of MIN/MAX is its constant argument, but if the
    query result is empty, then the result of MIN/MAX must be NULL
    irrespective of its argument.
  
  * The patch also simplifies a bit the branch that hadles COUNT().

1 files changed, 136 insertions, 0 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;