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

Wrong query result with condition pushdown into derived table with ORDER BY ... LIMIT

    Details

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

      Description

      Test dataset:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      select a from ten order by a limit 5;
      +------+
      | a    |
      +------+
      |    0 |
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      +------+
      

      Use the above query in a derived table, without condition_pushdown_for_derived:

      set optimizer_switch='condition_pushdown_for_derived=off';
      Query OK, 0 rows affected (0.00 sec)
       
      select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
      +------+
      | a    |
      +------+
      |    0 |
      |    4 |
      +------+
      

      Enabling condition_pushdown_for_derived:

      set optimizer_switch='condition_pushdown_for_derived=on';
      Query OK, 0 rows affected (0.00 sec)
       
      select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
      +------+
      | a    |
      +------+
      |    0 |
      |    4 |
      |    5 |
      |    6 |
      |    7 |
      +------+
      

      and see rows with id=5 and so forth. It looks like TBL.a not in (1,2,3) was pushed through ORDER BY ... LIMIT, which changes the query result.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: