• gkodinov@mysql.com's avatar
    Bug #16458: Simple SELECT FOR UPDATE causes "Result Set not updatable" error · 9ec681ef
    gkodinov@mysql.com authored
    'SELECT DISTINCT a,b FROM t1' should not use temp table if there is unique 
    index (or primary key) on a.
    There are a number of other similar cases that can be calculated without the
    use of a temp table : multi-part unique indexes, primary keys or using GROUP BY 
    instead of DISTINCT.
    When a GROUP BY/DISTINCT clause contains all key parts of a unique
    index, then it is guaranteed that the fields of the clause will be
    unique, therefore we can optimize away GROUP BY/DISTINCT altogether.
    This optimization has two effects:
    * there is no need to create a temporary table to compute the
       GROUP/DISTINCT operation (or the temporary table will be smaller if only GROUP 
       is removed and DISTINCT stays or if DISTINCT is removed and GROUP BY stays)
    * this causes the statement in effect to become updatable in Connector/Java
    because the result set columns will be direct reference to the primary key of 
    the table (instead to the temporary table that it currently references). 
    
    Implemented a check that will optimize away GROUP BY/DISTINCT for queries like 
    the above.
    Currently it will work only for single non-constant table in the FROM clause.
    9ec681ef
distinct.test 14.1 KB