[MDEV-31250] ROW variables do not get assigned from subselects - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 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)
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
Component/s: Data Manipulation - Subquery, Data types, Stored routines
Labels:
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

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2023-05-12 07:28

Alexander Barkov made changes - 2023-05-12 07:28

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-14139~~ [ ~~MDEV-14139~~ ]

Alexander Barkov made changes - 2023-05-12 07:28

Link

This issue relates to ~~MDEV-14212~~ [ ~~MDEV-14212~~ ]

Alexander Barkov made changes - 2023-05-12 07:29

Link

This issue relates to ~~MDEV-10914~~ [ ~~MDEV-10914~~ ]

Alexander Barkov made changes - 2023-05-12 07:30

Description

This script using the SELECT..INTO syntax correctly initializes a ROW variable:
{code:sql}
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 ;
{code}
{noformat}
+------+------+
| r.a | r.b |
+------+------+
| 1 | b1 |
+------+------+
{noformat}
so far so good.

However if I modify the script slightly and replace SELECT..INTO to an equivalent SET, it does not work:
{code:sql}
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 ;
{code}
{noformat}
+------+------+
| r.a | r.b |
+------+------+
| NULL | NULL |
+------+------+
{noformat}

This equivalent script with a subselect in the DEFAULT clause of a variable declaration also does not work:
{code:sql}
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 ;
{code}
{noformat}
+------+------+
| r.a | r.b |
+------+------+
| NULL | NULL |
+------+------+
{noformat}

It should be fixed to have the secord and the third script produce equal results to the first script.

This script using the SELECT..INTO syntax correctly initializes a ROW variable:
{code:sql}
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 ;
{code}
{noformat}
+------+------+
| r.a | r.b |
+------+------+
| 1 | b1 |
+------+------+
{noformat}
so far so good.

However if I modify the script slightly and replace SELECT..INTO to an equivalent SET, it does not work:
{code:sql}
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 ;
{code}
{noformat}
+------+------+
| r.a | r.b |
+------+------+
| NULL | NULL |
+------+------+
{noformat}

Another equivalent script with a subselect in the DEFAULT clause of a variable declaration also does not work:
{code:sql}
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 ;
{code}
{noformat}
+------+------+
| r.a | r.b |
+------+------+
| NULL | NULL |
+------+------+
{noformat}

It should be fixed to have the secord and the third script produce equal results to the first script.

Alexander Barkov made changes - 2023-05-12 08:51

Priority

Major [ 3 ]

Critical [ 2 ]

Alexander Barkov made changes - 2023-05-12 08:52

issue.field.resolutiondate

2023-05-12 08:52:37.0

2023-05-12 08:52:37.471

Alexander Barkov made changes - 2023-05-12 08:52

Fix Version/s		11.0.2 [ 28706 ]
Fix Version/s		11.1.1 [ 28704 ]
Fix Version/s		10.4.30 [ 28912 ]
Fix Version/s		10.5.21 [ 28913 ]
Fix Version/s		10.6.14 [ 28914 ]
Fix Version/s		10.8.9 [ 28915 ]
Fix Version/s		10.9.7 [ 28916 ]
Fix Version/s		10.10.5 [ 28917 ]
Fix Version/s		10.11.4 [ 28918 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Julien Fritsch made changes - 2023-05-15 12:00

Fix Version/s

10.8.9 [ 28915 ]

Julien Fritsch made changes - 2023-06-06 07:11

Assignee

Alexander Barkov [ bar ]

Daniel Bartholomew made changes - 2023-06-07 12:49

Fix Version/s		10.4.31 [ 29010 ]
Fix Version/s		10.5.22 [ 29011 ]
Fix Version/s		10.6.15 [ 29013 ]
Fix Version/s		10.9.8 [ 29015 ]
Fix Version/s		10.10.6 [ 29017 ]
Fix Version/s		10.11.5 [ 29019 ]
Fix Version/s		11.0.3 [ 28920 ]
Fix Version/s		11.1.2 [ 28921 ]
Fix Version/s	11.1.1 [ 28704 ]
Fix Version/s	11.0.2 [ 28706 ]
Fix Version/s	10.4.30 [ 28912 ]
Fix Version/s	10.5.21 [ 28913 ]
Fix Version/s	10.6.14 [ 28914 ]
Fix Version/s	10.9.7 [ 28916 ]
Fix Version/s	10.10.5 [ 28917 ]
Fix Version/s	10.11.4 [ 28918 ]

Alexander Barkov made changes - 2023-09-25 17:50

Link

This issue relates to ~~MDEV-12252~~ [ ~~MDEV-12252~~ ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-05-12 07:28

Updated:: 2023-09-25 17:50

Resolved:: 2023-05-12 08:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration