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

Bad join results with orderby_uses_equalities=on

Details

    • 10.2.11

    Description

      CREATE TABLE `books` (
      `id` int(16) NOT NULL AUTO_INCREMENT,
      `library_id` int(16) NOT NULL DEFAULT 0,
      `wings_id` int(12) NOT NULL DEFAULT 0,
      `scheduled_for_removal` int(1) DEFAULT 0,
      PRIMARY KEY (`id`),
      KEY `library_idx` (`library_id`)
      ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

      INSERT INTO `books` VALUES (32625,8663,707,0),(32624,8663,505,1);

      CREATE TABLE `wings` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `department_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

      INSERT INTO `wings` VALUES (505,11745),(707,11768);

      SELECT wings.id as wing_id, wings.department_id FROM wings WHERE wings.id IN ( SELECT books.wings_id FROM books WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id;

      The result should be (707, 11768), but it results in (505, 11745).

      Changing the engine for the books table to InnoDB, resolves the problem. Removing the library_idx on books also resolves the problem.

      I also tested on mysql 5.5.57, 5.6.37, 5.7.19, and mariadb 5.5.57, 10.0.32 and 10.1.28, where the problem does not occur.

      I've added `script.sql` containing the sql from above, and a wrapper script `reproduce.sh` to set up a docker environment for testing.

      Attachments

        1. reproduce.sh
          0.5 kB
          Jan Willem
        2. script.sql
          0.8 kB
          Jan Willem

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13390 Identity server Db Select Statement order by issue.

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13704 Nested query does not give same result as subquery and an outer query

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14071 Wildly wrong result from subquery in WHERE clause

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13390 Identity server Db Select Statement order by issue.

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Thanks for the report and test case. For a workaround, try setting optimizer_switch='orderby_uses_equalities=off'.

            Same test case as in script.sql, suitable for MTR:

            --source include/have_innodb.inc
            		 
             
            		 
            CREATE TABLE `books` (
            		 
              `id` int(16) NOT NULL AUTO_INCREMENT,
            		 
              `library_id` int(16) NOT NULL DEFAULT 0,
            		 
              `wings_id` int(12) NOT NULL DEFAULT 0,
            		 
              `scheduled_for_removal` int(1) DEFAULT 0,
            		 
              PRIMARY KEY (`id`),
            		 
              KEY `library_idx` (`library_id`)
            		 
            ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
            		 
             
            		 
             
            		 
            INSERT INTO `books` VALUES (32625,8663,707,0),(32624,8663,505,1);
            		 
             
            		 
            CREATE TABLE `wings` (
            		 
              `id` int(11) NOT NULL AUTO_INCREMENT,
            		 
              `department_id` int(11) DEFAULT NULL,
            		 
              PRIMARY KEY (`id`)
            		 
            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
            		 
             
            		 
            INSERT INTO `wings` VALUES (505,11745),(707,11768);
            		 
             
            		 
            SELECT wings.id as wing_id, wings.department_id FROM wings WHERE wings.id IN ( SELECT books.wings_id FROM books  WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id;
            		 
             
            		 
            DROP TABLE books, wings;

            Actual result (10.2)

            		 
            +---------+---------------+
            		 
            | wing_id | department_id |
            		 
            +---------+---------------+
            		 
            |     505 |         11745 |
            		 
            +---------+---------------+

            Expected result

            		 
            +---------+---------------+
            		 
            | wing_id | department_id |
            		 
            +---------+---------------+
            		 
            |     707 |         11768 |
            		 
            +---------+---------------+
            		 
            1 row in set (0.00 sec)

            elenst Elena Stepanova added a comment - Thanks for the report and test case. For a workaround, try setting optimizer_switch='orderby_uses_equalities=off' . Same test case as in script.sql, suitable for MTR: --source include/have_innodb.inc   CREATE TABLE `books` ( `id` int (16) NOT NULL AUTO_INCREMENT, `library_id` int (16) NOT NULL DEFAULT 0, `wings_id` int (12) NOT NULL DEFAULT 0, `scheduled_for_removal` int (1) DEFAULT 0, PRIMARY KEY (`id`), KEY `library_idx` (`library_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;     INSERT INTO `books` VALUES (32625,8663,707,0),(32624,8663,505,1);   CREATE TABLE `wings` ( `id` int (11) NOT NULL AUTO_INCREMENT, `department_id` int (11) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;   INSERT INTO `wings` VALUES (505,11745),(707,11768);   SELECT wings.id as wing_id, wings.department_id FROM wings WHERE wings.id IN ( SELECT books.wings_id FROM books WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id;   DROP TABLE books, wings; Actual result (10.2) +---------+---------------+ | wing_id | department_id | +---------+---------------+ | 505 | 11745 | +---------+---------------+ Expected result +---------+---------------+ | wing_id | department_id | +---------+---------------+ | 707 | 11768 | +---------+---------------+ 1 row in set (0.00 sec)
            psergei Sergei Petrunia added a comment -

            MDEV-13390 and MDEV-13994 look similar.

            psergei Sergei Petrunia added a comment - MDEV-13390 and MDEV-13994 look similar.
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 10.2.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.2.

            People

              igor Igor Babaev (Inactive)
              jagij Jan Willem
              Votes:
              0 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.