1
drop table if exists t1,t2;
3
ERROR 42S22: Unknown column 'foo' in 'field list'
4
set @a := connection_id() + 3;
5
select @a - connection_id();
12
CREATE TABLE t1 ( i int not null, v int not null,index (i));
13
insert into t1 values (1,1),(1,3),(2,1);
14
create table t2 (i int not null, unique (i));
15
insert into t2 select distinct i from t1;
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
i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3
24
explain select * from t1 where i=@vv1;
25
id select_type table type possible_keys key key_len ref rows Extra
26
1 SIMPLE t1 ref i i 4 const 1
27
select @vv1,i,v from t1 where i=@vv1;
31
explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
32
id select_type table type possible_keys key key_len ref rows Extra
33
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
34
explain select @vv1:=i from t1 where i=@vv1;
35
id select_type table type possible_keys key key_len ref rows Extra
36
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
37
explain select * from t1 where i=@vv1;
38
id select_type table type possible_keys key key_len ref rows Extra
39
1 SIMPLE t1 ref i i 4 const 1
42
select @a:=10, @b:=1, @a > @b, @a < @b;
43
@a:=10 @b:=1 @a > @b @a < @b
45
select @a:="10", @b:="1", @a > @b, @a < @b;
46
@a:="10" @b:="1" @a > @b @a < @b
48
select @a:=10, @b:=2, @a > @b, @a < @b;
49
@a:=10 @b:=2 @a > @b @a < @b
51
select @a:="10", @b:="2", @a > @b, @a < @b;
52
@a:="10" @b:="2" @a > @b @a < @b
60
create table t1 (id int, d double, c char(10));
61
insert into t1 values (1,2.0, "test");
65
update t1 SET id=(@c:=@c+1);
72
update t1 set id=(@c:=@c+1);
82
select @d,(@d:=id),@d from t1;
85
select @e,(@e:=d),@e from t1;
88
select @f,(@f:=c),@f from t1;
92
select @g,(@g:=c),@g from t1;
95
select @c, @d, @e, @f;
98
select @d:=id, @e:=id, @f:=id, @g:=@id from t1;
99
@d:=id @e:=id @f:=id @g:=@id
101
select @c, @d, @e, @f, @g;
105
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;
106
@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
create table t1 (i int not null);
109
insert t1 values (1),(2),(2),(3),(3),(3);
113
select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
114
@a @a:=@a+count(*) count(*) @a
121
select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
122
@a+0 @a:=@a+0+count(*) count(*) @a+0
127
select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
128
@a @a:="hello" @a @a:=3 @a @a:="hello again"
129
0 hello 0 3 0 hello again
130
0 hello 0 3 0 hello again
131
0 hello 0 3 0 hello again
132
select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
133
@a @a:="hello" @a @a:=3 @a @a:="hello again"
134
hello again hello hello again 3 hello again hello again
135
hello again hello hello again 3 hello again hello again
136
hello again hello hello again 3 hello again hello again
139
select collation(@a),coercibility(@a);
140
collation(@a) coercibility(@a)
145
select @a='TEST' collate utf8_bin;
146
@a='TEST' collate utf8_bin
148
set @a='test' collate utf8_general_ci;
149
select collation(@a),coercibility(@a);
150
collation(@a) coercibility(@a)
155
select @a='TEST' collate utf8_bin;
156
@a='TEST' collate utf8_bin
158
select collation(@a:='test');
159
collation(@a:='test')
161
select coercibility(@a:='test');
162
coercibility(@a:='test')
164
select collation(@a:='test' collate utf8_bin);
165
collation(@a:='test' collate utf8_bin)
167
select coercibility(@a:='test' collate utf8_bin);
168
coercibility(@a:='test' collate utf8_bin)
170
select (@a:='test' collate utf8_bin) = 'TEST';
171
(@a:='test' collate utf8_bin) = 'TEST'
173
select collation(@a),coercibility(@a);
174
collation(@a) coercibility(@a)
176
select (@a:='test' collate utf8_bin) = 'TEST' collate utf8_general_ci;
177
(@a:='test' collate utf8_bin) = 'TEST' collate utf8_general_ci
180
select FIELD( @var,'1it','Hit') as my_column;
183
select @v, coercibility(@v);
186
set @v1=null, @v2=1, @v3=1.1, @v4=now();
187
select coercibility(@v1),coercibility(@v2),coercibility(@v3),coercibility(@v4);
188
coercibility(@v1) coercibility(@v2) coercibility(@v3) coercibility(@v4)
190
set session @honk=99;
191
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '@honk=99' at line 1
192
select @@local.max_allowed_packet;
193
@@local.max_allowed_packet
195
select @@session.max_allowed_packet;
196
@@session.max_allowed_packet
198
select @@global.max_allowed_packet;
199
@@global.max_allowed_packet
201
select @@max_allowed_packet;
204
select @@Max_Allowed_Packet;
210
select @@global.version;
214
set @first_var= NULL;
215
create table t1 select @first_var;
216
show create table t1;
218
t1 CREATE TABLE `t1` (
222
set @first_var= cast(NULL as integer);
223
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'integer)' at line 1
224
set @first_var= NULL;
225
create table t1 select @first_var;
226
show create table t1;
228
t1 CREATE TABLE `t1` (
232
set @first_var= concat(NULL);
233
create table t1 select @first_var;
234
show create table t1;
236
t1 CREATE TABLE `t1` (
241
set @first_var= cast(NULL as CHAR);
242
create table t1 select @first_var;
243
show create table t1;
245
t1 CREATE TABLE `t1` (
249
set @a=18446744071710965857;
253
CREATE TABLE `bigfailure` (
254
`afield` BIGINT NOT NULL
256
INSERT INTO `bigfailure` VALUES (18446744071710965857);
257
SELECT * FROM bigfailure;
260
select * from (SELECT afield FROM bigfailure) as b;
263
select * from bigfailure where afield = (SELECT afield FROM bigfailure);
266
select * from bigfailure where afield = 18446744071710965857;
269
select * from bigfailure where afield = '18446744071710965857';
271
select * from bigfailure where afield = 18446744071710965856+1;
274
SET @a := (SELECT afield FROM bigfailure);
278
SET @a := (select afield from (SELECT afield FROM bigfailure) as b);
282
SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure));
286
drop table bigfailure;
287
create table t1(f1 int, f2 int);
288
insert into t1 values (1,2),(2,3),(3,1);
289
select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
296
insert into city 'blah';
297
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ''blah'' at line 1
298
SHOW COUNT(*) WARNINGS;
299
@@session.warning_count
301
SHOW COUNT(*) ERRORS;
302
@@session.error_count
304
create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1));
305
insert into t1 values
306
(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6),
307
(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6),
308
(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6);
309
select @a:=f1, count(f1) from t1 group by 1 desc;
315
select @a:=f1, count(f1) from t1 group by 1 asc;
321
select @a:=f2, count(f2) from t1 group by 1 desc;
327
select @a:=f3, count(f3) from t1 group by 1 desc;
333
select @a:=f4, count(f4) from t1 group by 1 desc;
340
create table t1 (f1 int);
341
insert into t1 values (2), (1);
342
select @i := f1 as j from t1 order by 1;
347
create table t1(a int);
348
insert into t1 values(5),(4),(4),(3),(2),(2),(2),(1);
351
set @prev_score := NULL;
352
select @rownum := @rownum + 1 as row,
353
@rank := IF(@prev_score!=a, @rownum, @rank) as rank,
354
@prev_score := a as score
355
from t1 order by score desc;
added
removed
tests/r/pbxt/user_var.result
