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

Different result with LEFT OUTER JOIN and GROUP BY starting with 10.0.30

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.30, 10.0.31, 10.0.32, 10.0.33, 10.0.34
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Assuming the following in a database bugtest:

      bugtest_dump.sql

      DROP TABLE IF EXISTS `t1`;
      CREATE TABLE `t1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `works` VARCHAR(1) DEFAULT NULL,
        PRIMARY KEY (`id`)
      );
      INSERT INTO `t1` VALUES (1,'y'),(2,'n');
      DROP TABLE IF EXISTS `t2`;
      CREATE TABLE `t2` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `t1_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
      );
      INSERT INTO `t2` VALUES (1,1),(2,1),(3,3),(4,3);}}
      

      And the following query:

      SELECT * FROM t1 LEFT OUTER JOIN (SELECT t1_id FROM t2 WHERE id IN (SELECT MAX(id) FROM t2 GROUP BY t1_id)) tmpname on tmpname.t1_id = t1.id;
      

      pre 10.0.30 the result is:

      +----+-------+-------+
      | id | works | t1_id |
      +----+-------+-------+
      |  1 | y     |     1 |
      |  2 | n     |  NULL |
      +----+-------+-------+
      2 rows in set (0.00 sec)
      

      Since 10.0.30 it is:

      +----+-------+-------+
      | id | works | t1_id |
      +----+-------+-------+
      |  1 | y     |     1 |
      +----+-------+-------+
      1 row in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              alice Alice Sherepa
              Reporter:
              shoentjen Sander Hoentjen
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: