[MDEV-10884] Wrong query result with condition pushdown into derived table with ORDER BY ... LIMIT Created: 2016-09-24  Updated: 2016-09-26  Resolved: 2016-09-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2
Fix Version/s: 10.2.2

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-9197 Pushdown conditions into non-mergeabl... Closed

 Description   

Test dataset:

create table ten(a int);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
select a from ten order by a limit 5;
 
+------+
 
| a    |
 
+------+
 
|    0 |
 
|    1 |
 
|    2 |
 
|    3 |
 
|    4 |
 
+------+

Use the above query in a derived table, without condition_pushdown_for_derived:

set optimizer_switch='condition_pushdown_for_derived=off';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
 
+------+
 
| a    |
 
+------+
 
|    0 |
 
|    4 |
 
+------+

Enabling condition_pushdown_for_derived:

set optimizer_switch='condition_pushdown_for_derived=on';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);
 
+------+
 
| a    |
 
+------+
 
|    0 |
 
|    4 |
 
|    5 |
 
|    6 |
 
|    7 |
 
+------+

and see rows with id=5 and so forth. It looks like TBL.a not in (1,2,3) was pushed through ORDER BY ... LIMIT, which changes the query result.

 Comments   
Comment by Igor Babaev [ 2016-09-26 ]

The condition should not have been pushed into SELECTs with ORDER BY ... LIMIT.

Similarly they should not be pushed into SELECTs of a unit with global ORDER BY ... LIMIT.
With condition pushdown into such units allowed we also can observe wrong result sets:

ariaDB [test]> set statement optimizer_switch='condition_pushdown_for_derived=on' for 
select * from  (select a from t1 where a < 4 union select a from t1 where a > 5  order by a limit 5) t where t.a not in (2,9);
 
+------+
 
| a    |
 
+------+
 
|    0 |
 
|    1 |
 
|    3 |
 
|    6 |
 
|    7 |
 
+------+
 
 
 
MariaDB [test]> set statement optimizer_switch='condition_pushdown_for_derived=off' for  select * from  (select a from t1 where a < 4 union select a from t1 where a > 5  order by a limit 5) t where t.a not in (2,9);
 
+------+
 
| a    |
 
+------+
 
|    0 |
 
|    1 |
 
|    3 |
 
|    6 |
 
+------+

Comment by Igor Babaev [ 2016-09-26 ]

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

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