[MDEV-34173] Some JSON functions print the output as NULL/incorrect when character-sets is set with mbminlen > 1 - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
Fix Version/s: 10.5, 10.6, 10.11, 11.4
Component/s: JSON
Labels:
None

Description

JSON_EXTRACT function prints the output as NULL when setting character sets with mbminlen > 1

Character sets with mbminlen > 1

11.5.0-dbg>set character_set_connection=ucs2;

Query OK, 0 rows affected (0.000 sec)

Actual result

11.5.0-dbg>SELECT JSON_EXTRACT('{"a": 1,"b": 2}','$.a');

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

| JSON_EXTRACT('{"a": 1,"b": 2}','$.a') |

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

| NULL                                  |

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

1 row in set, 1 warning (0.000 sec)

11.5.0-dbg>

Expected result

11.5.0-dbg>SELECT JSON_EXTRACT('{"a": 1,"b": 2}','$.a');

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

| JSON_EXTRACT('{"a": 1,"b": 2}','$.a') |

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

| 1                                     |

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

1 row in set (0.000 sec)

11.5.0-dbg>

Character sets with mbminlen = 1

11.5.0-dbg>set character_set_connection=utf8;

Query OK, 0 rows affected (0.000 sec)

11.5.0-dbg>SELECT JSON_EXTRACT('{"a": 1,"b": 2}','$.a');

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

| JSON_EXTRACT('{"a": 1,"b": 2}','$.a') |

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

| 1                                     |

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

1 row in set (0.000 sec)

11.5.0-dbg>

Attachments

Issue Links

relates to

MDEV-34143 Server crashes when executing JSON_EXTRACT after setting non-default collation_connection

Closed

Activity

Descending order - Click to sort in ascending order

Rucha Deodhar added a comment - 2024-06-07 12:49

Once this is fixed, please enable view protocol for ~~MDEV-34143~~ in func_json.test

Rucha Deodhar added a comment - 2024-06-07 12:49 Once this is fixed, please enable view protocol for MDEV-34143 in func_json.test

Ramesh Sivaraman added a comment - 2024-05-16 10:35

rucha174 Following JSON functions also print NULL/incorrect value when character_sets is set with mbminlen > 1

JSON_ARRAY_APPEND
JSON_ARRAY_INTERSECT
JSON_INSERT
JSON_MERGE
JSON_MERGE_PRESERVE
JSON_OBJECT_FILTER_KEYS
JSON_OBJECT_TO_ARRAY
JSON_OVERLAPS
JSON_SCHEMA_VALID
JSON_SET

JSON_ARRAY_APPEND

------------------

10.6.18-dbg>set character_set_connection=ucs2;

Query OK, 0 rows affected (0.000 sec)

10.6.18-dbg>SET @json = '[1, 2, [3, 4]]';

Query OK, 0 rows affected (0.000 sec)

10.6.18-dbg>SELECT JSON_ARRAY_APPEND(@json, '$[0]', 5);

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

| JSON_ARRAY_APPEND(@json, '$[0]', 5) |

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

| NULL                                |

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

1 row in set, 1 warning (0.000 sec)

10.6.18-dbg>

JSON_ARRAY_INTERSECT

--------------------

11.5.0-opt>SET @json1= '[1,2,3]';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SET @json2= '[1,2,4]';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>

11.5.0-opt>SELECT JSON_ARRAY_INTERSECT(@json1, @json2);

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

| json_array_intersect(@json1, @json2) |

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

| NULL                                 |

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

1 row in set (0.001 sec)

JSON_INSERT

-----------

11.5.0-opt>SET @json = '{ "A": 0, "B": [1, 2]}';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>

11.5.0-opt>SELECT JSON_INSERT(@json, '$.C', '[3, 4]');

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

| JSON_INSERT(@json, '$.C', '[3, 4]') |

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

| NULL                                |

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

1 row in set, 1 warning (0.000 sec)

11.5.0-opt>

JSON_MERGE

----------

11.5.0-opt>SET @json1 = '[1, 2]';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SET @json2 = '[3, 4]';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>

11.5.0-opt>SELECT JSON_MERGE(@json1,@json2);

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

| JSON_MERGE(@json1,@json2) |

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

| NULL                      |

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

1 row in set, 1 warning (0.000 sec)

11.5.0-opt>

JSON_MERGE_PRESERVE

-------------------

11.5.0-opt>SET @json1 = '[1, 2]';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SET @json2 = '[2, 3]';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SELECT JSON_MERGE_PRESERVE(@json1,@json2);

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

| JSON_MERGE_PRESERVE(@json1,@json2) |

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

| NULL                               |

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

1 row in set, 1 warning (0.000 sec)

11.5.0-opt>

JSON_OBJECT_FILTER_KEYS

-----------------------

11.5.0-opt>SET @obj1= '{ "a": 1, "b": 2, "c": 3}';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SET @obj2= '{"b" : 10, "c": 20, "d": 30}';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SELECT JSON_OBJECT_FILTER_KEYS (@obj1, JSON_ARRAY_INTERSECT(JSON_KEYS(@obj1), JSON_KEYS(@obj2)));

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

| JSON_OBJECT_FILTER_KEYS (@obj1, JSON_ARRAY_INTERSECT(JSON_KEYS(@obj1), JSON_KEYS(@obj2))) |

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

| NULL                                                                                      |

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

1 row in set, 1 warning (0.000 sec)

11.5.0-opt>

JSON_OBJECT_TO_ARRAY

--------------------

11.5.0-opt>SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": {"key3":"val3"} }}';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>

11.5.0-opt>SELECT JSON_OBJECT_TO_ARRAY(@obj1);

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

| JSON_OBJECT_TO_ARRAY(@obj1) |

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

| NULL                        |

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

1 row in set, 1 warning (0.000 sec)

11.5.0-opt>

JSON_OVERLAPS

--------------

11.5.0-opt>set character_set_connection=default;

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SELECT JSON_OVERLAPS('{"A": 1, "B": {"C":2}}', '{"A": 2, "B": {"C":2}}') AS is_overlap;

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

| is_overlap |

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

|          1 |

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

1 row in set (0.000 sec)

11.5.0-opt>set character_set_connection=ucs2;

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SELECT JSON_OVERLAPS('{"A": 1, "B": {"C":2}}', '{"A": 2, "B": {"C":2}}') AS is_overlap;

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

| is_overlap |

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

|          0 |

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

1 row in set (0.000 sec)

11.5.0-opt>

JSON_SCHEMA_VALID

-----------------

11.5.0-opt>SET @schema= '{

    '>   "properties" : {

    '>     "number1":{ "maximum":10 },

    '>     "string1" : { "maxLength": 3}

    '>   }

    '> }';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>

11.5.0-opt>SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }');

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

| JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }') |

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

|                                                              1 |

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

1 row in set (0.000 sec)

11.5.0-opt>set character_set_connection=default;

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>SET @schema= '{

    '>   "properties" : {

    '>     "number1":{ "maximum":10 },

    '>     "string1" : { "maxLength": 3}

    '>   }

    '> }';

Query OK, 0 rows affected (0.000 sec)

11.5.0-opt>

11.5.0-opt>SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }');

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

| JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }') |

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

|                                                              0 |

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

1 row in set (0.000 sec)

11.5.0-opt>

JSON_SET

--------

11.5.0-opt>SELECT JSON_SET(Priv, '$.locked', 'true') FROM mysql.global_priv;

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

| JSON_SET(Priv, '$.locked', 'true') |

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

| NULL                               |

| NULL                               |

| NULL                               |

| NULL                               |

| NULL                               |

| NULL                               |

| NULL                               |

| NULL                               |

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

8 rows in set, 8 warnings (0.040 sec)

11.5.0-opt>

Ramesh Sivaraman added a comment - 2024-05-16 10:35 rucha174 Following JSON functions also print NULL/incorrect value when character_sets is set with mbminlen > 1 JSON_ARRAY_APPEND JSON_ARRAY_INTERSECT JSON_INSERT JSON_MERGE JSON_MERGE_PRESERVE JSON_OBJECT_FILTER_KEYS JSON_OBJECT_TO_ARRAY JSON_OVERLAPS JSON_SCHEMA_VALID JSON_SET JSON_ARRAY_APPEND ------------------ 10.6.18-dbg> set character_set_connection=ucs2; Query OK, 0 rows affected (0.000 sec) 10.6.18-dbg> SET @json = '[1, 2, [3, 4]]' ; Query OK, 0 rows affected (0.000 sec) 10.6.18-dbg> SELECT JSON_ARRAY_APPEND(@json, '$[0]' , 5); + -------------------------------------+ | JSON_ARRAY_APPEND(@json, '$[0]' , 5) | + -------------------------------------+ | NULL | + -------------------------------------+ 1 row in set , 1 warning (0.000 sec) 10.6.18-dbg> JSON_ARRAY_INTERSECT -------------------- 11.5.0-opt> SET @json1= '[1,2,3]' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SET @json2= '[1,2,4]' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> 11.5.0-opt> SELECT JSON_ARRAY_INTERSECT(@json1, @json2); + --------------------------------------+ | json_array_intersect(@json1, @json2) | + --------------------------------------+ | NULL | + --------------------------------------+ 1 row in set (0.001 sec) JSON_INSERT ----------- 11.5.0-opt> SET @json = '{ "A": 0, "B": [1, 2]}' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> 11.5.0-opt> SELECT JSON_INSERT(@json, '$.C' , '[3, 4]' ); + -------------------------------------+ | JSON_INSERT(@json, '$.C' , '[3, 4]' ) | + -------------------------------------+ | NULL | + -------------------------------------+ 1 row in set , 1 warning (0.000 sec) 11.5.0-opt> JSON_MERGE ---------- 11.5.0-opt> SET @json1 = '[1, 2]' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SET @json2 = '[3, 4]' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> 11.5.0-opt> SELECT JSON_MERGE(@json1,@json2); + ---------------------------+ | JSON_MERGE(@json1,@json2) | + ---------------------------+ | NULL | + ---------------------------+ 1 row in set , 1 warning (0.000 sec) 11.5.0-opt> JSON_MERGE_PRESERVE ------------------- 11.5.0-opt> SET @json1 = '[1, 2]' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SET @json2 = '[2, 3]' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SELECT JSON_MERGE_PRESERVE(@json1,@json2); + ------------------------------------+ | JSON_MERGE_PRESERVE(@json1,@json2) | + ------------------------------------+ | NULL | + ------------------------------------+ 1 row in set , 1 warning (0.000 sec) 11.5.0-opt> JSON_OBJECT_FILTER_KEYS ----------------------- 11.5.0-opt> SET @obj1= '{ "a": 1, "b": 2, "c": 3}' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SET @obj2= '{"b" : 10, "c": 20, "d": 30}' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SELECT JSON_OBJECT_FILTER_KEYS (@obj1, JSON_ARRAY_INTERSECT(JSON_KEYS(@obj1), JSON_KEYS(@obj2))); + -------------------------------------------------------------------------------------------+ | JSON_OBJECT_FILTER_KEYS (@obj1, JSON_ARRAY_INTERSECT(JSON_KEYS(@obj1), JSON_KEYS(@obj2))) | + -------------------------------------------------------------------------------------------+ | NULL | + -------------------------------------------------------------------------------------------+ 1 row in set , 1 warning (0.000 sec) 11.5.0-opt> JSON_OBJECT_TO_ARRAY -------------------- 11.5.0-opt> SET @obj1= '{ "a": [1, 2, 3], "b": { "key1":"val1", "key2": {"key3":"val3"} }}' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> 11.5.0-opt> SELECT JSON_OBJECT_TO_ARRAY(@obj1); + -----------------------------+ | JSON_OBJECT_TO_ARRAY(@obj1) | + -----------------------------+ | NULL | + -----------------------------+ 1 row in set , 1 warning (0.000 sec) 11.5.0-opt> JSON_OVERLAPS -------------- 11.5.0-opt> set character_set_connection= default ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SELECT JSON_OVERLAPS( '{"A": 1, "B": {"C":2}}' , '{"A": 2, "B": {"C":2}}' ) AS is_overlap; + ------------+ | is_overlap | + ------------+ | 1 | + ------------+ 1 row in set (0.000 sec) 11.5.0-opt> set character_set_connection=ucs2; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> SELECT JSON_OVERLAPS( '{"A": 1, "B": {"C":2}}' , '{"A": 2, "B": {"C":2}}' ) AS is_overlap; + ------------+ | is_overlap | + ------------+ | 0 | + ------------+ 1 row in set (0.000 sec) 11.5.0-opt> JSON_SCHEMA_VALID ----------------- 11.5.0-opt> SET @ schema = '{ ' > "properties" : { '> "number1":{ "maximum":10 }, ' > "string1" : { "maxLength" : 3} '> } ' > } '; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> 11.5.0-opt>SELECT JSON_SCHEMA_VALID(@schema, ' { "number1" :25, "string1" : "ab" } '); +----------------------------------------------------------------+ | JSON_SCHEMA_VALID(@schema, ' { "number1" :25, "string1" : "ab" } ') | +----------------------------------------------------------------+ | 1 | +----------------------------------------------------------------+ 1 row in set (0.000 sec) 11.5.0-opt>set character_set_connection=default; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt>SET @schema= ' { '> "properties" : { ' > "number1" :{ "maximum" :10 }, '> "string1" : { "maxLength": 3} ' > } '> }' ; Query OK, 0 rows affected (0.000 sec) 11.5.0-opt> 11.5.0-opt> SELECT JSON_SCHEMA_VALID(@ schema , '{ "number1":25, "string1":"ab" }' ); + ----------------------------------------------------------------+ | JSON_SCHEMA_VALID(@ schema , '{ "number1":25, "string1":"ab" }' ) | + ----------------------------------------------------------------+ | 0 | + ----------------------------------------------------------------+ 1 row in set (0.000 sec) 11.5.0-opt> JSON_SET -------- 11.5.0-opt> SELECT JSON_SET(Priv, '$.locked' , 'true' ) FROM mysql.global_priv; + ------------------------------------+ | JSON_SET(Priv, '$.locked' , 'true' ) | + ------------------------------------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | + ------------------------------------+ 8 rows in set , 8 warnings (0.040 sec) 11.5.0-opt>

People

Assignee:: Alexander Barkov

Reporter:: Ramesh Sivaraman

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2024-05-15 12:11

Updated:: 2024-11-22 17:09

Git Integration

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

MariaDB Server