3
drop table if exists t1,t2;
8
set @a := connection_id() + 3;
9
select @a - connection_id();
14
# Check using and setting variables with SELECT DISTINCT
16
CREATE TABLE t1 ( i int not null, v int not null,index (i));
17
insert into t1 values (1,1),(1,3),(2,1);
18
create table t2 (i int not null, unique (i));
19
insert into t2 select distinct i from t1;
21
select distinct t2.i,@vv1:=if(sv1.i,1,0),@vv2:=if(sv2.i,1,0),@vv3:=if(sv3.i,1,0), @vv1+@vv2+@vv3 from t2 left join t1 as sv1 on sv1.i=t2.i and sv1.v=1 left join t1 as sv2 on sv2.i=t2.i and sv2.v=2 left join t1 as sv3 on sv3.i=t2.i and sv3.v=3;
22
explain select * from t1 where i=@vv1;
23
select @vv1,i,v from t1 where i=@vv1;
24
explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
25
explain select @vv1:=i from t1 where i=@vv1;
26
explain select * from t1 where i=@vv1;
29
# Check types of variables
31
select @a:=10, @b:=1, @a > @b, @a < @b;
32
# Note that here a and b will be avaluated as number
33
select @a:="10", @b:="1", @a > @b, @a < @b;
34
# Note that here a and b will be avaluated as strings
35
select @a:=10, @b:=2, @a > @b, @a < @b;
36
select @a:="10", @b:="2", @a > @b, @a < @b;
42
create table t1 (id int, d double, c char(10));
43
insert into t1 values (1,2.0, "test");
45
update t1 SET id=(@c:=@c+1);
48
update t1 set id=(@c:=@c+1);
52
select @d,(@d:=id),@d from t1;
53
select @e,(@e:=d),@e from t1;
54
select @f,(@f:=c),@f from t1;
56
select @g,(@g:=c),@g from t1;
57
select @c, @d, @e, @f;
58
select @d:=id, @e:=id, @f:=id, @g:=@id from t1;
59
select @c, @d, @e, @f, @g;
63
select @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b, @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b;
67
# Item_func_set_user_var sets update_query_id, Item_func_get_user_var checks it
69
create table t1 (i int not null);
70
insert t1 values (1),(2),(2),(3),(3),(3);
71
select @a:=0; select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
72
select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
75
select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
76
select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
80
# Bug #2244: User variables didn't copy collation and derivation
81
# attributes from values they were initialized to.
85
select charset(@a),collation(@a),coercibility(@a);
86
select @a=_latin2'TEST';
87
select @a=_latin2'TEST' collate latin2_bin;
89
set @a=_latin2'test' collate latin2_general_ci;
90
select charset(@a),collation(@a),coercibility(@a);
91
select @a=_latin2'TEST';
92
select @a=_latin2'TEST' collate latin2_bin;
95
# Check the same invoking Item_set_user_var
97
select charset(@a:=_latin2'test');
98
select collation(@a:=_latin2'test');
99
select coercibility(@a:=_latin2'test');
100
select collation(@a:=_latin2'test' collate latin2_bin);
101
select coercibility(@a:=_latin2'test' collate latin2_bin);
102
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST';
103
select charset(@a),collation(@a),coercibility(@a);
104
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci;
107
# Bug #6321 strange error:
108
# string function FIELD(<uservariable content NULL>, ...)
111
select FIELD( @var,'1it','Hit') as my_column;
114
# Bug#9425 A user variable doesn't always have implicit coercibility
116
select @v, coercibility(@v);
117
set @v1=null, @v2=1, @v3=1.1, @v4=now();
118
select coercibility(@v1),coercibility(@v2),coercibility(@v3),coercibility(@v4);
121
# Bug #9286 SESSION/GLOBAL should be disallowed for user variables
124
set session @honk=99;
126
set one_shot @honk=99;
129
# Bug #10724 @@local not preserved in column name of select
131
# The value doesn't actually matter, we just care about the column name
133
select @@local.max_allowed_packet;
135
select @@session.max_allowed_packet;
137
select @@global.max_allowed_packet;
139
select @@max_allowed_packet;
141
select @@Max_Allowed_Packet;
145
select @@global.version;
147
--echo End of 4.1 tests
149
# Bug #6598: problem with cast(NULL as signed integer);
152
set @first_var= NULL;
153
create table t1 select @first_var;
154
show create table t1;
156
set @first_var= cast(NULL as signed integer);
157
create table t1 select @first_var;
158
show create table t1;
160
set @first_var= NULL;
161
create table t1 select @first_var;
162
show create table t1;
164
set @first_var= concat(NULL);
165
create table t1 select @first_var;
166
show create table t1;
169
set @first_var= cast(NULL as CHAR);
170
create table t1 select @first_var;
171
show create table t1;
175
# Bug #7498 User variable SET saves SIGNED BIGINT as BIGINT
178
# First part, set user var to large number and select it
179
set @a=18446744071710965857;
182
# Second part, set user var from large number in table
184
CREATE TABLE `bigfailure` (
185
`afield` BIGINT NOT NULL
187
INSERT INTO `bigfailure` VALUES (18446744071710965857);
188
SELECT * FROM bigfailure;
189
select * from (SELECT afield FROM bigfailure) as b;
190
select * from bigfailure where afield = (SELECT afield FROM bigfailure);
191
select * from bigfailure where afield = 18446744071710965857;
192
# This is fixed in 5.0, to be uncommented there
193
#select * from bigfailure where afield = '18446744071710965857';
194
select * from bigfailure where afield = 18446744071710965856+1;
196
SET @a := (SELECT afield FROM bigfailure);
198
SET @a := (select afield from (SELECT afield FROM bigfailure) as b);
200
SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure));
203
drop table bigfailure;
206
# Bug#16861: User defined variable can have a wrong value if a tmp table was
209
create table t1(f1 int, f2 int);
210
insert into t1 values (1,2),(2,3),(3,1);
211
select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
213
create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
219
# Bug#19024 - SHOW COUNT(*) WARNINGS not return Errors
222
insert into city 'blah';
223
SHOW COUNT(*) WARNINGS;
224
SHOW COUNT(*) ERRORS;
227
# Bug#28494: Grouping by Item_func_set_user_var produces incorrect result.
229
create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1));
230
insert into t1 values
231
(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6),
232
(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6),
233
(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6);
234
select @a:=f1, count(f1) from t1 group by 1 desc;
235
select @a:=f1, count(f1) from t1 group by 1 asc;
236
select @a:=f2, count(f2) from t1 group by 1 desc;
237
select @a:=f3, count(f3) from t1 group by 1 desc;
238
select @a:=f4, count(f4) from t1 group by 1 desc;
242
# Bug#32482: Crash for a query with ORDER BY a user variable.
244
create table t1 (f1 int);
245
insert into t1 values (2), (1);
246
select @i := f1 as j from t1 order by 1;
248
# Bug #32260: User variables in query cause server crash
250
create table t1(a int);
251
insert into t1 values(5),(4),(4),(3),(2),(2),(2),(1);
254
set @prev_score := NULL;
255
# Disable the result log as we assign a value to a user variable in one part
256
# of a statement and use the same variable in other part of the same statement,
257
# so we can get unexpected results.
259
select @rownum := @rownum + 1 as row,
260
@rank := IF(@prev_score!=a, @rownum, @rank) as rank,
261
@prev_score := a as score
262
from t1 order by score desc;
266
--echo End of 5.1 tests
added
removed
tests/t/user_var.test
