oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# Test file for stored procedure bugfixes
5

6
#owner: linlin.xll
7
#owner group: sql1
8
#description: test pl transformed
9
#tags: pl, funcs
10

11
--result_format 4
12

13
--echo #
14
--echo # Bug #47412: Valgrind warnings / user can read uninitalized memory
15
--echo # using SP variables
16
--echo #
17

18
--disable_warnings
19
DROP SCHEMA IF EXISTS testdb;
20
--enable_warnings
21

22
CREATE SCHEMA testdb;
23
USE testdb;
24
DELIMITER |;
25

26
CREATE FUNCTION f2 () RETURNS INTEGER
27
BEGIN
28
   DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1;
29
   RETURN f_not_exists () ;
30
END|
31
CREATE PROCEDURE p3 ( arg1 VARCHAR(32) )
32
BEGIN
33
   CALL p_not_exists();
34
END|
35
DELIMITER ;|
36
--echo # should not return valgrind warnings
37
### TODO : --error 1305 
38
--error 1321
39
CALL p3(f2());
40

41
DROP SCHEMA testdb;
42

43
CREATE SCHEMA testdb;
44
USE testdb;
45
DELIMITER |;
46

47
CREATE FUNCTION f2() RETURNS INTEGER
48
BEGIN
49
   DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1;
50
   RETURN f_not_exists();
51
END|
52
CREATE PROCEDURE p3(arg2 INTEGER)
53
BEGIN
54
   CALL p_not_exists();
55
END|
56
DELIMITER ;|
57
--echo # should not return valgrind warnings
58
### TODO : --error 1305 
59
--error 1321 
60
CALL p3(f2());
61

62
DROP SCHEMA testdb;
63

64
CREATE SCHEMA testdb;
65
USE testdb;
66
DELIMITER |;
67

68
CREATE FUNCTION f2 () RETURNS INTEGER
69
BEGIN
70
   DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @aux = 1;
71
   RETURN f_not_exists () ;
72
END|
73
DELIMITER ;|
74
--echo # should not return valgrind warnings
75
### TODO : --error 1305 
76
--error 1321
77
SELECT f2();
78

79
DROP SCHEMA testdb;
80

81
USE test;
82

83
--echo #
84
--echo # Bug#50423: Crash on second call of a procedure dropping a trigger
85
--echo #
86

87
--disable_warnings
88
## coeanbase not support trigger yet !
89
##
90
## DROP TABLE IF EXISTS t1;
91
## DROP TRIGGER IF EXISTS tr1;
92
## DROP PROCEDURE IF EXISTS p1;
93
## --enable_warnings
94
## 
95
## CREATE TABLE t1 (f1 INTEGER);
96
## CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1;
97
## CREATE PROCEDURE p1 () DROP TRIGGER tr1;
98
## 
99
## CALL p1 ();
100
## --error ER_TRG_DOES_NOT_EXIST
101
## CALL p1 ();
102
## 
103
## DROP TABLE t1;
104
## DROP PROCEDURE p1;
105
## 
106
## --echo #
107
## --echo # Bug#50423: Crash on second call of a procedure dropping a trigger
108
## --echo #
109
## 
110
## --disable_warnings
111
## DROP TABLE IF EXISTS t1;
112
## DROP TRIGGER IF EXISTS tr1;
113
## DROP PROCEDURE IF EXISTS p1;
114
## --enable_warnings
115
## 
116
## CREATE TABLE t1 (f1 INTEGER);
117
## CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1;
118
## CREATE PROCEDURE p1 () DROP TRIGGER tr1;
119
## 
120
## CALL p1 ();
121
## --error ER_TRG_DOES_NOT_EXIST
122
## CALL p1 ();
123
## 
124
## DROP TABLE t1;
125
## DROP PROCEDURE p1;
126

127
--echo #
128
--echo # Bug#54375: Error in stored procedure leaves connection
129
--echo # in different default schema
130
--echo #
131

