|
Hopefully it is closely related to MDEV-25254 and will be fixed by the same patch, but I'm not quite sure about that.
The query works in MariaDB:
|
bb-10.6-mdev17399-hf 8611fb30b
|
SELECT * FROM
|
JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1
|
RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2
|
ON(1)
|
RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3
|
ON(1)
|
WHERE 0;
|
MariaDB [test]> SELECT * FROM
|
-> JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1
|
-> RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2
|
-> ON(1)
|
-> RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3
|
-> ON(1)
|
-> WHERE 0;
|
Empty set (0.001 sec)
|
But when it is executed via a view, it complains about invalid tables/columns:
MariaDB [test]> CREATE VIEW v AS
|
-> SELECT * FROM
|
-> JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1
|
-> RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2
|
-> ON(1)
|
-> RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3
|
-> ON(1)
|
-> WHERE 0;
|
Query OK, 0 rows affected (0.015 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> SELECT * FROM v;
|
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
In MySQL both the query and CREATE VIEW fail with ER_BAD_FIELD_ERROR:
|
MySQL 8.0.23
|
MySQL [test]> SELECT * FROM
|
-> JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1
|
-> RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2
|
-> ON(1)
|
-> RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3
|
-> ON(1)
|
-> WHERE 0;
|
ERROR 1054 (42S22): Unknown column 'jt1.a' in 'a table function argument'
|
MySQL [test]> CREATE VIEW v AS
|
-> SELECT * FROM
|
-> JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1
|
-> RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2
|
-> ON(1)
|
-> RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3
|
-> ON(1)
|
-> WHERE 0;
|
ERROR 1054 (42S22): Unknown column 'jt1.a' in 'a table function argument'
|
|