[MDEV-27132] Wrong result from query when using split optimization - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5.11, 10.6.4, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
Fix Version/s: 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2
Component/s: Optimizer
Labels:
None

Description

We've found similar open issues already reported, but I can't say if it's the same cause or not:

~~MDEV-21328~~
~~MDEV-26749~~

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

bugreport.sql.gz
227 kB
2021-11-28 12:44
mariadb10.4.22.tar.gz
35 kB
2022-01-07 10:28
raw_ab.tar.gz
0.6 kB
2022-01-10 01:38

Issue Links

is duplicated by

MDEV-26749 Optimizer_switch split_materialized Returns Wrong Results

Closed

MDEV-26965 Left join to derived table on multiple clauses with aggregation returns missing/incorrect results

Closed

relates to

MDEV-25714 Join using derived with aggregation returns incorrect results

Closed

MDEV-26965 Left join to derived table on multiple clauses with aggregation returns missing/incorrect results

Closed

MDEV-27510 Query returns wrong result when using split optimization

Closed

Activity

Ascending order - Click to sort in descending order

Peter Dulacka created issue - 2021-11-28 12:50

Peter Dulacka made changes - 2021-11-28 12:53

Field	Original Value	New Value
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: - https://jira.mariadb.org/browse/MDEV-21328 - https://jira.mariadb.org/browse/MDEV-26749 Disabling the optimization fixed the issue for us and the query results started to behave: {code} set global optimizer_switch='split_materialized=off' {code} I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is: {code:sql} 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 {code} The anticipated result is 2, but when LATERAL DERIVED is used, the number of returned rows is 0*.	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: - https://jira.mariadb.org/browse/MDEV-21328 - https://jira.mariadb.org/browse/MDEV-26749 Disabling the optimization fixed the issue for us and the query results started to behave: {code} set global optimizer_switch='split_materialized=off' {code} I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is: {code:sql} 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 {code} 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: {code} +------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+ \| 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) MariaDB [bugreport]> +------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+ {code} {{EXPLAIN}} of the query with enabled LATERAL DERIVED looks like this: {code} +------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+ \| 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 \| +------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+ {code}

Peter Dulacka made changes - 2021-11-28 12:54

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:

- https://jira.mariadb.org/browse/MDEV-21328
- https://jira.mariadb.org/browse/MDEV-26749

Disabling the optimization fixed the issue for us and the query results started to behave:

{code}
set global optimizer_switch='split_materialized=off'
{code}

I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is:

{code:sql}
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
{code}

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:

{code}
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
| 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) MariaDB [bugreport]>
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
{code}

{{EXPLAIN}} of the query with enabled LATERAL DERIVED looks like this:

{code}
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
| 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 |
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
{code}

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:

- https://jira.mariadb.org/browse/MDEV-21328
- https://jira.mariadb.org/browse/MDEV-26749

Disabling the optimization fixed the issue for us and the query results started to behave:

{code}
set global optimizer_switch='split_materialized=off'
{code}

I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is:

{code:sql}
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
{code}

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:

{code}
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
| 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) |
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
{code}

{{EXPLAIN}} of the query with enabled LATERAL DERIVED looks like this:

{code}
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
| 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)
{code}

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 128012 ]

MariaDB v4 [ 143392 ]

Silver Asu made changes - 2022-01-07 10:28

Attachment

mariadb10.4.22.tar.gz [ 61514 ]

Alice Sherepa made changes - 2022-01-09 16:48

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]

Alice Sherepa made changes - 2022-01-09 16:48

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]

Alice Sherepa made changes - 2022-01-09 16:48

Assignee

Igor Babaev [ igor ]

Alice Sherepa made changes - 2022-01-09 16:48

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2022-01-09 17:18

Affects Version/s

10.7 [ 24805 ]

Alice Sherepa made changes - 2022-01-09 17:18

Fix Version/s

10.7 [ 24805 ]

Igor Babaev (Inactive) made changes - 2022-01-09 21:43

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Marios Hadjieleftheriou made changes - 2022-01-10 01:38

Attachment

raw_ab.tar.gz [ 61563 ]

Sergei Golubchik made changes - 2022-01-10 20:15

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:

- https://jira.mariadb.org/browse/MDEV-21328
- https://jira.mariadb.org/browse/MDEV-26749

Disabling the optimization fixed the issue for us and the query results started to behave:

{code}
set global optimizer_switch='split_materialized=off'
{code}

I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is:

{code:sql}
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
{code}

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:

{code}
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
| 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) |
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
{code}

{{EXPLAIN}} of the query with enabled LATERAL DERIVED looks like this:

{code}
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
| 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)
{code}

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:

- ~~MDEV-21328~~
- ~~MDEV-26749~~

Disabling the optimization fixed the issue for us and the query results started to behave:

{code}
set global optimizer_switch='split_materialized=off'
{code}

I'm attaching minimal dump, that can be used to reproduce the issue. The query to reproduce the issue is:

{code:sql}
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
{code}

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:

{code}
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
| 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) |
+------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
{code}

{{EXPLAIN}} of the query with enabled LATERAL DERIVED looks like this:

{code}
+------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
| 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)
{code}

Alice Sherepa made changes - 2022-01-11 10:53

Link

This issue relates to ~~MDEV-26965~~ [ ~~MDEV-26965~~ ]

Alice Sherepa made changes - 2022-01-12 12:53

Link

This issue relates to ~~MDEV-25714~~ [ ~~MDEV-25714~~ ]

Igor Babaev (Inactive) made changes - 2022-01-13 22:26

Summary

Query using LATERAL DERIVED not returning correct results

Wrong result from query when using split optimization

Igor Babaev (Inactive) made changes - 2022-01-14 00:07

Assignee	Igor Babaev [ igor ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alice Sherepa made changes - 2022-01-14 14:29

Link

This issue relates to ~~MDEV-27510~~ [ ~~MDEV-27510~~ ]

Oleksandr Byelkin made changes - 2022-01-14 18:14

Assignee	Oleksandr Byelkin [ sanja ]	Igor Babaev [ igor ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Igor Babaev (Inactive) made changes - 2022-01-18 20:59

Component/s		Optimizer [ 10200 ]
Fix Version/s		10.3.33 [ 26805 ]
Fix Version/s		10.4.23 [ 26807 ]
Fix Version/s		10.5.14 [ 26809 ]
Fix Version/s		10.6.6 [ 26811 ]
Fix Version/s		10.7.2 [ 26813 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Igor Babaev (Inactive) made changes - 2022-01-31 18:17

Link

This issue is duplicated by ~~MDEV-26965~~ [ ~~MDEV-26965~~ ]

Alice Sherepa made changes - 2024-12-04 11:55

Link

This issue is duplicated by ~~MDEV-26749~~ [ ~~MDEV-26749~~ ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Peter Dulacka

Votes:: 3 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2021-11-28 12:50

Updated:: 2024-12-04 11:55

Resolved:: 2022-01-18 20:59

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration