maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

A patch for this issue:
http://lists.askmonty.org/pipermail/commits/2021-March/014500.html

I'll need to check one more thing.

On Sun, Mar 14, 2021 at 08:42:40PM +0300, Sergey Petrunia wrote:
> On Sat, Mar 13, 2021 at 04:01:56PM +0400, Alexey Botchkov wrote:
> > I've rewritten it using the json->select_lex->leaf_tables as you suggested.
> > So the JSON_TABLE is calculated properly and all the dependencies seem to
> > be marked.
> > Still your testcase query fails the same way, so needs some more
> > investigation. To me it
> > looks more like optimizer issue than the JSON_TABLE-s.
> 
> Can you construct an example that would demonstrate the issue without use of
> JSON_TABLE? (No). I've looked - it looks like the optimizer is unable to handle
> certain kinds of dependencies... which didn't exist before JSON_TABLE was
> added.
> 
> == Short ==
> 
> The optimizer fails to construct a complete join order:
> 1. It builds a "dead-end" join prefix, which cannot be expanded to a complete
> join order. 
> 2. Join prefix pruning removes other join prefixes from consideration.
> 
> == Long ==
> Table dependencies:
> 
>   t20 -> {}
>   t21 -> t20
>   t31 -> {}
>   t32 -> t31
>   jt  -> t20 t21 t31
> 
> jt depends on:
>   t31, because it's a left join
>   t21, because it refers to it in its argument 
>   t20 due to transitive clouse of the dependency relation. t21 depends on t20.
> 
> The optimizer constructs a prefix:
> 
>   t31, t32
> 
> This is a "dead end", it cannot be extended to a full join order:
> 
> we cannot add t20 or t21 because we've entered the join nest:
>   
>   t1 left join (t32 ...)
> 
> and must add all tables in the nest before we add any table that's not part of
> the nest.
> The table in the nest is "JSON_TABLE(...) as jt", but we cannot add it, because
> it needs t21 to be added first.
> 
> Why doesn't the optimizer construct a join order starting from e.g. 
> 
>   t20 t21 t31 ...
> 
> The issue is in pruning. The join orders starting from t20 are pruned away
> because they look less promising than the join orders starting from t31.
> 
> (Does MySQL-8 have this issue?  No, they don't. They have a
> found_plan_with_allowed_sj member, and this logic: do not do pruning if there
> was no complete join order constructed, yet.)
> 
> BR
>  Sergei
> -- 
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
> 
> 

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