Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21243

Join buffer: condition is checked in wrong place for range access

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.3.10, 10.3.21, 10.4
    • Fix Version/s: 10.3.22, 10.4.12
    • Component/s: Optimizer
    • Labels:
      None

      Description

      When one uses

      • join buffer on table $T, with BNL-join
      • the table $T uses a range access $RA
      • There is also a possible ref access on the same index, but $RA uses
        more key parts

      then the non-index condition $COND on $T is checked after $T is joined with the contents of the join buffer. It should be checked before that.
      (Note that Only condition on non-indexed columns is affected. Index Condition Pushdown works - whatever can be pushed to ICP, will be pushed and checked at appropriate place)

      create table ten(a int primary key);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table hundred (a int);
      insert into hundred select A.a + 10*B.a from ten A, ten B;
       
      create table t12 (
        kp1 int,
        kp2 int,
        col1 int,
        col2 int,
        key (kp1, kp2)
      );
       
      insert into t12
      select
        A.a,
        B.a,
        A.a + 100*B.a,
        A.a + 100*B.a
      from 
        hundred A, hundred B;
      analyze table t12;
      

      explain format=json
      select * 
      from ten A, t12 B
      where 
        B.kp1=A.a and
        B.kp1 <= 10 and
        B.kp2 <= 10 and 
        B.col1 +1 < 33333;
      

      shows

      | {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "A",
            "access_type": "index",
            "possible_keys": ["PRIMARY"],
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["a"],
            "rows": 10,
            "filtered": 100,
            "attached_condition": "A.a <= 10",
            "using_index": true
          },
          "block-nl-join": {
            "table": {
              "table_name": "B",
              "access_type": "range",
              "possible_keys": ["kp1"],
              "key": "kp1",
              "key_length": "10",
              "used_key_parts": ["kp1", "kp2"],
              "rows": 1011,
              "filtered": 10.148,
              "index_condition": "B.kp2 <= 10"
            },
            "buffer_type": "flat",
            "buffer_size": "54",
            "join_type": "BNL",
            "attached_condition": "B.kp1 = A.a and B.col1 + 1 < 33333"
          }
        }
      } |
      

      Note that B.col1 + 1 < 33333 is checked when doing the join with the join buffer.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: