Details
Description
We've encountered inconsistent query results. By an trial/error we found out that the cause of the inconsistency is use of LATERAL DERIVED in the query plan. When it's used, the query doesn't return correct results.
We've found similar open issues already reported, but I can't say if it's the same cause or not:
Disabling the optimization fixed the issue for us and the query results started to behave:
set global optimizer_switch='split_materialized=off'
|
I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is:
SELECT COUNT(*) |
FROM project_time_lines_dates ptld |
|
|
INNER JOIN date_revision dr |
ON dr.id = ptld.date_revision_id |
|
|
INNER JOIN ( |
SELECT |
ptld.project_id AS project_id, |
ptld.project_type_id AS project_type_id, |
ptld.leaf_component_id AS leaf_component_id, |
dr.date_id AS date_id, |
MAX(dr.order_idx) AS max_order |
FROM project_time_lines_dates ptld |
INNER JOIN date_revision dr |
ON dr.id = ptld.date_revision_id |
WHERE dr.date_id IN ( |
SELECT DISTINCT dr.date_id |
FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr |
INNER JOIN date_revision dr |
ON dr.id = tstrdr.start_date_revision_id |
WHERE tstrdr.offered_for_overdue |
)
|
|
GROUP BY |
ptld.project_id,
|
ptld.project_type_id,
|
ptld.leaf_component_id,
|
dr.date_id
|
) max_anticipated
|
ON max_anticipated.project_id = ptld.project_id |
AND max_anticipated.project_type_id = ptld.project_type_id |
AND max_anticipated.date_id = dr.date_id |
AND max_anticipated.max_order = dr.order_idx |
|
|
WHERE dr.is_anticipated = TRUE |
AND ptld.project_id = 5896 |
The anticipated result is 2, but when LATERAL DERIVED is used, the number of returned rows is 0.
EXPLAIN of the query with disabled LATERAL DERIVED looks like this:
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
|
| 1 | PRIMARY | ptld | ref | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2 | 4 | const | 34 | Using index |
|
| 1 | PRIMARY | dr | eq_ref | PRIMARY,date_id | PRIMARY | 4 | bugreport.ptld.date_revision_id | 1 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key1 | key1 | 9 | bugreport.dr.date_id,bugreport.dr.order_idx | 10 | Using where |
|
| 2 | DERIVED | ptld | ref | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2 | 4 | const | 34 | Using index; Using temporary; Using filesort |
|
| 2 | DERIVED | dr | eq_ref | PRIMARY,date_id | PRIMARY | 4 | bugreport.ptld.date_revision_id | 1 | |
|
| 2 | DERIVED | dr | ref | PRIMARY,date_id | date_id | 4 | bugreport.dr.date_id | 1 | Using index |
|
| 2 | DERIVED | tstrdr | ref | start_date_revision_id | start_date_revision_id | 5 | bugreport.dr.id | 4 | Using where; FirstMatch(dr) |
|
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
|
EXPLAIN of the query with enabled LATERAL DERIVED looks like this:
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
|
| 1 | PRIMARY | ptld | ref | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2 | 4 | const | 34 | Using index |
|
| 1 | PRIMARY | dr | eq_ref | PRIMARY,date_id | PRIMARY | 4 | bugreport.ptld.date_revision_id | 1 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 4 | bugreport.ptld.project_type_id | 2 | Using where |
|
| 2 | LATERAL DERIVED | dr | ref | PRIMARY,date_id | date_id | 4 | bugreport.dr.date_id | 1 | Using temporary; Using filesort |
|
| 2 | LATERAL DERIVED | dr | ref | PRIMARY,date_id | date_id | 4 | bugreport.dr.date_id | 1 | Using index; Start temporary |
|
| 2 | LATERAL DERIVED | tstrdr | ref | start_date_revision_id | start_date_revision_id | 5 | bugreport.dr.id | 4 | Using where; End temporary |
|
| 2 | LATERAL DERIVED | ptld | ref | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2 | 4 | const | 34 | Using index |
|
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
|
7 rows in set (0.001 sec)
|
Attachments
Issue Links
- is duplicated by
-
MDEV-26749 Optimizer_switch split_materialized Returns Wrong Results
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
| Transition | Time In Source Status | Execution Times |
|---|
|
42d 3h 57m | 1 |
|
4h 54m | 1 |
|
4d 2h 23m | 1 |
|
18h 7m | 1 |
|
4d 2h 44m | 1 |