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

Sergei Golubchik created issue - 2021-11-10 16:28

Sergei Golubchik made changes - 2021-11-10 17:25

Field	Original Value	New Value
Priority	Major [ 3 ]	Critical [ 2 ]

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.

Sergei Golubchik made changes - 2021-11-10 23:06

Link

This issue relates to ~~MDEV-13701~~ [ ~~MDEV-13701~~ ]

Alexander Barkov made changes - 2021-12-06 07:54

Status

Open [ 1 ]

Confirmed [ 10101 ]

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.

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

Workflow

MariaDB v3 [ 127273 ]

MariaDB v4 [ 144427 ]

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 made changes - 2021-12-23 12:26

Link

This issue relates to MDEV-27350 [ MDEV-27350 ]

Alexander Barkov made changes - 2021-12-24 09:19

Link

This issue relates to MDEV-27358 [ MDEV-27358 ]

Alexander Barkov made changes - 2021-12-24 09:26

Link

This issue relates to MDEV-27359 [ MDEV-27359 ]

Alexander Barkov made changes - 2021-12-24 09:48

Link

This issue relates to MDEV-27360 [ MDEV-27360 ]

Alexander Barkov made changes - 2021-12-25 17:16

Link

This issue relates to MDEV-27361 [ MDEV-27361 ]

Alexander Barkov made changes - 2021-12-27 10:00

Link

This issue relates to MDEV-27370 [ MDEV-27370 ]

Alexander Barkov made changes - 2021-12-27 10:02

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

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 made changes - 2021-12-27 13:59

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-12-28 18:11

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

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.

Alexander Barkov made changes - 2021-12-30 06:23

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Alexander Barkov made changes - 2021-12-30 09:31

Link

This issue blocks ~~MDEV-26506~~ [ ~~MDEV-26506~~ ]

Sergei Golubchik made changes - 2022-01-11 12:38

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2022-01-17 16:09

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2022-01-18 06:02

Link

This issue relates to MDEV-27533 [ MDEV-27533 ]

Alexander Barkov made changes - 2022-01-18 07:17

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

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

Sergei Golubchik made changes - 2022-01-20 12:08

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2022-01-21 17:13

issue.field.resolutiondate

2022-01-21 17:13:10.0

2022-01-21 17:13:10.371

Alexander Barkov made changes - 2022-01-21 17:13

Fix Version/s		10.5.14 [ 26809 ]
Fix Version/s		10.6.6 [ 26811 ]
Fix Version/s		10.7.2 [ 26813 ]
Fix Version/s		10.8.1 [ 26815 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Alexander Barkov made changes - 2022-06-07 07:12

Link

This issue relates to ~~MDEV-26506~~ [ ~~MDEV-26506~~ ]

Alexander Barkov made changes - 2022-06-07 08:58

Comment

[ This script:

{code:sql}
SET profiling=1;
SET profiling_history_size=1;

CREATE OR REPLACE TABLE stat (state TEXT, types TEXT);

CREATE OR REPLACE TABLE t1 (a TEXT);
ALTER TABLE t1 MODIFY a JSON;
INSERT INTO stat
SELECT state, '1. TEXT to JSON' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';

ALTER TABLE t1 MODIFY a JSON;
INSERT INTO stat
SELECT state, '2. JSON to JSON' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';

ALTER TABLE t1 MODIFY a TEXT;
INSERT INTO stat
SELECT state, '3. JSON to TEXT' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';

ALTER TABLE t1 MODIFY a TEXT;
INSERT INTO stat
SELECT state, '4. TEXT to TEXT' AS types FROM INFORMATION_SCHEMA.PROFILING WHERE state LIKE 'Copy to tmp table';

SELECT state, types FROM stat;
{code}

in 10.4.26 returns:

{noformat}
+-------------------+-----------------+
| state | types |
+-------------------+-----------------+
| Copy to tmp table | 1. TEXT to JSON |
| Copy to tmp table | 1. TEXT to JSON |
| Copy to tmp table | 2. JSON to JSON |
| Copy to tmp table | 2. JSON to JSON |
| Copy to tmp table | 3. JSON to TEXT |
| Copy to tmp table | 3. JSON to TEXT |
+-------------------+-----------------
{noformat}

in 10.5.17 returns:
{noformat}
+-------------------+-----------------+
| state | types |
+-------------------+-----------------+
| copy to tmp table | 1. TEXT to JSON |
| copy to tmp table | 3. JSON to TEXT |
+-------------------+-----------------+
{noformat}

Temporary table JSON->JSON conversion:
- is created in 10.4.26
- is not created in 10.5.17
]

Alexander Barkov made changes - 2022-06-07 10:09

Link

This issue relates to ~~MDEV-27864~~ [ ~~MDEV-27864~~ ]

Jira Automation (IT) made changes - 2024-07-04 01:55

Zendesk Related Tickets

176096

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

Details

Description

Attachments

Issue Links

Activity

More bad behavior examples

Single row subselect

UNION

People

Dates

Git Integration