[MDEV-26167] JSON_OBJECT value is truncated under some condition Created: 2021-07-16  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.5.11, 10.6.3, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

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

linux, docker


 Description   

With the following scenario, the JSON_OBJECT output is truncated of one characters per boolean field present.

CREATE DATABASE test_bug
 
 
 
CREATE table one (
 
  `id`          int(10) unsigned NOT NULL,
 
  `some_column` int(10) unsigned NULL,
 
  PRIMARY KEY (`id`)
 
)
 
 
 
INSERT INTO one (id, some_column) VALUES (1, NULL), (2, 42)
 
 
 
CREATE table two (
 
  `id`     int(10) unsigned NOT NULL,
 
  `fk_one` int(10) unsigned NOT NULL,
 
  PRIMARY KEY (`id`)
 
)
 
 
 
SELECT
 
	one.id,
 
	JSON_OBJECT(
 
		'foo', true,
 
		'bar', 1 = 1,
 
		'nullable', one.some_column IS NULL,
 
		'quaz', (SELECT 1 < 0)
 
	) as obj
 
FROM one
 
LEFT JOIN two
 
	ON two.fk_one = one.id
 
LEFT JOIN three
 
	ON 1
 
GROUP BY one.id

Result

id|obj                                                     |
 
--+--------------------------------------------------------+
 
 1|{"foo": true, "bar": true, "nullable": true, "quaz": fal|
 
 2|{"foo": true, "bar": true, "nullable": false, "quaz": fa|

Expected

id|obj                                                         |
 
--+------------------------------------------------------------+
 
 1|{"foo": true, "bar": true, "nullable": true, "quaz": false} |
 
 2|{"foo": true, "bar": true, "nullable": false, "quaz": false}|

Observation

This problem is not triggered when you :

  • Remove the join clause
  • Remove the group by clause
  • Remove the null column from the SELECT query ( one.some_column IS NULL )

But even when you do any of those changes, the column definition is still wrong ( VARCHAR(number), number being too low ), despite the result being recieved in full, .

 Comments   
Comment by Alice Sherepa [ 2021-07-19 ]

Thank you! I repeated on 10.2-10.6:

MariaDB [test]> SELECT t1.id, json_object('nullable', t1.a IS NULL, 'quaz', (SELECT 1 < 0)) 
    -> FROM t1
 
    -> GROUP BY t1.id;
 
+------+---------------------------------------------------------------+
 
| id   | json_object('nullable', t1.a IS NULL, 'quaz', (SELECT 1 < 0)) |
 
+------+---------------------------------------------------------------+
 
|    1 | {"nullable": true, "quaz": false                              |
 
|    2 | {"nullable": false, "quaz": fals                              |
 
+------+---------------------------------------------------------------+
 
2 rows in set (0.03 sec)


CREATE TABLE t1 ( id int, a int);
 
INSERT INTO t1  VALUES (1, NULL), (2, 42);
 
  
SELECT t1.id, json_object('nullable', t1.a IS NULL, 'quaz', (SELECT 1 < 0)) 
FROM t1
 
GROUP BY t1.id;
 
 
 
drop table t1;

Comment by Adrian [ 2022-11-23 ]

tested versions:
10.6.9 OK
10.6.10,10.6.11 truncated

SELECT
tabl1.col1,
"some_value1",
0,
"{}",
JSON_SET('{}',"$.key1",IF(JSON_VALUE(tabl1.col2), "$.key2")< 100, 1, 0)) ,
UNIX_TIMESTAMP(),
UNIX_TIMESTAMP()
FROM tabl1
INNER JOIN `tabl2` ON `tabl2`.`col1` = `tabl1`.`col1` and `tabl2`.`col2`=`tabl1`.`col3`
INNER JOIN `tabl3` ON `tabl3`.`col1` = `tabl2`.`col3`
WHERE `tabl3`.`col2` = "some_val"
AND `tabl2`.`col4` = "some_val"
AND `tabl1`.`col6` = "some val"
GROUP BY tabl1.col1

if i make only the select is working
if i make select and first join is working
if i make select and both join is working
if i make select and both join and GROUP BY is NOT working
if i make select and first join and GROUP BY is working

WHERE doesn't seem to impact anything

working meaning the JSON_SET('{}',"$.key1",IF(JSON_VALUE(tabl1.col2), "$.key2")< 100, 1, 0)) returns correct json(

{"key1": 0}

), not truncated ({"key"

Comment by Janez Resnik [ 2022-11-23 ]

In ver. 10.3 and 10.7.7, https://onecompiler.com/mariadb/3yptrhewz

JSON_COMPACT fix seems to work:

select version();
 
 
 
CREATE TABLE t1 ( id int, a int);
 
INSERT INTO t1  VALUES (1, NULL), (2, 42);
 
  
SELECT t1.id, json_object('nullable', t1.a IS NULL, 'quaz', (SELECT 1 < 0))
 
FROM t1
 
GROUP BY t1.id;
 
# wrong
 
 
 
SELECT t1.id, json_compact(json_object('nullable', t1.a IS NULL, 'quaz', (SELECT 1 < 0))) 
FROM t1
 
GROUP BY t1.id;
 
# ok
 
 
 
drop table t1;

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