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