• Martin Hansson's avatar
    Bug#47650: using group by with rollup without indexes · 6863f7dc
    Martin Hansson authored
    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
    6863f7dc
join_outer.result 41.3 KB