maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

Well i just did that end_lateral_table trick for now.

HF


On Tue, Feb 9, 2021 at 3:05 PM Sergey Petrunia <sergey@xxxxxxxxxxx> 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:
>
>
> 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
>
>
>