|
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.
|