Recursive CTE query in EXISTS

Question

I'm running into an issue with recursive cte's on mariaDB/mysql. The setup isn't too complicated: there's a users table, a roles table, and a user_roles table that assign roles to users. However, roles can be nested, and the roles table contains a parent_id field to accomplish this. The parent_id field will be NULL when it's a root role, and contain the ID of another role if it's not.

This can be setup with following creates and inserts:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY (id)
);

CREATE TABLE roles (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    parent_id INT,
    PRIMARY KEY (id)
);

CREATE TABLE user_roles (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT,
    role_id INT,
    PRIMARY KEY (id)
);

INSERT INTO users(name) VALUES ('Alice'),('Bob'),('Charlie');

INSERT INTO roles(name, parent_id) VALUES
('superuser',null),
('admin',1),
('ceo',1),
('employee', null);

INSERT INTO user_roles(user_id, role_id) VALUES
(1,2),
(2,3),
(3,4);

My goal is to create a query that selects all users where one of the user's roles (or one of it's ancenstors) matches a given role name. I came up with this:

SELECT * FROM users
WHERE EXISTS(
    SELECT 1 FROM roles
    INNER JOIN user_roles ON roles.id = user_roles.role_id
    WHERE users.id = user_roles.user_id
    AND EXISTS(
        WITH RECURSIVE cte AS (
            SELECT * FROM roles as roles_recursive
            WHERE roles_recursive.id = roles.id
            UNION ALL
            SELECT roles_recursive.* FROM roles as roles_recursive
            INNER JOIN cte ON cte.parent_id = roles_recursive.id
        )
        SELECT 1 from cte WHERE name='superuser'
    )
);

I setup a DB fiddle to reproduce my issue. Please check out the full repro case over there. This produces different results on different engines:

MySQL 8.0.18: my current development machine only runs macOS 10.13, so I'm limited to this MySQL version. On this version, I only get 'Alice' as a result, while 'Bob' should be in the results as well.
The DB fiddle, MariaDB 10.3.30: produces an error: "ER_BAD_FIELD_ERROR: Unknown column 'roles.id' in 'where clause'". It looks like the roles-scope isn't available within the CTE.
dbfiddle.uk seems to be running yet another version of MySQL and does return the correct result set.

Is there any way to do this that at least works in MariaDB? I'm not looking for solutions with simple joins, the roles should support a variable depth.

user_2445629 · Answer

Here's an example.

all_roles recursively finds all roles paired with their complete ancestry.

all_roles.base_role is the starting child role id, with base_name the starting name.

uRoles finds the corresponding user_id associated with each base_role.

Finally, we find all users which have a uRoles row matching the given role name.

Note: I added a level restriction, just in case. Remove that, if you wish, or increase it as needed, to avoid problems that might lead to infinite recursion.

WITH RECURSIVE all_roles (base_role, base_name, id, name, parent_id, lev)  AS (
         SELECT r.id  AS base_role, r.name      , r.*, 1 AS lev
           FROM roles AS r
          UNION ALL
         SELECT r0.base_role      , r0.base_name, r.*, lev + 1
           FROM roles     AS r
           JOIN all_roles AS r0
             ON r.id = r0.parent_id
            AND lev < 8
     )
   , uRoles AS (
         SELECT ur.*, ar.name, ar.base_name, ar.lev
           FROM all_roles  AS ar
           JOIN user_roles AS ur
             ON ar.base_role = ur.role_id
     )
SELECT *
  FROM users
 WHERE id IN (SELECT user_id FROM uRoles WHERE name = 'superuser')
;


Result:

+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
+----+-------+

user_6219979 · Answer

First find all the roles down the hierachy from the given one then select users taking any of the roles found.

WITH RECURSIVE cte AS (
   -- The role and its descendants 
   SELECT * 
   FROM roles 
   WHERE name = 'superuser'
   UNION ALL
   SELECT roles.* 
   FROM roles 
   JOIN cte ON cte.id = roles.parent_id 
)
SELECT DISTINCT u.* 
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN cte r ON r.id = ur.role_id

db<>fiddle

Recursive CTE query in EXISTS

2 Answers