Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
N/A
-
None
Description
This works:
|
bb-10.6-mdev17399-hf 8611fb30b |
MariaDB [test]> CREATE TABLE t (a INT) ENGINE=MyISAM; |
Query OK, 0 rows affected (0.031 sec) |
|
|
MariaDB [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; |
Empty set (0.034 sec) |
But via a view doesn't, it complains about an unknown table (the query within the view definition is the same):
MariaDB [test]> CREATE TABLE t (a INT) ENGINE=MyISAM; |
Query OK, 0 rows affected (0.015 sec) |
|
|
MariaDB [test]> CREATE VIEW v AS SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; |
Query OK, 0 rows affected (0.016 sec) |
|
|
MariaDB [test]> SELECT * FROM v; |
ERROR 1109 (42S02): Unknown table 'test.t' in JSON_TABLE argument |
And with InnoDB istead of MyISAM (or with a non-empty MyISAM table) it also doesn't, now it complains about a cross-dependency:
MariaDB [test]> CREATE TABLE t (a INT) ENGINE=InnoDB; |
Query OK, 0 rows affected (0.035 sec) |
|
|
MariaDB [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; |
ERROR 1120 (42000): Cross dependency found in OUTER JOIN; examine your ON conditions |
All this seems to be inconsistent, or at least not obvious. Either there should be a simple rule for what is allowed or not, or it should be clearly stated that name resolution is non-deterministic as it depends on an execution plan (or whatever else).
In MySQL all three variants fail with the same ER_UNKNOWN_TABLE (and the variant with view fails upon the view creation):
|
MySQL 8.0.23 |
MySQL [test]> CREATE TABLE t (a INT) ENGINE=MyISAM; |
Query OK, 0 rows affected (0.035 sec) |
|
|
MySQL [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; |
ERROR 1109 (42S02): Unknown table 't' in a table function argument |
|
|
MySQL [test]> CREATE VIEW v AS SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; |
ERROR 1109 (42S02): Unknown table 't' in a table function argument |
|
|
MySQL [test]> ALTER TABLE t ENGINE=InnoDB; |
Query OK, 0 rows affected (0.579 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MySQL [test]> SELECT * FROM t RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; |
ERROR 1109 (42S02): Unknown table 't' in a table function argument |
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-
-
-
- Closed
-