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

condition in pushed index condition is not removed from the WHERE in BNL joins

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Review (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1
    • Fix Version/s: 10.0, 10.1, 10.2
    • Component/s: Optimizer
    • Labels:
      None

      Description

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      create table t10 (
      i0 int, i1 int, i2 int, i3 int, i4 int,
      c1 int, c2 int, c3 int, c4 int,
      index(i0,i1,i2,i3,i4)) engine=myisam;
      insert into t10 select a,a,a,a,a, a,a,a,a from one_k where a> 5;
      create table t11 like t10;
      insert into t11 select * from t10;
      explain
      format=json
      select * from t10, t11
      where
      t11.i0<10 and t10.i0<10;
      EXPLAIN
      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t10",
            "access_type": "range",
            "possible_keys": ["i0"],
            "key": "i0",
            "key_length": "5",
            "used_key_parts": ["i0"],
            "rows": 2,
            "filtered": 100,
            "index_condition": "t10.i0 < 10"
          },
          "block-nl-join": {
            "table": {
              "table_name": "t11",
              "access_type": "range",
              "possible_keys": ["i0"],
              "key": "i0",
              "key_length": "5",
              "used_key_parts": ["i0"],
              "rows": 2,
              "filtered": 100,
              "index_condition": "t11.i0 < 10",
              "attached_condition": "t11.i0 < 10"
            },
            "buffer_type": "flat",
            "buffer_size": "256Kb",
            "join_type": "BNL"
          }
        }
      }
      

      Notice that the index condition for the inner table of the join has the same index_condition and attached_condition

      Gives correct output in MYSQL.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: