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

JSON_SEARCH returns warning instead of error if given invalid data

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2(EOL)
    • N/A
    • JSON
    • None

    Description

      Following on from the comment about the non-working example in MDEV-12877 that MySQL and MariaDB behave the same - they don't in that MySQL gives an error, MariaDB a warning. Is this desirable? Doesn't an error make more sense here?

      MySQL 5.7:

      SELECT JSON_SEARCH(@json, 'onee', 'AB');
      		 
      ERROR 3154 (42000): The oneOrAll argument to json_search may take these values: 'one' or 'all'.
      		 
       
      		 
      select json_extract(json_object('truc',json_array('machin',4,json_object('chose','XML'))),json_search(json_object('truc',json_array('machin',4,json_object('chose','XML'))),'one','XML')) as Result;
      		 
      ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 1.

      MariaDB 10.2

      SELECT JSON_SEARCH(@json, 'onee', 'AB');
      		 
      +----------------------------------+
      		 
      | JSON_SEARCH(@json, 'onee', 'AB') |
      		 
      +----------------------------------+
      		 
      | NULL                             |
      		 
      +----------------------------------+
      		 
      1 row in set, 1 warning (0.00 sec)
      		 
       
      		 
      SHOW WARNINGS;
      		 
      +---------+------+--------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                      |
      		 
      +---------+------+--------------------------------------------------------------+
      		 
      | Warning | 4046 | Argument 2 to function 'json_search' must be "one" or "all". |
      		 
      +---------+------+--------------------------------------------------------------+
      		 
       
      		 
      select json_extract(json_object('truc',json_array('machin',4,json_object('chose','XML'))),json_search(json_object('truc',json_array('machin',4,json_object('chose','XML'))),'one','XML')) as Result;
      		 
      +--------+
      		 
      | Result |
      		 
      +--------+
      		 
      | NULL   |
      		 
      +--------+
      		 
      1 row in set, 1 warning (0.00 sec)
      		 
       
      		 
      mysql [localhost] {msandbox} (test) > SHOW WARNINGS;
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                                          |
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      | Warning | 4042 | Syntax error in JSON path in argument 2 to function 'json_extract' at position 1 |
      		 
      +---------+------+----------------------------------------------------------------------------------+

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12877 Wrong result from JSON native function.

          • Major - Major loss of function.
          • Closed

          Activity

            elenst Elena Stepanova added a comment -

            Unfortunately yes, this is intended.
            Our justification is that we've decided to follow standard, which sets the default behavior to be NULL ON ERROR. Standard also describes non-default behavior, configured by ON ERROR clause, but it hasn't been implemented (yet?).
            Since standard does not seem to prohibit warnings, our compromise was to at least produce warnings.
            More discussion in MDEV-11826.

            elenst Elena Stepanova added a comment - Unfortunately yes, this is intended. Our justification is that we've decided to follow standard, which sets the default behavior to be NULL ON ERROR . Standard also describes non-default behavior, configured by ON ERROR clause, but it hasn't been implemented (yet?). Since standard does not seem to prohibit warnings, our compromise was to at least produce warnings. More discussion in MDEV-11826 .

            People

              Unassigned Unassigned
              greenman Ian Gilfillan
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.