Bug#16539979 - BASIC SELECT COUNT(DISTINCT ID) IS BROKEN

Bug#17867117 - ERROR RESULT WHEN "COUNT + DISTINCT + CASE WHEN" NEED MERGE_WALK 

Problem:
COUNT DISTINCT gives incorrect result when it uses a Unique
Tree and its last inserted record has null value.

Here is how COUNT DISTINCT is processed, given that this query is not
using loose index scan.

When a row is produced as a result of joining tables (there is only
one table here), we store the SELECTed value in a Unique tree. This
allows elimination of any duplicates, and thus implements DISTINCT.

When we have processed all rows like this, we walk the Unique tree,
counting its elements, in Aggregator_distinct::endup() (tree->walk());
for each element we call Item_sum_count::add(). Such function wants to
ignore any NULL value, for that it checks item_sum -> args[0] ->
null_value. It is a mistake: when walking the Unique tree, the value
to be aggregated is not item_sum ->args[0] but rather table ->
field[0].

Solution:
instead of item_sum -> args[0] -> null_value, use arg_is_null(), which
knows where to look (like in fix for bug 57932).

As a consequence of this solution, we have to make arg_is_null() a
little more general:
1) Because it was so far only used for AVG() (which always has a
single argument), this function was looking at a single argument; now
that it has to work with COUNT(DISTINCT expression1,expression2), it
must look at all arguments.
2) Because we start using arg_is_null () for COUNT(DISTINCT), i.e. in
Item_sum_count::add (), it implies that we are also using it for
COUNT(no DISTINCT) (same add ()). For COUNT(no DISTINCT), the
nullness to check is that of item_sum -> args[0]. But the null_value
of such item is reliable only if val_*() has been called on it. So far
arg_is_null() was always used after a call to arg_val*(), so could
rely on null_value; but for COUNT, there is no call to arg_val*(), so
arg_is_null() has to call is_null() instead.

Testcase for 16539979 by Neeraj. Testcase for 17867117 contributed by
Xiaobin Lin from Taobao.

1 files changed, 45 insertions, 22 deletions

diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 3fbbc1b811a..3b2a916f30a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1052,18 +1052,19 @@ void Aggregator_distinct::endup()
       endup_done= TRUE;
     }
   }
-  else
-  {
-    /*
-      We don't have a tree only if 'setup()' hasn't been called;
-      this is the case of sql_select.cc:return_zero_rows.
-    */
-    if (tree)
-      table->field[0]->set_notnull();
-  }
 
+ /*
+   We don't have a tree only if 'setup()' hasn't been called;
+   this is the case of sql_executor.cc:return_zero_rows.
+ */
   if (tree && !endup_done)
   {
+   /*
+     All tree's values are not NULL.
+     Note that value of field is changed as we walk the tree, in
+     Aggregator_distinct::unique_walk_function, but it's always not NULL.
+   */
+   table->field[0]->set_notnull();
     /* go over the tree of distinct keys and calculate the aggregate value */
     use_distinct_values= TRUE;
     tree->walk(item_sum_distinct_walk, (void*) this);
@@ -1334,7 +1335,7 @@ bool Item_sum_sum::add()
   {
     my_decimal value;
     const my_decimal *val= aggr->arg_val_decimal(&value);
-    if (!aggr->arg_is_null())
+    if (!aggr->arg_is_null(true))
     {
       my_decimal_add(E_DEC_FATAL_ERROR, dec_buffs + (curr_dec_buff^1),
                      val, dec_buffs + curr_dec_buff);
@@ -1345,7 +1346,7 @@ bool Item_sum_sum::add()
   else
   {
     sum+= aggr->arg_val_real();
-    if (!aggr->arg_is_null())
+    if (!aggr->arg_is_null(true))
       null_value= 0;
   }
   DBUG_RETURN(0);
@@ -1455,9 +1456,27 @@ double Aggregator_simple::arg_val_real()
 }
 
 
-bool Aggregator_simple::arg_is_null()
+bool Aggregator_simple::arg_is_null(bool use_null_value)
 {
-  return item_sum->args[0]->null_value;
+  Item **item= item_sum->args;
+  const uint item_count= item_sum->arg_count;
+  if (use_null_value)
+  {
+    for (uint i= 0; i < item_count; i++)
+    {
+      if (item[i]->null_value)
+        return true;
+    }
+  }
+  else
+  {
+    for (uint i= 0; i < item_count; i++)
+    {
+      if (item[i]->maybe_null && item[i]->is_null())
+        return true;
+    }
+  }
+  return false;
 }
 
 
@@ -1475,10 +1494,17 @@ double Aggregator_distinct::arg_val_real()
 }
 
 
-bool Aggregator_distinct::arg_is_null()
+bool Aggregator_distinct::arg_is_null(bool use_null_value)
 {
-  return use_distinct_values ? table->field[0]->is_null() :
-    item_sum->args[0]->null_value;
+  if (use_distinct_values)
+  {
+    const bool rc= table->field[0]->is_null();
+    DBUG_ASSERT(!rc); // NULLs are never stored in 'tree'
+    return rc;
+  }
+  return use_null_value ?
+    item_sum->args[0]->null_value :
+    (item_sum->args[0]->maybe_null && item_sum->args[0]->is_null());
 }
 
 
@@ -1496,11 +1522,8 @@ void Item_sum_count::clear()
 
 bool Item_sum_count::add()
 {
-  for (uint i=0; i<arg_count; i++)
-  {
-    if (args[i]->maybe_null && args[i]->is_null())
-      return 0;
-  }
+  if (aggr->arg_is_null(false))
+    return 0;
   count++;
   return 0;
 }
@@ -1591,7 +1614,7 @@ bool Item_sum_avg::add()
 {
   if (Item_sum_sum::add())
     return TRUE;
-  if (!aggr->arg_is_null())
+  if (!aggr->arg_is_null(true))
     count++;
   return FALSE;
 }