Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-9197 Pushdown conditions into non-mergeable views/derived tables
-
- Closed
-
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' forselect * 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 |+------+