maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Alexey,

At the moment, name resolution of JSON_TABLE's first argument is done "like in
the WHERE clause" - one can refer to any table that is defined in the WHERE
clause.

This allows one to write queries where JSON_TABLE tables have incorrect
dependencies
- circular
- dependency that contradicts the dependency imposed by OUTER JOIN
- dependency that contradicts STRAIGHT_JOIN
(WRONG-DEPS)

the patch checks for these cases but I've reported cases where it fails.

I haven't been find any statement in the SQL Standard about this, but it makes 
a statement about similar constructs, table functions and LATERAL subqueries:

https://jira.mariadb.org/browse/MDEV-17399?focusedCommentId=179327&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-179327

Other databases seem to apply the same limitation to JSON_TABLE's argument.

When they do it, this automatically fixes all the wrong-dependency issues -
a query with (WRONG-DEPS) is rejected at the name resolution phase.

I think we should follow this and modify name resolution process to work in the
same way.

The way MySQL did it is described here: https://dev.mysql.com/worklog/task/?id=8867,
LLD, grep for end_lateral_table. I'm not fond of having implicit parameters
(end_lateral_table) which change they way name resolution works, but I think
it is an acceptable solution (and we already have other such parameters).

(An alternative option would be to have items in JSON_TABLE's first argument to
use their own Name_resolution_context object that would specify the correct
first/last table they should look at. This seems to be much harder to do).

What do you think?

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net