Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9
-
None
-
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; |