[MDEV-19160] JSON_DETAILED output unnecessarily verbose (Part Time project) Created: 2019-04-03  Updated: 2023-03-07  Resolved: 2023-01-06

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.4
Fix Version/s: 10.4.28, 10.5.19, 10.6.12

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: beginner-friendly, gsoc21, gsoc22, optimizer

Attachments: File mdev19160-data.sql    
Issue Links:
PartOf
is part of MDEV-28906 MySQL 8.0 desired compatibility Open
Relates
relates to MDEV-6111 optimizer trace Closed

 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 the JSON pretty-printer that is used by EXPLAIN FORMAT=JSON and optimizer trace. It produces a better result (but it has room for improvement, too.)

Extra: in MySQL, the function is called JSON_PRETTY. We should add ability to use this name as an alias.

 Comments   
Comment by Sergei Petrunia [ 2019-04-03 ]

cc: holyfoot

Comment by Nimit Savant [ 2021-03-14 ]

Hey @varun, I want to contribute to this issue.
Can you provide me a link to, where this function is being implemented in the repo server

Thank You!

Comment by Nimit Savant [ 2021-03-29 ]

@Sergei Petrunia can you help me with this issue, and give me some more information about this issue. Thank You!

Comment by Robert Bindar [ 2021-03-29 ]

Hey Nimit2801! You can start from Create_func_json_detailed class from item_create.cc and Item_func_json_format class from item_jsonfunc.cc
Tracing these will lead you to where the actual pretty-printing happens. Let us know if you have any questions and sorry for the late reply. Great to see you interested in MariaDB for GSoC21!

psergey If there is anything you want to add or if you want Nimit2801 to tackle the proposed improvements in a particular order, feel free to step in.

Comment by Nimit Savant [ 2021-04-02 ]

Hey @robertbindar, thanks for answering the question.
1. Can you please guide me on how to which way would be the best to debug the code in
Visual Studio in Windows.
2. Also this bug was originally generated in 10.4, how would be my approach to fix this bug for all other branches.
Thank You

Comment by Robert Bindar [ 2021-04-05 ]

Hey Nimit2801!
1. Have you seen this https://mariadb.com/kb/en/Building_MariaDB_on_Windows/? I can't help you too much on setting up the windows environment for MariaDB, If you get stuck, please ask on MariaDB Zulip channel, people using windows might be able to help.
2. If the bug is tagged 10.4, this means that the earliest version of MariaDB where the bug could be reproduced is 10.4. Basically you create a patch for 10.4 and once it is merged in 10.4, it will be propagated upwards by someone to 10.5, 10.6

Let me know if you have more questions. Also I encourage you to ask this type of questions in Zulip because there you have more visibility, this means faster and better answers most probably

Comment by Kai [ 2022-01-29 ]

Hi, is there anyone working on this? If not, I would like to improve it.

Comment by Sergei Golubchik [ 2022-01-31 ]

No, nobody is, please, feel free to do it

Comment by Nayuta Yanagisawa (Inactive) [ 2022-06-25 ]

https://github.com/MariaDB/server/pull/2168

Comment by Rucha Deodhar [ 2023-01-06 ]

Patch (submitted as contribution): https://github.com/MariaDB/server/commit/111a752b968561b34a88f33052519cb989a8a90f

Generated at Thu Feb 08 08:49:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.