Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25381

JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: JSON, Optimizer
    • Labels:
      None

      Description

      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1),(2);
       
      CREATE TABLE t2 (b INT, c TEXT);
      INSERT INTO t2 VALUES (1,'{}'),(2,'[]');
      CREATE VIEW v2 AS SELECT * FROM t2;
       
      SELECT * FROM t1 RIGHT JOIN t2 AS tt LEFT JOIN JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      SELECT * FROM t1 RIGHT JOIN v2 AS tt LEFT JOIN JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
       
      # Cleanup
      DROP VIEW v2;
      DROP TABLE t1, t2;
      

      In the test case above the only difference between two SELECTs is that the first one uses the table t2 AS tt, while the second one uses the view v2 AS tt.
      The first query succeeds, the second one fails with ER_WRONG_OUTER_JOIN:

      bb-10.6-mdev17399-hf a962039213

      MariaDB [test]> SELECT * FROM t1 RIGHT JOIN t2 AS tt LEFT JOIN JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      +------+------+------+------+
      | a    | b    | c    | o    |
      +------+------+------+------+
      |    1 |    1 | {}   |    1 |
      |    2 |    2 | []   | NULL |
      +------+------+------+------+
      2 rows in set (0.002 sec)
       
      MariaDB [test]> SELECT * FROM t1 RIGHT JOIN v2 AS tt LEFT JOIN JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      ERROR 1120 (42000): Cross dependency found in OUTER JOIN; examine your ON conditions
      

      MySQL isn't better at this, there the first query also passes and the second one fails, only with a different error:

      MySQL 8.0.23

      MySQL [test]> SELECT * FROM t1 RIGHT JOIN t2 AS tt LEFT JOIN JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      +------+------+------+------+
      | a    | b    | c    | o    |
      +------+------+------+------+
      |    1 |    1 | {}   |    1 |
      |    2 |    2 | []   | NULL |
      +------+------+------+------+
      2 rows in set (0.001 sec)
       
      MySQL [test]> SELECT * FROM t1 RIGHT JOIN v2 AS tt LEFT JOIN JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      ERROR 1210 (HY000): Incorrect arguments to JSON_TABLE
      

      Further, in MySQL this one works (a function instead of a plain column reference):

      MySQL 8.0.23

      MySQL [test]> SELECT * FROM t1 RIGHT JOIN v2 AS tt LEFT JOIN JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      +------+------+------+------+
      | a    | b    | c    | o    |
      +------+------+------+------+
      |    1 |    1 | {}   |    1 |
      |    2 |    2 | []   | NULL |
      +------+------+------+------+
      2 rows in set (0.002 sec)
      

      While in MariaDB it fails:

      bb-10.6-mdev17399-hf a962039213

      MariaDB [test]> SELECT * FROM t1 RIGHT JOIN v2 AS tt LEFT JOIN JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt ON tt.b = jt.o ON t1.a = tt.b;
      ERROR 1120 (42000): Cross dependency found in OUTER JOIN; examine your ON conditions
      

      So, in MySQL it's consistent with the behavior of the first query, and in MariaDB – with the second.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: