maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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