• unknown's avatar
    Fix for bug lp:992405 · 213476ef
    unknown authored
    The patch backports two patches from mysql 5.6:
    - BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A DIFFERENT QUERY OUTPUT
    - Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY ROWS WHEN GROUP IS OPTIMIZED AWAY
    
    Original comment:
    -----------------
    3714 Jorgen Loland	2012-03-01
          BUG#12640437 - USING SQL_BUFFER_RESULT RESULTS IN A DIFFERENT 
                         QUERY OUTPUT
          
          For all but simple grouped queries, temporary tables are used to
          resolve grouping. In these cases, the list of grouping fields is
          stored in the temporary table and grouping is resolved
          there (e.g. by adding a unique constraint on the involved
          fields). Because of this, grouping is already done when the rows
          are read from the temporary table.
          
          In the case where a group clause may be optimized away, grouping
          does not have to be resolved using a temporary table. However, if
          a temporary table is explicitly requested (e.g. because the
          SQL_BUFFER_RESULT hint is used, or the statement is
          INSERT...SELECT), a temporary table is used anyway. In this case,
          the temporary table is created with an empty group list (because
          the group clause was optimized away) and it will therefore not
          create groups. Since the temporary table does not take care of
          grouping, JOIN::group shall not be set to false in 
          make_simple_join(). This was fixed in bug 12578908. 
          
          However, there is an exception where make_simple_join() should
          set JOIN::group to false even if the query uses a temporary table
          that was explicitly requested but is not strictly needed. That
          exception is if the loose index scan access method (explain
          says "Using index for group-by") is used to read into the 
          temporary table. With loose index scan, grouping is resolved 
          by the access method. This is exactly what happens in this bug.
    213476ef
group_by.result 62.3 KB