[MDEV-19160] JSON_DETAILED output unnecessarily verbose (Part Time project) - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL)
Fix Version/s: 10.4.28, 10.5.19, 10.6.12
Component/s: JSON
Labels:

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.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mdev19160-data.sql
3 kB
2019-04-03 13:43

Issue Links

is part of

MDEV-28906 MySQL 8.0 desired compatibility

Open

relates to

MDEV-6111 optimizer trace

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2019-04-03 13:06

Sergei Petrunia made changes - 2019-04-03 13:42

Field	Original Value	New Value
Description	JSON_DETAILED function (https://mariadb.com/kb/en/library/json_detailed/ ) is described as {noformat} Represents JSON in the most understandable way emphasizing nested structures. {noformat} 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 {noformat} select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; {noformat} Our experience is that {{JSON_DETAILED}} has some room for improvement when it comes to the quality of automatic JSON formatting. Example:	JSON_DETAILED function (https://mariadb.com/kb/en/library/json_detailed/ ) is described as {noformat} Represents JSON in the most understandable way emphasizing nested structures. {noformat} 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 {noformat} select JSON_DETAILED(JSON_EXTRACT(trace, '$.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; {noformat} Our experience is that {{JSON_DETAILED}} has some room for improvement when it comes to the quality of automatic JSON formatting. Example: {code} 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": [ ] } ] {code} Things to note: * empty lines at the start * {{"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?

Sergei Petrunia made changes - 2019-04-03 13:43

Description

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

{noformat}
Represents JSON in the most understandable way emphasizing nested structures.
{noformat}

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
{noformat}
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{noformat}

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

Example:
{code}
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":
        [
        ]
    }
]
{code}

Things to note:
* empty lines at the start
* {{"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?

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

{noformat}
Represents JSON in the most understandable way emphasizing nested structures.
{noformat}

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
{noformat}
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{noformat}

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

Example:
{code}
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":
        [
        ]
    }
]
{code}

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?

Sergei Petrunia made changes - 2019-04-03 13:43

Attachment

mdev19160-data.sql [ 47904 ]

Sergei Petrunia made changes - 2019-04-03 13:44

Fix Version/s

10.4 [ 22408 ]

Sergei Petrunia made changes - 2019-04-03 13:44

Assignee

Varun Gupta [ varun ]

Sergei Petrunia made changes - 2019-04-03 13:44

Link

This issue relates to ~~MDEV-6111~~ [ ~~MDEV-6111~~ ]

Sergei Petrunia added a comment - 2019-04-03 13:45

cc: holyfoot

Sergei Petrunia added a comment - 2019-04-03 13:45 cc: holyfoot

Sergei Petrunia made changes - 2019-04-03 19:37

Summary

DRAFT JSON_DETAILED output unnecessarily verbose

JSON_DETAILED output unnecessarily verbose

Sergei Petrunia made changes - 2021-02-19 09:55

Description

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

{noformat}
Represents JSON in the most understandable way emphasizing nested structures.
{noformat}

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
{noformat}
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{noformat}

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

Example:
{code}
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":
        [
        ]
    }
]
{code}

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?

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

{noformat}
Represents JSON in the most understandable way emphasizing nested structures.
{noformat}

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
{noformat}
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{noformat}

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

Example:
{code}
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":
        [
        ]
    }
]
{code}

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.)

Perhaps, we should switch JSON_DETAILED to use it?

Sergei Petrunia made changes - 2021-02-19 10:15

Description

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

{noformat}
Represents JSON in the most understandable way emphasizing nested structures.
{noformat}

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
{noformat}
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{noformat}

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

Example:
{code}
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":
        [
        ]
    }
]
{code}

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.)

Perhaps, we should switch JSON_DETAILED to use it?

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

{noformat}
Represents JSON in the most understandable way emphasizing nested structures.
{noformat}

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
{noformat}
select
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{noformat}

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

Example:
{code}
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":
        [
        ]
    }
]
{code}

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.

