Bug #106938 | lateral derived table with LIMIT produces wrong result | ||
---|---|---|---|
Submitted: | 7 Apr 2022 2:26 | Modified: | 8 Apr 2022 1:35 |
Reporter: | xiaoyang chen | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
Version: | 8.0, 8.0.28 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Apr 2022 2:26]
xiaoyang chen
[7 Apr 2022 10:19]
MySQL Verification Team
Hello xiaoyang chen, Thank you for the report and test case. Verified as described. regards, Umesh
[8 Apr 2022 1:33]
xiaoyang chen
The tree explain of this query is as follows: ``` | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 1 row(s) -> Nested loop inner join -> Invalidate materialized tables (row from t1) (cost=0.95 rows=7) -> Sort: t1.b (cost=0.95 rows=7) -> Table scan on t1 (cost=0.95 rows=7) -> Table scan on tt (cost=2.72 rows=2) -> Materialize (invalidate on row from t1) -> Filter: (mt > 10) -> Aggregate: min(t2.b) -> Filter: (t1.a = (t2.a + 1)) (cost=0.95 rows=7) -> Table scan on t2 (cost=0.95 rows=7) | ``` Clearly, the wrong result is due to "LIMIT 1" being pushed to table t1. This is caused by the "filesort_limit" optimization when making a temporary table for sort. Suggest fix: ``` --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5065,7 +5065,7 @@ bool JOIN::make_tmp_tables_info() { m_select_limit == HA_POS_ERROR (we need a full table scan) unit->select_limit_cnt == 1 (we only need one row in the result set) */ - if (sort_tab->filesort) + if (sort_tab->filesort && !sort_tab->lateral_derived_tables_depend_on_me) sort_tab->filesort->limit = (has_group_by || (primary_tables > curr_tmp_table + 1) || calc_found_rows) ``` When the table is the lateral deepened table, we cannot pushdown limit to this table, as the lateral derived table wants to see all the records in this table.
[8 Apr 2022 1:35]
xiaoyang chen
Sorry, the above-listed tree explain is the right one. The original is that ``` | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Limit: 1 row(s) -> Nested loop inner join -> Invalidate materialized tables (row from t1) (cost=0.95 rows=7) -> Sort: t1.b, limit input to 1 row(s) per chunk (cost=0.95 rows=7) -> Table scan on t1 (cost=0.95 rows=7) -> Table scan on tt (cost=2.72 rows=2) -> Materialize (invalidate on row from t1) -> Filter: (mt > 10) -> Aggregate: min(t2.b) -> Filter: (t1.a = (t2.a + 1)) (cost=0.95 rows=7) -> Table scan on t2 (cost=0.95 rows=7) | ```