• unknown's avatar
    Fix bug lp:985667, MDEV-229 · a4336eb6
    unknown authored
    Analysis:
    
    The reason for the wrong result is the interaction between constant
    optimization (in this case 1-row table) and subquery optimization.
    
    - First the outer query is optimized, and 'make_join_statistics' finds that
    table t2 has one row, reads that row, and marks the whole table as constant.
    This also means that all fields of t2 are constant.
    
    - Next, we optimize the subquery in the end of the outer 'make_join_statistics'.
    The field 'f2' is considered constant, with value '3'. The subquery predicate
    is rewritten as the constant TRUE.
    
    - The outer query execution detects early that the whole query result is empty
    and calls 'return_zero_rows'. Since the query is with implicit grouping, we
    have to produce one row with special values for the aggregates (depending on
    each aggregate function), and NULL values for all non-aggregate fields.  This
    function calls 'no_rows_in_result' to set each aggregate function to the
    default value when it aggregates over an empty result, and then calls
    'send_data', which in turn evaluates each Item in the SELECT list.
    
    - When evaluation reaches the subquery predicate, it executes the subquery
    with field 'f2' having a constant value '3', and the subquery produces the
    incorrect result '7'.
    
    Solution:
    
    Implement Item::no_rows_in_result for all subquery predicates. In order to
    make this work, it is also needed to make all val_* methods of all subquery
    predicates respect the Item_subselect::forced_const flag. Otherwise subqueries
    are executed anyways, and override the default value set by no_rows_in_result
    with whatever result is produced from the subquery evaluation.
    a4336eb6
subselect_no_opts.result 203 KB