MariaDB JOIN syntax

I'm trying select some records from three linked tables (many to many relationship) and then left join the results with another table. I get the following error:

#1054 - Unknown column 'u.id' in 'on clause'

here is the full example of the query that raises that error:

drop database if exists example1;
create database example1;
use example1;
create table users (id integer not null auto_increment primary key, name varchar(50));
insert into users (name) values ('Alice'),('Bob'),('Carol');

create table roles (id integer not null auto_increment primary key, role varchar(50));
insert into roles (role) values ('developer'),('hr'),('sales');

create table users_roles (user_id integer, role_id integer);
insert into users_roles (user_id, role_id) values (1,1),(1,2),(2,3),(3,2);

create table activity (id integer not null auto_increment primary key, user_id integer not null, description varchar (200));
insert into activity (user_id, description) values (1, 'logged in'), (1, 'logged out'), (2, 'changed password');

SELECT u.name AS 'Username',
       a.description AS 'Activity',
       r.role AS 'Role'

FROM users u, users_roles ur, roles r

LEFT JOIN activity a ON a.user_id = u.id

WHERE ur.role_id = r.id AND ur.user_id = u.id;

After several tests, I've come to the conclusion that the problem is listing more than one table in the FROM clause and then following it with a LEFT JOIN. So I suppose I should write this instead:

[...]
SELECT u.name AS 'Username',
       a.description AS 'Activity',
       r.role AS 'Role'

FROM users u 
 JOIN users_roles ur ON ur.user_id = u.id
 JOIN roles r ON r.id = ur.role_id

LEFT JOIN activity a ON a.user_id = u.id

And in fact this does work as expected. My question is: what's wrong with the first version of my SELECT? Shouldn't that be supported by the MySQL syntax and, if not, why not?

Solution

Don't mix implicit and explicit joins! Bad things happen when you do that, because different types of joins have different precedence rules: explicit joins are evaluated first, which causes the error you are getting. At the time when the left join is interpreted, aliases defined in the implicit joins have not been seen yet.

Matter of fact, use explicit, standard joins consistently, in all your queries: implicit joins are legacy syntax, that should not be used in new code.

SELECT u.name AS Username,
       a.description AS Activity,
       r.role AS Role
FROM users u
INNER JOIN users_roles ur ON ur.user_id = u.id
INNER JOIN roles r ON r.id = ur.role_id
LEFT JOIN activity a ON a.user_id = u.id