Why is JSON_TABLE() joining inconsistent?

In MySQL 8, we can now use a JSON typed column and also use built-in functions like JSON_TABLE() but as I'm using in different scenarios sometimes I see results I did not expect.

Docs for JSON_TABLE() : https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

Maybe JSON_TABLE is not the way to accomplish joining with a piece of JSON. There are some search functions MySQL provides, but nothing jumped out at me as an alternative to JSON_TABLE()

Docs for JSON search functions: https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Schema (MySQL v8.0)

CREATE TABLE USER (
    NAME varchar(128) NOT NULL,
    METADATA JSON NULL
);

INSERT INTO USER VALUES
('John', '[1,3]'),
('Jane', '[2]'),
('Bob', null),
('Sally', '[9]');


CREATE TABLE ROLES (
  ID int NOT NULL,
  NAME varchar(64) NOT NULL
);

INSERT INTO ROLES VALUES
(1, 'Originator'),
(2, 'Approver'),
(3, 'Reviewer');

Query #1 - Why is Bob not returned?

SELECT * 
FROM USER,
JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK;

## Results ##
| NAME  | METADATA | ID  |
| ----- | -------- | --- |
| John  | [1, 3]   | 1   |
| John  | [1, 3]   | 3   |
| Jane  | [2]      | 2   |
| Sally | [9]      | 9   |

Query #2

SELECT * FROM ROLES;

## Results ##
| ID  | NAME       |
| --- | ---------- |
| 1   | Originator |
| 2   | Approver   |
| 3   | Reviewer   |

Query #3 - Why are there no results?

SELECT * 
FROM USER
JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);

##There are no results to be displayed.

Query #4 - Not JOINing with an IN() returns correct results.

SELECT * 
FROM USER,
JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

## Results ##
| NAME | METADATA | ID  | ID  | NAME       |
| ---- | -------- | --- | --- | ---------- |
| John | [1, 3]   | 1   | 1   | Originator |
| John | [1, 3]   | 3   | 3   | Reviewer   |
| Jane | [2]      | 2   | 2   | Approver   |

Query #5 - Where is Bob?

SELECT * 
FROM USER,
JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
LEFT JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

## Results ##
| NAME  | METADATA | ID  | ID  | NAME       |
| ----- | -------- | --- | --- | ---------- |
| John  | [1, 3]   | 1   | 1   | Originator |
| Jane  | [2]      | 2   | 2   | Approver   |
| John  | [1, 3]   | 3   | 3   | Reviewer   |
| Sally | [9]      | 9   |     |            |

Query #6 - Why does a LEFT JOIN with IN() return the expected results when query #3 returned nothing?

SELECT * 
FROM USER
LEFT JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);

## Results ##
| NAME  | METADATA | ID  | NAME       |
| ----- | -------- | --- | ---------- |
| John  | [1, 3]   | 1   | Originator |
| John  | [1, 3]   | 3   | Reviewer   |
| Jane  | [2]      | 2   | Approver   |
| Bob   |          |     |            |
| Sally | [9]      |     |            |

View on DB Fiddle

Solution

Use ISNULL property to create dummy json

SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK;

DB FIDDLE

#1 No JOINS with JSON_TABLE where is Bob?
SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK;

#2 Verify our ROLE recrods exist
SELECT * FROM ROLES;

#3 Regular JOIN with JSON_TABLE inside the IN(), why are there no results?
SELECT * 
FROM USER
JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM USER, JSON_TABLE(
      USER.METADATA, "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);

#4 Regular JOIN with JSON_TABLE, returns expected results
SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

#5 LEFT JOIN with JSON_TABLE, where is Bob?
SELECT * 
FROM USER,
JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
) AS JSON_ROLE_LINK
LEFT JOIN ROLES ON ROLES.ID = JSON_ROLE_LINK.ID;

#6 LEFT JOIN with JSON_TABLE inside the IN(), returns expected results
SELECT * 
FROM USER
LEFT JOIN ROLES ON ROLES.id IN (
    SELECT ID FROM JSON_TABLE(
      IFNULL(USER.METADATA,'[0]'), "$[*]" 
      COLUMNS(ID int PATH "$" NULL ON ERROR NULL ON EMPTY)
    ) AS JSON_ROLE_LINK     
);