1set @a := foo; 2ERROR 42S22: Unknown column 'foo' in 'field list' 3set @a := connection_id() + 3; 4select @a - connection_id(); 5@a - connection_id() 63 7set @b := 1; 8select @b; 9@b 101 11CREATE TABLE t1 ( i int not null, v int not null,index (i)); 12insert into t1 values (1,1),(1,3),(2,1); 13create table t2 (i int not null, unique (i)); 14insert into t2 select distinct i from t1; 15select * from t2; 16i 171 182 19select 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; 20i @vv1:=if(sv1.i,1,0) @vv2:=if(sv2.i,1,0) @vv3:=if(sv3.i,1,0) @vv1+@vv2+@vv3 211 1 0 1 2 222 1 0 0 1 23explain select * from t1 where i=@vv1; 24id select_type table type possible_keys key key_len ref rows Extra 251 SIMPLE t1 ref i i 4 const 2 26select @vv1,i,v from t1 where i=@vv1; 27@vv1 i v 281 1 1 291 1 3 30explain select * from t1 where @vv1:=@vv1+1 and i=@vv1; 31id select_type table type possible_keys key key_len ref rows Extra 321 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 33explain select @vv1:=i from t1 where i=@vv1; 34id select_type table type possible_keys key key_len ref rows Extra 351 SIMPLE t1 index NULL i 4 NULL 3 Using where; Using index 36explain select * from t1 where i=@vv1; 37id select_type table type possible_keys key key_len ref rows Extra 381 SIMPLE t1 ref i i 4 const 2 39drop table t1,t2; 40set @a=0,@b=0; 41select @a:=10, @b:=1, @a > @b, @a < @b; 42@a:=10 @b:=1 @a > @b @a < @b 4310 1 1 0 44select @a:="10", @b:="1", @a > @b, @a < @b; 45@a:="10" @b:="1" @a > @b @a < @b 4610 1 1 0 47select @a:=10, @b:=2, @a > @b, @a < @b; 48@a:=10 @b:=2 @a > @b @a < @b 4910 2 0 1 50select @a:="10", @b:="2", @a > @b, @a < @b; 51@a:="10" @b:="2" @a > @b @a < @b 5210 2 1 0 53select @a:=1; 54@a:=1 551 56select @a, @a:=1; 57@a @a:=1 581 1 59create table t1 (id int, d double, c char(10)); 60insert into t1 values (1,2.0, "test"); 61select @c:=0; 62@c:=0 630 64update t1 SET id=(@c:=@c+1); 65select @c; 66@c 671 68select @c:=0; 69@c:=0 700 71update t1 set id=(@c:=@c+1); 72select @c; 73@c 741 75select @c:=0; 76@c:=0 770 78select @c:=@c+1; 79@c:=@c+1 801 81select @d,(@d:=id),@d from t1; 82@d (@d:=id) @d 83NULL 1 1 84select @e,(@e:=d),@e from t1; 85@e (@e:=d) @e 86NULL 2 2 87select @f,(@f:=c),@f from t1; 88@f (@f:=c) @f 89NULL test test 90set @g=1; 91select @g,(@g:=c),@g from t1; 92@g (@g:=c) @g 931 test 0 94select @c, @d, @e, @f; 95@c @d @e @f 961 1 2 test 97select @d:=id, @e:=id, @f:=id, @g:=@id from t1; 98@d:=id @e:=id @f:=id @g:=@id 991 1 1 NULL 100select @c, @d, @e, @f, @g; 101@c @d @e @f @g 1021 1 1 1 NULL 103drop table t1; 104select @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b, @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b; 105@a:=10 @b:=2 @a>@b @a:="10" @b:="2" @a>@b @a:=10 @b:=2 @a>@b @a:="10" @b:="2" @a>@b 10610 2 1 10 2 1 10 2 1 10 2 1 107create table t1 (i int not null); 108insert t1 values (1),(2),(2),(3),(3),(3); 109select @a:=0; 110@a:=0 1110 112select @a, @a:=@a+count(*), count(*), @a from t1 group by i; 113@a @a:=@a+count(*) count(*) @a 1140 1 1 0 1150 2 2 0 1160 3 3 0 117select @a:=0; 118@a:=0 1190 120select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i; 121@a+0 @a:=@a+0+count(*) count(*) @a+0 1220 1 1 0 1230 2 2 0 1240 3 3 0 125set @a=0; 126select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; 127@a @a:="hello" @a @a:=3 @a @a:="hello again" 1280 hello 0 3 0 hello again 1290 hello 0 3 0 hello again 1300 hello 0 3 0 hello again 131select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i; 132@a @a:="hello" @a @a:=3 @a @a:="hello again" 133hello again hello hello again 3 hello again hello again 134hello again hello hello again 3 hello again hello again 135hello again hello hello again 3 hello again hello again 136drop table t1; 137set @a=_latin2'test'; 138select charset(@a),collation(@a),coercibility(@a); 139charset(@a) collation(@a) coercibility(@a) 140latin2 latin2_general_ci 2 141select @a=_latin2'TEST'; 142@a=_latin2'TEST' 1431 144select @a=_latin2'TEST' collate latin2_bin; 145@a=_latin2'TEST' collate latin2_bin 1460 147set @a=_latin2'test' collate latin2_general_ci; 148select charset(@a),collation(@a),coercibility(@a); 149charset(@a) collation(@a) coercibility(@a) 150latin2 latin2_general_ci 2 151select @a=_latin2'TEST'; 152@a=_latin2'TEST' 1531 154select @a=_latin2'TEST' collate latin2_bin; 155@a=_latin2'TEST' collate latin2_bin 1560 157select charset(@a:=_latin2'test'); 158charset(@a:=_latin2'test') 159latin2 160select collation(@a:=_latin2'test'); 161collation(@a:=_latin2'test') 162latin2_general_ci 163select coercibility(@a:=_latin2'test'); 164coercibility(@a:=_latin2'test') 1652 166select collation(@a:=_latin2'test' collate latin2_bin); 167collation(@a:=_latin2'test' collate latin2_bin) 168latin2_bin 169select coercibility(@a:=_latin2'test' collate latin2_bin); 170coercibility(@a:=_latin2'test' collate latin2_bin) 1712 172select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'; 173(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' 1740 175select charset(@a),collation(@a),coercibility(@a); 176charset(@a) collation(@a) coercibility(@a) 177latin2 latin2_bin 2 178select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci; 179(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci 1801 181set @var= NULL ; 182select FIELD( @var,'1it','Hit') as my_column; 183my_column 1840 185select @v, coercibility(@v); 186@v coercibility(@v) 187NULL 2 188set @v1=null, @v2=1, @v3=1.1, @v4=now(); 189select coercibility(@v1),coercibility(@v2),coercibility(@v3),coercibility(@v4); 190coercibility(@v1) coercibility(@v2) coercibility(@v3) coercibility(@v4) 1912 5 5 2 192set session @honk=99; 193ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@honk=99' at line 1 194select @@local.max_allowed_packet; 195@@local.max_allowed_packet 196# 197select @@session.max_allowed_packet; 198@@session.max_allowed_packet 199# 200select @@global.max_allowed_packet; 201@@global.max_allowed_packet 202# 203select @@max_allowed_packet; 204@@max_allowed_packet 205# 206select @@Max_Allowed_Packet; 207@@Max_Allowed_Packet 208# 209select @@version; 210@@version 211# 212select @@global.version; 213@@global.version 214# 215End of 4.1 tests 216set @first_var= NULL; 217create table t1 select @first_var; 218show create table t1; 219Table Create Table 220t1 CREATE TABLE `t1` ( 221 `@first_var` longblob DEFAULT NULL 222) ENGINE=MyISAM DEFAULT CHARSET=latin1 223drop table t1; 224set @first_var= cast(NULL as signed integer); 225create table t1 select @first_var; 226show create table t1; 227Table Create Table 228t1 CREATE TABLE `t1` ( 229 `@first_var` bigint(20) DEFAULT NULL 230) ENGINE=MyISAM DEFAULT CHARSET=latin1 231drop table t1; 232set @first_var= NULL; 233create table t1 select @first_var; 234show create table t1; 235Table Create Table 236t1 CREATE TABLE `t1` ( 237 `@first_var` bigint(20) DEFAULT NULL 238) ENGINE=MyISAM DEFAULT CHARSET=latin1 239drop table t1; 240set @first_var= concat(NULL); 241create table t1 select @first_var; 242show create table t1; 243Table Create Table 244t1 CREATE TABLE `t1` ( 245 `@first_var` longblob DEFAULT NULL 246) ENGINE=MyISAM DEFAULT CHARSET=latin1 247drop table t1; 248set @first_var=1; 249set @first_var= cast(NULL as CHAR); 250create table t1 select @first_var; 251show create table t1; 252Table Create Table 253t1 CREATE TABLE `t1` ( 254 `@first_var` longtext DEFAULT NULL 255) ENGINE=MyISAM DEFAULT CHARSET=latin1 256drop table t1; 257set @a=18446744071710965857; 258select @a; 259@a 26018446744071710965857 261CREATE TABLE `bigfailure` ( 262`afield` BIGINT UNSIGNED NOT NULL 263); 264INSERT INTO `bigfailure` VALUES (18446744071710965857); 265SELECT * FROM bigfailure; 266afield 26718446744071710965857 268select * from (SELECT afield FROM bigfailure) as b; 269afield 27018446744071710965857 271select * from bigfailure where afield = (SELECT afield FROM bigfailure); 272afield 27318446744071710965857 274select * from bigfailure where afield = 18446744071710965857; 275afield 27618446744071710965857 277select * from bigfailure where afield = 18446744071710965856+1; 278afield 27918446744071710965857 280SET @a := (SELECT afield FROM bigfailure); 281SELECT @a; 282@a 28318446744071710965857 284SET @a := (select afield from (SELECT afield FROM bigfailure) as b); 285SELECT @a; 286@a 28718446744071710965857 288SET @a := (select * from bigfailure where afield = (SELECT afield FROM bigfailure)); 289SELECT @a; 290@a 29118446744071710965857 292drop table bigfailure; 293create table t1(f1 int, f2 int); 294insert into t1 values (1,2),(2,3),(3,1); 295select @var:=f2 from t1 group by f1 order by f2 desc limit 1; 296@var:=f2 2973 298select @var; 299@var 3003 301create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1; 302select * from t2; 303@var:=f2 3043 305select @var; 306@var 3073 308drop table t1,t2; 309insert into city 'blah'; 310ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''blah'' at line 1 311SHOW COUNT(*) WARNINGS; 312@@session.warning_count 3131 314SHOW COUNT(*) ERRORS; 315@@session.error_count 3161 317create table t1(f1 int, f2 varchar(2), f3 float, f4 decimal(2,1)); 318insert into t1 values 319(1, "a", 1.5, 1.6), (1, "a", 1.5, 1.6), (2, "b", 2.5, 2.6), 320(3, "c", 3.5, 3.6), (4, "d", 4.5, 4.6), (1, "a", 1.5, 1.6), 321(3, "c", 3.5, 3.6), (1, "a", 1.5, 1.6); 322select @a:=f1, count(f1) from t1 group by 1 desc; 323@a:=f1 count(f1) 3244 1 3253 2 3262 1 3271 4 328select @a:=f1, count(f1) from t1 group by 1 asc; 329@a:=f1 count(f1) 3301 4 3312 1 3323 2 3334 1 334select @a:=f2, count(f2) from t1 group by 1 desc; 335@a:=f2 count(f2) 336d 1 337c 2 338b 1 339a 4 340select @a:=f3, count(f3) from t1 group by 1 desc; 341@a:=f3 count(f3) 3424.5 1 3433.5 2 3442.5 1 3451.5 4 346select @a:=f4, count(f4) from t1 group by 1 desc; 347@a:=f4 count(f4) 3484.6 1 3493.6 2 3502.6 1 3511.6 4 352drop table t1; 353create table t1 (f1 int); 354insert into t1 values (2), (1); 355select @i := f1 as j from t1 order by 1; 356j 3571 3582 359drop table t1; 360create table t1(a int); 361insert into t1 values(5),(4),(4),(3),(2),(2),(2),(1); 362set @rownum := 0; 363set @rank := 0; 364set @prev_score := NULL; 365select @rownum := @rownum + 1 as row, 366@rank := IF(@prev_score!=a, @rownum, @rank) as rank, 367@prev_score := a as score 368from t1 order by score desc; 369drop table t1; 370create table t1(b bigint); 371insert into t1 (b) values (10), (30), (10); 372set @var := 0; 373select if(b=@var, 999, b) , @var := b from t1 order by b; 374if(b=@var, 999, b) @var := b 37510 10 376999 10 37730 30 378drop table t1; 379create temporary table t1 (id int); 380insert into t1 values (2), (3), (3), (4); 381set @lastid=-1; 382select @lastid != id, @lastid, @lastid := id from t1; 383@lastid != id @lastid @lastid := id 3841 -1 2 3851 2 3 3860 3 3 3871 3 4 388drop table t1; 389create temporary table t1 (id bigint); 390insert into t1 values (2), (3), (3), (4); 391set @lastid=-1; 392select @lastid != id, @lastid, @lastid := id from t1; 393@lastid != id @lastid @lastid := id 3941 -1 2 3951 2 3 3960 3 3 3971 3 4 398drop table t1; 399CREATE TABLE t1(a INT, b INT); 400INSERT INTO t1 VALUES (0, 0), (2, 1), (2, 3), (1, 1), (30, 20); 401SELECT a, b INTO @a, @b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b; 402SELECT @a, @b; 403@a @b 4042 3 405SELECT a, b FROM t1 WHERE a=2 AND b=3 GROUP BY a, b; 406a b 4072 3 408DROP TABLE t1; 409CREATE TABLE t1 (f1 int(11) default NULL, f2 int(11) default NULL); 410CREATE TABLE t2 (f1 int(11) default NULL, f2 int(11) default NULL, foo int(11)); 411CREATE TABLE t3 (f1 int(11) default NULL, f2 int(11) default NULL); 412INSERT INTO t1 VALUES(10, 10); 413INSERT INTO t1 VALUES(10, 10); 414INSERT INTO t2 VALUES(10, 10, 10); 415INSERT INTO t2 VALUES(10, 10, 10); 416INSERT INTO t3 VALUES(10, 10); 417INSERT INTO t3 VALUES(10, 10); 418SELECT MIN(t2.f1), 419@bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo) 420FROM t1,t2 WHERE t1.f1 = t2.f1 ORDER BY t2.f1; 421MIN(t2.f1) @bar:= (SELECT MIN(t3.f2) FROM t3 WHERE t3.f2 > foo) 42210 NULL 423DROP TABLE t1, t2, t3; 424End of 5.0 tests 425CREATE TABLE t1 (i INT); 426CREATE TRIGGER t_after_insert AFTER INSERT ON t1 FOR EACH ROW SET @bug42188 = 10; 427INSERT INTO t1 VALUES (1); 428INSERT INTO t1 VALUES (1); 429DROP TABLE t1; 430CREATE TABLE t1(a INT); 431INSERT INTO t1 VALUES (0),(0); 432# BUG#55615 : should not crash 433SELECT (@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) FROM t1 GROUP BY @a; 434(@a:=(SELECT @a:=1 FROM t1 LIMIT 1)) AND COUNT(1) 4351 4361 437# BUG#55564 : should not crash 438SELECT IF( 439@v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a), 440count(*), 1) 441FROM t1 GROUP BY a LIMIT 1; 442IF( 443@v:=LEAST((SELECT 1 FROM t1 t2 LEFT JOIN t1 ON (@v) GROUP BY t1.a), a), 444count(*), 1) 4451 446DROP TABLE t1; 447select @v:=@v:=sum(1) from dual; 448@v:=@v:=sum(1) 4491 450CREATE TABLE t1(a DECIMAL(31,21)); 451INSERT INTO t1 VALUES (0); 452SELECT (@v:=a) <> (@v:=1) FROM t1; 453(@v:=a) <> (@v:=1) 4541 455DROP TABLE t1; 456CREATE TABLE t1(a int); 457INSERT INTO t1 VALUES (1), (2); 458SELECT DISTINCT @a:=MIN(t1.a) FROM t1, t1 AS t2 459GROUP BY @b:=(SELECT COUNT(*) > t2.a); 460@a:=MIN(t1.a) 4611 462DROP TABLE t1; 463SET @bug12408412=1; 464SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412; 465End of 5.1 tests 466CREATE TABLE t1(a INT); 467INSERT INTO t1 VALUES (0); 468SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a)) 469AS b FROM t1 GROUP BY a; 470b 4711 472SELECT @a; 473@a 4741 475DROP TABLE t1; 476CREATE TABLE t1(f1 INT, f2 INT); 477INSERT INTO t1 VALUES (1,2),(2,3),(3,1); 478CREATE TABLE t2(a INT); 479INSERT INTO t2 VALUES (1); 480SET @var=NULL; 481SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC 482LIMIT 1; 483@var:=(SELECT f2 FROM t2 WHERE @var) 484NULL 485SELECT @var; 486@var 487NULL 488DROP TABLE t1, t2; 489CREATE TABLE t1(a INT); 490INSERT INTO t1 VALUES (0),(1),(3); 491SELECT DISTINCT POW(COUNT(distinct a), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a limit 1)) AS b FROM t1 GROUP BY a; 492b 4931 494SELECT @a; 495@a 4961 497DROP TABLE t1; 498End of 5.2 tests 499CREATE TABLE t1(f1 INT AUTO_INCREMENT, PRIMARY KEY(f1)); 500INSERT INTO t1 SET f1 = NULL ; 501SET @aux = NULL ; 502INSERT INTO t1 SET f1 = @aux ; 503SET @aux1 = 0.123E-1; 504SET @aux1 = NULL; 505INSERT INTO t1 SET f1 = @aux1 ; 506SELECT * FROM t1; 507f1 5081 5092 5103 511DROP TABLE t1; 512CREATE TABLE t1(f1 VARCHAR(257) , f2 INT, PRIMARY KEY(f2)); 513CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1; 514SET @aux = 1; 515SET @aux = NULL; 516INSERT INTO test.t1 (f1, f2) VALUES (1, 1), (@aux, 2); 517SET @aux = 'text'; 518SET @aux = NULL; 519INSERT INTO t1(f1, f2) VALUES (1, 3), (@aux, 4); 520SELECT f1, f2 FROM t1 ORDER BY f2; 521f1 f2 5221 1 5231 2 5241 3 5251 4 526DROP TRIGGER trg1; 527DROP TABLE t1; 528# 529# Bug #12408412: GROUP_CONCAT + ORDER BY + INPUT/OUTPUT 530# SAME USER VARIABLE = CRASH 531# 532SET @bug12408412=1; 533SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412; 534CREATE TABLE t1(a INT); 535INSERT INTO t1 VALUES (0); 536SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a)) 537AS b FROM t1 GROUP BY a; 538b 5391 540SELECT @a; 541@a 5421 543DROP TABLE t1; 544CREATE TABLE t1(f1 INT, f2 INT); 545INSERT INTO t1 VALUES (1,2),(2,3),(3,1); 546CREATE TABLE t2(a INT); 547INSERT INTO t2 VALUES (1); 548SET @var=NULL; 549SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC 550LIMIT 1; 551@var:=(SELECT f2 FROM t2 WHERE @var) 552NULL 553SELECT @var; 554@var 555NULL 556DROP TABLE t1, t2; 557CREATE TABLE t1(a INT); 558INSERT INTO t1 VALUES (0),(1),(3); 559SELECT DISTINCT POW(COUNT(distinct a), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a limit 1)) AS b FROM t1 GROUP BY a; 560b 5611 562SELECT @a; 563@a 5641 565DROP TABLE t1; 566End of 5.5 tests 567# 568# Check that used memory extends if we set a variable 569# 570set @var= repeat('a',20000); 5711 572explain select @a:=max(seq) from seq_1_to_1000000; 573id select_type table type possible_keys key key_len ref rows Extra 5741 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 575# 576# Start of 10.3 tests 577# 578# 579# MDEV-12859 Out-of-range error for CREATE..SELECT @a:=EXTRACT(MINUTE_MICROSECOND FROM.. 580# 581SET sql_mode=STRICT_ALL_TABLES; 582CREATE OR REPLACE TABLE t1 AS 583SELECT @a:=EXTRACT(MINUTE_MICROSECOND FROM '2001-01-01 11:22:33.999999') AS c1; 584SHOW CREATE TABLE t1; 585Table Create Table 586t1 CREATE TABLE `t1` ( 587 `c1` bigint(11) DEFAULT NULL 588) ENGINE=MyISAM DEFAULT CHARSET=latin1 589SELECT * FROM t1; 590c1 5912233999999 592DROP TABLE t1; 593SET sql_mode=DEFAULT; 594