[MDEV-31096] GROUP_CONCAT does cut the string with quotes Created: 2023-04-20  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: JSON, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.11.2, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Soner Sayakci Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None
Environment:

macOS Ventura and Ubuntu 22.04


 Description   

We found that with an weird string that our GROUP_CONCAT with JSON_OBJECT fails as it get's cut and the JSON is invalid.

The reproducer would be:

CREATE TEMPORARY TABLE t1 SELECT '1"i """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""' as name;
 
 
 
SELECT CONCAT(
 
               '[',
 
               GROUP_CONCAT(
 
                       JSON_OBJECT(
 
                               'name', t1.name
 
                           )
 
                   ),
 
               ']'
 
           ) as translations
 
FROM t1;

Without a table it works:

SELECT CONCAT(
 
               '[',
 
               GROUP_CONCAT(
 
                       JSON_OBJECT(
 
                               'name', '1"i """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""'
 
                           )
 
                   ),
 
               ']'
 
           ) as translations;

Tested with multiple MariaDB versions. With MySQL it works https://www.db-fiddle.com/f/4fk9vN8rTCpYASuKwyhUz2/0

 Comments   
Comment by Alice Sherepa [ 2023-04-20 ]

Thank you for the report! I repeated as described on 10.4-10.11 with InnoDB, with Myisam and Aria - string is not shortened.

 MariaDB [test]> CREATE  TABLE t1(name varchar(131)) engine=innodb;
 
Query OK, 0 rows affected (0,040 sec)
 
 
 
MariaDB [test]> insert into t1  SELECT '1"i """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""' ;
 
Query OK, 1 row affected (0,006 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SELECT json_object( 'a', t1.name) a FROM t1 ;
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| a                                                                                                                                                                                                                                                                            |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| {"a": "1\"i \"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\""} |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0,000 sec)
 
 
 
MariaDB [test]> SELECT json_object( 'a', t1.name) a FROM t1 group by name;
 
+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| a                                                                                                                                              |
 
+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| {"a": "1\"i \"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\"\" |
 
+------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0,001 sec)
 
MariaDB [test]> explain extended SELECT json_object( 'a', t1.name) a FROM t1 group by name;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using temporary; Using filesort |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+---------------------------------+
 
1 row in set, 1 warning (0,001 sec)
 
 
 
Note (Code 1003): select json_object('a',`test`.`t1`.`name`) AS `a` from `test`.`t1` group by `test`.`t1`.`name`

Generated at Thu Feb 08 10:21:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.