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

Subquery with group by returns wrong results

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.9
    • Fix Version/s: 10.3.11
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      Tested on Windows (laptop) and CentOS (server). I don't think this bug has anything to do with the platform/hardware.

      Description

      In MariaDB 10.3.9, while using a subquery with group by, we get incorrect join results. The same query works fine on MariaDB 10.1.32. It also works fine without the group by.

      We can reproduce this with:

      CREATE TABLE `user` (
      	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      	`username` VARCHAR(50) NULL DEFAULT '0',
      	PRIMARY KEY (`id`)
      )
      COLLATE='utf8_general_ci'
      ;
       
      CREATE TABLE `audit` (
      	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      	`userid` INT UNSIGNED NOT NULL,
      	`logindate` DATETIME NOT NULL,
      	PRIMARY KEY (`id`)
      )
      COLLATE='utf8_general_ci'
      ;
       
      insert into user (id, username) VALUES (1,"user1"), (2, "user2");
      insert into audit (id, userid, logindate) VALUES (1,1,"2015-06-19 12:17:02.828"),
        (2,1,"2016-06-19 12:17:02.828"),
        (3,2,"2017-06-19 12:17:02.828"),
        (4,2,"2018-06-19 12:17:02.828");
        
      select *
      from user u
      left join (select * from audit au group by au.userid) as auditlastlogin on u.id=auditlastlogin.userid;
      
      

      Results in 10.1.32 (correct):

      "id"	"username"	"id"	"userid"	"logindate"
      "1"	"user1"	"1"	"1"	"2015-06-19 12:17:02"
      "2"	"user2"	"3"	"2"	"2017-06-19 12:17:02"
      

      On 10.3.9 (wrong):

      "id"	"username"	"id"	"userid"	"logindate"
      "1"	"user1"	"1"	"1"	"2015-06-19 12:17:02"
      "1"	"user1"	"3"	"2"	"2017-06-19 12:17:02"
      "2"	"user2"	"1"	"1"	"2015-06-19 12:17:02"
      "2"	"user2"	"3"	"2"	"2017-06-19 12:17:02"
      

      The same thing happens for:

      select *
      from user u, (select * from audit au group by au.userid) as auditlastlogin
      where u.id=auditlastlogin.userid;
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            stijn Stijn
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: