[MDEV-29867] A column is extracted as NULL in a stored procedure when the procedure has an output param of the same name Created: 2022-10-24  Updated: 2022-10-26  Resolved: 2022-10-26

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.5, 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Assen Totin (Inactive) Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 1
Labels: None


 Description   

If a stored procedure contains an output parameter with a certain name, and if the procedure selects a column of with the same name, the selected value is reported inside the procedure as NULL.

The issue also present when the SELECT is run in a cursor with a subsequent FETCH - the fetched value is NULL.

The error occurs on any data type (the example below shows JSON, but it also happens on INT etc.)

Example:

CREATE TABLE `t` (
 
  `id` int(11) DEFAULT NULL,
 
  `value` JSON
 
) ENGINE=InnoDB
 
 
 
INSERT INTO t VALUES (1, '[42]');
 
INSERT INTO t VALUES (2, '[69]');
 
 
 
CREATE OR REPLACE PROCEDURE TEST_JSON()
 
    BEGIN
 
        SELECT id, value FROM t;
 
    END;
 
 
 
MariaDB [test]> call TEST_JSON();
 
+------+-------+
 
| id   | value |
 
+------+-------+
 
|    1 | [42]  |
 
|    1 | [69]  |
 
+------+-------+
 
 
 
CREATE OR REPLACE PROCEDURE TEST_JSON(OUT value JSON)
 
    BEGIN
 
        SELECT id, value FROM t;
 
    END;
 
 
 
MariaDB [test]> call TEST_JSON(@a);
 
+------+-------+
 
| id   | value |
 
+------+-------+
 
|    1 | NULL  |
 
|    1 | NULL  |
 
+------+-------+



 Comments   
Comment by Sergei Golubchik [ 2022-10-26 ]

You see NULL because in

CREATE OR REPLACE PROCEDURE TEST_JSON(OUT value JSON)
 
    BEGIN
 
        SELECT id, value FROM t;
 
    END;

the second column is not a table's column, but a stored procedure parameter.

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