MariaDB [test]> CREATE TABLE `user` (

    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

    -> `username` VARCHAR(50) NULL DEFAULT '0',

    -> PRIMARY KEY (`id`)

    -> )

    -> COLLATE='utf8_general_ci'

    -> ;

Query OK, 0 rows affected (0.029 sec)

MariaDB [test]> 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'

    -> ;

Query OK, 0 rows affected (0.027 sec)

MariaDB [test]> insert into user (id, username) VALUES (1,"user1"), (2, "user2");

Query OK, 2 rows affected (0.006 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> 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");

Query OK, 4 rows affected (0.008 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select *

    -> from user u

    -> left join (select * from audit au group by au.userid) as auditlastlogin on u.id=auditlastlogin.userid;

+----+----------+------+--------+---------------------+

| 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 |

+----+----------+------+--------+---------------------+

2 rows in set (0.001 sec)

MariaDB [test]> select version();

+----------------+

| version()      |

+----------------+

| 10.3.9-MariaDB |

+----------------+

1 row in set (0.000 sec)

MariaDB [test]> select *

    -> from user u, (select * from audit au group by au.userid) as auditlastlogin

    -> where u.id=auditlastlogin.userid;

+----+----------+----+--------+---------------------+

| 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 |

+----+----------+----+--------+---------------------+

2 rows in set (0.001 sec)

commit 1eca49577e979220f3ab663a7e46e0eb70d728c4

Author: Igor Babaev <igor@askmonty.org>

Date:   Sun Oct 7 10:19:19 2018 -0700

    MDEV-17382 Hash join algorithm should not be used to join materialized

               derived table / view by equality

    Now rows of a materialized derived table are always put into a

    temporary table before join operation. If BNLH is used to join this

    table with the result of a partial join then both operands of the

    join are actually put into main memory. In most cases this is not

    efficient.

    We could avoid this by sending the rows of the derived table directly

    to the join operation. However this kind of data flow is not supported

    yet.

    Fixed by not allowing usage of hash join algorithm to join a materialized

    derived table if it's joined by an equality predicate of the form

    f=e where f is a field of the derived table.