1

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.

3
  • 2
    So, what's your question? If you're trying to report a bug for a MariaDB version that's almost out of support, I'm afraid you have chosen the wrong venue.
    – mustaccio
    Jan 19 at 1:38
  • Hi and welcome to the forum! Check out this bug (PostgreSQL returns ISO format dates whereas MySQL and MariaDB (all versions). I've long since stopped getting hot under the collar over weird things happening with MySQL and its derivatives - I just use PostgreSQL - sleep far better at night! :-) There are a number of pointers on how to ask questions here on my profile - you might want to take a look? Don't be discouraged though - I find it interesting and wish that this site was a bit more discursive - but always remember, this is a Q&A site and general discussion is (ahem..l.) discouraged here
    – Vérace
    Jan 19 at 6:35
  • I just thought that this site is where a lot of Google searches for this kind of stuff end, so it might be helpful for someone to stumble upon this and know that yes, this is a bug. It's a pretty esoteric thing to search for. Jan 19 at 17:57

Browse other questions tagged or ask your own question.