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

ORed condition in pushed index condition is not removed from the WHERE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.4.5
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Noticed this when working on MyRocks but it seems to be an issue affecting any storage engine:

      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 (key1 int not null, filler char(100)) engine=rocksdb;
      insert into t10 select A.a + 1000 *B.a, 'filler-data' from one_k A, ten B;
      alter table t10 add key(key1);
      

      explain format=json select * from t10 where key1 < 3 or key1 > 99999\G
      *************************** 1. row ***************************
      EXPLAIN: {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t10",
            "access_type": "range",
            "possible_keys": ["key1"],
            "key": "key1",
            "key_length": "4",
            "used_key_parts": ["key1"],
            "rows": 2,
            "filtered": 100,
            "index_condition": "t10.key1 < 3 or t10.key1 > 99999",
            "attached_condition": "t10.key1 < 3 or t10.key1 > 99999"
          }
        }
      }
      

      Note that index_condition and attached_condition have the same condition. This should not happen, the optimizer should try to remove the condition that is already checked. FB/mysql does it, and MariaDB's one should, too. ( We should check the revision history - did this got broken un-intentionally?)

      The storage engine doesn't matter:

      alter table t10 engine=myisam;
      explain format=json select * from t10 where key1 < 3 or key1 > 99999\G
      <the same output>
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: