• Alexander Barkov's avatar
    MDEV-32958 Unusable key notes do not get reported for some operations · 4ced4898
    Alexander Barkov authored
    Enable unusable key notes for non-equality predicates:
       <, <=, =>, >, BETWEEN, IN, LIKE
    
    Note, in some scenarios it displays duplicate notes, e.g.
    for queries with ORDER BY:
    
      SELECT * FROM t1
      WHERE    indexed_string_column >= 10
      ORDER BY indexed_string_column
      LIMIT 5;
    
    This should be tolarable. Getting rid of the diplicate note
    completely would need a much more complex patch, which is
    not desiable in 10.6.
    
    Details:
    
    - Changing RANGE_OPT_PARAM::note_unusable_keys from bool
      to a new data type Item_func::Bitmap, so the caller can
      choose with a better granuality which predicates
      should raise unusable key notes inside the range optimizer:
        a. all predicates (=, <=>, <, <=, =>, >, BETWEEN, IN, LIKE)
        b. all predicates except equality (=, <=>)
        c. none of the predicates
    
      "b." is needed because in some scenarios equality predicates (=, <=>)
      send unusable key notes at an earlier stage, before the range optimizer,
      during update_ref_and_keys(). Calling the range optimizer with
      "all predicates" would produce duplicate notes for = and <=> in such cases.
    
    - Fixing get_quick_record_count() to call the range optimizer
      with "all predicates except equality" instead of "none of the predicates".
      Before this change the range optimizer suppressed all notes for
      non-equality predicates: <, <=, =>, >, BETWEEN, IN, LIKE.
      This actually fixes the reported problem.
    
    - Fixing JOIN::make_range_rowid_filters() to call the range optimizer
      with "all predicates except equality" instead of "all predicates".
      Before this change the range optimizer produced duplicate notes
      for = and <=> during a rowid_filter optimization.
    
    - Cleanup:
      Adding the op_collation argument to Field::raise_note_cannot_use_key_part()
      and displaying the operation collation rather than the argument collation
      in the unusable key note. This is important for operations with more than
      two arguments: BETWEEN and IN, e.g.:
    
        SELECT * FROM t1
        WHERE column_utf8mb3_general_ci
              BETWEEN 'a' AND 'b' COLLATE utf8mb3_unicode_ci;
    
        SELECT * FROM t1
        WHERE column_utf8mb3_general_ci
              IN ('a', 'b' COLLATE utf8mb3_unicode_ci);
    
        The note for 'a' now prints utf8mb3_unicode_ci as the collation.
        which is the collation of the entire operation:
    
          Cannot use key key1 part[0] for lookup:
          "`column_utf8mb3_general_ci`" of collation `utf8mb3_general_ci` >=
          "'a'" of collation `utf8mb3_unicode_ci`
    
        Before this change it printed the collation of 'a',
        so the note was confusing:
    
          Cannot use key key1 part[0] for lookup:
          "`column_utf8mb3_general_ci`" of collation `utf8mb3_general_ci` >=
          "'a'" of collation `utf8mb3_general_ci`"
    4ced4898
mrr_icp_extra.result 25.7 KB