Skip to content
  • Ajo Robert's avatar
    ebce2b52
    Bug#24423143 - WRONG RESULTS FOR AGGREGATE QUERY · ebce2b52
    Ajo Robert authored
    Optimizer chooses loose index scan (QUICK_GROUP_MIN_MAX_SELECT)
    for a group by even when there exists a predicate with disjunction.
    This is due to that either of the below two cases are encountered
    and not handled properly.
    
    1. A range tree created for index merge scan is not taken
    into account while checking for the presence of disjuncted
    conditions in get_best_group_min_max().
    
    2. A disjunction condition could lead to null tree if an index
    merge scan is not possible. A NULL tree will cause
    get_best_group_min_max() to skip many relevent checks
    like WA2.
    
    There are two scenarios here,
      (a) The WHERE clause is a disjunction of conditions on
         MIN/MAX column only.
        => We can use min_max optimization considering all
        other criteria (eg:SA2) are met.
    
     (b) MIN/MAX column is participating in a disjunctive WHERE
         clause along with other columns.
         => min_max optimization is not applicable in this scenario.
    
    Fix:
    Skip loose index scan in below scenarios involving disjoint conditions
    in WHERE clause.
    
    1. When the condition in WHERE clause results in more than
    one disjoint range trees (when index merge scan is possible).
    
    2. The range tree is null and MIN/MAX column participates
    in the WHERE clause along with other columns.
    [There is no range tree if WHERE condition can't be represented
    in a single range tree and index merge is not possible]
    
    Change-Id: I2ec8ef815d9dfa64e0bf60e97d3b6129c070e24b
    ebce2b52
    Bug#24423143 - WRONG RESULTS FOR AGGREGATE QUERY
    Ajo Robert authored
    Optimizer chooses loose index scan (QUICK_GROUP_MIN_MAX_SELECT)
    for a group by even when there exists a predicate with disjunction.
    This is due to that either of the below two cases are encountered
    and not handled properly.
    
    1. A range tree created for index merge scan is not taken
    into account while checking for the presence of disjuncted
    conditions in get_best_group_min_max().
    
    2. A disjunction condition could lead to null tree if an index
    merge scan is not possible. A NULL tree will cause
    get_best_group_min_max() to skip many relevent checks
    like WA2.
    
    There are two scenarios here,
      (a) The WHERE clause is a disjunction of conditions on
         MIN/MAX column only.
        => We can use min_max optimization considering all
        other criteria (eg:SA2) are met.
    
     (b) MIN/MAX column is participating in a disjunctive WHERE
         clause along with other columns.
         => min_max optimization is not applicable in this scenario.
    
    Fix:
    Skip loose index scan in below scenarios involving disjoint conditions
    in WHERE clause.
    
    1. When the condition in WHERE clause results in more than
    one disjoint range trees (when index merge scan is possible).
    
    2. The range tree is null and MIN/MAX column participates
    in the WHERE clause along with other columns.
    [There is no range tree if WHERE condition can't be represented
    in a single range tree and index merge is not possible]
    
    Change-Id: I2ec8ef815d9dfa64e0bf60e97d3b6129c070e24b
Loading