Skip to content
  • Knut Anders Hatlen's avatar
    ea56001e
    Bug#29723353: ADDING FUNCTIONAL INDEX ON JSON EXPRESSION CHANGES RESULT · ea56001e
    Knut Anders Hatlen authored
    When adding a functional index on an expression that returned a
    character string, the index was sometimes used for predicates that
    compared strings using another collation than the indexed expression.
    This could cause wrong results.
    
    substitute_gc_expression() has a check that prevents use of functional
    indexes which don't have the same collation as the corresponding
    expression in the query. WL#8955 unintentionally made the check
    looser, by only checking the collation if both the hidden generated
    column backing the functional index and the indexed expression had the
    type VARCHAR. Since JSON_UNQUOTE returns LONGTEXT, the collation was
    not checked for functional indexes on JSON_UNQUOTE.
    
    Fixed by making the check stricter and checking the collation for all
    string types.
    
    Change-Id: I4cc5b0fdff505a4a9c8bee75ae8d1c93c9ed09b4
    ea56001e
    Bug#29723353: ADDING FUNCTIONAL INDEX ON JSON EXPRESSION CHANGES RESULT
    Knut Anders Hatlen authored
    When adding a functional index on an expression that returned a
    character string, the index was sometimes used for predicates that
    compared strings using another collation than the indexed expression.
    This could cause wrong results.
    
    substitute_gc_expression() has a check that prevents use of functional
    indexes which don't have the same collation as the corresponding
    expression in the query. WL#8955 unintentionally made the check
    looser, by only checking the collation if both the hidden generated
    column backing the functional index and the indexed expression had the
    type VARCHAR. Since JSON_UNQUOTE returns LONGTEXT, the collation was
    not checked for functional indexes on JSON_UNQUOTE.
    
    Fixed by making the check stricter and checking the collation for all
    string types.
    
    Change-Id: I4cc5b0fdff505a4a9c8bee75ae8d1c93c9ed09b4
Loading