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

Bad join results with orderby_uses_equalities=on

    XMLWordPrintable

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

          Activity

            People

              igor Igor Babaev
              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.