[MDEV-31250] ROW variables do not get assigned from subselects Created: 2023-05-12  Updated: 2023-09-25  Resolved: 2023-05-12

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Data types, Stored routines
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2

Type: Bug Priority: Critical
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-10914 ROW data type for stored routine vari... Closed
relates to MDEV-12252 ROW data type for stored function ret... In Review
relates to MDEV-14139 Anchored data types for variables Closed
relates to MDEV-14212 Add Field_row for SP ROW variables Closed

 Description   

This script using the SELECT..INTO syntax correctly initializes a ROW variable:

CREATE OR REPLACE TABLE t1 (a INT, b TEXT);
 
INSERT INTO t1 VALUES (1,'b1');
 
DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE r ROW TYPE OF t1;
 
  SELECT * INTO r FROM t1 WHERE a=1;
 
  SELECT r.a, r.b;
 
END;
 
$$
 
DELIMITER ;


+------+------+
 
| r.a  | r.b  |
 
+------+------+
 
|    1 | b1   |
 
+------+------+

so far so good.

However if I modify the script slightly and replace SELECT..INTO to an equivalent SET, it does not work:

CREATE OR REPLACE TABLE t1 (a INT, b TEXT);
 
INSERT INTO t1 VALUES (1,'b1');
 
DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE r ROW TYPE OF t1;
 
  SET r=(SELECT * FROM t1 WHERE a=1);
 
  SELECT r.a, r.b;
 
END;
 
$$
 
DELIMITER ;


+------+------+
 
| r.a  | r.b  |
 
+------+------+
 
| NULL | NULL |
 
+------+------+

Another equivalent script with a subselect in the DEFAULT clause of a variable declaration also does not work:

CREATE OR REPLACE TABLE t1 (a INT, b TEXT);
 
INSERT INTO t1 VALUES (1,'b1');
 
DELIMITER $$
 
BEGIN NOT ATOMIC
 
  DECLARE r ROW TYPE OF t1 DEFAULT (SELECT * FROM t1 WHERE a=1);
 
  SELECT r.a, r.b;
 
END;
 
$$
 
DELIMITER ;


+------+------+
 
| r.a  | r.b  |
 
+------+------+
 
| NULL | NULL |
 
+------+------+

It should be fixed to have the secord and the third script produce equal results to the first script.
Generated at Thu Feb 08 10:22:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.