1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
SET sql_mode=ORACLE;
#
# MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters
#
CREATE TABLE t1 (a INT, b TEXT, c ENUM('a','b','c'));
CREATE PROCEDURE p1 (a t1%ROWTYPE) AS
BEGIN
CREATE TABLE t2 AS SELECT a.a AS a, a.b AS b, a.c AS c;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
CREATE PROCEDURE p2 AS
a t1%ROWTYPE;
BEGIN
CALL p1(a);
END;
$$
CALL p2();
Table Create Table
t2 CREATE TABLE "t2" (
"a" int(11) DEFAULT NULL,
"b" text DEFAULT NULL,
"c" char(1) DEFAULT NULL
)
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP TABLE t1;
CREATE TABLE t1 (a INT, b TEXT);
CREATE PROCEDURE p1 (a OUT t1%ROWTYPE) AS
BEGIN
SET a.a=10;
SET a.b='text';
END;
$$
CREATE PROCEDURE p2 AS
a t1%ROWTYPE;
BEGIN
CALL p1(a);
SELECT a.a, a.b;
END;
$$
CREATE FUNCTION f1(a t1%ROWTYPE) RETURN TEXT AS
BEGIN
RETURN CONCAT(a.a, ' ', a.b);
END;
$$
CREATE FUNCTION f2 RETURN TEXT AS
a t1%ROWTYPE;
BEGIN
CALL p1(a);
RETURN f1(a);
END;
$$
CALL p2();
a.a a.b
10 text
SELECT f2();
f2()
10 text
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP FUNCTION f2;
DROP FUNCTION f1;
DROP TABLE t1;
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT, b TEXT);
CREATE PROCEDURE p1 (a OUT db1.t1%ROWTYPE) AS
BEGIN
SET a.a=10;
SET a.b='text';
END;
$$
CREATE PROCEDURE p2 AS
a db1.t1%ROWTYPE;
BEGIN
CALL p1(a);
SELECT a.a, a.b;
END;
$$
CREATE FUNCTION f1(a db1.t1%ROWTYPE) RETURN TEXT AS
BEGIN
RETURN CONCAT(a.a, ' ', a.b);
END;
$$
CREATE FUNCTION f2() RETURN TEXT AS
a db1.t1%ROWTYPE;
BEGIN
CALL p1(a);
RETURN f1(a);
END;
$$
CALL p2();
a.a a.b
10 text
SELECT f2();
f2()
10 text
DROP PROCEDURE p2;
DROP PROCEDURE p1;
DROP FUNCTION f2;
DROP FUNCTION f1;
DROP DATABASE db1;
#
# MDEV-14139 Anchored data types for variables
#
CREATE TABLE t1 (int11 INT, text0 TEXT);
DECLARE
row1 t1%ROWTYPE;
a_row1 row1%TYPE;
aa_row1 a_row1%TYPE;
BEGIN
CREATE TABLE t2 AS SELECT a_row1.int11 AS int11, a_row1.text0 AS text0;
SHOW CREATE TABLE t2;
DROP TABLE t2;
CREATE TABLE t2 AS SELECT aa_row1.int11 AS int11, aa_row1.text0 AS text0;
SHOW CREATE TABLE t2;
DROP TABLE t2;
END;
$$
Table Create Table
t2 CREATE TABLE "t2" (
"int11" int(11) DEFAULT NULL,
"text0" text DEFAULT NULL
)
Table Create Table
t2 CREATE TABLE "t2" (
"int11" int(11) DEFAULT NULL,
"text0" text DEFAULT NULL
)
DROP TABLE t1;
|
