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

Incorrect handling of conditions with subquery materialization

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 5.5.57
    • Optimizer
    • 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

          Thanks for the report and the test case.
          Reproducible on 5.3 (down to 5.3.5).
          Not reproducible on 5.2.
          Not reproducible on MySQL 5.6.

          elenst Elena Stepanova added a comment - Thanks for the report and the test case. Reproducible on 5.3 (down to 5.3.5). Not reproducible on 5.2. Not reproducible on MySQL 5.6.

          OK to push after fixing issue with spaces at the end of lines.

          sanja Oleksandr Byelkin added a comment - OK to push after fixing issue with spaces at the end of lines.
          igor Igor Babaev added a comment -

          The fix for this bug was pushed into the 5.5 tree.

          igor Igor Babaev added a comment - The fix for this bug was pushed into the 5.5 tree.

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.