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
|
# Initialise
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
--error 1054
set @a := foo;
set @a := connection_id() + 3;
select @a - connection_id();
set @b := 1;
select @b;
# Check using and setting variables with SELECT DISTINCT
CREATE TABLE t1 ( i int not null, v int not null,index (i));
insert into t1 values (1,1),(1,3),(2,1);
create table t2 (i int not null, unique (i));
insert into t2 select distinct i from t1;
select * from t2;
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;
explain select * from t1 where i=@vv1;
explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
explain select @vv1:=i from t1 where i=@vv1;
explain select * from t1 where i=@vv1;
drop table t1,t2;
# Check types of variables
set @a=0,@b=0;
select @a:=10, @b:=1, @a > @b, @a < @b;
# Note that here a and b will be avaluated as number
select @a:="10", @b:="1", @a > @b, @a < @b;
# Note that here a and b will be avaluated as strings
select @a:=10, @b:=2, @a > @b, @a < @b;
select @a:="10", @b:="2", @a > @b, @a < @b;
# Fixed bug #1194
select @a:=1;
select @a, @a:=1;
create table t1 (id int, d double, c char(10));
insert into t1 values (1,2.0, "test");
select @c:=0;
update t1 SET id=(@c:=@c+1);
select @c;
select @c:=0;
update t1 set id=(@c:=@c+1);
select @c;
select @c:=0;
select @c:=@c+1;
select @d,(@d:=id),@d from t1;
select @e,(@e:=d),@e from t1;
select @f,(@f:=c),@f from t1;
set @g=1;
select @g,(@g:=c),@g from t1;
select @c, @d, @e, @f;
select @d:=id, @e:=id, @f:=id, @g:=@id from t1;
select @c, @d, @e, @f, @g;
drop table t1;
# just for fun :)
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;
#
# bug#1739
# Item_func_set_user_var sets update_query_id, Item_func_get_user_var checks it
#
create table t1 (i int not null);
insert t1 values (1),(2),(2),(3),(3),(3);
select @a:=0; select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
select @a:=0; select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
drop table t1;
#
# Bug #2244: User variables didn't copy collation and derivation
# attributes from values they were initialized to.
#
set @a=_latin2'test';
select charset(@a),collation(@a),coercibility(@a);
select @a=_latin2'TEST';
select @a=_latin2'TEST' collate latin2_bin;
set @a=_latin2'test' collate latin2_general_ci;
select charset(@a),collation(@a),coercibility(@a);
select @a=_latin2'TEST';
--error 1267
select @a=_latin2'TEST' collate latin2_bin;
#
# Check the same invoking Item_set_user_var
#
select charset(@a:=_latin2'test');
select collation(@a:=_latin2'test');
select coercibility(@a:=_latin2'test');
select collation(@a:=_latin2'test' collate latin2_bin);
select coercibility(@a:=_latin2'test' collate latin2_bin);
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST';
select charset(@a),collation(@a),coercibility(@a);
--error 1267
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci;
# Check that user variables are binlogged correctly (BUG#3875)
create table t1 (a varchar(50));
reset master;
SET TIMESTAMP=10000;
SET @`a b`='hello';
INSERT INTO t1 VALUES(@`a b`);
set @var1= "';aaa";
insert into t1 values (@var1);
create table t2 (c char(30)) charset=ucs2;
set @v=convert('abc' using ucs2);
insert into t2 values (@v);
show binlog events from 79;
# more important than SHOW BINLOG EVENTS, mysqlbinlog (where we
# absolutely need variables names to be quoted and strings to be
# escaped).
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
--exec $MYSQL_BINLOG --short-form $MYSQL_TEST_DIR/var/log/master-bin.000001
drop table t1, t2;
|
