maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello,

There was this question raised: 

Why can't JSON_TABLE code just use the current Name_resolution_context objects?

== Context ==
Name_resolution_context object has these two members that specify a subset of
tables to use:

  TABLE_LIST *first_name_resolution_table;
  TABLE_LIST *last_name_resolution_table;

JSON_TABLE code also introduces this one:

  table_map ignored_tables;

With the meaning that name resolution process will not search for the field of 
interest in the tables listed in the bitmap.

== Rationale ==

For a JSON_TABLE invocation  

select ... from json_table(ARG, ...) as JT ....

the ARG may refer to any table that precedes JT in the from clause. Let's call
the set of the tables ALLOWED_DEPS.

Can one take a JSON_TABLE invocation and pick
  
  first_name_resolution_table=FIRST,
  last_name_resolution_table=LAST 

such that

ALLOWED_DEPS = { FIRST,
                 SECOND= FIRST->next_name_resolution_table,
                 THIRD= SECOND->next_name_resolution_table,
                 ...
                 LAST 
                }
?

I claim that there are cases where this is not possible.

== An example ==

select * from
  t1,
    (t2 natural join
     (json_table('{}', '$' COLUMNS (d for ordinality)) as JT
      natural join
      t3
     )
    );

Here, JT's ALLOWED_DEPS={t1, t2}.

Looking at the data structures (each Table name denotes a TABLE_LIST object
referring to the table)

  select_lex.name_resolution_context.first_name_resolution_table= t1
    
  t1->next_name_resolution_table= $join_nest1
  $join_nest1->next_name_resolution_table= NULL

  $join_nest1->nested_join.join_list= { $join_nest2, t2 }

  t2->next_name_resolution_table= $join_nest2
  $join_nest2->next_name_resolution_table= NULL

  $join_nest2->nested_join.join_list= {t3, JT}
  JT->next_name_resolution_table= t3
  t3->next_name_resolution_table= NULL

Apparently there is no single chain that includes t1 and t2.

== Possible objections ==
"But nested (outer) joins do it"

Nested outer joins never need to pick a subset that includes only
- one of the tables in a NATURAL JOIN
- one of the tables outside that NATURAL JOIN.

so there's no comparison.

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