[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

Thomas K. created issue - 2021-02-04 09:19

Thomas K. made changes - 2021-02-04 09:19

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-16620~~ [ ~~MDEV-16620~~ ]

Alice Sherepa made changes - 2021-02-04 09:54

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)

JSON_ARRAYAGG() doesn't evaluate correct charset.

reproduce:

{noformat}
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)
{noformat}

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)

Alice Sherepa made changes - 2021-02-04 10:16

Affects Version/s

10.5 [ 23123 ]

Alice Sherepa made changes - 2021-02-04 10:16

Fix Version/s

10.5 [ 23123 ]

Alice Sherepa made changes - 2021-02-04 10:16

Priority

Critical [ 2 ]

Major [ 3 ]

Alice Sherepa made changes - 2021-02-04 10:16

Assignee

Alexey Botchkov [ holyfoot ]

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

Workflow

MariaDB v3 [ 118801 ]

MariaDB v4 [ 142561 ]

Nayuta Yanagisawa (Inactive) made changes - 2022-06-01 08:06

Assignee

Alexey Botchkov [ holyfoot ]

Rucha Deodhar [ rucha174 ]

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)

Alice Sherepa made changes - 2023-11-02 08:29

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.0 [ 28320 ]
Fix Version/s		11.1 [ 28549 ]
Fix Version/s		11.2 [ 28603 ]

Sergei Golubchik made changes - 2023-11-02 11:02

Assignee

Rucha Deodhar [ rucha174 ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2023-11-02 11:17

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2023-11-02 11:17

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2023-11-19 14:42

Fix Version/s		10.5.24 [ 29517 ]
Fix Version/s		10.6.17 [ 29518 ]
Fix Version/s		10.11.7 [ 29519 ]
Fix Version/s		11.0.5 [ 29520 ]
Fix Version/s		11.1.4 [ 29024 ]
Fix Version/s		11.2.3 [ 29521 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]
Fix Version/s	11.0 [ 28320 ]
Fix Version/s	11.1 [ 28549 ]
Fix Version/s	11.2 [ 28603 ]
Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

Alice Sherepa made changes - 2024-08-09 10:37

Link

This issue is duplicated by ~~MDEV-31159~~ [ ~~MDEV-31159~~ ]

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration