[MDEV-7791] Incorrect handling of conditions with subquery materialization Created: 2015-03-17  Updated: 2017-05-17  Resolved: 2017-05-17

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.3.12, 5.5, 10.0, 10.1
Fix Version/s: 5.5.57

Type: Bug Priority: Major
Reporter: Markus Behm Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: verified

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



 Comments   
Comment by Elena Stepanova [ 2015-03-17 ]

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.

Comment by Oleksandr Byelkin [ 2017-05-16 ]

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

Comment by Igor Babaev [ 2017-05-17 ]

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

Generated at Thu Feb 08 07:22:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.