132
--disable_warnings
133
SET @SQL_MODE_SAVE = @@SQL_MODE;
134
SET @@SQL_MODE = 'STRICT_ALL_TABLES';
135
DROP DATABASE IF EXISTS db1;
136
CREATE DATABASE db1;
137
USE db1;
138
DROP TABLE IF EXISTS t1;
139
CREATE TABLE t1 (c1 int NOT NULL PRIMARY KEY);
140
INSERT INTO t1 VALUES (1);
141
DELIMITER $$;
142
CREATE FUNCTION f1 (
143
	some_value int
144
)
145
RETURNS smallint
146
DETERMINISTIC
147
BEGIN
148
	INSERT INTO t1 SET c1 = some_value;
149
        RETURN(LAST_INSERT_ID());
150
END$$
151
DELIMITER ;$$
152
DROP DATABASE IF EXISTS db2;
153
CREATE DATABASE db2;
154
--enable_warnings
155
USE db2;
156
SELECT DATABASE();
157
--error ER_DUP_ENTRY,1235
158
SELECT db1.f1(1);
159
SELECT DATABASE();
160
USE test;
161
DROP FUNCTION db1.f1;
162
DROP TABLE db1.t1;
163
DROP DATABASE db1;
164
DROP DATABASE db2;
165

166
--echo #
167
--echo # Bug#13105873:valgrind warning:possible crash in foreign 
168
--echo # key handling on subsequent create table if not exists 
169
--echo #
170

171
## oceanbase not support foreign key yet!
172
## --disable_warnings
173
## DROP DATABASE IF EXISTS testdb;
174
## --enable_warnings
175
## CREATE DATABASE testdb;
176
## USE testdb;
177
## CREATE TABLE t1 (id1 INT PRIMARY KEY);
178
## DELIMITER $;
179
## CREATE PROCEDURE `p1`()
180
## BEGIN
181
##     CREATE TABLE IF NOT EXISTS t2(id INT PRIMARY KEY,
182
##     CONSTRAINT FK FOREIGN KEY (id) REFERENCES t1( id1 ));
183
## END$
184
## DELIMITER ;$
185
## CALL p1();
186
## --echo # below stmt should not return valgrind warnings
187
## CALL p1(); 
188
## DROP DATABASE testdb;
189
## USE test;
190

191
--echo End of 5.1 tests
192

193
--echo #
194
--echo # BUG#13489996 valgrind:conditional jump or move depends on
195
--echo # uninitialised values-field_blob
196
--echo #
197

198
## oceanbase not support BLOB yet!
199
## CREATE FUNCTION sf() RETURNS BLOB RETURN "";
200
## SELECT sf();
201
## DROP FUNCTION sf;
202

203
--echo #
204
--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
205
--echo #
206
SET @@SQL_MODE = '';
207
DELIMITER $;
208
CREATE FUNCTION testf_bug11763507() RETURNS INT
209
BEGIN
210
    RETURN 0;
211
END
212
$
213

214
CREATE PROCEDURE testp_bug11763507()
215
BEGIN
216
    SELECT "PROCEDURE testp_bug11763507";
217
END
218
$
219

220
DELIMITER ;$
221

222
# STORED FUNCTIONS
223
SELECT testf_bug11763507();
224
SELECT TESTF_bug11763507();
225

226
#SHOW FUNCTION STATUS WHERE NAME='TESTF_bug11763507';
227

228
#SHOW CREATE FUNCTION testf_bug11763507;
229
#SHOW CREATE FUNCTION TESTF_bug11763507;
230

231
# STORED PROCEDURE
232
CALL testp_bug11763507();
233
CALL TESTP_bug11763507();
234

235
#SHOW PROCEDURE STATUS WHERE NAME='TESTP_bug11763507';
236

237
#SHOW CREATE PROCEDURE testp_bug11763507;
238
#SHOW CREATE PROCEDURE TESTP_bug11763507;
239

240
# INFORMATION SCHEMA 
241
SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'testf_bug11763507';
242
SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name LIKE 'TESTF_bug11763507';
243

244
SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='testf_bug11763507';
245
SELECT specific_name FROM INFORMATION_SCHEMA.ROUTINES WHERE specific_name='TESTF_bug11763507';
246

247
DROP PROCEDURE testp_bug11763507;
248
DROP FUNCTION testf_bug11763507;
249

250
SET @@SQL_MODE = @SQL_MODE_SAVE;
251

252
--echo #END OF BUG#11763507 test.
253