[MDEV-31958] JSON_OBJECT() doesn't honour datatype in function Created: 2023-08-18  Updated: 2023-10-06

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.10
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Martín Alfano Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux

Attachments: PNG File screenshot-1.png    

 Description   

Also happens with json_array()

DELIMITER //
 
CREATE OR REPLACE FUNCTION json_function_test ( a INT )
 
    RETURNS JSON
 
BEGIN
 
    DECLARE `_id` INT UNSIGNED;
 
    DECLARE `_id2` INT UNSIGNED;
 
    DECLARE `_id3` INT UNSIGNED DEFAULT 9;
 
 
 
    SET @_temp = UNIX_TIMESTAMP();
 
    SET _id    = UNIX_TIMESTAMP();
 
    SET _id2   = 444;
 
 
 
    return JSON_OBJECT("argument_int", a, "_id",_id, "_id2",_id2, "_id3", _id3, "temp_var", @_temp) ;
 
END;//
 
DELIMITER ;
 
 
 
select json_function_test(123);

Everything is a string:

{"argument_int": "123", "_id": "1692396866", "_id2": "444", "_id3": "9", "temp_var": "1692396866"}


select JSON_OBJECT("temp_var",@_temp, "a",UNIX_TIMESTAMP());
 
| {"temp_var": "1692396982", "a": 1692397120}          |

---------------

In 10.3 in the other hand, the results are different:

select JSON_OBJECT("temp_var",@_temp, "a",UNIX_TIMESTAMP());
 
| {"temp_var": 1692397171, "a": 1692397176}            |



 Comments   
Comment by Sergei Golubchik [ 2023-09-05 ]

I cannot repeat it, I'm getting

{"argument_int": 123, "_id": 1693949532, "_id2": 444, "_id3": 9, "temp_var": 1693949532}

may be there's some important detail I'm missing? but simply creating the function as you provided and running it as you provided gives me the correct result as above

Comment by Martín Alfano [ 2023-09-05 ]

Strange... some unknown factor is changing the result then...

I've just copy/pasted it again and the result is the same, I'll add a screenshot.

Let me know if you want me to try something else.

Comment by Sergei Golubchik [ 2023-09-05 ]

What do you get for

show variables like 'character%';
 
show variables like 'collation%';

Comment by Martín Alfano [ 2023-09-05 ] 

show variables like 'character%';
 
+--------------------------+----------------------------+
 
| Variable_name            | Value                      |
 
+--------------------------+----------------------------+
 
| character_set_client     | utf8mb3                    |
 
| character_set_connection | utf8mb3                    |
 
| character_set_database   | utf8mb4                    |
 
| character_set_filesystem | binary                     |
 
| character_set_results    | utf8mb3                    |
 
| character_set_server     | utf8mb4                    |
 
| character_set_system     | utf8mb3                    |
 
| character_sets_dir       | /usr/share/mysql/charsets/ |
 
+--------------------------+----------------------------+
 
 
 
 
 
show variables like 'collation%';
 
+----------------------+--------------------+
 
| Variable_name        | Value              |
 
+----------------------+--------------------+
 
| collation_connection | utf8mb3_general_ci |
 
| collation_database   | utf8mb4_general_ci |
 
| collation_server     | utf8mb4_general_ci |
 
+----------------------+--------------------+

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