[MDEV-17578] JSON_ARRAY returns list of String when given a list of integer by passing in variables in stored procedure Created: 2018-10-31  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.3.8, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Stevan0102 Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None
Environment:

MacBook Pro (13-inch, 2017, Two Thunderbolt 3 ports)
OS High Sierra V10.13.6


 Description   

When I try to use JSON_ARRAY in a stored procedure, it outputs an array of String even though a list of Integer was given. 

DELIMITER //
 
 
 
DROP PROCEDURE IF EXISTS test;
 
 
 
CREATE PROCEDURE test(OUT testOutput TEXT)
 
BEGIN
 
  DECLARE C1 INT;
 
  DECLARE C2 INT;
 
 
 
  select count(*) from `tb1` into C1;
 
  select count(*) from `tb2` into C2;
 
 
 
  select JSON_ARRAY(C1
 
           , C2) INTO testOutput;
 
 
 
END;
 
//
 
 
 
DELIMITER ;
 
 
 
CALL test(@testOutput);
 
 
 
SELECT @testOutput;

output is : ["1", "2"]

If I change the last line into:

select JSON_ARRAY((select count(*) from `tb1`)
 
           , C2) INTO testOutput;

output is correct as : [ 1, 2 ]

 Comments   
Comment by Alice Sherepa [ 2018-11-02 ]

Thanks for the report! Reproducible with "utf8", not with "latin1".

set names utf8;
 
create table tb1 (i int);
 
	insert into tb1 values (1);
 
create table tb2 (i int);
 
	insert into tb2 values (1),(2);
 
 
 
DELIMITER //;
 
CREATE PROCEDURE test(out t1 text, out t2 text)
 
BEGIN
 
  DECLARE C1 INT;
 
  DECLARE C2 INT;
 
 
  select count(*) from tb1 into C1;
 
  select count(*) from tb2 into C2;
 
 	select JSON_ARRAY(C1, C2) into t1;
 
	select JSON_ARRAY((select count(*) from `tb1`), C2) INTO t2;
 
END; //
 
 
DELIMITER ;//
 
 
 
CALL test(@t1,@t2);
 
select @t1,@t2;
 
 
 
drop table tb1,tb2;
 
drop procedure test;


MariaDB [test]> CALL test(@t1,@t2);
 
Query OK, 4 rows affected (0.001 sec)
 
 
 
MariaDB [test]> select @t1,@t2;
 
+------------+--------+
 
| @t1        | @t2    |
 
+------------+--------+
 
| ["1", "2"] | [1, 2] |
 
+------------+--------+
 
1 row in set (0.000 sec)

Generated at Thu Feb 08 08:37:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.