[#MDEV-18284] JSON casting using JSON_COMPACT doesn't always work with values from subqueries

[MDEV-18284] JSON casting using JSON_COMPACT doesn't always work with values from subqueries Created: 2019-01-17 Updated: 2022-01-19 Resolved: 2022-01-19
Status:	Closed
Project:	MariaDB Server
Component/s:	JSON, Server
Affects Version/s:	10.3.12, 10.2, 10.3
Fix Version/s:	10.2.42, 10.3.33, 10.4.23

Type:

Bug

Priority:

Minor

Reporter:

Heinz Wiesinger

Assignee:

Anel Husakovic

Resolution:

Fixed

Votes:

Labels:

JSON

Description

Given this test data

CREATE TABLE json_test (a JSON, b JSON);

INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}');

I can observe the following behavior:

MariaDB [bugs]> SELECT JSON_OBJECT("a", JSON_COMPACT(a)) FROM (SELECT * FROM json_test) AS json_test_values;

+-----------------------------------+

| JSON_OBJECT("a", JSON_COMPACT(a)) |

+-----------------------------------+

| {"a": [1,2,3]}                    |

+-----------------------------------+

MariaDB [bugs]> SELECT JSON_OBJECT("a", JSON_COMPACT(a), "b", b) FROM (SELECT * FROM json_test) AS json_test_values;

+-------------------------------------------+

| JSON_OBJECT("a", JSON_COMPACT(a), "b", b) |

+-------------------------------------------+

| {"a": "[1,2,3]", "b": "{\"a\":\"foo\"}"}  |

+-------------------------------------------+

MariaDB [bugs]> SELECT JSON_OBJECT("a", JSON_COMPACT(a), "b", JSON_COMPACT(b)) FROM (SELECT * FROM json_test) AS json_test_values;

+---------------------------------------------------------+

| JSON_OBJECT("a", JSON_COMPACT(a), "b", JSON_COMPACT(b)) |

+---------------------------------------------------------+

| {"a": [1,2,3], "b": {"a":"foo"}}                        |

+---------------------------------------------------------+

The output of `JSON_COMPACT(a)` is sometimes treated as JSON and sometimes as a plain string

Comments

Comment by Alice Sherepa [ 2019-01-17 ]

Thanks for the report! Reproducible on 10.2-10.3

set names utf8;

SET @j = '[1,2]';

SELECT JSON_OBJECT("a",JSON_COMPACT(@j));

SELECT JSON_OBJECT("a",JSON_COMPACT(@j), "b", @j);

MariaDB [test]> SET @j = '[1,2]';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT JSON_OBJECT("a",JSON_COMPACT(@j));

+-----------------------------------+

| JSON_OBJECT("a",JSON_COMPACT(@j)) |

+-----------------------------------+

| {"a": [1,2]}                      |

+-----------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT JSON_OBJECT("a",JSON_COMPACT(@j), "b", @j);

+--------------------------------------------+

| JSON_OBJECT("a",JSON_COMPACT(@j), "b", @j) |

+--------------------------------------------+

| {"a": "[1,2]", "b": "[1,2]"}               |

+--------------------------------------------+

1 row in set (0.00 sec)

Comment by Anel Husakovic [ 2019-01-20 ]

Actually here is one more problem ; func_item like `json_compact` is not working in `json_object`, so the following will not be good:

CREATE TABLE json_test (a JSON, b JSON);

INSERT INTO json_test VALUES ("[   1,  2,  3]", '{"a":"foo"}');

SELECT JSON_OBJECT("a", JSON_COMPACT(a))

JSON_OBJECT ("a", JSON_COMPACT(a))

{"A": [   1,  2,  3]}

Result should be

  {"A": [1,2,3]}

, right ?
Think this needs to be solved too. holyfoot can I have a try ?

Comment by Anel Husakovic [ 2019-01-28 ]

Comment by Anel Husakovic [ 2021-06-04 ]

julien.fritsch sorry for delay.
It is still in review and I guess holyfoot should decide where to go.

Comment by Alexey Botchkov [ 2022-01-18 ]

512d81c0f88 is ok to push.

Comment by Anel Husakovic [ 2022-01-19 ]

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