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
-
Activity
The fact that CREATE VIEW is accepted is not surprising as the check for ER_WRONG_OUTER_JOIN is located in make_join_statistics(), which is called from JOIN::optimize, which is not called when one is creating a VIEW...
This got me wondering, how about other queries (ones w/o JSON_TABLE) - they ought to have the same property, ER_WRONG_OUTER_JOIN is produced when the query is executed, and not produced if one is just creating a VIEW...
Well, grepping for error name (ER_WRONG_OUTER_JOIN) or text ("Cross dependency found in OUTER JOIN; examine your ON conditions") in the testsuite finds nothing, neither in 10.6 nor in 5.5 tree.
The warning seems to be dead code.
Analysis:
The code is trying to have this limitation:
a JSON_TABLE is on the outer side of an outer join but has a dependency from a table on the inner side of that outer join.What the first example shows is a special case:
SELECT *FROMt RIGHT JOIN JSON_TABLE(t.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;Here, t is an empty MyISAM table which has no records, and is a single
inner table of an outer join.
In this case, the optimizer changes the outer join into inner with table t having NULL-complemented row.
Then, the query is not rejected.