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

IF and COALESCE lose "json" property

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
    • 10.5.14, 10.6.6, 10.7.2, 10.8.1
    • JSON
    • None

    Description

      MariaDB [test]> select json_object('a', json_object('b', 'c'));
      		 
      +-----------------------------------------+
      		 
      | json_object('a', json_object('b', 'c')) |
      		 
      +-----------------------------------------+
      		 
      | {"a": {"b": "c"}}                       |
      		 
      +-----------------------------------------+
      		 
      1 row in set (0.000 sec)
      		 
       
      		 
      MariaDB [test]> select json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f')));
      		 
      +-----------------------------------------------------------------------+
      		 
      | json_object('a', if(1, json_object('b', 'c'), json_object('e', 'f'))) |
      		 
      +-----------------------------------------------------------------------+
      		 
      | {"a": "{\"b\": \"c\"}"}                                               |
      		 
      +-----------------------------------------------------------------------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [test]> select json_object('a', coalesce(json_object('b', 'c')));
      		 
      +---------------------------------------------------+
      		 
      | json_object('a', coalesce(json_object('b', 'c'))) |
      		 
      +---------------------------------------------------+
      		 
      | {"a": "{\"b\": \"c\"}"}                           |
      		 
      +---------------------------------------------------+
      		 
      1 row in set (0.000 sec)

      other functions (CASE, NULLIF, IFNULL) are likely affected too.

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27350 Complex CHECK constraint loses JSON property

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27358 Table level CHECK(JSON_VALID()) constraint loses JSON property

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27359 Crossed JSON_VALID constraint imposes JSON property to a wrong column

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27361 Hybrid functions with JSON arguments do not send format metadata

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27370 UNION looses JSON property

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13701 Nesting JSON

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27360 Column level CHECK constraint is not SQL Standard compliant

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27533 The result of `CONVERT(json USING ...)` is erroneously treated as JSON

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27864 Alter table modify column for same data type copying table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            salle Alexander Keremidarski added a comment - - edited

            It seems all string functions are affected including no-op functions like CONCAT(x) and LOWER(x)

            ariaDB [test]> SELECT JSON_OBJECT('a', CONCAT(JSON_OBJECT('b', 'c')));
            		 
            +-------------------------------------------------+
            		 
            | JSON_OBJECT('a', CONCAT(JSON_OBJECT('b', 'c'))) |
            		 
            +-------------------------------------------------+
            		 
            | {"a": "{\"b\": \"c\"}"}                         |
            		 
            +-------------------------------------------------+
            		 
            1 row in set (0.000 sec)
            		 
             
            		 
            MariaDB [test]> SELECT JSON_OBJECT('a', LOWER(JSON_OBJECT('b', 'c')));
            		 
            +------------------------------------------------+
            		 
            | JSON_OBJECT('a', LOWER(JSON_OBJECT('b', 'c'))) |
            		 
            +------------------------------------------------+
            		 
            | {"a": "{\"b\": \"c\"}"}                        |
            		 
            +------------------------------------------------+
            		 
            1 row in set (0.000 sec)

            salle Alexander Keremidarski added a comment - - edited It seems all string functions are affected including no-op functions like CONCAT(x) and LOWER(x) ariaDB [test]> SELECT JSON_OBJECT( 'a' , CONCAT(JSON_OBJECT( 'b' , 'c' ))); + -------------------------------------------------+ | JSON_OBJECT( 'a' , CONCAT(JSON_OBJECT( 'b' , 'c' ))) | + -------------------------------------------------+ | { "a" : "{\"b\": \"c\"}" } | + -------------------------------------------------+ 1 row in set (0.000 sec)   MariaDB [test]> SELECT JSON_OBJECT( 'a' , LOWER (JSON_OBJECT( 'b' , 'c' ))); + ------------------------------------------------+ | JSON_OBJECT( 'a' , LOWER (JSON_OBJECT( 'b' , 'c' ))) | + ------------------------------------------------+ | { "a" : "{\"b\": \"c\"}" } | + ------------------------------------------------+ 1 row in set (0.000 sec)
            serg Sergei Golubchik added a comment -

            On the contrary, these two are not bugs, as explained in this comment.

            CONCAT(x) and LOWER(x) are not "no-op functions". Try

            CREATE TABLE t AS SELECT 1, CONCAT(1), LOWER(1);
            		 
            SHOW CREATE TABLE t;

            and you will see that CONCAT and LOWER change the argument, they convert it to a string. So, when you use CONCAT(json), this JSON value becomes a plain string, not a structured data type. It loses its "json-ness". And a plain string is correctly escaped when used as a JSON value.

            serg Sergei Golubchik added a comment - On the contrary, these two are not bugs, as explained in this comment . CONCAT(x) and LOWER(x) are not "no-op functions". Try CREATE TABLE t AS SELECT 1, CONCAT(1), LOWER (1); SHOW CREATE TABLE t; and you will see that CONCAT and LOWER change the argument, they convert it to a string. So, when you use CONCAT(json), this JSON value becomes a plain string, not a structured data type. It loses its "json-ness". And a plain string is correctly escaped when used as a JSON value.
            bar Alexander Barkov added a comment -

            julien.fritsch, yes this is a bug. I've marked it as Confirmed.

            bar Alexander Barkov added a comment - julien.fritsch , yes this is a bug. I've marked it as Confirmed.
            bar Alexander Barkov added a comment - - edited

            More bad behavior examples

            Single row subselect

            SELECT json_objectagg(b, c) FROM (SELECT 'b','c') d;


            +----------------------+
            		 
            | json_objectagg(b, c) |
            		 
            +----------------------+
            		 
            | {"b":"c"}            |
            		 
            +----------------------+

            Looks good so far.

            Now I pass the same statement as a subselect to abother json_object:

            SELECT json_object('a', (SELECT json_objectagg(b, c) FROM (SELECT 'b','c') d)) AS j FROM DUAL;


            +------------------------+
            		 
            | j                      |
            		 
            +------------------------+
            		 
            | {"a": "{\"b\":\"c\"}"} |
            		 
            +------------------------+

            The value was erroneously escaped.

            If I run similar queries in Oracle, the value does not get escaped:

            SELECT json_objectagg(b, c) FROM (SELECT 'b' AS b,'c' AS c FROM DUAL) d;
            		 
            SELECT json_object('a' VALUE (SELECT json_objectagg(b, c) FROM (SELECT 'b' AS b,'c' AS c FROM DUAL) d)) FROM DUAL;


            JSON_OBJECTAGG(B,C)
            		 
            {"b" : "c"}


            JSON_OBJECT('A'VALUE(SELECTJSON_OBJECTAGG(B,C)FROM(SELECT'B'ASB,'C'ASCFROMDUAL)D))
            		 
            {"a" : {"b" : "c"}}

            UNION

            It's now reported as a separate issue MDEV-27370

            bar Alexander Barkov added a comment - - edited More bad behavior examples Single row subselect SELECT json_objectagg(b, c) FROM ( SELECT 'b' , 'c' ) d; +----------------------+ | json_objectagg(b, c) | +----------------------+ | {"b":"c"} | +----------------------+ Looks good so far. Now I pass the same statement as a subselect to abother json_object: SELECT json_object( 'a' , ( SELECT json_objectagg(b, c) FROM ( SELECT 'b' , 'c' ) d)) AS j FROM DUAL; +------------------------+ | j | +------------------------+ | {"a": "{\"b\":\"c\"}"} | +------------------------+ The value was erroneously escaped. If I run similar queries in Oracle, the value does not get escaped: SELECT json_objectagg(b, c) FROM ( SELECT 'b' AS b, 'c' AS c FROM DUAL) d; SELECT json_object( 'a' VALUE ( SELECT json_objectagg(b, c) FROM ( SELECT 'b' AS b, 'c' AS c FROM DUAL) d)) FROM DUAL; JSON_OBJECTAGG(B,C) {"b" : "c"} JSON_OBJECT('A'VALUE(SELECTJSON_OBJECTAGG(B,C)FROM(SELECT'B'ASB,'C'ASCFROMDUAL)D)) {"a" : {"b" : "c"}} UNION It's now reported as a separate issue MDEV-27370
            bar Alexander Barkov added a comment -

            serg, please review a patch:

            https://github.com/MariaDB/server/commit/734aee3cd3c59bb9a9050f32db406803f8237320

            or can be found in this branch:

            https://github.com/mariadb/server/tree/bb-10.5-bar-MDEV-27018

            Thanks.

            bar Alexander Barkov added a comment - serg , please review a patch: https://github.com/MariaDB/server/commit/734aee3cd3c59bb9a9050f32db406803f8237320 or can be found in this branch: https://github.com/mariadb/server/tree/bb-10.5-bar-MDEV-27018 Thanks.
            bar Alexander Barkov added a comment -

            serg, I replied to your review comments by email. Please have a look.

            bar Alexander Barkov added a comment - serg , I replied to your review comments by email. Please have a look.
            serg Sergei Golubchik added a comment -

            0478f474020466 is ok to push

            serg Sergei Golubchik added a comment - 0478f474020466 is ok to push

            People

              bar Alexander Barkov
              serg Sergei Golubchik
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.