Details
Description
With a fairly ordinary query optimizer trace can produce a trace which breaches the limit of nested structures hardcoded to 32, and thus becomes invalid JSON from the inbuilt functions' perspective.
The example below uses a query with 11 joins and no other conditions. With a bit more complicated queries I think it can be achieved with even less joins.
SET optimizer_trace= 'enabled=on'; |
|
create table t (a int); |
insert into t values (1),(2); |
|
select t1.a from t t1 left join t t2 on t1.a=t2.a left join t t3 on t3.a=t2.a left join t t4 on t4.a=t3.a left join t t5 on t5.a=t4.a left join t t6 on t6.a=t5.a left join t t7 on t7.a=t6.a left join t t8 on t8.a=t7.a left join t t9 on t9.a=t8.a left join t t10 on t10.a=t9.a left join t t11 on t11.a=t10.a left join t t12 on t12.a=t11.a; |
|
select json_valid(trace) from information_schema.optimizer_trace; |
|
drop table t; |
10.5 b8f4b984 |
json_valid(trace)
|
0
|
The actual (well-concealed) problem is
4040 Limit of 32 on JSON nested structures depth is reached in argument 1 to function 'json_compact' at position 23132 |
The deeply-nested part is this:
"steps": [
|
{
|
"considered_execution_plans": [
|
{
|
...
|
"rest_of_plan": [
|
{
|
...
|
"rest_of_plan": [
|
...
|
I'm not sure what should be done about it. It seems clearly wrong that one part of the server legitimately produces a JSON structure which another part of the server cannot handle.
Forbidding optimizer trace to produce it isn't a reasonable solution, as it's not even an extraordinary corner case.
As for increasing the JSON depth limit, one can make an argument that for any given limit there is a long enough query which will hit it. Also, as mentioned on slack, it's unclear what effect increasing the limit may have on the server memory usage.
Maybe it should be a two-fold solution – to increase the limit to cover the realistic traces the optimizer trace can produce (it will require an input from the optimizer team), and then forbid creating traces which exceed the limit, e.g. by adding a JSON_VALID check to the trace column.
Attachments
Issue Links
- relates to
-
MDEV-32854 Make JSON_DEPTH_LIMIT configurable
- In Review