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

Incorrect result without an index hint

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.25, 5.5.57, 10.0.32, 10.2.7
    • Optimizer
    • None
    • RHEL 7

    Description

      Query returns incorrect result without an index hint.

      Test case provided.

      Attachments

        1. a.sql
          940 kB
        2. test_wrong.tar.gz
          159 kB

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Reproduced on 10.1.19- 10.1.24, not reproducible on versions >=10.1.25.
            Little bit shorter test case in a.sql. a.sql

            alice Alice Sherepa added a comment - Reproduced on 10.1.19- 10.1.24, not reproducible on versions >=10.1.25. Little bit shorter test case in a.sql. a.sql
            aadant Arnaud Adant added a comment - - edited

            please note that the a test case is random ... in 10.1.20.

            MariaDB [test]> source a.sql;
            Show warnings enabled.
            Query OK, 0 rows affected (0.02 sec)
             
            Query OK, 0 rows affected (0.03 sec)
             
            Query OK, 347 rows affected (0.03 sec)
            Records: 347  Duplicates: 0  Warnings: 0
             
            Query OK, 0 rows affected (0.02 sec)
             
            Query OK, 16302 rows affected (0.20 sec)
            Records: 16302  Duplicates: 0  Warnings: 0
             
            Query OK, 0 rows affected (0.03 sec)
            

            Query OK, 6 rows affected (0.02 sec)
            Records: 6  Duplicates: 0  Warnings: 0
             
            +-----------+
            | count(id) |
            +-----------+
            |         2 |
            +-----------+
            1 row in set (0.02 sec)
             
            +-----------+
            | count(id) |
            +-----------+
            |         6 |
            +-----------+
            1 row in set (0.02 sec)
            

            MariaDB [test]> source a.sql;
            Show warnings enabled.
            Query OK, 0 rows affected (0.02 sec)
             
            Query OK, 0 rows affected (0.02 sec)
             
            Query OK, 347 rows affected (0.03 sec)
            Records: 347  Duplicates: 0  Warnings: 0
             
            Query OK, 0 rows affected (0.02 sec)
             
            Query OK, 16302 rows affected (0.19 sec)
            Records: 16302  Duplicates: 0  Warnings: 0
             
            Query OK, 0 rows affected (0.03 sec)
            

            Query OK, 6 rows affected (0.02 sec)
            Records: 6  Duplicates: 0  Warnings: 0
             
            +-----------+
            | count(id) |
            +-----------+
            |         6 |
            +-----------+
            1 row in set (0.02 sec)
             
            +-----------+
            | count(id) |
            +-----------+
            |         6 |
            +-----------+
            1 row in set (0.02 sec)
            

            aadant Arnaud Adant added a comment - - edited please note that the a test case is random ... in 10.1.20. MariaDB [test]> source a.sql; Show warnings enabled. Query OK, 0 rows affected (0.02 sec)   Query OK, 0 rows affected (0.03 sec)   Query OK, 347 rows affected (0.03 sec) Records: 347 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.02 sec)   Query OK, 16302 rows affected (0.20 sec) Records: 16302 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.03 sec) Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0   +-----------+ | count(id) | +-----------+ | 2 | +-----------+ 1 row in set (0.02 sec)   +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.02 sec) MariaDB [test]> source a.sql; Show warnings enabled. Query OK, 0 rows affected (0.02 sec)   Query OK, 0 rows affected (0.02 sec)   Query OK, 347 rows affected (0.03 sec) Records: 347 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.02 sec)   Query OK, 16302 rows affected (0.19 sec) Records: 16302 Duplicates: 0 Warnings: 0   Query OK, 0 rows affected (0.03 sec) Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0   +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.02 sec)   +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.02 sec)
            alice Alice Sherepa added a comment - - edited

            I think it is related to this bug https://jira.mariadb.org/browse/MDEV-12838 and was fixed with this revision #c258ca2463.
            Results of queries are the same if materialization is off.

            set optimizer_switch='materialization=off';
            

            alice Alice Sherepa added a comment - - edited I think it is related to this bug https://jira.mariadb.org/browse/MDEV-12838 and was fixed with this revision #c258ca2463. Results of queries are the same if materialization is off. set optimizer_switch= 'materialization=off' ;

            I agree with Alice's finding, the initial test case (which appears to be deterministic or close to it) also returns wrong results before the patch for MDEV-12838 and correct results after it.

            commit c258ca2463947fcc3d69bb50a8b5cf6906778508
            Author: Igor Babaev <igor@askmonty.org>
            Date:   Wed Jun 7 12:45:09 2017 -0700
             
                Fixed the bug mdev-12838.
                
                If the optimizer chose an execution plan where
                a semi-join nest were materialized and the
                result of materialization was scanned to access
                other tables by ref access it could build a key
                over columns of the tables from the nest that
                were actually inaccessible.
                The patch performs a proper check whether a key
                that uses columns of the tables from a materialized
                semi-join nest can be employed to access outer tables.
            

            elenst Elena Stepanova added a comment - I agree with Alice's finding, the initial test case (which appears to be deterministic or close to it) also returns wrong results before the patch for MDEV-12838 and correct results after it. commit c258ca2463947fcc3d69bb50a8b5cf6906778508 Author: Igor Babaev <igor@askmonty.org> Date: Wed Jun 7 12:45:09 2017 -0700   Fixed the bug mdev-12838. If the optimizer chose an execution plan where a semi-join nest were materialized and the result of materialization was scanned to access other tables by ref access it could build a key over columns of the tables from the nest that were actually inaccessible. The patch performs a proper check whether a key that uses columns of the tables from a materialized semi-join nest can be employed to access outer tables.

            People

              Unassigned Unassigned
              kjoiner Kyle Joiner (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              5 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.