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

Incorrect handling of conditions with subquery materialization

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 5.3.12, 10.1, 10.0, 5.5
        • Fix Version/s: 10.1, 10.0, 5.5
        • Component/s: Optimizer
        • Labels:

          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);

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                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.

                Show
                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.

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    mbehm Markus Behm
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated: