[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 ]

Marios Hadjieleftheriou added a comment - 2022-01-05 15:12 - edited

I have a similar issue. The query result in my case is incorrect only if I use the SELECT ... FROM (<subquery>) as a, (<subquery>) as b WHERE a.foo = b.foo and ... In that case the optimizer uses a LATERAL_DERIVED table and the result is incorrect. If I use the "(<subquery.) as a LEFT JOIN (<subquery>) as b ON ..." syntax, it works fine.

The problem happens only if my table has a specific size (yep, if I delete a single character from a random column the problem goes away). It also happens only if the table has a specific set of indexes with a very specific set of columns.

My query is quite simple:

SELECT a.date, a.c, b.foo, a.foo

    FROM

        (SELECT date,c,SUM(foo) as foo

         FROM raw_b

         WHERE date >= 20211231

         GROUP BY date,c

        ) as b,

        (SELECT date, c, SUM(foo) as foo

         FROM raw_a

         WHERE date >= 20211231

         GROUP BY date,c

        ) as a where a.date = b.date AND a.c = b.c

Schema:

CREATE TABLE `raw_a` (

  `date` varchar(100) COLLATE latin1_general_cs DEFAULT NULL,

  `v` varchar(100) COLLATE latin1_general_cs DEFAULT NULL,

  `c` char(5) COLLATE latin1_general_cs DEFAULT NULL,

  `foo` bigint(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC

CREATE TABLE `raw_b` (

  `date` int(11) NOT NULL,

  `v` varchar(10) COLLATE latin1_general_cs NOT NULL,

  `t` varchar(512) COLLATE latin1_general_cs NOT NULL,

  `c` varchar(50) COLLATE latin1_general_cs NOT NULL,

  `o` varchar(10) COLLATE latin1_general_cs NOT NULL,

  `sh` varchar(10) COLLATE latin1_general_cs NOT NULL,

  `p` char(5) COLLATE latin1_general_cs NOT NULL,

  `s` varchar(20) COLLATE latin1_general_cs NOT NULL,

  `foo` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`date`,`v`,`t`,`c`,`o`,`sh`,`p`,`s`),

  KEY `date` (`date`,`c`,`v`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC

I can provide a small dataset to reproduce the issue if needed.

Server version: 5.5.5-10.6.5-MariaDB MariaDB Server
Linux <host> 5.10.0-9-amd64 #1 SMP Debian 5.10.70-1 (2021-09-30) x86_64 GNU/Linux

Marios Hadjieleftheriou added a comment - 2022-01-05 15:12 - edited I have a similar issue. The query result in my case is incorrect only if I use the SELECT ... FROM (<subquery>) as a, (<subquery>) as b WHERE a.foo = b.foo and ... In that case the optimizer uses a LATERAL_DERIVED table and the result is incorrect. If I use the "(<subquery.) as a LEFT JOIN (<subquery>) as b ON ..." syntax, it works fine. The problem happens only if my table has a specific size (yep, if I delete a single character from a random column the problem goes away). It also happens only if the table has a specific set of indexes with a very specific set of columns. My query is quite simple: SELECT a.date, a.c, b.foo, a.foo FROM (SELECT date,c,SUM(foo) as foo FROM raw_b WHERE date >= 20211231 GROUP BY date,c ) as b, (SELECT date, c, SUM(foo) as foo FROM raw_a WHERE date >= 20211231 GROUP BY date,c ) as a where a.date = b.date AND a.c = b.c Schema: CREATE TABLE `raw_a` ( `date` varchar(100) COLLATE latin1_general_cs DEFAULT NULL, `v` varchar(100) COLLATE latin1_general_cs DEFAULT NULL, `c` char(5) COLLATE latin1_general_cs DEFAULT NULL, `foo` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC CREATE TABLE `raw_b` ( `date` int(11) NOT NULL, `v` varchar(10) COLLATE latin1_general_cs NOT NULL, `t` varchar(512) COLLATE latin1_general_cs NOT NULL, `c` varchar(50) COLLATE latin1_general_cs NOT NULL, `o` varchar(10) COLLATE latin1_general_cs NOT NULL, `sh` varchar(10) COLLATE latin1_general_cs NOT NULL, `p` char(5) COLLATE latin1_general_cs NOT NULL, `s` varchar(20) COLLATE latin1_general_cs NOT NULL, `foo` bigint(20) DEFAULT NULL, PRIMARY KEY (`date`,`v`,`t`,`c`,`o`,`sh`,`p`,`s`), KEY `date` (`date`,`c`,`v`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC I can provide a small dataset to reproduce the issue if needed. Server version: 5.5.5-10.6.5-MariaDB MariaDB Server Linux <host> 5.10.0-9-amd64 #1 SMP Debian 5.10.70-1 (2021-09-30) x86_64 GNU/Linux

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

Attachment

mariadb10.4.22.tar.gz [ 61514 ]

Silver Asu added a comment - 2022-01-07 10:29

We have similar problem with 10.4.22. It is very interesting that it will "break" after some time. It works fine after import, but will start giving wrong answers after some time (maybe depends on server load?)

Also, select_type changes from DERIVED to LATERAL DERIVED.

(root:localhost) [test]> select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);

+------+------------------+

| id   | email            |

+------+------------------+

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

+------+------------------+

8 rows in set (0.001 sec)

(root:localhost) [test]> explain select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);

+------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+

| id   | select_type | table      | type | possible_keys | key        | key_len | ref       | rows | Extra                                        |

+------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+

|    1 | PRIMARY     | c          | ref  | id,deleted    | deleted    | 4       | const     | 1    | Using where; Using index                     |

|    1 | PRIMARY     | d          | ref  | company_id    | company_id | 4       | test.c.id | 1    | Using index                                  |

|    1 | PRIMARY     | <derived2> | ref  | key0          | key0       | 4       | test.c.id | 2    |                                              |

|    2 | DERIVED     | cpt        | ALL  | company_id    | NULL       | NULL    | NULL      | 1    | Using where; Using temporary; Using filesort |

+------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+

4 rows in set (0.000 sec)

(root:localhost) [test]> select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);

+------+-------+

| id   | email |

+------+-------+

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

+------+-------+

8 rows in set (0.001 sec)

(root:localhost) [test]> explain select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);

+------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+

| id   | select_type     | table      | type  | possible_keys | key        | key_len | ref       | rows | Extra                    |

+------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+

|    1 | PRIMARY         | c          | range | id,deleted    | deleted    | 8       | NULL      | 2    | Using where; Using index |

|    1 | PRIMARY         | d          | ref   | company_id    | company_id | 4       | test.c.id | 1    | Using index              |

|    1 | PRIMARY         | <derived2> | ref   | key0          | key0       | 4       | test.c.id | 2    |                          |

|    2 | LATERAL DERIVED | cpt        | ref   | company_id    | company_id | 4       | test.c.id | 1    | Using index condition    |

+------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+

4 rows in set (0.000 sec)

Dump and query are in mariadb10.4.22.tar.gz file

Silver Asu added a comment - 2022-01-07 10:29 We have similar problem with 10.4.22. It is very interesting that it will "break" after some time. It works fine after import, but will start giving wrong answers after some time (maybe depends on server load?) Also, select_type changes from DERIVED to LATERAL DERIVED. (root:localhost) [test]> select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join ( select company_id, email from company_contact_person cpt where reporting_person= 1 group by company_id ) cpe ON cpe.company_id=c.id where c.deleted= 0 and c.id in ( 1 , 2070 ); +------+------------------+ | id | email | +------+------------------+ | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | | 2070 | 2559test @test .ee | +------+------------------+ 8 rows in set ( 0.001 sec) (root:localhost) [test]> explain select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join ( select company_id, email from company_contact_person cpt where reporting_person= 1 group by company_id ) cpe ON cpe.company_id=c.id where c.deleted= 0 and c.id in ( 1 , 2070 ); +------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+ | 1 | PRIMARY | c | ref | id,deleted | deleted | 4 | const | 1 | Using where; Using index | | 1 | PRIMARY | d | ref | company_id | company_id | 4 | test.c.id | 1 | Using index | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 4 | test.c.id | 2 | | | 2 | DERIVED | cpt | ALL | company_id | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | +------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+ 4 rows in set ( 0.000 sec) (root:localhost) [test]> select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join ( select company_id, email from company_contact_person cpt where reporting_person= 1 group by company_id ) cpe ON cpe.company_id=c.id where c.deleted= 0 and c.id in ( 1 , 2070 ); +------+-------+ | id | email | +------+-------+ | 2070 | | | 2070 | | | 2070 | | | 2070 | | | 2070 | | | 2070 | | | 2070 | | | 2070 | | +------+-------+ 8 rows in set ( 0.001 sec) (root:localhost) [test]> explain select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join ( select company_id, email from company_contact_person cpt where reporting_person= 1 group by company_id ) cpe ON cpe.company_id=c.id where c.deleted= 0 and c.id in ( 1 , 2070 ); +------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+ | 1 | PRIMARY | c | range | id,deleted | deleted | 8 | NULL | 2 | Using where; Using index | | 1 | PRIMARY | d | ref | company_id | company_id | 4 | test.c.id | 1 | Using index | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 4 | test.c.id | 2 | | | 2 | LATERAL DERIVED | cpt | ref | company_id | company_id | 4 | test.c.id | 1 | Using index condition | +------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+ 4 rows in set ( 0.000 sec) Dump and query are in mariadb10.4.22.tar.gz file

Marios Hadjieleftheriou added a comment - 2022-01-09 14:30

I think it is related to the size of the table (which affects the summary stats, which affects which index the optimizer decides to use). So, it happens after some threshold of data is inserted into the table.

Marios Hadjieleftheriou added a comment - 2022-01-09 14:30 I think it is related to the size of the table (which affects the summary stats, which affects which index the optimizer decides to use). So, it happens after some threshold of data is inserted into the table.

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 added a comment - 2022-01-09 17:17

I repeated on 10.3-10.7 ( test case based on the one, reported by BB ):

--source include/have_innodb.inc

CREATE TABLE t1 ( id int, UNIQUE KEY id (id) ) ENGINE=InnoDB;

INSERT INTO t1 VALUES (-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834),(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844),(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854),(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864),(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874),(4875),(4876);

CREATE TABLE t2 ( id int, deleted int(1), t1_id int, email varchar(255), reporting_person int(1),

  UNIQUE KEY id (id),

  KEY t1_id (t1_id)

) ENGINE=InnoDB;

INSERT INTO t2 VALUES (1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1),(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1),(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0),(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0),(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1),(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0),(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0),(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1);

CREATE TABLE t3 ( id int, deleted int, t1_id int, YEAR int(4), quarter int(1),

  UNIQUE KEY id (id),

  KEY t1_id (t1_id,year,quarter)

) ENGINE=InnoDB;

INSERT INTO t3 VALUES (1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1),(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1),(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2),(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2),(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3),(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3),(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3),(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3),(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3),(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3),(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3),(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3),(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3),(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3),(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3),(350,0,2304,2020,3),(351,0,3896,2020,3);

SELECT t1.id, tx.email

FROM t1

JOIN t3  ON t3.t1_id = t1.id

JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

WHERE t1.id in(1, 2070);

set  optimizer_switch='split_materialized=off';

SELECT t1.id, tx.email

FROM t1

JOIN t3  ON t3.t1_id = t1.id

JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

WHERE t1.id in(1, 2070);

MariaDB [test]> SELECT t1.id, tx.email

    -> FROM t1

    -> JOIN t3  ON t3.t1_id = t1.id

    -> JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

    -> WHERE t1.id in(1, 2070);

+------+-------+

| id   | email |

+------+-------+

| 2070 |       |

| 2070 |       |

| 2070 |       |

+------+-------+

3 rows in set (0.002 sec)

MariaDB [test]> set  optimizer_switch='split_materialized=off';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT t1.id, tx.email

    -> FROM t1

    -> JOIN t3  ON t3.t1_id = t1.id

    -> JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

    -> WHERE t1.id in(1, 2070);

Empty set (0.002 sec)

please check the initial test case before closing the bug

Alice Sherepa added a comment - 2022-01-09 17:17 I repeated on 10.3-10.7 ( test case based on the one, reported by BB ): --source include/have_innodb.inc CREATE TABLE t1 ( id int , UNIQUE KEY id (id) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834),(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844),(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854),(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864),(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874),(4875),(4876); CREATE TABLE t2 ( id int , deleted int (1), t1_id int , email varchar (255), reporting_person int (1), UNIQUE KEY id (id), KEY t1_id (t1_id) ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,0,2064, '1test@test.ee' ,1),(2,1626095588,2066, '2test@test.ee' ,1),(3,0,2066, '3test@test.ee' ,1),(4,0,2068, '4test@test.ee' ,1),(5,0,2068, '5test@test.ee' ,1),(6,0,2069, '6test@test.ee' ,1),(7,0,2070, '' ,0),(8,0,2070, '' ,0),(9,0,2071, '' ,0),(10,0,2071, '' ,0),(11,0,2072, '' ,0),(12,0,2072, '' ,0),(13,0,2072, '13test@test.ee' ,1),(14,0,2073, '14test@test.ee' ,1),(15,0,2074, '15test@test.ee' ,1),(16,0,2075, '16test@test.ee' ,1),(17,0,2075, '' ,0),(18,0,2075, '' ,0),(19,0,2076, '19test@test.ee' ,1),(20,0,2077, '' ,0),(21,0,2078, '21test@test.ee' ,1),(22,0,2078, '22test@test.ee' ,1); CREATE TABLE t3 ( id int , deleted int , t1_id int , YEAR int (4), quarter int (1), UNIQUE KEY id (id), KEY t1_id (t1_id, year ,quarter) ) ENGINE=InnoDB; INSERT INTO t3 VALUES (1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1),(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1),(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2),(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2),(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3),(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3),(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3),(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3),(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3),(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3),(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3),(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3),(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3),(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3),(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3),(350,0,2304,2020,3),(351,0,3896,2020,3); SELECT t1.id, tx.email FROM t1 JOIN t3 ON t3.t1_id = t1.id JOIN ( SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id WHERE t1.id in (1, 2070); set optimizer_switch= 'split_materialized=off' ; SELECT t1.id, tx.email FROM t1 JOIN t3 ON t3.t1_id = t1.id JOIN ( SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id WHERE t1.id in (1, 2070); MariaDB [test]> SELECT t1.id, tx.email -> FROM t1 -> JOIN t3 ON t3.t1_id = t1.id -> JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id -> WHERE t1.id in(1, 2070); +------+-------+ | id | email | +------+-------+ | 2070 | | | 2070 | | | 2070 | | +------+-------+ 3 rows in set (0.002 sec) MariaDB [test]> set optimizer_switch='split_materialized=off'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SELECT t1.id, tx.email -> FROM t1 -> JOIN t3 ON t3.t1_id = t1.id -> JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id -> WHERE t1.id in(1, 2070); Empty set (0.002 sec) please check the initial test case before closing the bug

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 ]

Igor Babaev (Inactive) added a comment - 2022-01-09 21:44

alice ,
Your test case contains a derived table that is not deterministic:

(SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx

Note that email is not in GROUP BY list.
The same problem is with BB's query.

Igor Babaev (Inactive) added a comment - 2022-01-09 21:44 alice , Your test case contains a derived table that is not deterministic: ( SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx Note that email is not in GROUP BY list. The same problem is with BB 's query.

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

Attachment

raw_ab.tar.gz [ 61563 ]

Marios Hadjieleftheriou added a comment - 2022-01-10 01:39

I attached a fairly small test case for my join query that reproduces the issue on my system when set optimizer_switch="split_materialized=on".

Marios Hadjieleftheriou added a comment - 2022-01-10 01:39 I attached a fairly small test case for my join query that reproduces the issue on my system when set optimizer_switch="split_materialized=on".

Alice Sherepa added a comment - 2022-01-10 16:06

test case from mhadji@gmail.com :

--source include/have_innodb.inc

CREATE TABLE raw_a (

  date varchar(100),

  v varchar(100),

  c char(5),

  foo bigint(20)

) ENGINE=InnoDB;

INSERT INTO raw_a VALUES ('20211231','A','bh',1),('20211231','B','bh',0),('20211231','C','bh',0),('20211231','D','bh',0),('20211231','E','bh',0),('20211231','G','bh',0),('20211231','H','bh',0),('20211231','I','bh',3),('20211231','J','bh',0),('20211231','K','bh',0),('20211231','A','bl',9),('20211231','B','bl',0),('20211231','C','bl',0),('20211231','D','bl',0),('20211231','E','bl',0),('20211231','G','bl',0),('20211231','H','bl',0),('20211231','I','bl',7),('20211231','J','bl',0),('20211231','K','bl',0),('20211231','M','bl',0),('20211231','N','bl',3),('20211231','R','bl',9),('20211231','S','bl',0),('20211231','T','bl',0),('20211231','U','bl',0),('20211231','V','bl',0);

CREATE TABLE raw_b (

  date int(11) NOT NULL,

  v varchar(50),

  t varchar(512),

  c varchar(50),

  o varchar(10),

  sh varchar(10),

  p char(5),

  s varchar(20),

  foo bigint(20),

  PRIMARY KEY (date,v,t,c,o,sh,p,s),

  KEY date (date,c,v)

) ENGINE=InnoDB;

INSERT INTO raw_b VALUES (20211231,'A','a','bl','a','f','0','[0-5]',2),(20211231,'A','a','bl','a','f','0','[6-9)',2),(20211231,'A','b','bl','a','f','0','[0-5]',2),(20211231,'A','b','bl','a','f','0','[6-9)',2),(20211231,'A','c','bh','a','f','0','[0-5]',2),(20211231,'A','c','bh','a','f','0','[10-15)',2),(20211231,'A','c','bh','a','f','0','[15-20)',2),(20211231,'A','c','bh','a','f','0','[20-30)',2),(20211231,'A','c','bh','a','f','0','[6-9)',2),(20211231,'A','c','bl','a','f','0','[0-5]',2),(20211231,'A','c','bl','a','f','0','[20-30)',2),(20211231,'A','c','bl','a','f','0','[6-9)',2),(20211231,'A','cc','bl','a','f','0','[0-5]',2),(20211231,'A','cc','bl','a','f','0','[10-15)',2),(20211231,'A','cc','bl','a','f','0','[15-20)',2),(20211231,'A','cc','bl','a','f','0','[6-9)',2),(20211231,'A','cc','bl','a','LO','0','[0-5]',2),(20211231,'A','ccc','bl','a','f','0','[0-5]',2),(20211231,'A','d','bh','a','f','0','[0-5]',2),(20211231,'A','d','bh','a','f','0','[10-15)',2),(20211231,'A','d','bh','a','f','0','[20-30)',2),(20211231,'A','d','bh','a','f','0','[6-9)',2),(20211231,'A','e','bh','a','f','0','[0-5]',2),(20211231,'A','e','bh','a','f','0','[10-15)',2),(20211231,'A','e','bh','a','f','0','[20-30)',2),(20211231,'A','e','bh','a','f','0','[6-9)',2),(20211231,'A','f','bh','a','f','0','[6-9)',2),(20211231,'A','g','bh','a','f','0','[0-5]',2),(20211231,'A','g','bh','a','f','0','[10-15)',2),(20211231,'A','g','bh','a','f','0','[6-9)',2),(20211231,'A','h','bh','a','f','0','[0-5]',2),(20211231,'A','h','bh','a','f','0','[10-15)',2),(20211231,'A','h','bh','a','f','0','[15-20)',2),(20211231,'A','h','bh','a','f','0','[20-30)',2),(20211231,'A','h','bh','a','f','0','[30-inf)',2),(20211231,'A','h','bh','a','f','0','[6-9)',2),(20211231,'A','h','bh','a','f','1-4','[10-15)',2),(20211231,'A','h','bh','a','f','<0','[0-5]',2),(20211231,'A','i','bh','a','f','0','[0-5]',2),(20211231,'A','i','bh','a','f','0','[20-30)',2),(20211231,'A','i','bh','a','f','0','[6-9)',2),(20211231,'A','j','bh','a','f','0','[0-5]',2),(20211231,'A','j','bh','a','f','0','[6-9)',2),(20211231,'A','k','bh','a','f','0','[0-5]',2),(20211231,'A','k','bh','a','f','0','[10-15)',2),(20211231,'A','k','bh','a','f','0','[15-20)',2),(20211231,'A','k','bh','a','f','0','[20-30)',2),(20211231,'A','k','bh','a','f','0','[30-inf)',2),(20211231,'A','k','bh','a','f','0','[6-9)',2),(20211231,'A','l','bh','a','f','0','[0-5]',2),(20211231,'A','m','bh','a','f','0','[0-5]',2),(20211231,'A','m','bh','a','f','0','[10-15)',2),(20211231,'A','m','bh','a','f','0','[15-20)',2),(20211231,'A','m','bh','a','f','0','[20-30)',2),(20211231,'A','m','bh','a','f','0','[6-9)',2),(20211231,'A','m','bh','a','f','>4','[10-15)',2),(20211231,'A','n','bh','a','f','0','[0-5]',2),(20211231,'A','n','bl','a','f','0','[0-5]',2),(20211231,'A','o','bh','a','f','0','[0-5]',2),(20211231,'A','o','bh','a','f','0','[6-9)',2),(20211231,'A','p','bh','a','f','0','[0-5]',2),(20211231,'A','p','bh','a','f','0','[10-15)',2),(20211231,'A','p','bh','a','f','0','[15-20)',2),(20211231,'A','p','bh','a','f','0','[6-9)',2),(20211231,'A','p','bh','a','f','<0','[0-5]',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[0-5]',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[10-15)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[15-20)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[20-30)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[30-inf)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[6-9)',2);

SELECT a.date, a.c, b.foo, a.foo

    FROM

        (SELECT date,c,SUM(foo) as foo

         FROM raw_b

         WHERE date >= 20211231

         GROUP BY date,c

        ) as b,

        (SELECT date, c, SUM(foo) as foo

         FROM raw_a

         WHERE date >= 20211231

         GROUP BY date,c

        ) as a where a.date = b.date AND a.c = b.c;

 set optimizer_switch="split_materialized=off";

SELECT a.date, a.c, b.foo, a.foo

    FROM

        (SELECT date,c,SUM(foo) as foo

         FROM raw_b

         WHERE date >= 20211231

         GROUP BY date,c

        ) as b,

        (SELECT date, c, SUM(foo) as foo

         FROM raw_a

         WHERE date >= 20211231

         GROUP BY date,c

        ) as a where a.date = b.date AND a.c = b.c;

igor for the previously reported test - please use this query instead:

SELECT t1.id

FROM t1

JOIN t3  ON t3.t1_id = t1.id

JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

WHERE t1.id in(1, 2070);

Alice Sherepa added a comment - 2022-01-10 16:06 test case from mhadji@gmail.com : --source include/have_innodb.inc CREATE TABLE raw_a ( date varchar (100), v varchar (100), c char (5), foo bigint (20) ) ENGINE=InnoDB; INSERT INTO raw_a VALUES ( '20211231' , 'A' , 'bh' ,1),( '20211231' , 'B' , 'bh' ,0),( '20211231' , 'C' , 'bh' ,0),( '20211231' , 'D' , 'bh' ,0),( '20211231' , 'E' , 'bh' ,0),( '20211231' , 'G' , 'bh' ,0),( '20211231' , 'H' , 'bh' ,0),( '20211231' , 'I' , 'bh' ,3),( '20211231' , 'J' , 'bh' ,0),( '20211231' , 'K' , 'bh' ,0),( '20211231' , 'A' , 'bl' ,9),( '20211231' , 'B' , 'bl' ,0),( '20211231' , 'C' , 'bl' ,0),( '20211231' , 'D' , 'bl' ,0),( '20211231' , 'E' , 'bl' ,0),( '20211231' , 'G' , 'bl' ,0),( '20211231' , 'H' , 'bl' ,0),( '20211231' , 'I' , 'bl' ,7),( '20211231' , 'J' , 'bl' ,0),( '20211231' , 'K' , 'bl' ,0),( '20211231' , 'M' , 'bl' ,0),( '20211231' , 'N' , 'bl' ,3),( '20211231' , 'R' , 'bl' ,9),( '20211231' , 'S' , 'bl' ,0),( '20211231' , 'T' , 'bl' ,0),( '20211231' , 'U' , 'bl' ,0),( '20211231' , 'V' , 'bl' ,0); CREATE TABLE raw_b ( date int (11) NOT NULL , v varchar (50), t varchar (512), c varchar (50), o varchar (10), sh varchar (10), p char (5), s varchar (20), foo bigint (20), PRIMARY KEY ( date ,v,t,c,o,sh,p,s), KEY date ( date ,c,v) ) ENGINE=InnoDB; INSERT INTO raw_b VALUES (20211231, 'A' , 'a' , 'bl' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'a' , 'bl' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'b' , 'bl' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'b' , 'bl' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'c' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'c' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'c' , 'bh' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'A' , 'c' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'c' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'c' , 'bl' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'c' , 'bl' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'c' , 'bl' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'cc' , 'bl' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'cc' , 'bl' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'cc' , 'bl' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'A' , 'cc' , 'bl' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'cc' , 'bl' , 'a' , 'LO' , '0' , '[0-5]' ,2),(20211231, 'A' , 'ccc' , 'bl' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'd' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'd' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'd' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'd' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'e' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'e' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'e' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'e' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'f' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'g' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'g' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'g' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '0' , '[30-inf)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '1-4' , '[10-15)' ,2),(20211231, 'A' , 'h' , 'bh' , 'a' , 'f' , '<0' , '[0-5]' ,2),(20211231, 'A' , 'i' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'i' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'i' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'j' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'j' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'k' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'k' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'k' , 'bh' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'A' , 'k' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'k' , 'bh' , 'a' , 'f' , '0' , '[30-inf)' ,2),(20211231, 'A' , 'k' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'l' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'm' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'm' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'm' , 'bh' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'A' , 'm' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'A' , 'm' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'm' , 'bh' , 'a' , 'f' , '>4' , '[10-15)' ,2),(20211231, 'A' , 'n' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'n' , 'bl' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'o' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'o' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'p' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'A' , 'p' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'A' , 'p' , 'bh' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'A' , 'p' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2),(20211231, 'A' , 'p' , 'bh' , 'a' , 'f' , '<0' , '[0-5]' ,2),(20211231, 'Afdaskjlhfaslkjfhasdklfhdskla' , 'j' , 'bh' , 'a' , 'f' , '0' , '[0-5]' ,2),(20211231, 'Afdaskjlhfaslkjfhasdklfhdskla' , 'j' , 'bh' , 'a' , 'f' , '0' , '[10-15)' ,2),(20211231, 'Afdaskjlhfaslkjfhasdklfhdskla' , 'j' , 'bh' , 'a' , 'f' , '0' , '[15-20)' ,2),(20211231, 'Afdaskjlhfaslkjfhasdklfhdskla' , 'j' , 'bh' , 'a' , 'f' , '0' , '[20-30)' ,2),(20211231, 'Afdaskjlhfaslkjfhasdklfhdskla' , 'j' , 'bh' , 'a' , 'f' , '0' , '[30-inf)' ,2),(20211231, 'Afdaskjlhfaslkjfhasdklfhdskla' , 'j' , 'bh' , 'a' , 'f' , '0' , '[6-9)' ,2); SELECT a. date , a.c, b.foo, a.foo FROM ( SELECT date ,c, SUM (foo) as foo FROM raw_b WHERE date >= 20211231 GROUP BY date ,c ) as b, ( SELECT date , c, SUM (foo) as foo FROM raw_a WHERE date >= 20211231 GROUP BY date ,c ) as a where a. date = b. date AND a.c = b.c; set optimizer_switch= "split_materialized=off" ; SELECT a. date , a.c, b.foo, a.foo FROM ( SELECT date ,c, SUM (foo) as foo FROM raw_b WHERE date >= 20211231 GROUP BY date ,c ) as b, ( SELECT date , c, SUM (foo) as foo FROM raw_a WHERE date >= 20211231 GROUP BY date ,c ) as a where a. date = b. date AND a.c = b.c; igor for the previously reported test - please use this query instead: SELECT t1.id FROM t1 JOIN t3 ON t3.t1_id = t1.id JOIN ( SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id WHERE t1.id in (1, 2070);

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) added a comment - 2022-01-12 19:06

The output of EXPLAIN FORMAT=JSON for the last query shows the the condition reporting_person = 1 has been lost:

MariaDB [test]> EXPLAIN FORMAT=JSON

    -> SELECT t1.id

    -> FROM t1

    -> JOIN t3  ON t3.t1_id = t1.id

    -> JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx

    -> ON tx.t1_id = t1.id

    -> WHERE t1.id in(1, 2070);

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "t3",

      "access_type": "index",

      "possible_keys": ["t1_id"],

      "key": "t1_id",

      "key_length": "15",

      "used_key_parts": ["t1_id", "YEAR", "quarter"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "t3.t1_id in (1,2070) and t3.t1_id is not null and t3.t1_id is not null",

      "using_index": true

},

    "table": {

      "table_name": "t1",

      "access_type": "eq_ref",

      "possible_keys": ["id"],

      "key": "id",

      "key_length": "5",

      "used_key_parts": ["id"],

      "ref": ["test.t3.t1_id"],

      "rows": 1,

      "filtered": 100,

      "using_index": true

},

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "5",

      "used_key_parts": ["t1_id"],

      "ref": ["test.t3.t1_id"],

      "rows": 2,

      "filtered": 100,

      "materialized": {

        "lateral": 1,

        "query_block": {

          "select_id": 2,

          "outer_ref_condition": "t1.`id` is not null",

          "table": {

            "table_name": "t2",

            "access_type": "ref",

            "possible_keys": ["t1_id"],

            "key": "t1_id",

            "key_length": "5",

            "used_key_parts": ["t1_id"],

            "ref": ["test.t1.id"],

            "rows": 1,

            "filtered": 100,

            "index_condition": "t2.t1_id in (1,2070)"

} |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.001 sec)

Igor Babaev (Inactive) added a comment - 2022-01-12 19:06 The output of EXPLAIN FORMAT=JSON for the last query shows the the condition reporting_person = 1 has been lost: MariaDB [test]> EXPLAIN FORMAT=JSON -> SELECT t1.id -> FROM t1 -> JOIN t3 ON t3.t1_id = t1.id -> JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx -> ON tx.t1_id = t1.id -> WHERE t1.id in(1, 2070); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "t3", "access_type": "index", "possible_keys": ["t1_id"], "key": "t1_id", "key_length": "15", "used_key_parts": ["t1_id", "YEAR", "quarter"], "rows": 1, "filtered": 100, "attached_condition": "t3.t1_id in (1,2070) and t3.t1_id is not null and t3.t1_id is not null", "using_index": true }, "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["id"], "key": "id", "key_length": "5", "used_key_parts": ["id"], "ref": ["test.t3.t1_id"], "rows": 1, "filtered": 100, "using_index": true }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "5", "used_key_parts": ["t1_id"], "ref": ["test.t3.t1_id"], "rows": 2, "filtered": 100, "materialized": { "lateral": 1, "query_block": { "select_id": 2, "outer_ref_condition": "t1.`id` is not null", "table": { "table_name": "t2", "access_type": "ref", "possible_keys": ["t1_id"], "key": "t1_id", "key_length": "5", "used_key_parts": ["t1_id"], "ref": ["test.t1.id"], "rows": 1, "filtered": 100, "index_condition": "t2.t1_id in (1,2070)" } } } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)

Igor Babaev (Inactive) added a comment - 2022-01-13 06:50

alice,
Please create a new MDEV for the bug of mhadji@gmail.com. The cause of his problem is quite different.

Igor Babaev (Inactive) added a comment - 2022-01-13 06:50 alice , Please create a new MDEV for the bug of mhadji@gmail.com . The cause of his problem is quite different.

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 added a comment - 2022-01-14 18:14

OK to push.

Oleksandr Byelkin added a comment - 2022-01-14 18:14 OK to push.

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) added a comment - 2022-01-18 20:49

If we look at the output of EXPLAIN FORMAT=JSON for the first reported test we see that the condition
ptld.date_revision_id = dr.`id` has been lost:

MariaDB [test]> EXPLAIN FORMAT=JSON

    -> 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

    -> ;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    8

Current database: test

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "ptld",

      "access_type": "ref",

      "possible_keys": [

        "project_id_project_type_id_leaf_component_id_date_revision_id",

        "project_id",

        "project_id_2"

],

      "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

      "key_length": "4",

      "used_key_parts": ["project_id"],

      "ref": ["const"],

      "rows": 34,

      "filtered": 100,

      "using_index": true

},

    "table": {

      "table_name": "dr",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY", "date_id"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "ref": ["test.ptld.date_revision_id"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "dr.is_anticipated = 1"

},

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "4",

      "used_key_parts": ["project_type_id"],

      "ref": ["test.ptld.project_type_id"],

      "rows": 2,

      "filtered": 100,

      "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx",

      "materialized": {

        "lateral": 1,

        "query_block": {

          "select_id": 2,

          "filesort": {

            "sort_key": "ptld.leaf_component_id",

            "temporary_table": {

              "table": {

                "table_name": "dr",

                "access_type": "ref",

                "possible_keys": ["PRIMARY", "date_id"],

                "key": "date_id",

                "key_length": "4",

                "used_key_parts": ["date_id"],

                "ref": ["test.dr.date_id"],

                "rows": 1,

                "filtered": 100

},

              "duplicates_removal": {

                "table": {

                  "table_name": "dr",

                  "access_type": "ref",

                  "possible_keys": ["PRIMARY", "date_id"],

                  "key": "date_id",

                  "key_length": "4",

                  "used_key_parts": ["date_id"],

                  "ref": ["test.dr.date_id"],

                  "rows": 1,

                  "filtered": 100,

                  "using_index": true

},

                "table": {

                  "table_name": "tstrdr",

                  "access_type": "ref",

                  "possible_keys": ["start_date_revision_id"],

                  "key": "start_date_revision_id",

                  "key_length": "5",

                  "used_key_parts": ["start_date_revision_id"],

                  "ref": ["test.dr.id"],

                  "rows": 4,

                  "filtered": 100,

                  "attached_condition": "tstrdr.offered_for_overdue <> 0"

},

              "table": {

                "table_name": "ptld",

                "access_type": "ref",

                "possible_keys": [

                  "project_id_project_type_id_leaf_component_id_date_revision_id",

                  "project_id",

                  "project_id_2"

],

                "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

                "key_length": "4",

                "used_key_parts": ["project_id"],

                "ref": ["const"],

                "rows": 34,

                "filtered": 100,

                "using_index": true

} |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

After the fix we have:

MariaDB [test]> EXPLAIN FORMAT=JSON

    -> 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

    -> ;

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "ptld",

      "access_type": "ref",

      "possible_keys": [

        "project_id_project_type_id_leaf_component_id_date_revision_id",

        "project_id",

        "project_id_2"

],

      "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

      "key_length": "4",

      "used_key_parts": ["project_id"],

      "ref": ["const"],

      "rows": 34,

      "filtered": 100,

      "using_index": true

},

    "table": {

      "table_name": "dr",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY", "date_id"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "ref": ["test.ptld.date_revision_id"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "dr.is_anticipated = 1"

},

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "4",

      "used_key_parts": ["project_type_id"],

      "ref": ["test.ptld.project_type_id"],

      "rows": 2,

      "filtered": 100,

      "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx",

      "materialized": {

        "lateral": 1,

        "query_block": {

          "select_id": 2,

          "filesort": {

            "sort_key": "ptld.leaf_component_id",

            "temporary_table": {

              "table": {

                "table_name": "dr",

                "access_type": "ref",

                "possible_keys": ["PRIMARY", "date_id"],

                "key": "date_id",

                "key_length": "4",

                "used_key_parts": ["date_id"],

                "ref": ["test.dr.date_id"],

                "rows": 1,

                "filtered": 100

},

              "duplicates_removal": {

                "table": {

                  "table_name": "dr",

                  "access_type": "ref",

                  "possible_keys": ["PRIMARY", "date_id"],

                  "key": "date_id",

                  "key_length": "4",

                  "used_key_parts": ["date_id"],

                  "ref": ["test.dr.date_id"],

                  "rows": 1,

                  "filtered": 100,

                  "using_index": true

},

                "table": {

                  "table_name": "tstrdr",

                  "access_type": "ref",

                  "possible_keys": ["start_date_revision_id"],

                  "key": "start_date_revision_id",

                  "key_length": "5",

                  "used_key_parts": ["start_date_revision_id"],

                  "ref": ["test.dr.id"],

                  "rows": 4,

                  "filtered": 100,

                  "attached_condition": "tstrdr.offered_for_overdue <> 0"

},

              "table": {

                "table_name": "ptld",

                "access_type": "ref",

                "possible_keys": [

                  "project_id_project_type_id_leaf_component_id_date_revision_id",

                  "project_id",

                  "project_id_2"

],

                "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

                "key_length": "4",

                "used_key_parts": ["project_id"],

                "ref": ["const"],

                "rows": 34,

                "filtered": 100,

                "attached_condition": "ptld.date_revision_id = dr.`id`",

                "using_index": true

} |

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> 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

    -> ;

+----------+

| COUNT(*) |

+----------+

|        2 |

+----------+

Igor Babaev (Inactive) added a comment - 2022-01-18 20:49 If we look at the output of EXPLAIN FORMAT=JSON for the first reported test we see that the condition ptld.date_revision_id = dr.`id` has been lost: MariaDB [test]> EXPLAIN FORMAT=JSON -> 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 -> ; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 8 Current database: test +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "using_index": true }, "table": { "table_name": "dr", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "date_id"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["test.ptld.date_revision_id"], "rows": 1, "filtered": 100, "attached_condition": "dr.is_anticipated = 1" }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["project_type_id"], "ref": ["test.ptld.project_type_id"], "rows": 2, "filtered": 100, "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx", "materialized": { "lateral": 1, "query_block": { "select_id": 2, "filesort": { "sort_key": "ptld.leaf_component_id", "temporary_table": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100 }, "duplicates_removal": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100, "using_index": true }, "table": { "table_name": "tstrdr", "access_type": "ref", "possible_keys": ["start_date_revision_id"], "key": "start_date_revision_id", "key_length": "5", "used_key_parts": ["start_date_revision_id"], "ref": ["test.dr.id"], "rows": 4, "filtered": 100, "attached_condition": "tstrdr.offered_for_overdue <> 0" } }, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "using_index": true } } } } } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ After the fix we have: MariaDB [test]> EXPLAIN FORMAT=JSON -> 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 -> ; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "using_index": true }, "table": { "table_name": "dr", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "date_id"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["test.ptld.date_revision_id"], "rows": 1, "filtered": 100, "attached_condition": "dr.is_anticipated = 1" }, "table": { "table_name": "<derived2>", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "4", "used_key_parts": ["project_type_id"], "ref": ["test.ptld.project_type_id"], "rows": 2, "filtered": 100, "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx", "materialized": { "lateral": 1, "query_block": { "select_id": 2, "filesort": { "sort_key": "ptld.leaf_component_id", "temporary_table": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100 }, "duplicates_removal": { "table": { "table_name": "dr", "access_type": "ref", "possible_keys": ["PRIMARY", "date_id"], "key": "date_id", "key_length": "4", "used_key_parts": ["date_id"], "ref": ["test.dr.date_id"], "rows": 1, "filtered": 100, "using_index": true }, "table": { "table_name": "tstrdr", "access_type": "ref", "possible_keys": ["start_date_revision_id"], "key": "start_date_revision_id", "key_length": "5", "used_key_parts": ["start_date_revision_id"], "ref": ["test.dr.id"], "rows": 4, "filtered": 100, "attached_condition": "tstrdr.offered_for_overdue <> 0" } }, "table": { "table_name": "ptld", "access_type": "ref", "possible_keys": [ "project_id_project_type_id_leaf_component_id_date_revision_id", "project_id", "project_id_2" ], "key": "project_id_project_type_id_leaf_component_id_date_revision_id", "key_length": "4", "used_key_parts": ["project_id"], "ref": ["const"], "rows": 34, "filtered": 100, "attached_condition": "ptld.date_revision_id = dr.`id`", "using_index": true } } } } } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ MariaDB [test]> 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 -> ; +----------+ | COUNT(*) | +----------+ | 2 | +----------+

Igor Babaev (Inactive) added a comment - 2022-01-18 20:59

A fix for this bug was pushed into 10.3. It has to be merged upstream as it is.

Igor Babaev (Inactive) added a comment - 2022-01-18 20:59 A fix for this bug was pushed into 10.3. It has to be merged upstream as it is.

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.