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

Incorrect handling of conditions with subquery materialization

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5, 10.0, 10.1
    • Fix Version/s: 5.5.57
    • Component/s: Optimizer
    • Labels:
    • Sprint:
      10.0.24

      Description

      Description:
      Subquery materialization causes a weird issue where a where condition seems to be ignored sometimes. This seems to only happen with certain functions like RAND().

      The following queries should both obviously result in zero rows as RAND can never return less than zero, yet with materialization all rows are returned.

      MariaDB [test]> select * from t1 where (rand() < 0) and i in (select i from t2);
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      |    5 |
      |    6 |
      |    7 |
      |    8 |
      |    9 |
      |   10 |
      +------+
      10 rows in set (0.00 sec)
       
      MariaDB [test]> set session optimizer_switch='materialization=off';                                                                                                                                                                                                                                                         
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> select * from t1 where (rand() < 0) and i in (select i from t2);
      Empty set (0.00 sec)

      How to repeat:

      create table t1(i int);
      insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
      create table t2(i int);
      insert into t2 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
      set session optimizer_switch='materialization=on';
      select * from t1 where (rand() < 0) and i in (select i from t2);
      set session optimizer_switch='materialization=off';
      select * from t1 where (rand() < 0) and i in (select i from t2);

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              mbehm Markus Behm
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: