Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Fix Version/s: 10.5.14, 10.6.6, 10.7.2, 10.8.1
Component/s: JSON
Labels:
None

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.

Attachments

Issue Links

blocks

MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT

Closed

relates to

MDEV-27350 Complex CHECK constraint loses JSON property

Open

MDEV-27358 Table level CHECK(JSON_VALID()) constraint loses JSON property

Open

MDEV-27359 Crossed JSON_VALID constraint imposes JSON property to a wrong column

Open

MDEV-27361 Hybrid functions with JSON arguments do not send format metadata

Open

MDEV-27370 UNION looses JSON property

Open

MDEV-13701 Nesting JSON

Closed

MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT

Closed

MDEV-27360 Column level CHECK constraint is not SQL Standard compliant

Open

MDEV-27533 The result of `CONVERT(json USING ...)` is erroneously treated as JSON

Open

MDEV-27864 Alter table modify column for same data type copying table

Closed

(6 relates to)

Activity

Ascending order - Click to sort in descending order

Alexander Keremidarski added a comment - 2021-11-10 21:58 - edited

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)

Alexander Keremidarski added a comment - 2021-11-10 21:58 - edited 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)

Sergei Golubchik added a comment - 2021-11-10 23:05

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.

Sergei Golubchik added a comment - 2021-11-10 23:05 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.

Alexander Barkov added a comment - 2021-12-06 07:54

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

Alexander Barkov added a comment - 2021-12-06 07:54 julien.fritsch , yes this is a bug. I've marked it as Confirmed.

Alexander Barkov added a comment - 2021-12-22 12:03 - edited

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

Alexander Barkov added a comment - 2021-12-22 12:03 - edited 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

Alexander Barkov added a comment - 2021-12-27 13:59

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.

Alexander Barkov added a comment - 2021-12-27 13:59 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.

Alexander Barkov added a comment - 2021-12-30 06:23

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

Alexander Barkov added a comment - 2021-12-30 06:23 serg , I replied to your review comments by email. Please have a look.

Sergei Golubchik added a comment - 2022-01-20 12:08

0478f474020466 is ok to push

Sergei Golubchik added a comment - 2022-01-20 12:08 0478f474020466 is ok to push

People

Assignee:: Alexander Barkov

Reporter:: Sergei Golubchik

Votes:: 2 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2021-11-10 16:28

Updated:: 2024-07-07 21:01

Resolved:: 2022-01-21 17:13

Git Integration

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

MariaDB Server

IF and COALESCE lose "json" property