Fix bug lp:1008773

Analysis:
Queries with implicit grouping (there is aggregate, but no group by)
follow some non-obvious semantics in the case of empty result set.
Aggregate functions produce some special "natural" value depending on
the function. For instance MIN/MAX return NULL, COUNT returns 0.

The complexity comes from non-aggregate expressions in the select list.
If the non-aggregate expression is a constant, it can be computed, so
we should return its value, however if the expression is non-constant,
and depends on columns from the empty result set, then the only meaningful
value is NULL.

The cause of the wrong result was that for subqueries the optimizer didn't
make a difference between constant and non-constant ones in the case of
empty result for implicit grouping.

Solution:
In all implementations of Item_subselect::no_rows_in_result() check if the
subquery predicate is constant. If it is constant, do not set it to the
default value for implicit grouping, instead let it be evaluated.

3 files changed, 97 insertions, 0 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 5e2baf47aec..8573b5bb8c4 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2139,6 +2139,61 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	SUBQUERY	five	ALL	NULL	NULL	NULL	NULL	5	Using where
 drop table ten, t1, five;
+#
+# LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+EXPLAIN
+SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
+MAX(a)	bb
+NULL	1
+EXPLAIN
+SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
+MAX(a)	bb
+NULL	1
+EXPLAIN
+SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+MAX(a)	bb
+NULL	1
+EXPLAIN
+SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
+MAX(a)	bb
+NULL	NULL
+EXPLAIN
+SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
+MAX(a)	bb
+NULL	NULL
+EXPLAIN
+SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+MAX(a)	bb
+NULL	NULL
+drop table t1, t2;
 set optimizer_switch=@subselect4_tmp;
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 92b34db05e4..e978ebdbfcc 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -1777,6 +1777,40 @@ explain select * from t1 where 33 in (select b from five) or c > 11;
 
 drop table ten, t1, five;
 
+--echo #
+--echo # LP BUG#1008773 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING
+--echo #
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+
+EXPLAIN
+SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
+SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
+
+EXPLAIN
+SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
+SELECT MAX(a), 1 in ( SELECT b FROM t2 ) AS bb FROM t1;
+
+EXPLAIN
+SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+SELECT MAX(a), 1 >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+
+
+EXPLAIN
+SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
+SELECT MAX(a), ( SELECT 1 FROM t2 where b = a) AS bb FROM t1;
+
+EXPLAIN
+SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
+SELECT MAX(a), a in ( SELECT b FROM t2 ) AS bb FROM t1;
+
+EXPLAIN
+SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+SELECT MAX(a), a >= ALL ( SELECT b FROM t2 ) AS bb FROM t1;
+
+drop table t1, t2;
 
 set optimizer_switch=@subselect4_tmp;
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index cae1a9f7541..5af8eb9ebc9 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -892,6 +892,8 @@ void Item_maxmin_subselect::print(String *str, enum_query_type query_type)
 
 void Item_maxmin_subselect::no_rows_in_result()
 {
+  if (const_item())
+    return;
   value= Item_cache::get_cache(new Item_null());
   null_value= 0;
   was_values= 0;
@@ -901,6 +903,8 @@ void Item_maxmin_subselect::no_rows_in_result()
 
 void Item_singlerow_subselect::no_rows_in_result()
 {
+  if (const_item())
+    return;
   value= Item_cache::get_cache(new Item_null());
   reset();
   make_const();
@@ -1363,6 +1367,8 @@ Item* Item_exists_subselect::expr_cache_insert_transformer(uchar *thd_arg)
 
 void Item_exists_subselect::no_rows_in_result()
 {
+  if (const_item())
+    return;
   value= 0;
   null_value= 0;
   make_const();
@@ -2707,6 +2713,8 @@ void Item_allany_subselect::print(String *str, enum_query_type query_type)
 
 void Item_allany_subselect::no_rows_in_result()
 {
+  if (const_item())
+    return;
   value= 0;
   null_value= 0;
   was_null= 0;