Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27132

Wrong result from query when using split optimization

    XMLWordPrintable

Details

    Description

      We've encountered inconsistent query results. By an trial/error we found out that the cause of the inconsistency is use of LATERAL DERIVED in the query plan. When it's used, the query doesn't return correct results.

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

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

      set global optimizer_switch='split_materialized=off'
      

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

      SELECT COUNT(*)
      FROM project_time_lines_dates ptld
       
      INNER JOIN date_revision dr
          ON dr.id = ptld.date_revision_id
       
      INNER JOIN (
          SELECT
              ptld.project_id AS project_id,
              ptld.project_type_id AS project_type_id,
              ptld.leaf_component_id AS leaf_component_id,
              dr.date_id AS date_id,
               MAX(dr.order_idx) AS max_order
          FROM project_time_lines_dates ptld
          INNER JOIN date_revision dr
              ON dr.id = ptld.date_revision_id
          WHERE dr.date_id IN (
              SELECT DISTINCT dr.date_id
              FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr
              INNER JOIN date_revision dr
                  ON dr.id = tstrdr.start_date_revision_id
              WHERE tstrdr.offered_for_overdue
          )
          
          GROUP BY
              ptld.project_id,
              ptld.project_type_id,
              ptld.leaf_component_id,
              dr.date_id
      ) max_anticipated
          ON max_anticipated.project_id = ptld.project_id
          AND max_anticipated.project_type_id = ptld.project_type_id
          AND max_anticipated.date_id = dr.date_id
          AND max_anticipated.max_order = dr.order_idx
       
      WHERE dr.is_anticipated = TRUE
          AND ptld.project_id = 5896
      

      The anticipated result is 2, but when LATERAL DERIVED is used, the number of returned rows is 0.

      EXPLAIN of the query with disabled LATERAL DERIVED looks like this:

      +------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
      | id   | select_type | table      | type   | possible_keys                                                                         | key                    | key_len | ref                                         | rows | Extra                                        |
      +------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
      |    1 | PRIMARY     | ptld       | ref    | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2           | 4       | const                                       | 34   | Using index                                  |
      |    1 | PRIMARY     | dr         | eq_ref | PRIMARY,date_id                                                                       | PRIMARY                | 4       | bugreport.ptld.date_revision_id             | 1    | Using where                                  |
      |    1 | PRIMARY     | <derived2> | ref    | key1                                                                                  | key1                   | 9       | bugreport.dr.date_id,bugreport.dr.order_idx | 10   | Using where                                  |
      |    2 | DERIVED     | ptld       | ref    | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2           | 4       | const                                       | 34   | Using index; Using temporary; Using filesort |
      |    2 | DERIVED     | dr         | eq_ref | PRIMARY,date_id                                                                       | PRIMARY                | 4       | bugreport.ptld.date_revision_id             | 1    |                                              |
      |    2 | DERIVED     | dr         | ref    | PRIMARY,date_id                                                                       | date_id                | 4       | bugreport.dr.date_id                        | 1    | Using index                                  |
      |    2 | DERIVED     | tstrdr     | ref    | start_date_revision_id                                                                | start_date_revision_id | 5       | bugreport.dr.id                             | 4    | Using where; FirstMatch(dr)                  |
      +------+-------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------------------+------+----------------------------------------------+
      

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

      +------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
      | id   | select_type     | table      | type   | possible_keys                                                                         | key                    | key_len | ref                             | rows | Extra                           |
      +------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
      |    1 | PRIMARY         | ptld       | ref    | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2           | 4       | const                           | 34   | Using index                     |
      |    1 | PRIMARY         | dr         | eq_ref | PRIMARY,date_id                                                                       | PRIMARY                | 4       | bugreport.ptld.date_revision_id | 1    | Using where                     |
      |    1 | PRIMARY         | <derived2> | ref    | key0                                                                                  | key0                   | 4       | bugreport.ptld.project_type_id  | 2    | Using where                     |
      |    2 | LATERAL DERIVED | dr         | ref    | PRIMARY,date_id                                                                       | date_id                | 4       | bugreport.dr.date_id            | 1    | Using temporary; Using filesort |
      |    2 | LATERAL DERIVED | dr         | ref    | PRIMARY,date_id                                                                       | date_id                | 4       | bugreport.dr.date_id            | 1    | Using index; Start temporary    |
      |    2 | LATERAL DERIVED | tstrdr     | ref    | start_date_revision_id                                                                | start_date_revision_id | 5       | bugreport.dr.id                 | 4    | Using where; End temporary      |
      |    2 | LATERAL DERIVED | ptld       | ref    | project_id_project_type_id_leaf_component_id_date_revision_id,project_id,project_id_2 | project_id_2           | 4       | const                           | 34   | Using index                     |
      +------+-----------------+------------+--------+---------------------------------------------------------------------------------------+------------------------+---------+---------------------------------+------+---------------------------------+
      7 rows in set (0.001 sec)
      

      Attachments

        1. bugreport.sql.gz
          227 kB
          Peter Dulacka
        2. mariadb10.4.22.tar.gz
          35 kB
          Silver Asu
        3. raw_ab.tar.gz
          0.6 kB
          Marios Hadjieleftheriou

        Issue Links

          Activity

            People

              igor Igor Babaev
              rootpd Peter Dulacka
              Votes:
              3 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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