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