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

JSON_DETAILED output unnecessarily verbose

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4
    • Component/s: JSON
    • Labels:
      None

      Description

      JSON_DETAILED function (https://mariadb.com/kb/en/library/json_detailed/ ) is described as

      Represents JSON in the most understandable way emphasizing nested structures.
      

      We now got a use case for it: Optimizer Trace output. Optimizer trace is too large to be copied in full, instead we use expressions like

      select 
      JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) 
      from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
      

      Our experience is that JSON_DETAILED has some room for improvement when it comes to the quality of automatic JSON formatting.

      Example:

      source mdev19160-data.sql
      mysql> select JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')) from t200\G
      *************************** 1. row ***************************
      JSON_DETAILED(JSON_EXTRACT(a, '$**.analyzing_range_alternatives')): [
          
          {
              "range_scan_alternatives": 
              [
                  
                  {
                      "index": "a_b",
                      "ranges": 
                      [
                          "2 <= a <= 2 AND 4 <= b <= 4"
                      ],
                      "rowid_ordered": true,
                      "using_mrr": false,
                      "index_only": true,
                      "rows": 1,
                      "cost": 1.1752,
                      "chosen": true
                  }
              ],
              "analyzing_roworder_intersect": 
              {
                  "cause": "too few roworder scans"
              },
              "analyzing_index_merge_union": 
              [
              ]
          }
      ]
      

      Things to note:

      • empty lines at the start (right before/after the "range_scan_alternatives")
      • "analyzing_index_merge_union":[] occupies 3 lines where one would be sufficient.
      • the same goes for "ranges"

      One can look at JSON pretty-printer that EXPLAIN FORMAT=JSON and optimizer trace use. It produces a better result.
      Perhaps, we should switch JSON_DETAILED to use it?

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: