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

Inconsistent handling while fetching values in json

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.2(EOL)
    • 10.5, 10.6, 10.11
    • JSON
    • None

    Description

      The JSON_KEY_VALUE() (in 11.2) , JSON_QUERY() and JSON_VALUE() function does not print a warning about syntax error if variable is combined with string.
      JSON_EXTRACT() does. So the behaviour is non consistent.

      11.1.0-dbg>set @json = '{"a":1, "b":2, "c" : 3}';
      Query OK, 0 rows affected (0.000 sec)
       
      11.1.0-dbg>select @json;
      +-------------------------+
      | @json                   |
      +-------------------------+
      | {"a":1, "b":2, "c" : 3} |
      +-------------------------+
      1 row in set (0.000 sec)
       
      11.1.0-dbg>select  JSON_KEY_VALUE('{"key1": @json}', '$.key1');
      +---------------------------------------------+
      | JSON_KEY_VALUE('{"key1": @json}', '$.key1') |
      +---------------------------------------------+
      | NULL                                        |
      +---------------------------------------------+
      1 row in set (0.001 sec)
       
      11.1.0-dbg>
      

      Expected syntax error message

      11.1.0-dbg>select  JSON_KEY_VALUE('{"key1": @json}', '$.key1');
      ERROR 4038 (HY000): Syntax error in JSON text in argument 1 to function 'JSON_KEY_VALUE' at position x
      11.1.0-dbg>
      

      JSON_KEY_VALUE function should also print syntax error if the curly braces are not properly closed

      11.1.0-dbg>SELECT JSON_KEY_VALUE('{"key1":{"a":1, "b":2}', '$.key1');
      +------------------------------------------------------+
      | JSON_KEY_VALUE('{"key1":{"a":1, "b":2}', '$.key1')   |
      +------------------------------------------------------+
      | [{"key": "a", "value": 1}, {"key": "b", "value": 2}] |
      +------------------------------------------------------+
      1 row in set (0.000 sec)
       
      11.1.0-dbg>
      

      in 10.4:

      MariaDB [test]> select json_extract('

      {"key1": @json}

      ', '$.key1');
      -------------------------------------------

      json_extract(' {"key1": @json}

      ', '$.key1')

      -------------------------------------------

      NULL

      -------------------------------------------
      1 row in set, 1 warning (0.001 sec)

      Warning (Code 4038): Syntax error in JSON text in argument 1 to function 'json_extract' at position 10

      Attachments

        Issue Links

          Activity

            should it be an error? for JSON_EXTRACT it's a warning

            MariaDB [test]> select json_extract('{"key1": @json}', '$.key1');
            +-------------------------------------------+
            | json_extract('{"key1": @json}', '$.key1') |
            +-------------------------------------------+
            | NULL                                      |
            +-------------------------------------------+
            1 row in set, 1 warning (0.001 sec)
             
            Warning (Code 4038): Syntax error in JSON text in argument 1 to function 'json_extract' at position 10
            

            for JSON_VALUE it's just NULL

            MariaDB [test]> select json_value('{"key1": @json}', '$.key1');
            +-----------------------------------------+
            | json_value('{"key1": @json}', '$.key1') |
            +-----------------------------------------+
            | NULL                                    |
            +-----------------------------------------+
            1 row in set (0.000 sec)
            

            serg Sergei Golubchik added a comment - should it be an error ? for JSON_EXTRACT it's a warning MariaDB [test]> select json_extract( '{"key1": @json}' , '$.key1' ); + -------------------------------------------+ | json_extract( '{"key1": @json}' , '$.key1' ) | + -------------------------------------------+ | NULL | + -------------------------------------------+ 1 row in set , 1 warning (0.001 sec)   Warning (Code 4038): Syntax error in JSON text in argument 1 to function 'json_extract' at position 10 for JSON_VALUE it's just NULL MariaDB [test]> select json_value( '{"key1": @json}' , '$.key1' ); + -----------------------------------------+ | json_value( '{"key1": @json}' , '$.key1' ) | + -----------------------------------------+ | NULL | + -----------------------------------------+ 1 row in set (0.000 sec)
            rucha174 Rucha Deodhar added a comment - - edited

            It should be a warning, behaviour should be uniform. There was a bug in Json_path_extractor::extract() function. Will fix it in the lowest version

            rucha174 Rucha Deodhar added a comment - - edited It should be a warning, behaviour should be uniform. There was a bug in Json_path_extractor::extract() function. Will fix it in the lowest version
            rucha174 Rucha Deodhar added a comment - - edited Patch: https://github.com/MariaDB/server/tree/bb-10.4-MDEV-31477 https://github.com/MariaDB/server/tree/bb-11.2-MDEV-31477-json_key_value https://github.com/MariaDB/server/tree/bb-10.5-MDEV-31477
            holyfoot Alexey Botchkov added a comment - - edited

            Patch looks mostly fine.
            One modification recommended in the comment.
            here:
            https://github.com/MariaDB/server/tree/bb-11.2-MDEV-31477-json_key_value

            holyfoot Alexey Botchkov added a comment - - edited Patch looks mostly fine. One modification recommended in the comment. here: https://github.com/MariaDB/server/tree/bb-11.2-MDEV-31477-json_key_value

            People

              rucha174 Rucha Deodhar
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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