Debian Codegraph @

mysql-test/suite/engines/funcs/r/rpl_user_variables.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 reset master;
5 connection master;
6 create table t1(n char(30));
7 set @i1:=12345678901234, @i2:=-12345678901234, @i3:=0, @i4:=-1;
8 set @s1:='This is a test', @r1:=12.5, @r2:=-12.5;
9 set @n1:=null;
10 set @s2:='', @s3:='abc\'def', @s4:= 'abc\\def', @s5:= 'abc''def';
11 insert into t1 values (@i1), (@i2), (@i3), (@i4);
12 insert into t1 values (@r1), (@r2);
13 insert into t1 values (@s1), (@s2), (@s3), (@s4), (@s5);
14 insert into t1 values (@n1);
15 insert into t1 values (@n2);
16 insert into t1 values (@a:=0), (@a:=@a+1), (@a:=@a+1);
17 insert into t1 values (@a+(@b:=@a+1));
18 set @q:='abc';
19 insert t1 values (@q), (@q:=concat(@q, 'n1')), (@q:=concat(@q, 'n2'));
20 set @a:=5;
21 insert into t1 values (@a),(@a);
22 select * from t1 where n = '<nonexistant>';
23 n
24 connection master1;
25 insert into t1 values (@a),(@a),(@a*5);
26 SELECT * FROM t1 ORDER BY n;
27 n
28 NULL
29 NULL
30 NULL
31 NULL
32 NULL
33
34 -1
35 -12.5
36 -12345678901234
37 0
38 0
39 1
40 12.5
41 12345678901234
42 2
43 5
44 5
45 5
46 abc
47 abc'def
48 abc'def
49 abc\def
50 abcn1
51 abcn1n2
52 This is a test
53 connection slave;
54 SELECT * FROM t1 ORDER BY n;
55 n
56 NULL
57 NULL
58 NULL
59 NULL
60 NULL
61
62 -1
63 -12.5
64 -12345678901234
65 0
66 0
67 1
68 12.5
69 12345678901234
70 2
71 5
72 5
73 5
74 abc
75 abc'def
76 abc'def
77 abc\def
78 abcn1
79 abcn1n2
80 This is a test
81 connection master;
82 insert into t1 select * FROM (select @var1 union select @var2) AS t2;
83 drop table t1;
84 End of 4.1 tests.
85 DROP TABLE IF EXISTS t20;
86 DROP TABLE IF EXISTS t21;
87 DROP PROCEDURE IF EXISTS test.insert;
88 CREATE TABLE t20 (a VARCHAR(20));
89 CREATE TABLE t21 (a VARCHAR(20));
90 CREATE PROCEDURE test.insert()
91 BEGIN
92 IF (@VAR)
93 THEN
94 INSERT INTO test.t20 VALUES ('SP_TRUE');
95 ELSE
96 INSERT INTO test.t20 VALUES ('SP_FALSE');
97 END IF;
98 END|
99 CREATE TRIGGER test.insert_bi BEFORE INSERT
100 ON test.t20 FOR EACH ROW
101 BEGIN
102 IF (@VAR)
103 THEN
104 INSERT INTO test.t21 VALUES ('TRIG_TRUE');
105 ELSE
106 INSERT INTO test.t21 VALUES ('TRIG_FALSE');
107 END IF;
108 END|
109 connection slave;
110 connection master;
111 SET @VAR=0;
112 CALL test.insert();
113 SET @VAR=1;
114 CALL test.insert();
115 Check the tables for correct data
116 SELECT * FROM t20;
117 a
118 SP_FALSE
119 SP_TRUE
120 SELECT * FROM t21;
121 a
122 TRIG_FALSE
123 TRIG_TRUE
124 connection slave;
125 Check the tables for correct data and it matches master
126 SELECT * FROM t20;
127 a
128 SP_FALSE
129 SP_TRUE
130 SELECT * FROM t21;
131 a
132 TRIG_FALSE
133 TRIG_TRUE
134 connection master;
135 DROP TABLE t20;
136 DROP TABLE t21;
137 DROP PROCEDURE test.insert;
138 DROP TABLE IF EXISTS t1;
139 DROP FUNCTION IF EXISTS test.square;
140 CREATE TABLE t1 (i INT);
141 CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN
142 (@var * @var);
143 SET @var = 1;
144 INSERT INTO t1 VALUES (square());
145 SET @var = 2;
146 INSERT INTO t1 VALUES (square());
147 SET @var = 3;
148 INSERT INTO t1 VALUES (square());
149 SET @var = 4;
150 INSERT INTO t1 VALUES (square());
151 SET @var = 5;
152 INSERT INTO t1 VALUES (square());
153 Retrieve the values from the table
154 SELECT * FROM t1;
155 i
156 1
157 4
158 9
159 16
160 25
161 connection slave;
162 Retrieve the values from the table and verify they are the same as on master
163 SELECT * FROM t1;
164 i
165 1
166 4
167 9
168 16
169 25
170 connection master;
171 DROP TABLE t1;
172 DROP FUNCTION test.square;
173 DROP TABLE IF EXISTS t1;
174 DROP FUNCTION IF EXISTS f1;
175 DROP FUNCTION IF EXISTS f2;
176 CREATE TABLE t1(a int);
177 CREATE FUNCTION f1() returns int deterministic BEGIN
178 return @a;
179 END |
180 CREATE FUNCTION f2() returns int deterministic BEGIN
181 IF (@b > 0) then
182 SET @c = (@a + @b);
183 else
184 SET @c = (@a - 1);
185 END if;
186 return @c;
187 END |
188 connection slave;
189 connection master;
190 SET @a=500;
191 INSERT INTO t1 values(f1());
192 SET @b = 125;
193 SET @c = 1;
194 INSERT INTO t1 values(f2());
195 Retrieve the values from the table
196 connection slave;
197 connection master;
198 SELECT * from t1;
199 a
200 500
201 625
202 connection slave;
203 Check the tables for correct data and it matches master
204 SELECT * from t1;
205 a
206 500
207 625
208 connection master;
209 DROP TABLE t1;
210 DROP FUNCTION f1;
211 DROP FUNCTION f2;
212 DROP TABLE IF EXISTS t1;
213 DROP TABLE IF EXISTS t2;
214 CREATE TABLE t1 (i int);
215 CREATE TABLE t2 (k int);
216 CREATE trigger t1_bi before INSERT on t1 for each row BEGIN
217 INSERT INTO t2 values (@a);
218 SET @a:=42;
219 INSERT INTO t2 values (@a);
220 END |
221 connection slave;
222 connection master;
223 SET @a:=100;
224 INSERT INTO t1 values (5);
225 Check to see that data was inserted correctly in both tables
226 SELECT * from t1;
227 i
228 5
229 SELECT * from t2;
230 k
231 100
232 42
233 connection slave;
234 Check the tables for correct data and it matches master
235 SELECT * from t1;
236 i
237 5
238 SELECT * from t2;
239 k
240 100
241 42
242 connection master;
243 drop table t1, t2;
244 connection master;
245 create table t1(a int, b int);
246 prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
247 set @x=1;
248 execute s1 using @x;
249 select * from t1;
250 a b
251 2 1
252 connection slave;
253 connection slave;
254 select * from t1;
255 a b
256 2 1
257 connection master;
258 drop table t1;
259 connection master;
260 create table t1(a int);
261 insert into t1 values (1),(2);
262 prepare s1 from 'insert into t1 select a from t1 limit ?';
263 set @x='1.1';
264 execute s1 using @x;
265 select * from t1;
266 a
267 1
268 2
269 1
270 connection slave;
271 connection slave;
272 select * from t1;
273 a
274 1
275 2
276 1
277 connection master;
278 drop table t1;
279 End of 5.0 tests.
280 DROP FUNCTION IF EXISTS f1;
281 DROP FUNCTION IF EXISTS f2;
282 CREATE TABLE t1 (i INT);
283 CREATE FUNCTION f1() RETURNS INT RETURN @a;
284 CREATE
285 FUNCTION f2() RETURNS INT BEGIN
286 INSERT INTO t1 VALUES (10 + @a);
287 RETURN 0;
288 END|
289 connection slave;
290 connection master;
291 SET @a:=123;
292 SELECT f1(), f2();
293 f1() f2()
294 123 0
295 Check to see that data was inserted correctly
296 INSERT INTO t1 VALUES(f1());
297 SELECT * FROM t1;
298 i
299 133
300 123
301 connection slave;
302 Check the table for correct data and it matches master
303 SELECT * FROM t1;
304 i
305 133
306 123
307 connection master;
308 DROP FUNCTION f1;
309 DROP FUNCTION f2;
310 DROP TABLE t1;
311 connection slave;
312 include/rpl_end.inc