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, 9 insertions, 0 deletions

diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 4f3c85fea62..ec3b44dc96c 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -146,6 +146,11 @@ public:
     eliminated= FALSE;
     null_value= 1;
   }
+  /**
+    Set the subquery result to the default value for the predicate when the
+    subquery is known to produce an empty result.
+  */
+  void no_rows_in_result()= 0;
   virtual bool select_transformer(JOIN *join);
   bool assigned() { return value_assigned; }
   void assigned(bool a) { value_assigned= a; }
@@ -262,6 +267,7 @@ public:
   subs_type substype() { return SINGLEROW_SUBS; }
 
   void reset();
+  void no_rows_in_result() { reset(); make_const(); }
   bool select_transformer(JOIN *join);
   void store(uint i, Item* item);
   double val_real();
@@ -314,6 +320,7 @@ public:
   bool any_value() { return was_values; }
   void register_value() { was_values= TRUE; }
   void reset_value_registration() { was_values= FALSE; }
+  void no_rows_in_result();
 };
 
 /* exists subselect */
@@ -335,6 +342,7 @@ public:
     eliminated= FALSE;
     value= 0;
   }
+  void no_rows_in_result();
 
   enum Item_result result_type() const { return INT_RESULT;}
   longlong val_int();
@@ -664,6 +672,7 @@ public:
   virtual void print(String *str, enum_query_type query_type);
   bool is_maxmin_applicable(JOIN *join);
   bool transform_into_max_min(JOIN *join);
+  void no_rows_in_result();
 };