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

Optimizer_switch split_materialized Returns Wrong Results

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.5.11, 10.5.12, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
    • N/A
    • Optimizer
    • None
    • Centos7

    Description

      There is a bug with split_materialized.
      When optimizer_switch='split_materialized=on'; the attached query returns very wrong results. After disabling the split_materialized, it returns the correct results.

      This bug starts in 10.5.10, which is the version that includes the fixes from 10.4.19. 10.4.19 includes MDEV-25128 fix which could be a blame option. The bug does not happen in 10.5.9.

      I've attached the .sql to reproduce the bug.

      Attachments

        Issue Links

          Activity

            tttpapi Tomáš Huda added a comment - - edited

            I think we have the same problem with 10.5.21-MariaDB-1:10.5.21+maria~deb10-log on server with replication.
            With split_materialized=on query returns correct results for 70 rows. 71+ rows returns correct result only for first row other rows returns null values for joins.

            Example of result:
            70 ids:
            editionNames | editionsIds | p.id
            rock oldies | 1 | 1
            rock oldies | 1 | 2
            indies | 2 | 3

            71+ ids:
            editionNames | editionsIds | p.id
            rock oldies | 1 | 1
            null | null | 2
            null | null | 3

            The ids are not the problem I tried to swap them.

            Local version 10.5.17-MariaDB works fine but there is no replication. I will try to reproduce the problem locally with 10.5.21-MariaDB-1:10.5.21+maria~ubu2004-log.

            Problem should not be caused by data, because it appeared right after upgrade from 10.3.x to 10.5.21.

            tttpapi Tomáš Huda added a comment - - edited I think we have the same problem with 10.5.21-MariaDB-1:10.5.21+maria~deb10-log on server with replication. With split_materialized=on query returns correct results for 70 rows. 71+ rows returns correct result only for first row other rows returns null values for joins. Example of result: 70 ids: editionNames | editionsIds | p.id rock oldies | 1 | 1 rock oldies | 1 | 2 indies | 2 | 3 71+ ids: editionNames | editionsIds | p.id rock oldies | 1 | 1 null | null | 2 null | null | 3 The ids are not the problem I tried to swap them. Local version 10.5.17-MariaDB works fine but there is no replication. I will try to reproduce the problem locally with 10.5.21-MariaDB-1:10.5.21+maria~ubu2004-log. Problem should not be caused by data, because it appeared right after upgrade from 10.3.x to 10.5.21.

            Automated message:
            ----------------------------
            Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            julien.fritsch Julien Fritsch added a comment - Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
            JIraAutomate JiraAutomate added a comment -

            Automated message:
            ----------------------------
            Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            JIraAutomate JiraAutomate added a comment - Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
            alice Alice Sherepa added a comment -

            tttpapi If you still have this problem, could you please report it separately? The other reported tests return the expected results now.

            alice Alice Sherepa added a comment - tttpapi If you still have this problem, could you please report it separately? The other reported tests return the expected results now.
            alice Alice Sherepa added a comment -

            Currently 10.5 (b8ad202da1336550e6987a9c673d1a23e4b26e7b) -11.7 return correct results, I've checked both test cases, that are attached to this report.

            alice Alice Sherepa added a comment - Currently 10.5 (b8ad202da1336550e6987a9c673d1a23e4b26e7b) -11.7 return correct results, I've checked both test cases, that are attached to this report.

            People

              igor Igor Babaev
              ksizemore Kathryn Sizemore
              Votes:
              7 Vote for this issue
              Watchers:
              12 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.