[MDEV-25254] JSON_TABLE: Inconsistent name resolution with right joins Created: 2021-03-25  Updated: 2021-04-21  Resolved: 2021-04-08

Status: Closed
Project: MariaDB Server
Component/s: JSON, Optimizer
Affects Version/s: N/A
Fix Version/s: 10.6.0

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-25202 JSON_TABLE: Early table reference lea... Closed
relates to MDEV-25352 JSON_TABLE: Inconsistent name resolut... Closed
relates to MDEV-17399 Add support for JSON_TABLE Closed
relates to MDEV-25256 JSON_TABLE: Error ER_VIEW_INVALID upo... Closed

 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



 Comments   
Comment by Sergei Petrunia [ 2021-04-01 ]

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 * 
FROM 
  t 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.

Comment by Sergei Petrunia [ 2021-04-01 ]

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

Comment by Sergei Petrunia [ 2021-04-01 ]

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.

Comment by Sergei Petrunia [ 2021-04-08 ]

Closed as part of MDEV-25202

Generated at Thu Feb 08 09:36:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.