Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30343

Optimizer trace produces JSON too deep for MariaDB JSON functions

    XMLWordPrintable

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

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.