This is less a question than just a "beware." Thankfully MariaDB 10.2 is an old version, but it does some funky things when joining to nested derived tables. I updated my database from MariaDB 10.0.35 to 10.2.12 and this bug popped up. I tested the same setup and query on 10.0.35, 10.2.12, and 10.4.17 and got different results.
Here's the setup:
CREATE TABLE `users`
(
`id` int AUTO_INCREMENT PRIMARY KEY,
`username` varchar(128) NULL
) CHARSET = `utf8`;
INSERT INTO `users` (`id`, `username`)
VALUES (6171, 'user01'),
(6172, 'user02'),
(6880, 'user03'),
(7184, 'user04'),
(7185, 'user05'),
(7186, 'user06'),
(7187, 'user07'),
(7188, 'user08'),
(7190, 'user09'),
(7874, 'user10'),
(9307, 'user11'),
(9585, 'user12'),
(12918, 'user13');
CREATE TABLE `addons_users`
(
`id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`user_id` int NULL,
`addon_id` int NULL,
`active` tinyint(1) NULL,
`start_date` datetime NULL,
`end_date` datetime NULL
) CHARSET = `utf8`;
INSERT INTO `addons_users` (`id`,
`user_id`,
`addon_id`,
`active`,
`start_date`,
`end_date`)
VALUES (623, 7185, 1, 0, '2018-10-19 22:56:01', NULL),
(624, 7185, 1, 0, '2019-10-19 22:56:01', NULL),
(625, 7185, 1, 1, '2020-10-19 22:56:01', NULL),
(626, 7186, 1, 0, '2020-04-19 22:56:01', NULL);
Here's the query:
SELECT `users`.`id`, `users`.`username`, `addons_users`.`active`
FROM `users`
LEFT JOIN (SELECT *
FROM `addons_users`
WHERE `id` IN (SELECT MAX(`id`) AS `id`
FROM `addons_users`
GROUP BY `user_id`)) AS `addons_users_unique_current`
ON `addons_users_unique_current`.`user_id` = `users`.`id`;
And here are the results:
MariaDB 10.0.35 MariaDB 10.2.12 MariaDB 10.4.17
+-------+----------+--------+ +------+----------+--------+ +-------+----------+--------+
| id | username | active | | id | username | active | | id | username | active |
+-------+----------+--------+ +------+----------+--------+ +-------+----------+--------+
| 7185 | user05 | 1 | | 7185 | user05 | 1 | | 7185 | user05 | 1 |
| 7186 | user06 | 0 | | 7186 | user06 | 0 | | 7186 | user06 | 0 |
| 6171 | user01 | NULL | +------+----------+--------+ | 6171 | user01 | NULL |
| 6172 | user02 | NULL | | 6172 | user02 | NULL |
| 6880 | user03 | NULL | | 6880 | user03 | NULL |
| 7184 | user04 | NULL | | 7184 | user04 | NULL |
| 7187 | user07 | NULL | | 7187 | user07 | NULL |
| 7188 | user08 | NULL | | 7188 | user08 | NULL |
| 7190 | user09 | NULL | | 7190 | user09 | NULL |
| 7874 | user10 | NULL | | 7874 | user10 | NULL |
| 9307 | user11 | NULL | | 9307 | user11 | NULL |
| 9585 | user12 | NULL | | 9585 | user12 | NULL |
| 12918 | user13 | NULL | | 12918 | user13 | NULL |
+-------+----------+--------+ +-------+----------+--------+
As you can see, 10.2 treats the LEFT JOIN like an INNER JOIN.
