[MDEV-27151] JSON_VALUE() does not parse NULL properties properly - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5, 10.9.3
Component/s: JSON
Labels:
None

Description

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

duplicates

MDEV-19664 JSON_VALUE and null values

Closed

Activity

Ascending order - Click to sort in descending order

Assen Totin (Inactive) created issue - 2021-12-01 21:10

Assen Totin (Inactive) made changes - 2021-12-01 21:11

Field	Original Value	New Value
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. {code:sql} 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) {code} Example 2: Alternative way to verify the bug is to use PL/SQL code. {code:sql} 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) {code} Example 3: Javascript code to illustrate the standard handling of NULL as a value of an object property through JSON serialisation(run under NodeJS): {code:sql} 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 {code:sql}	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. {code:sql} 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) {code} Example 2: Alternative way to verify the bug is to use PL/SQL code. {code:sql} 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) {code} Example 3: Javascript code to illustrate the standard handling of NULL as a value of an object property through JSON serialisation(run under NodeJS): {code:javascript} 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 {code:sql}

Assen Totin (Inactive) made changes - 2021-12-01 21:12

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.

{code:sql}
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)
{code}

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

{code:sql}
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)
{code}

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

{code:javascript}
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
{code:sql}

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.

{code:sql}
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)
{code}

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

{code:sql}
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)
{code}

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

{code:javascript}

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

{code}

Alice Sherepa made changes - 2021-12-02 17:15

Link

This issue duplicates ~~MDEV-19664~~ [ ~~MDEV-19664~~ ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 128077 ]

MariaDB v4 [ 143401 ]

Oleksandr Byelkin made changes - 2022-08-01 06:47

Affects Version/s		10.9.1 [ 27114 ]
Affects Version/s		10.8.3 [ 27502 ]
Affects Version/s		10.7.4 [ 27504 ]
Affects Version/s		10.6.8 [ 27506 ]
Affects Version/s		10.5.16 [ 27508 ]
Affects Version/s		10.4.25 [ 27510 ]
Affects Version/s		10.3.35 [ 27512 ]
Affects Version/s	10.6.5 [ 26034 ]

Oleksandr Byelkin made changes - 2022-08-01 06:47

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]

Oleksandr Byelkin made changes - 2022-08-01 06:47

Priority

Major [ 3 ]

Critical [ 2 ]

Oleksandr Byelkin made changes - 2022-08-01 06:47

Assignee

Rucha Deodhar [ rucha174 ]

Oleksandr Byelkin made changes - 2022-08-01 06:50

Summary

JSON_VALUE() does not parse NULL properties properly

JSON_VALUE()/JSON_EXTRACT() does not parse NULL properties properly

Rucha Deodhar made changes - 2022-08-01 14:49

Status

Open [ 1 ]

In Progress [ 3 ]

Rucha Deodhar made changes - 2022-08-01 14:49

Assignee	Rucha Deodhar [ rucha174 ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Rucha Deodhar made changes - 2022-08-02 07:33

Summary

JSON_VALUE()/JSON_EXTRACT() does not parse NULL properties properly

JSON_VALUE() does not parse NULL properties properly

Oleksandr Byelkin made changes - 2022-08-11 08:46

Assignee	Oleksandr Byelkin [ sanja ]	Rucha Deodhar [ rucha174 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Rucha Deodhar made changes - 2022-08-11 14:13

Fix Version/s		10.3.36 [ 27513 ]
Fix Version/s		10.4.26 [ 27511 ]
Fix Version/s		10.5.17 [ 27509 ]
Fix Version/s		10.6.9 [ 27507 ]
Fix Version/s		10.7.5 [ 27505 ]
Fix Version/s		10.8.4 [ 27503 ]
Fix Version/s		10.9.2 [ 27115 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]
Fix Version/s	10.8 [ 26121 ]
Fix Version/s	10.9 [ 26905 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Rucha Deodhar made changes - 2022-09-23 15:36

Fix Version/s		10.3.37 [ 28404 ]
Fix Version/s		10.4.27 [ 28405 ]
Fix Version/s		10.5.18 [ 28421 ]
Fix Version/s		10.9.3 [ 28409 ]
Fix Version/s		10.8.5 [ 28308 ]
Fix Version/s		10.7.6 [ 28408 ]
Fix Version/s		10.6.10 [ 28407 ]
Fix Version/s	10.9.2 [ 27115 ]
Fix Version/s	10.8.4 [ 27503 ]
Fix Version/s	10.7.5 [ 27505 ]
Fix Version/s	10.6.9 [ 27507 ]
Fix Version/s	10.5.17 [ 27509 ]
Fix Version/s	10.4.26 [ 27511 ]
Fix Version/s	10.3.36 [ 27513 ]

People

Assignee:: Rucha Deodhar

Reporter:: Assen Totin (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2021-12-01 21:10

Updated:: 2024-07-04 11:07

Resolved:: 2022-08-11 14:13

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration