[MDEV-24784] JSON_ARRAYAGG charset issue - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5.8, 10.5
Fix Version/s: 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3
Component/s: Character Sets, JSON
Labels:
None
Environment:
Ubuntu 18.04 LTS

Description

JSON_ARRAYAGG() doesn't evaluate correct charset.

reproduce:

MariaDB [test]> CREATE TABLE t1 (a varchar(5));

Query OK, 0 rows affected (0.035 sec)

MariaDB [test]> INSERT INTO t1 VALUES ('a'),('ä');

Query OK, 2 rows affected (0.014 sec)

MariaDB [test]> select * from t1;

+------+

| a    |

+------+

| a    |

| ä    |

+------+

2 rows in set (0.000 sec)

MariaDB [test]> SELECT JSON_ARRAYAGG(a) FROM t1;

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

| JSON_ARRAYAGG(a) |

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

| ["a","▒"]         |

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

1 row in set (0.001 sec)

MariaDB [test]> SELECT JSON_ARRAY(a) FROM t1;

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

| JSON_ARRAY(a) |

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

| ["a"]         |

| ["ä"]         |

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

2 rows in set (0.000 sec)

MariaDB [test]> select @@version;

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

| @@version                            |

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

| 10.5.8-MariaDB-1:10.5.8+maria~bionic |

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

MariaDB [(none)]> select @@character_set_server,@@character_set_client,@@character_set_results,@@collation_connection;

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

| @@character_set_server | @@character_set_client | @@character_set_results | @@collation_connection |

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

| utf8mb4                | utf8                   | utf8                    | utf8_general_ci        |

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

1 row in set (0.000 sec)

Attachments

Issue Links

is duplicated by

MDEV-31159 Wrong encoding produced by JSON_ARRAYAGG

Closed

relates to

MDEV-16620 Add support for JSON_ARRAYAGG and JSON_OBJECTAGG

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2021-02-04 10:16

MariaDB [test]> set names latin1;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT JSON_ARRAYAGG('ä');

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

| JSON_ARRAYAGG('ä')  |

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

| ["ä"]               |

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

1 row in set (0.000 sec)

MariaDB [test]> set names utf8;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT JSON_ARRAYAGG('ä');

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

| JSON_ARRAYAGG('ä')  |

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

| ["�"]                |

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

1 row in set (0.000 sec)

MariaDB [test]> SELECT 'ä';

+----+

| ä  |

+----+

| ä  |

+----+

1 row in set (0.000 sec)

Alice Sherepa added a comment - 2021-02-04 10:16 MariaDB [test]> set names latin1; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SELECT JSON_ARRAYAGG('ä'); +---------------------+ | JSON_ARRAYAGG('ä') | +---------------------+ | ["ä"] | +---------------------+ 1 row in set (0.000 sec) MariaDB [test]> set names utf8; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> SELECT JSON_ARRAYAGG('ä'); +---------------------+ | JSON_ARRAYAGG('ä') | +---------------------+ | ["�"] | +---------------------+ 1 row in set (0.000 sec) MariaDB [test]> SELECT 'ä'; +----+ | ä | +----+ | ä | +----+ 1 row in set (0.000 sec)

Pavel Goran added a comment - 2023-10-24 03:00

This bug makes JSON_ARRAYAGG pretty much unusable for working with string data. (It seems to work fine with data from JSON columns, though.)

Pavel Goran added a comment - 2023-10-24 03:00 This bug makes JSON_ARRAYAGG pretty much unusable for working with string data. (It seems to work fine with data from JSON columns, though.)

Pavel Goran added a comment - 2023-10-24 03:12

JSON_OBJECTAGG is apparently not affected by this bug. Which allows to use it as a partial work-around (with DISTINCT, ORDER BY and other JSON_OBJECTAGG's aggregation clauses unavailable):

MariaDB [...]> SELECT JSON_EXTRACT(JSON_OBJECTAGG('1', a), '$.*') FROM t1;

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

| JSON_EXTRACT(JSON_OBJECTAGG('1', a), '$.*') |

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

| ["a", "ä"]                                  |

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

1 row in set (0.000 sec)

MariaDB [...]> select @@version;

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

| @@version           |

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

| 10.6.15-MariaDB-log |

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

1 row in set (0.000 sec)

Pavel Goran added a comment - 2023-10-24 03:12 JSON_OBJECTAGG is apparently not affected by this bug. Which allows to use it as a partial work-around (with DISTINCT , ORDER BY and other JSON_OBJECTAGG 's aggregation clauses unavailable): MariaDB [...]> SELECT JSON_EXTRACT(JSON_OBJECTAGG('1', a), '$.*') FROM t1; +---------------------------------------------+ | JSON_EXTRACT(JSON_OBJECTAGG('1', a), '$.*') | +---------------------------------------------+ | ["a", "ä"] | +---------------------------------------------+ 1 row in set (0.000 sec) MariaDB [...]> select @@version; +---------------------+ | @@version | +---------------------+ | 10.6.15-MariaDB-log | +---------------------+ 1 row in set (0.000 sec)

People

Assignee:: Sergei Golubchik

Reporter:: Thomas K.

Votes:: 3 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2021-02-04 09:19

Updated:: 2024-08-09 10:37

Resolved:: 2023-11-19 14:42

Git Integration

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

MariaDB Server