[MDEV-27018] IF and COALESCE lose "json" property Created: 2021-11-10  Updated: 2022-06-07  Resolved: 2022-01-21

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.5.14, 10.6.6, 10.7.2, 10.8.1

Type: Bug Priority: Critical
Reporter: Sergei Golubchik Assignee: Alexander Barkov
Resolution: Fixed Votes: 2
Labels: None

Issue Links:
Blocks
blocks MDEV-26506 Over-quoted JSON when combining JSON_... Closed
Relates
relates to MDEV-27350 Complex CHECK constraint loses JSON p... Open
relates to MDEV-27358 Table level CHECK(JSON_VALID()) const... Open
relates to MDEV-27359 Crossed JSON_VALID constraint imposes... Open
relates to MDEV-27361 Hybrid functions with JSON arguments ... Open
relates to MDEV-27370 UNION looses JSON property Open
relates to MDEV-13701 Nesting JSON Closed
relates to MDEV-26506 Over-quoted JSON when combining JSON_... Closed
relates to MDEV-27360 Column level CHECK constraint is not ... Open
relates to MDEV-27533 The result of `CONVERT(json USING ...... Open
relates to MDEV-27864 Alter table modify column for same da... Closed

 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.

 Comments   
Comment by Alexander Keremidarski [ 2021-11-10 ]

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)

Comment by Sergei Golubchik [ 2021-11-10 ]

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.

Comment by Alexander Barkov [ 2021-12-06 ]

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

Comment by Alexander Barkov [ 2021-12-22 ]

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

Comment by Alexander Barkov [ 2021-12-27 ]

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.

Comment by Alexander Barkov [ 2021-12-30 ]

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

Comment by Sergei Golubchik [ 2022-01-20 ]

0478f474020466 is ok to push

Generated at Thu Feb 08 09:49:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.