Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
    • N/A
    • JSON
    • None
    • 10.2.12, 10.2.14

    Description

      JSON data is not getting recognized, so statements like the following result in broken JSON-strings. In MySQL it doesn't happen because JSON is recognized as a datatype. If adding JSON as a datatype is not an option to consider, then maybe add an optional parameter to "JSON_OBJECT()" to check the passed attributes to not escape them again. When advertising JSON-support in MariaDB 10.2 results in generating those strings manually via "CONCAT()" is not what people expect and this is the only reason I can't use MariaDB in nearly all of my projects.

      Note: If testing in MySQL 5.7, cast the concatenated JSON-value of "Permission" as JSON manually, so it wont be escaped again.

      SELECT
                      JSON_OBJECT(
                          'Staff', JSON_OBJECT(
                              'Staff_ID', Staff_ID,
                              'StaffNumber', Staff_Number,
                              'StaffName', Staff_Name
                          ),
                          'Company', JSON_OBJECT(
                              'Company_ID', Company_ID,
                              'Description', Company_Name
                          ),
                          'Permissions', CONCAT(
                              '[',
                              GROUP_CONCAT(
                                  JSON_OBJECT(Permission_ID, Permission_Flag)
                              ),
                              ']'
                          )
                      ) AS StaffObj
      FROM            Staff
      NATURAL JOIN    Company
      NATURAL JOIN    Staff_Permissions
      GROUP BY        Staff_ID;
      

      Attachments

        Issue Links

          Activity

            yablacky L. Schwarz added a comment -

            I found a workaround:

            delimiter ;;
            create procedure json_bug_workaround() begin
              declare a, b json;
              set a = json_object("k", "foo"), b = json_object('kk', 'vv');
              set a = json_replace(a, "$.k", json_extract(b,'$'));
              select a, json_type(a), b, json_type(b);
            end;;
            delimiter ;
            call json_bug_workaround();
            

            Results in what I expect:

            +---------------------+--------------+--------------+--------------+
            | a                   | json_type(a) | b            | json_type(b) |
            +---------------------+--------------+--------------+--------------+
            | {"k": {"kk": "vv"}} | OBJECT       | {"kk": "vv"} | OBJECT       |
            +---------------------+--------------+--------------+--------------+
            

            This means that json_extract( value, '$' ) is kind of casting the value to the json thing
            it is, provided, it is valid json - but in case value is declared as json this is always the case.

            If value is not valid json, then the result of this kind of cast is NULL for mariadb and
            mysql will throw an error in this case - so be careful if you need to write your sql for both!

            yablacky L. Schwarz added a comment - I found a workaround: delimiter ;; create procedure json_bug_workaround() begin declare a, b json; set a = json_object( "k" , "foo" ), b = json_object( 'kk' , 'vv' ); set a = json_replace(a, "$.k" , json_extract(b, '$' )); select a, json_type(a), b, json_type(b); end ;; delimiter ; call json_bug_workaround(); Results in what I expect: +---------------------+--------------+--------------+--------------+ | a | json_type(a) | b | json_type(b) | +---------------------+--------------+--------------+--------------+ | {"k": {"kk": "vv"}} | OBJECT | {"kk": "vv"} | OBJECT | +---------------------+--------------+--------------+--------------+ This means that json_extract( value, '$' ) is kind of casting the value to the json thing it is, provided, it is valid json - but in case value is declared as json this is always the case. If value is not valid json, then the result of this kind of cast is NULL for mariadb and mysql will throw an error in this case - so be careful if you need to write your sql for both!
            yablacky L. Schwarz added a comment -

            mariadb 10.5.10

            CREATE FUNCTION t18(n int) RETURNS longtext return json_array(n, "workaround:", 0+n);
            SELECT t18(7);
            +-------------------------+
            | t18(7)                  |
            +-------------------------+
            | ["7", "workaround:", 7] |
            +-------------------------+
            
            

            The json string "7" at index 0 is unexpected. It should be the integer 7 because the function parameter n is declared as integer.
            As a workaround a numeric expression can be used just before passing n as json_array() parameter.

            yablacky L. Schwarz added a comment - mariadb 10.5.10 CREATE FUNCTION t18(n int ) RETURNS longtext return json_array(n, "workaround:" , 0+n); SELECT t18(7); + -------------------------+ | t18(7) | + -------------------------+ | [ "7" , "workaround:" , 7] | + -------------------------+ The json string "7" at index 0 is unexpected. It should be the integer 7 because the function parameter n is declared as integer. As a workaround a numeric expression can be used just before passing n as json_array() parameter.
            noyearzero Ryan Leadenham added a comment - - edited

            I'm guessing this bug affects JSON_TABLE() too. You can't put something in with double quotes and get it out the same as you put it in.

            Suppose you're working with the string

            The book's title is "Lord of the Rings"

            SELECT 
            *
            FROM 
            JSON_TABLE(
            	'["The book\'s title is \"Lord of the Rings\""]',
            	'$[*]' 
            	COLUMNS (
            		`text` TEXT PATH '$[0]'
            	)
            ) AS t
            

            Results in a syntax error

            SELECT 
            *
            FROM 
            JSON_TABLE(
            	'["The book\'s title is \\\"Lord of the Rings\\\""]',
            	'$[*]' 
            	COLUMNS (
            		`text` TEXT PATH '$[0]'
            	)
            ) AS t
            

            Returns
            The book's title is \"Lord of the Rings\"

            SELECT 
            *
            FROM 
            JSON_TABLE(
            	'[\"The book\'s title is \\\"Lord of the Rings\\\"\"]',
            	'$[*]' 
            	COLUMNS (
            		`text` TEXT PATH '$[0]'
            	)
            ) AS t
            

            Also returns
            The book's title is \"Lord of the Rings\"

            I believe that last one is most syntactically correct

            noyearzero Ryan Leadenham added a comment - - edited I'm guessing this bug affects JSON_TABLE() too. You can't put something in with double quotes and get it out the same as you put it in. Suppose you're working with the string The book's title is "Lord of the Rings" SELECT * FROM JSON_TABLE( '["The book\'s title is \"Lord of the Rings\""]' , '$[*]' COLUMNS ( `text` TEXT PATH '$[0]' ) ) AS t Results in a syntax error SELECT * FROM JSON_TABLE( '["The book\'s title is \\\"Lord of the Rings\\\""]' , '$[*]' COLUMNS ( `text` TEXT PATH '$[0]' ) ) AS t Returns The book's title is \"Lord of the Rings\" SELECT * FROM JSON_TABLE( '[\"The book\'s title is \\\"Lord of the Rings\\\"\"]' , '$[*]' COLUMNS ( `text` TEXT PATH '$[0]' ) ) AS t Also returns The book's title is \"Lord of the Rings\" I believe that last one is most syntactically correct

            And even worse, this happens

            SELECT 
            *
            FROM 
            JSON_TABLE(
            	'[{\"text\":\"The book\'s title is \\\"Lord of the Rings\\\"\"}]',
            	'$[*]' 
            	COLUMNS (
            		`text` VARCHAR(39) PATH '$.text'
            	)
            ) AS t
            

            Returns the `text` field as:
            The book's title is \"Lord of the Rings

            (Yes, the trailing quote is missing)

            noyearzero Ryan Leadenham added a comment - And even worse, this happens SELECT * FROM JSON_TABLE( '[{\"text\":\"The book\'s title is \\\"Lord of the Rings\\\"\"}]' , '$[*]' COLUMNS ( `text` VARCHAR (39) PATH '$.text' ) ) AS t Returns the `text` field as: The book's title is \"Lord of the Rings (Yes, the trailing quote is missing)

            The original bug report, in the issue Description, is not a bug. CONCAT() or GROUP_CONCAT() return a string, and when a string is put in a JSON it has to be properly quoted. One should use JSON_ARRAYAGG() or JSON_OBJECTAGG() for JSON.

            Some of the cases in comments might be genuine bugs, they should be reported separately. For example, see MDEV-27018 as one of these separately reported cases.

            serg Sergei Golubchik added a comment - The original bug report, in the issue Description , is not a bug. CONCAT() or GROUP_CONCAT() return a string, and when a string is put in a JSON it has to be properly quoted. One should use JSON_ARRAYAGG() or JSON_OBJECTAGG() for JSON. Some of the cases in comments might be genuine bugs, they should be reported separately. For example, see MDEV-27018 as one of these separately reported cases.

            People

              serg Sergei Golubchik
              Andrej Thomsen Andrej Thomsen
              Votes:
              11 Vote for this issue
              Watchers:
              19 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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