[Maria-developers] MDEV-17399: Name resolution and handling dependencies
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
Well i just did that end_lateral_table trick for now. HF On Tue, Feb 9, 2021 at 3:05 PM Sergey Petrunia <sergey@mariadb.com> wrote:
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:
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
participants (2)
-
Alexey Botchkov -
Sergey Petrunia