Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL)
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-10914
ROW data type for stored routine variables
-
- Closed
-
-
MDEV-12252
ROW data type for stored function return values
-
- Closed
-
-
MDEV-14139
Anchored data types for variables
-
- Closed
-
-
MDEV-14212
Add Field_row for SP ROW variables
-
- Closed
-