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

JSON_VALUE() does not parse NULL properties properly

    XMLWordPrintable

    Details

      Description

      A bug exists in the JSON_VALUE() function while parsing a JSON property that has a value of NULL. The function produces a string value "null" while it should actually produce a NULL value. The JSON standard clearly separates "null" as a string value from a NULL value for an object property (see Example 3 below).

      Example 1: Call the JSON_VALUE() function with a JSON where a property is set to NULL. Pay attention that the returned value of is lowercase; MariaDB CLI always prints the actual NULL values in uppercase in the result.

      MariaDB [xml]> SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest');
      +------------------------------------------------+
      | JSON_VALUE('{"nulltest": null}', '$.nulltest') |
      +------------------------------------------------+
      | null                                           |
      +------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [xml]> SELECT null;
      +------+
      | NULL |
      +------+
      | NULL |
      +------+
      1 row in set (0.000 sec)
      

      Example 2: Alternative way to verify the bug is to use PL/SQL code.

      DELIMITER //
      CREATE OR REPlACE PROCEDURE NULLTEST()
         IS
            nulltest   VARCHAR2(255);
         BEGIN
            SELECT JSON_VALUE('{"nulltest": null}', '$.nulltest') INTO nulltest;
       
            IF nulltest IS NULL THEN
               SELECT 'The value of nulltest was NULL' INTO @sess_nulltest;
            ELSE
               SELECT 'The value of nulltest was NOT NULL' INTO @sess_nulltest;
            END IF;
         END;
      //
      DELIMITER ;
       
      MariaDB [xml]> CALL NULLTEST();
      Query OK, 2 rows affected (0.000 sec)
       
      MariaDB [xml]> SELECT @sess_nulltest;
      +------------------------------------+
      | @sess_nulltest                     |
      +------------------------------------+
      | The value of nulltest was NOT NULL |
      +------------------------------------+
      1 row in set (0.000 sec)
      

      Example 3: Javascript code to illustrate the standard handling of NULL as a value of an object property through JSON serialisation(run under NodeJS):

       
      var a = {
              nulltest: null
      };
      console.log('Object a:');
      console.log(a);
      console.log();
       
      var b = JSON.stringify(a);
      console.log('JSON string b:');
      console.log(b);
      console.log();
       
      var c = JSON.parse(b);
      console.log('Object c:');
      console.log(c);
      console.log();
       
      if (c.nulltest == null)
          console.log('c.nulltest is NULL');
      else
          console.log('c.nulltest is NOT NULL');
       
      [assen.totin@archimed ~]$ node nulltest.js 
      Object a:
      { nulltest: null }
       
      JSON string b:
      {"nulltest":null}
       
      Object c:
      { nulltest: null }
       
      c.nulltest is NULL
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              rucha174 Rucha Deodhar
              Reporter:
              assen.totin Assen Totin
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.