[MDEV-23723] Crash when test_if_skip_sort_order() is checked for derived table subject to split Created: 2020-09-11 Updated: 2021-09-02 Resolved: 2021-04-30 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Optimizer |
| Affects Version/s: | 10.3.21, 10.3.23, 10.5.5, 10.3, 10.4, 10.5 |
| Fix Version/s: | 10.3.29, 10.4.19, 10.5.10, 10.6.1 |
| Type: | Bug | Priority: | Blocker |
| Reporter: | Rob Schwyzer | Assignee: | Sergei Petrunia |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | split_materialized | ||
| Environment: |
Tested on both AWS CentOS 7 VM and MariaDB Docker container |
||
| Issue Links: |
|
||||||||||||||||
| Description |
|
Partial stack trace:
Part of query which crashes performs a JOIN of a real InnoDB table with a temporary table generated via CTE earlier in the query. The ON condition directly compares like columns from the real table to like columns from the temporary table with =. There are four such comparisons in the ON clause, joined together via AND logic. Note that removing any one of the comparisons from the ON conditional returns NULL and avoids a crash. Due to the nature of AND, we expect the result of all four conditions being evaluated to also be NULL. Database is crashing while attempting to evaluate a condition it does not need to return a valid result for the given query. |
| Comments |
| Comment by Elena Stepanova [ 2020-09-14 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Reproducible on 10.3-10.5 (also older releases of 10.3). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Marko Mäkelä [ 2020-11-30 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The test case is crashing for me already outside InnoDB:
The test passes for Aria, MyISAM, and RocksDB. For InnoDB, the `length` will remain 0 because of the following:
The condition does not hold, because keyuse->validity_ref == false. I do not know this code, so it needs to be debugged by an optimizer developer. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2020-12-01 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Working on Elena's testcase from https://jira.mariadb.org/browse/MDEV-23723?focusedCommentId=165953&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-165953 : It fails an assert; it fails in the same way if I switch from using CTE to using a derived table:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2020-12-01 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Optimizer trace contents when the crash happens is: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2020-12-01 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The problem is with the "Split Materialized" optimization. The optimizer is trying to construct this query plan :
Let's look at the table t1 in the subquery.
Split-Materialized code decides to use the index KEY(a) Then, subquery optimization arrives into test_if_skip_sort_order() function, where it tries to use the index KEY(a,b). However, the injected KEYUSE objects for that index are disabled (KEYUSE::validity_ref points to 0), so it fails to do that. This causes the assertion failure. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2020-12-01 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Questions:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2020-12-01 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
On a side note, optimizer trace does not cover LATERAL DERIVED optimization at all. This is a gap in the optimizer trace and it should be fixed. (Filed | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2021-04-29 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Taking another look at this.
This is because column a can be "bound" by the top select with the equality "t1.a=cte.a", while column "b" cannot be.
As t1.b is not bound, the subquery will enumerate multiple GROUP BY groups. Sorting is not necessary though, as grouping is done using temp.table (and not by the sort-then-group algorithm)
This query tries to switch from KEY(a) to KEY(a,b) while the plan to do splitting assumes KEY(a) is used. We should either A. make adjustments to the splitting choice, or B. disallow changing the used index in such cases. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Sergei Petrunia [ 2021-04-29 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Patch implementing B: https://github.com/MariaDB/server/commit/3c564ae0e1235990794f2d0ab6c9b5760386d19f.diff Igor, please review | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Igor Babaev [ 2021-04-29 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Ok to push into 10.3 after addressing the notes in the review feedback sent by email in: |