drop table if exists t1,t2;
set @a := foo;
ERROR 42S22: Unknown column 'foo' in 'field list'
set @a := connection_id() + 3;
select @a - connection_id();
@a - connection_id()
3
set @b := 1;
select @b;
@b
1
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;
i
1
2
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;
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
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain select * from t1 where i=@vv1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	i	i	4	const	#	#	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t1`.`v` AS `v` from `test`.`t1` where (`test`.`t1`.`i` = (@`vv1`))
select @vv1,i,v from t1 where i=@vv1;
@vv1	i	v
1	1	1
1	1	3
explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	#	#	Using where
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t1`.`v` AS `v` from `test`.`t1` where (0 <> (@vv1:=((0 <> ((@`vv1`) + 1)) and (`test`.`t1`.`i` = (@`vv1`)))))
explain select @vv1:=i from t1 where i=@vv1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	i	4	NULL	#	#	Using where; Using index
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Note	1003	/* select#1 */ select (@vv1:=`test`.`t1`.`i`) AS `@vv1:=i` from `test`.`t1` where (`test`.`t1`.`i` = (@`vv1`))
explain select * from t1 where i=@vv1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	i	i	4	const	#	#	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t1`.`v` AS `v` from `test`.`t1` where (`test`.`t1`.`i` = (@`vv1`))
drop table t1,t2;
set @a=0,@b=0;
select @a:=10,   @b:=1,   @a > @b, @a < @b;
@a:=10	@b:=1	@a > @b	@a < @b
10	1	1	0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:="10", @b:="1", @a > @b, @a < @b;
@a:="10"	@b:="1"	@a > @b	@a < @b
10	1	1	0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=10,   @b:=2,   @a > @b, @a < @b;
@a:=10	@b:=2	@a > @b	@a < @b
10	2	0	1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:="10", @b:="2", @a > @b, @a < @b;
@a:="10"	@b:="2"	@a > @b	@a < @b
10	2	1	0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=1;
@a:=1
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a, @a:=1;
@a	@a:=1
1	1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
create table t1 (id int, d double, c char(10));
insert into t1 values (1,2.0, "test");
select @c:=0;
@c:=0
0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
update t1 SET id=(@c:=@c+1);
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @c;
@c
1
select @c:=0;
@c:=0
0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
update t1 set id=(@c:=@c+1);
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @c;
@c
1
select @c:=0;
@c:=0
0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @c:=@c+1;
@c:=@c+1
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @d,(@d:=id),@d from t1;
@d	(@d:=id)	@d
NULL	1	1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @e,(@e:=d),@e from t1;
@e	(@e:=d)	@e
NULL	2	2
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @f,(@f:=c),@f from t1;
@f	(@f:=c)	@f
NULL	test	test
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
set @g=1;
select @g,(@g:=c),@g from t1;
@g	(@g:=c)	@g
1	test	0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
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
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @c, @d, @e, @f, @g;
@c	@d	@e	@f	@g
1	1	1	1	NULL
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
10	2	1	10	2	1	10	2	1	10	2	1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
create table t1 (i int not null);
insert t1 values (1),(2),(2),(3),(3),(3);
select @a:=0;
@a:=0
0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
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
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=0;
@a:=0
0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
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
0	2	2	0
0	3	3	0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
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
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
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
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
set @a=_latin2'test';
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
latin2	latin2_general_ci	2
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)
latin2	latin2_general_ci	2
select @a=_latin2'TEST';
@a=_latin2'TEST'
1
select @a=_latin2'TEST' collate latin2_bin;
@a=_latin2'TEST' collate latin2_bin
0
select charset(@a:=_latin2'test');
charset(@a:=_latin2'test')
latin2
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select collation(@a:=_latin2'test');
collation(@a:=_latin2'test')
latin2_general_ci
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select coercibility(@a:=_latin2'test');
coercibility(@a:=_latin2'test')
2
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select collation(@a:=_latin2'test' collate latin2_bin);
collation(@a:=_latin2'test' collate latin2_bin)
latin2_bin
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select coercibility(@a:=_latin2'test' collate latin2_bin);
coercibility(@a:=_latin2'test' collate latin2_bin)
2
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST';
(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'
0
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
latin2	latin2_bin	2
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci;
(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
set @var= NULL ;
select FIELD( @var,'1it','Hit') as my_column;
my_column
0
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
set session @honk=99;
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
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
#
End of 4.1 tests
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=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
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` (
  `@first_var` bigint(20) DEFAULT NULL
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
drop table t1;
set @first_var= NULL;
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` bigint(20) DEFAULT NULL
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
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=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
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 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
drop table t1;
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;
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
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @var;
@var
3
create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select * from t2;
@var:=f2
3
select @var;
@var
3
drop table t1,t2;
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
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);
select @a:=f1, count(f1) from t1 group by 1 order by 1 desc;
@a:=f1	count(f1)
4	1
3	2
2	1
1	4
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=f1, count(f1) from t1 group by 1 order by 1 asc;
@a:=f1	count(f1)
1	4
2	1
3	2
4	1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=f2, count(f2) from t1 group by 1 order by 1 desc;
@a:=f2	count(f2)
d	1
c	2
b	1
a	4
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=f3, count(f3) from t1 group by 1 order by 1 desc;
@a:=f3	count(f3)
4.5	1
3.5	2
2.5	1
1.5	4
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select @a:=f4, count(f4) from t1 group by 1 order by 1 desc;
@a:=f4	count(f4)
4.6	1
3.6	2
2.6	1
1.6	4
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
create table t1 (f1 int);
insert into t1 values (2), (1);
select @i := f1 as j from t1 order by 1;
j
1
2
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
create table t1(a int);
insert into t1 values(5),(4),(4),(3),(2),(2),(2),(1);
set @rownum := 0;
set @rank := 0;
set @prev_score := NULL;
select @rownum := @rownum + 1 as `row`,
@rank := IF(@prev_score!=a, @rownum, @rank) as `rank`,
@prev_score := a as score
from t1 order by score desc;
drop table t1;
create table t1(b bigint);
insert into t1 (b) values (10), (30), (10);
set @var := 0;
select if(b=@var, 999, b) , @var := b from t1  order by b;
if(b=@var, 999, b)	@var := b
10	10
999	10
30	30
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
create temporary table t1 (id int);
insert into t1 values (2), (3), (3), (4);
set @lastid=-1;
select @lastid != id, @lastid, @lastid := id from t1;
@lastid != id	@lastid	@lastid := id
1	-1	2
1	2	3
0	3	3
1	3	4
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
create temporary table t1 (id bigint);
insert into t1 values (2), (3), (3), (4);
set @lastid=-1;
select @lastid != id, @lastid, @lastid := id from t1;
@lastid != id	@lastid	@lastid := id
1	-1	2
1	2	3
0	3	3
1	3	4
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 VALUES (0, 0), (2, 1), (2, 3), (1, 1), (30, 20);
SELECT a, b INTO @a, @b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;
SELECT @a, @b;
@a	@b
2	3
SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b;
a	b
2	3
DROP TABLE t1;
CREATE TABLE t1 (f1 int(11) default NULL, f2 int(11) default NULL);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t2 (f1 int(11) default NULL, f2 int(11) default NULL, foo int(11));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t3 (f1 int(11) default NULL, f2 int(11) default NULL);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES(10, 10);
INSERT INTO t1 VALUES(10, 10);
INSERT INTO t2 VALUES(10, 10, 10);
INSERT INTO t2 VALUES(10, 10, 10);
INSERT INTO t3 VALUES(10, 10);
INSERT INTO t3 VALUES(10, 10);
SELECT MIN(t2.f1),
@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)
FROM t1,t2 WHERE t1.f1 = t2.f1 ORDER BY t2.f1;
MIN(t2.f1)	@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo)
10	NULL
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
DROP TABLE t1, t2, t3;
End of 5.0 tests
CREATE TABLE t1 (i INT);
CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
DROP TABLE t1;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0),(0);
# BUG#55615 : should not crash
SELECT (@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) FROM t1 GROUP BY @a;
(@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1)
1
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
# BUG#55564 : should not crash
SELECT IF(
@v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a),
count(*), 1) 
FROM t1 GROUP BY a LIMIT 1;
IF(
@v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a),
count(*), 1)
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
DROP TABLE t1;
select @v:=@v:=sum(1) from dual;
@v:=@v:=sum(1)
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
CREATE TABLE t1(a DECIMAL(31,21));
INSERT INTO t1 VALUES (0);
SELECT (@v:=a) <> (@v:=1) FROM t1;
(@v:=a) <> (@v:=1)
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
DROP TABLE t1;
End of 5.1 tests
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(f1 INT AUTO_INCREMENT, PRIMARY KEY(f1));
INSERT INTO t1 SET f1 = NULL ;
SET @aux = NULL ;
INSERT INTO t1 SET f1 = @aux ;
SET @aux1 = 0.123E-1;
SET @aux1 = NULL;
INSERT INTO t1 SET f1 = @aux1 ;
SELECT * FROM t1;
f1
1
2
3
DROP TABLE t1;
CREATE TABLE t1(f1 VARCHAR(257) , f2 INT, PRIMARY KEY(f2));
CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1;
SET @aux = 1;
SET @aux = NULL;
INSERT INTO test.t1 (f1, f2) VALUES (1, 1), (@aux, 2);
SET @aux = 'text';
SET @aux = NULL;
INSERT INTO t1(f1, f2) VALUES (1, 3), (@aux, 4);
SELECT f1, f2 FROM t1 ORDER BY f2;
f1	f2
1	1
1	2
1	3
1	4
DROP TRIGGER trg1;
DROP TABLE t1;
#
# Bug #12408412: GROUP_CONCAT + ORDER BY + INPUT/OUTPUT
#   SAME USER VARIABLE = CRASH
#
SET @bug12408412=1;
SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412;
End of 5.5 tests
CREATE TABLE t1(a int);
INSERT INTO t1 VALUES (1), (2);
SELECT DISTINCT @a:=MIN(t1.a) FROM t1, t1 AS t2
GROUP BY @b:=(SELECT COUNT(*) > t2.a);
@a:=MIN(t1.a)
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
DROP TABLE t1;
CREATE TABLE t1(a INT) ENGINE=XENGINE;
INSERT INTO t1 VALUES (0);
SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a))
AS b FROM t1 GROUP BY a;
b
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
SELECT @a;
@a
1
DROP TABLE t1;
CREATE TABLE t1(f1 INT, f2 INT);
INSERT INTO t1 VALUES (1,2),(2,3),(3,1);
CREATE TABLE t2(a INT);
INSERT INTO t2 VALUES (1);
SET @var=NULL;
SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC
LIMIT 1;
@var:=(SELECT f2 FROM t2 WHERE @var)
NULL
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
SELECT @var;
@var
NULL
DROP TABLE t1, t2;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
CREATE TABLE t2(a INT);
INSERT INTO t2 VALUES (1), (3), (5), (7), (9);
CREATE TABLE t3(a INT);
INSERT INTO t3 VALUES (1), (4), (7), (10);
SET @var1 = 6;
ANALYZE TABLE t1,t2,t3;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
test.t2	analyze	status	OK
test.t3	analyze	status	OK
explain format=json SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var
FROM t1
LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1
LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "33.74"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 10,
          "rows_produced_per_join": 10,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.62",
            "eval_cost": "1.00",
            "prefix_cost": "3.62",
            "data_read_per_join": "80"
          },
          "used_columns": [
            "a"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 5,
          "rows_produced_per_join": 50,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "2.56",
            "eval_cost": "5.00",
            "prefix_cost": "11.19",
            "data_read_per_join": "400"
          },
          "used_columns": [
            "a"
          ],
          "attached_condition": "<if>(is_not_null_compl(t2), ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < (@`var1`))), true)"
        }
      },
      {
        "table": {
          "table_name": "t3",
          "access_type": "ALL",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 200,
          "filtered": "100.00",
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "2.56",
            "eval_cost": "20.00",
            "prefix_cost": "33.74",
            "data_read_per_join": "1K"
          },
          "used_columns": [
            "a"
          ],
          "attached_condition": "<if>(is_not_null_compl(t3), ((`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < (@`var1`))), true)"
        }
      }
    ]
  }
}
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a`,(@var1:=((@`var1`) + 0)) AS `var` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < (@`var1`)))) left join `test`.`t3` on(((`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < (@`var1`)))) where true
SELECT t1.a, t2.a, t3.a, (@var1:= @var1+0) AS var
FROM t1
LEFT JOIN t2 ON t1.a=t2.a AND t2.a < @var1
LEFT JOIN t3 ON t1.a=t3.a AND t3.a < @var1;
a	a	a	var
1	1	1	6
10	NULL	NULL	6
2	NULL	NULL	6
3	3	NULL	6
4	NULL	4	6
5	5	NULL	6
6	NULL	NULL	6
7	NULL	NULL	6
8	NULL	NULL	6
9	NULL	NULL	6
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warnings:
DROP TABLE t1, t2, t3;
set    @X234567890123456789012345678901234567890123456789012345678901234 = 12;
select @X234567890123456789012345678901234567890123456789012345678901234;
@X234567890123456789012345678901234567890123456789012345678901234
12
set    @X2345678901234567890123456789012345678901234567890123456789012345 = 12;
ERROR 42000: User variable name 'X2345678901234567890123456789012345678901234567890123456789012345' is illegal
set @``= "illegal";
ERROR 42000: User variable name '' is illegal
set @`endswithspace `= "illegal";
ERROR 42000: User variable name 'endswithspace ' is illegal
select @X2345678901234567890123456789012345678901234567890123456789012345;
@X2345678901234567890123456789012345678901234567890123456789012345
NULL
select @``;
@``
NULL
select @`endswithspace `;
@`endswithspace `
NULL
CREATE TABLE t1(a INT,KEY(a))ENGINE=XENGINE;
SELECT 1 NOT IN (SELECT 1 FROM t1 as t1 GROUP BY 1 LIKE (SELECT 1 FROM t1 as t2)) AS col;
col
1
SELECT 1 NOT IN (SELECT 1 FROM t1 as t1 ORDER BY 1 LIKE (SELECT 1 FROM t1 as t2)) AS col;
col
1
SET @c =(SELECT 1 NOT IN (SELECT 1 FROM t1 as t1 GROUP BY 1 LIKE (SELECT 1 FROM t1 as t2)) AS col);
SET @d =(SELECT 1 NOT IN (SELECT 1 FROM t1 as t1 ORDER BY 1 LIKE (SELECT 1 FROM t1 as t2)) AS col);
DROP TABLE t1;
Bug#25727892: Refactor Item::const_item() as a non-virtual function
Coverage for cached and non-cached user variables
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES (1,1), (1,2), (2,1);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain SELECT a FROM t1 WHERE a = @x;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	#	#	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((@`x`)))
explain SELECT a FROM t1 WHERE a = @x AND (@x:= @x);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	#	#	Using where
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = (@`x`)) and (0 <> (@x:=(@`x`))))
explain SELECT a FROM t1 WHERE a = @x INTO @x;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	#	#	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((@`x`)))
SET @x= 1;
SELECT a FROM t1 WHERE a = @x;
a
1
1
SELECT a FROM t1 WHERE a = @x AND (@x:= @x);
a
1
1
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
SELECT a FROM t1 WHERE a = @x INTO @x;
ERROR 42000: Result consisted of more than one row
SET @x= 2;
SELECT a FROM t1 WHERE a = @x INTO @x;
DROP TABLE t1;