title: Why is JSON_TABLE() joining inconsistent?
body: <p>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.</p>
  
  <blockquote>
    <p>Docs for JSON_TABLE() : <a href="https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html</a></p>
  </blockquote>
  
  <p>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()</p>
  
  <blockquote>
    <p>Docs for JSON search functions: <a href="https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html</a></p>
  </blockquote>
  
  <p><strong>Schema (MySQL v8.0)</strong></p>
  
  <pre><code>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');
  </code></pre>
  
  <hr>
  
  <p><strong>Query #1</strong> - Why is Bob not returned?</p>
  
  <pre><code>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   |
  </code></pre>
  
  <hr>
  
  <p><strong>Query #2</strong></p>
  
  <pre><code>SELECT * FROM ROLES;
  
  ## Results ##
  | ID  | NAME       |
  | --- | ---------- |
  | 1   | Originator |
  | 2   | Approver   |
  | 3   | Reviewer   |
  </code></pre>
  
  <hr>
  
  <p><strong>Query #3</strong> - Why are there no results?</p>
  
  <pre><code>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.
  </code></pre>
  
  <hr>
  
  <p><strong>Query #4</strong> - Not JOINing with an IN() returns correct results.</p>
  
  <pre><code>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   |
  </code></pre>
  
  <hr>
  
  <p><strong>Query #5</strong> - Where is Bob?</p>
  
  <pre><code>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   |     |            |
  </code></pre>
  
  <hr>
  
  <p><strong>Query #6</strong> - Why does a LEFT JOIN with IN() return the expected results when query #3 returned nothing?</p>
  
  <pre><code>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]      |     |            |
  </code></pre>
  
  <hr>
  
  <p><a href="https://www.db-fiddle.com/f/dAqQQFsi7wjjo3etqmfcSg/2" rel="nofollow noreferrer">View on DB Fiddle</a></p>