Bug#47650: using group by with rollup without indexes

returns incorrect results with where

An outer join of a const table (outer) and a normal table
(inner) with GROUP BY on a field from the outer table would
optimize away GROUP BY, and thus trigger the optimization to
do away with a temporary table if grouping was performed on
columns from the const table, hence executing the query with
filesort without temporary table. But this should not be
done if there is a non-indexed access to the inner table,
since filesort does not handle joins. It expects either ref
access, range ditto or table scan. The join condition will
thus not be applied.

Fixed by always forcing execution with temporary table in
the case of ROLLUP with a query involving an outer join. This
is a slightly broader class of queries than need fixing, but
it is hard to ascertain the position of a ROLLUP field wrt
outer join with current query representation.

mysql-test/r/join_outer.result:
  Bug#47650: Test result
mysql-test/t/join_outer.test:
  Bug#47650: Test case
sql/sql_select.cc:
  Bug#47650: Fix

1 files changed, 13 insertions, 1 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cbee09f1fdb..6383fe63012 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7124,7 +7124,19 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
   for (order=first_order; order ; order=order->next)
   {
     table_map order_tables=order->item[0]->used_tables();
-    if (order->item[0]->with_sum_func)
+    if (order->item[0]->with_sum_func ||
+        /*
+          If the outer table of an outer join is const (either by itself or
+          after applying WHERE condition), grouping on a field from such a
+          table will be optimized away and filesort without temporary table
+          will be used unless we prevent that now. Filesort is not fit to
+          handle joins and the join condition is not applied. We can't detect
+          the case without an expensive test, however, so we force temporary
+          table for all queries containing more than one table, ROLLUP, and an
+          outer join.
+         */
+        (join->tables > 1 && join->rollup.state == ROLLUP::STATE_INITED &&
+        join->outer_join))
       *simple_order=0;				// Must do a temp table to sort
     else if (!(order_tables & not_const_tables))
     {