Robert Bindar made changes - 2021-02-19 13:43

Labels

gsoc21

Nimit Savant added a comment - 2021-03-14 01:05

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!

Nimit Savant added a comment - 2021-03-14 01:05 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!

Julien Fritsch made changes - 2021-03-19 14:16

Assignee

Varun Gupta [ varun ]

Sergei Petrunia [ psergey ]

Nimit Savant added a comment - 2021-03-29 17:41

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

Nimit Savant added a comment - 2021-03-29 17:41 @Sergei Petrunia can you help me with this issue, and give me some more information about this issue. Thank You!

Sergei Golubchik made changes - 2021-03-29 17:48

Labels

gsoc21

beginner-friendly gsoc21

Robert Bindar added a comment - 2021-03-29 18:14

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.

Robert Bindar added a comment - 2021-03-29 18:14 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.

Nimit Savant added a comment - 2021-04-02 21:25 - edited

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

Nimit Savant added a comment - 2021-04-02 21:25 - edited 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

Robert Bindar added a comment - 2021-04-05 11:07

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

Robert Bindar added a comment - 2021-04-05 11:07 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

Sergei Petrunia made changes - 2021-10-13 11:11

Labels

beginner-friendly gsoc21

beginner-friendly gsoc21 optimizer

Sergei Petrunia made changes - 2021-10-29 14:27

Assignee

Sergei Petrunia [ psergey ]

Sergei Krivonos [ JIRAUSER49805 ]

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 94219 ]

MariaDB v4 [ 141237 ]

Sergei Petrunia made changes - 2021-12-22 17:38

Assignee

Sergei Krivonos [ JIRAUSER49805 ]

Kai added a comment - 2022-01-29 11:48

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

Kai added a comment - 2022-01-29 11:48 Hi, is there anyone working on this? If not, I would like to improve it.

Sergei Golubchik added a comment - 2022-01-31 18:42

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

Sergei Golubchik added a comment - 2022-01-31 18:42 No, nobody is, please, feel free to do it

Vicențiu Ciorbaru made changes - 2022-02-21 08:43

Labels

beginner-friendly gsoc21 optimizer

beginner-friendly gsoc21 gsoc22 optimizer

Vicențiu Ciorbaru made changes - 2022-02-21 08:43

Summary

JSON_DETAILED output unnecessarily verbose

JSON_DETAILED output unnecessarily verbose (Part Time project)

ziyitan made changes - 2022-04-05 04:45

Comment

[ Hello! @Sergei Golubchik I am Tan Ziyi from Huazhong University of Science and Technology (HUST, China),
a 1st computer graduate student
I want to work on this issue
Please guide me how to start working, looking forward to your reply!
(I am trying to build this project) ]

Nayuta Yanagisawa (Inactive) made changes - 2022-06-25 14:26

Assignee

Rucha Deodhar [ rucha174 ]

Nayuta Yanagisawa (Inactive) added a comment - 2022-06-25 14:26

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

Nayuta Yanagisawa (Inactive) added a comment - 2022-06-25 14:26 https://github.com/MariaDB/server/pull/2168

Rucha Deodhar made changes - 2023-01-06 19:04

Fix Version/s		10.4.28 [ 28509 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Rucha Deodhar added a comment - 2023-01-06 19:05

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

Rucha Deodhar added a comment - 2023-01-06 19:05 Patch (submitted as contribution): https://github.com/MariaDB/server/commit/111a752b968561b34a88f33052519cb989a8a90f

Daniel Black made changes - 2023-03-01 01:02

Link

This issue is part of MDEV-28906 [ MDEV-28906 ]

Ralf Gebhardt made changes - 2023-03-07 18:49

Fix Version/s		10.6.12 [ 28513 ]
Fix Version/s		10.5.19 [ 28511 ]

People

Assignee:: Rucha Deodhar

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Created:: 2019-04-03 13:06

Updated:: 2023-12-05 22:05

Resolved:: 2023-01-06 19:04

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration