Fix bug lp:985667, MDEV-229

Analysis:

The reason for the wrong result is the interaction between constant
optimization (in this case 1-row table) and subquery optimization.

- First the outer query is optimized, and 'make_join_statistics' finds that
table t2 has one row, reads that row, and marks the whole table as constant.
This also means that all fields of t2 are constant.

- Next, we optimize the subquery in the end of the outer 'make_join_statistics'.
The field 'f2' is considered constant, with value '3'. The subquery predicate
is rewritten as the constant TRUE.

- The outer query execution detects early that the whole query result is empty
and calls 'return_zero_rows'. Since the query is with implicit grouping, we
have to produce one row with special values for the aggregates (depending on
each aggregate function), and NULL values for all non-aggregate fields.  This
function calls 'no_rows_in_result' to set each aggregate function to the
default value when it aggregates over an empty result, and then calls
'send_data', which in turn evaluates each Item in the SELECT list.

- When evaluation reaches the subquery predicate, it executes the subquery
with field 'f2' having a constant value '3', and the subquery produces the
incorrect result '7'.

Solution:

Implement Item::no_rows_in_result for all subquery predicates. In order to
make this work, it is also needed to make all val_* methods of all subquery
predicates respect the Item_subselect::forced_const flag. Otherwise subqueries
are executed anyways, and override the default value set by no_rows_in_result
with whatever result is produced from the subquery evaluation.

1 files changed, 1 insertions, 0 deletions

diff --git a/sql/sql_select.h b/sql/sql_select.h
index 874ff959b1d..2724c4a5a63 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1322,6 +1322,7 @@ public:
     return (do_send_rows && implicit_grouping && !group_optimized_away &&
             having_value != Item::COND_FALSE);
   }
+  bool empty_result() { return (zero_result_cause && !implicit_grouping); }
   bool change_result(select_result *result);
   bool is_top_level_join() const
   {