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

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

Details

    Description

      I couldn't work out how to reopen an issue, so sorry for making a new one.

      The problem in MDEV-25714 and MDEV-21614 seems to be back in 10.6.4 (we recently upgraded from 10.5.6).

      Using `SET optimizer_switch="split_materialized=off";` fixes the problem, so this is our temporary work-around.

      Thanks for your help.

      Attachments

        1. dataset.sql
          2 kB
        2. screenshot-1.png
          screenshot-1.png
          13 kB
        3. screenshot-2.png
          screenshot-2.png
          65 kB
        4. screenshot-3.png
          screenshot-3.png
          9 kB
        5. screenshot-4.png
          screenshot-4.png
          56 kB

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Could you please add the test case to demonstrate the problem, tests from MDEV-25714 and MDEV-21614 return correct results on 10.6.4

            alice Alice Sherepa added a comment - Could you please add the test case to demonstrate the problem, tests from MDEV-25714 and MDEV-21614 return correct results on 10.6.4
            violuke Luke Cousins added a comment -

            Apologies for the delay. I will get a test case together for you. Our production query is very complicated, and I'm struggling to produce a simpler query that demonstrates the problem, but I'll make sure I get back to you soon with something suitable.

            violuke Luke Cousins added a comment - Apologies for the delay. I will get a test case together for you. Our production query is very complicated, and I'm struggling to produce a simpler query that demonstrates the problem, but I'll make sure I get back to you soon with something suitable.
            Matthieu Matthieu Lombard added a comment - - edited

            I have the same problem in version 10.3.32.
            We just switch from 10.3.23 to 10.3.32.
            I tried all versions between and problem seems to appear in 10.3.29 version. 10.3.28 is still good.
            Using `SET optimizer_switch="split_materialized=off";` fixes the problem too.

            To reproduce with the joined dataset.sql, try to execute this request :

            SELECT m.id AS mission_id, a.applicants_users_id AS applicant_id
                 ,sub.sent
            FROM t_missions m
            INNER JOIN t_applications a ON a.offers_id = m.offers_id
            INNER JOIN t_user u ON u.id = a.applicants_users_id
            LEFT JOIN (
                SELECT m2.id AS mission_id, a2.applicants_users_id AS applicant_id, TRUE as sent
                FROM t_missions m2
                INNER JOIN t_applications a2 ON a2.offers_id = m2.offers_id
                    AND a2.application_date IS NOT NULL
                WHERE m2.mission_status = 2
                GROUP BY a2.offers_id, a2.applicants_users_id
            ) sub ON sub.applicant_id = a.applicants_users_id AND sub.mission_id = m.id;
            

            It give us :

            With JOIN clause in subquery : WRONG RESULT

            Without some clauses in subquery, (comment `AND a2.application_date IS NOT NULL` or `WHERE...` or `ORDER BY...`) : GOOD RESULT

            (In both case, subquery return same results, so final result should be the same too)

            Without indexes in applications table, we got good result too.
            Problem appear when LATERAL DERIVED select type is used.

            Thanks for help !

            Matthieu Matthieu Lombard added a comment - - edited I have the same problem in version 10.3.32. We just switch from 10.3.23 to 10.3.32. I tried all versions between and problem seems to appear in 10.3.29 version. 10.3.28 is still good. Using `SET optimizer_switch="split_materialized=off";` fixes the problem too. To reproduce with the joined dataset.sql, try to execute this request : SELECT m.id AS mission_id, a.applicants_users_id AS applicant_id ,sub.sent FROM t_missions m INNER JOIN t_applications a ON a.offers_id = m.offers_id INNER JOIN t_user u ON u.id = a.applicants_users_id LEFT JOIN ( SELECT m2.id AS mission_id, a2.applicants_users_id AS applicant_id, TRUE as sent FROM t_missions m2 INNER JOIN t_applications a2 ON a2.offers_id = m2.offers_id AND a2.application_date IS NOT NULL WHERE m2.mission_status = 2 GROUP BY a2.offers_id, a2.applicants_users_id ) sub ON sub.applicant_id = a.applicants_users_id AND sub.mission_id = m.id; It give us : With JOIN clause in subquery : WRONG RESULT Without some clauses in subquery, (comment `AND a2.application_date IS NOT NULL` or `WHERE...` or `ORDER BY...`) : GOOD RESULT (In both case, subquery return same results, so final result should be the same too) Without indexes in applications table, we got good result too. Problem appear when LATERAL DERIVED select type is used. Thanks for help !
            alice Alice Sherepa added a comment -

            Matthieu Thanks! I repeated the bug, currently, it is fixed by the commit 97425f740faf83ac2d by Igor Bababev, MDEV-27132.

            alice Alice Sherepa added a comment - Matthieu Thanks! I repeated the bug, currently, it is fixed by the commit 97425f740faf83ac2d by Igor Bababev, MDEV-27132 .
            igor Igor Babaev added a comment -

            Closed as a duplicate of MDEV-27132 that is already fixed.

            igor Igor Babaev added a comment - Closed as a duplicate of MDEV-27132 that is already fixed.
            igor Igor Babaev added a comment -

            The patch for MDEV-27132 really fixes the problem with the above query. However the patch for MDEV-27510 provides a solution that covers a wider circle of queries that might employ the split optimization and return wrong result sets.

            igor Igor Babaev added a comment - The patch for MDEV-27132 really fixes the problem with the above query. However the patch for MDEV-27510 provides a solution that covers a wider circle of queries that might employ the split optimization and return wrong result sets.

            Thanks!

            Matthieu Matthieu Lombard added a comment - Thanks!

            People

              alice Alice Sherepa
              violuke Luke Cousins
              Votes:
              4 Vote for this issue
              Watchers:
              8 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.