user_var.result 9.2 KB
Newer Older
1
drop table if exists t1,t2;
unknown's avatar
unknown committed
2
set @a := foo;
3
ERROR 42S22: Unknown column 'foo' in 'field list'
unknown's avatar
unknown committed
4 5
set @a := connection_id() + 3;
select @a - connection_id();
6 7
@a - connection_id()
3
8 9 10 11
set @b := 1;
select @b;
@b
1
unknown's avatar
unknown committed
12 13 14 15 16
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;
17 18 19
i
1
2
unknown's avatar
unknown committed
20
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;
21 22 23
i	@vv1:=if(sv1.i,1,0)	@vv2:=if(sv2.i,1,0)	@vv3:=if(sv3.i,1,0)	@vv1+@vv2+@vv3
1	1	0	1	2
2	1	0	0	1
unknown's avatar
unknown committed
24
explain select * from t1 where i=@vv1;
unknown's avatar
unknown committed
25
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26 27 28 29 30
1	SIMPLE	t1	ref	i	i	4	const	1	
select @vv1,i,v from t1 where i=@vv1;
@vv1	i	v
1	1	1
1	1	3
unknown's avatar
unknown committed
31
explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
unknown's avatar
unknown committed
32
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
33
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
unknown's avatar
unknown committed
34
explain select @vv1:=i from t1 where i=@vv1;
unknown's avatar
unknown committed
35
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
36
1	SIMPLE	t1	index	NULL	i	4	NULL	3	Using where; Using index
unknown's avatar
unknown committed
37
explain select * from t1 where i=@vv1;
unknown's avatar
unknown committed
38
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39
1	SIMPLE	t1	ref	i	i	4	const	1	
unknown's avatar
unknown committed
40
drop table t1,t2;
41
set @a=0,@b=0;
unknown's avatar
unknown committed
42
select @a:=10,   @b:=1,   @a > @b, @a < @b;
43 44
@a:=10	@b:=1	@a > @b	@a < @b
10	1	1	0
unknown's avatar
unknown committed
45
select @a:="10", @b:="1", @a > @b, @a < @b;
46 47
@a:="10"	@b:="1"	@a > @b	@a < @b
10	1	1	0
unknown's avatar
unknown committed
48
select @a:=10,   @b:=2,   @a > @b, @a < @b;
49
@a:=10	@b:=2	@a > @b	@a < @b
50
10	2	0	1
unknown's avatar
unknown committed
51
select @a:="10", @b:="2", @a > @b, @a < @b;
52
@a:="10"	@b:="2"	@a > @b	@a < @b
53
10	2	1	0
unknown's avatar
unknown committed
54 55 56 57 58 59
select @a:=1;
@a:=1
1
select @a, @a:=1;
@a	@a:=1
1	1
60 61
create table t1 (id int, d double, c char(10));
insert into t1 values (1,2.0, "test");
unknown's avatar
unknown committed
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
select @c:=0;
@c:=0
0
update t1 SET id=(@c:=@c+1);
select @c;
@c
1
select @c:=0;
@c:=0
0
update t1 set id=(@c:=@c+1);
select @c;
@c
1
select @c:=0;
@c:=0
0
select @c:=@c+1;
@c:=@c+1
1
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
select @d,(@d:=id),@d from t1;
@d	(@d:=id)	@d
NULL	1	1
select @e,(@e:=d),@e from t1;
@e	(@e:=d)	@e
NULL	2	2
select @f,(@f:=c),@f from t1;
@f	(@f:=c)	@f
NULL	test	test
set @g=1;
select @g,(@g:=c),@g from t1;
@g	(@g:=c)	@g
1	test	test
select @c, @d, @e, @f;
@c	@d	@e	@f
1	1	2	test
select @d:=id, @e:=id, @f:=id, @g:=@id from t1;
@d:=id	@e:=id	@f:=id	@g:=@id
1	1	1	NULL
select @c, @d, @e, @f, @g;
@c	@d	@e	@f	@g
1	1	1	1	NULL
unknown's avatar
unknown committed
104 105 106
drop table t1;
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;
@a:=10	@b:=2	@a>@b	@a:="10"	@b:="2"	@a>@b	@a:=10	@b:=2	@a>@b	@a:="10"	@b:="2"	@a>@b
107
10	2	1	10	2	1	10	2	1	10	2	1
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
create table t1 (i int not null);
insert t1 values (1),(2),(2),(3),(3),(3);
select @a:=0;
@a:=0
0
select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
@a	@a:=@a+count(*)	count(*)	@a
0	1	1	0
0	2	2	0
0	3	3	0
select @a:=0;
@a:=0
0
select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
@a+0	@a:=@a+0+count(*)	count(*)	@a+0
0	1	1	0
1	3	2	0
3	6	3	0
126 127 128 129 130 131 132 133 134 135 136
set @a=0;
select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
@a	@a:="hello"	@a	@a:=3	@a	@a:="hello again"
0	hello	0	3	0	hello again
0	hello	0	3	0	hello again
0	hello	0	3	0	hello again
select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
@a	@a:="hello"	@a	@a:=3	@a	@a:="hello again"
hello again	hello	hello again	3	hello again	hello again
hello again	hello	hello again	3	hello again	hello again
hello again	hello	hello again	3	hello again	hello again
137
drop table t1;
138 139 140
set @a=_latin2'test';
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
141
latin2	latin2_general_ci	2
142 143 144 145 146 147 148 149 150
select @a=_latin2'TEST';
@a=_latin2'TEST'
1
select @a=_latin2'TEST' collate latin2_bin;
@a=_latin2'TEST' collate latin2_bin
0
set @a=_latin2'test' collate latin2_general_ci;
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
151
latin2	latin2_general_ci	2
152 153 154 155
select @a=_latin2'TEST';
@a=_latin2'TEST'
1
select @a=_latin2'TEST' collate latin2_bin;
156 157
@a=_latin2'TEST' collate latin2_bin
0
158 159 160 161 162 163 164 165
select charset(@a:=_latin2'test');
charset(@a:=_latin2'test')
latin2
select collation(@a:=_latin2'test');
collation(@a:=_latin2'test')
latin2_general_ci
select coercibility(@a:=_latin2'test');
coercibility(@a:=_latin2'test')
166
2
167 168 169 170 171
select collation(@a:=_latin2'test' collate latin2_bin);
collation(@a:=_latin2'test' collate latin2_bin)
latin2_bin
select coercibility(@a:=_latin2'test' collate latin2_bin);
coercibility(@a:=_latin2'test' collate latin2_bin)
172
2
173 174 175 176 177
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST';
(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'
0
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
178
latin2	latin2_bin	2
179
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci;
180 181
(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci
1
unknown's avatar
unknown committed
182 183 184 185
set @var= NULL ;
select FIELD( @var,'1it','Hit') as my_column;
my_column
0
186 187 188 189 190 191 192
select @v, coercibility(@v);
@v	coercibility(@v)
NULL	2
set @v1=null, @v2=1, @v3=1.1, @v4=now();
select coercibility(@v1),coercibility(@v2),coercibility(@v3),coercibility(@v4);
coercibility(@v1)	coercibility(@v2)	coercibility(@v3)	coercibility(@v4)
2	2	2	2
193
set session @honk=99;
194
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@honk=99' at line 1
195
set one_shot @honk=99;
unknown's avatar
unknown committed
196
ERROR HY000: The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server
197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
select @@local.max_allowed_packet;
@@local.max_allowed_packet
#
select @@session.max_allowed_packet;
@@session.max_allowed_packet
#
select @@global.max_allowed_packet;
@@global.max_allowed_packet
#
select @@max_allowed_packet;
@@max_allowed_packet
#
select @@Max_Allowed_Packet;
@@Max_Allowed_Packet
#
select @@version;
@@version
#
select @@global.version;
@@global.version
#
218
End of 4.1 tests
219 220 221 222 223 224 225 226 227 228 229 230 231
set @first_var= NULL;
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` longblob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var= cast(NULL as signed integer);
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
232
  `@first_var` bigint(20) DEFAULT NULL
233 234 235 236 237 238 239
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var= NULL;
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
240
  `@first_var` bigint(20) DEFAULT NULL
241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var= concat(NULL);
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` longblob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var=1;
set @first_var= cast(NULL as CHAR);
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295
set @a=18446744071710965857;
select @a;
@a
18446744071710965857
CREATE TABLE `bigfailure` (
`afield` BIGINT UNSIGNED NOT NULL
);
INSERT INTO `bigfailure` VALUES (18446744071710965857);
SELECT * FROM bigfailure;
afield
18446744071710965857
select * from (SELECT afield FROM bigfailure) as b;
afield
18446744071710965857
select * from bigfailure where afield = (SELECT afield FROM bigfailure);
afield
18446744071710965857
select * from bigfailure where afield = 18446744071710965857;
afield
18446744071710965857
select * from bigfailure where afield = 18446744071710965856+1;
afield
18446744071710965857
SET @a := (SELECT afield FROM bigfailure);
SELECT @a;
@a
18446744071710965857
SET @a := (select afield from (SELECT afield FROM bigfailure) as b);
SELECT @a;
@a
18446744071710965857
SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure));
SELECT @a;
@a
18446744071710965857
drop table bigfailure;
296 297 298 299 300 301 302 303
create table t1(f1 int, f2 int);
insert into t1 values (1,2),(2,3),(3,1);
select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
@var:=f2
3
select @var;
@var
3
304 305 306 307 308 309 310 311
create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
select * from t2;
@var:=f2
3
select @var;
@var
3
drop table t1,t2;
312 313 314 315 316 317 318 319
insert into city 'blah';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''blah'' at line 1
SHOW COUNT(*) WARNINGS;
@@session.warning_count
1
SHOW COUNT(*) ERRORS;
@@session.error_count
1
unknown's avatar
unknown committed
320 321 322 323 324
create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1));
insert into t1 values 
(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6),
(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6),
(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6);
325
select @a:=f1, count(f1) from t1 group by 1 desc;
326
@a:=f1	count(f1)
327 328
4	1
3	2
329
2	1
330 331 332 333 334 335 336
1	4
select @a:=f1, count(f1) from t1 group by 1 asc;
@a:=f1	count(f1)
1	4
2	1
3	2
4	1
unknown's avatar
unknown committed
337 338 339
select @a:=f2, count(f2) from t1 group by 1 desc;
@a:=f2	count(f2)
d	1
unknown's avatar
unknown committed
340 341 342
c	2
b	1
a	4
unknown's avatar
unknown committed
343 344 345
select @a:=f3, count(f3) from t1 group by 1 desc;
@a:=f3	count(f3)
4.5	1
unknown's avatar
unknown committed
346 347 348
3.5	2
2.5	1
1.5	4
unknown's avatar
unknown committed
349 350 351 352 353 354
select @a:=f4, count(f4) from t1 group by 1 desc;
@a:=f4	count(f4)
4.6	1
3.6	2
2.6	1
1.6	4
355
drop table t1;