summaryrefslogtreecommitdiff
path: root/mysql-test/main/user_var.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/user_var.result')
-rw-r--r--mysql-test/main/user_var.result593
1 files changed, 593 insertions, 0 deletions
diff --git a/mysql-test/main/user_var.result b/mysql-test/main/user_var.result
new file mode 100644
index 00000000000..bf3d4f6dada
--- /dev/null
+++ b/mysql-test/main/user_var.result
@@ -0,0 +1,593 @@
+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
+explain select * from t1 where i=@vv1;
+id select_type table type possible_keys key key_len ref rows Extra
+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
+explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
+explain select @vv1:=i from t1 where i=@vv1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL i 4 NULL 3 Using where; Using index
+explain select * from t1 where i=@vv1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i i 4 const 1
+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
+select @a:="10", @b:="1", @a > @b, @a < @b;
+@a:="10" @b:="1" @a > @b @a < @b
+10 1 1 0
+select @a:=10, @b:=2, @a > @b, @a < @b;
+@a:=10 @b:=2 @a > @b @a < @b
+10 2 0 1
+select @a:="10", @b:="2", @a > @b, @a < @b;
+@a:="10" @b:="2" @a > @b @a < @b
+10 2 1 0
+select @a:=1;
+@a:=1
+1
+select @a, @a:=1;
+@a @a:=1
+1 1
+create table t1 (id int, d double, c char(10));
+insert into t1 values (1,2.0, "test");
+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
+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 0
+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
+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
+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
+0 2 2 0
+0 3 3 0
+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
+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
+select collation(@a:=_latin2'test');
+collation(@a:=_latin2'test')
+latin2_general_ci
+select coercibility(@a:=_latin2'test');
+coercibility(@a:=_latin2'test')
+2
+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)
+2
+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)
+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
+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 MariaDB 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 DEFAULT NULL
+) 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` (
+ `@first_var` bigint(20) DEFAULT NULL
+) 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` (
+ `@first_var` bigint(20) DEFAULT NULL
+) 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 DEFAULT NULL
+) 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 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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
+select @var;
+@var
+3
+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;
+insert into city 'blah';
+ERROR 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
+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 desc;
+@a:=f1 count(f1)
+4 1
+3 2
+2 1
+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
+select @a:=f2, count(f2) from t1 group by 1 desc;
+@a:=f2 count(f2)
+d 1
+c 2
+b 1
+a 4
+select @a:=f3, count(f3) from t1 group by 1 desc;
+@a:=f3 count(f3)
+4.5 1
+3.5 2
+2.5 1
+1.5 4
+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
+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
+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
+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
+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
+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);
+CREATE TABLE t2 (f1 int(11) default NULL, f2 int(11) default NULL, foo int(11));
+CREATE TABLE t3 (f1 int(11) default NULL, f2 int(11) default NULL);
+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
+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
+# 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
+DROP TABLE t1;
+select @v:=@v:=sum(1) from dual;
+@v:=@v:=sum(1)
+1
+CREATE TABLE t1(a DECIMAL(31,21));
+INSERT INTO t1 VALUES (0);
+SELECT (@v:=a) <> (@v:=1) FROM t1;
+(@v:=a) <> (@v:=1)
+1
+DROP TABLE t1;
+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
+DROP TABLE t1;
+SET @bug12408412=1;
+SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412;
+End of 5.1 tests
+CREATE TABLE t1(a INT);
+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
+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
+SELECT @var;
+@var
+NULL
+DROP TABLE t1, t2;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(3);
+SELECT 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;
+b
+1
+SELECT @a;
+@a
+1
+DROP TABLE t1;
+End of 5.2 tests
+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;
+CREATE TABLE t1(a INT);
+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
+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
+SELECT @var;
+@var
+NULL
+DROP TABLE t1, t2;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(3);
+SELECT 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;
+b
+1
+SELECT @a;
+@a
+1
+DROP TABLE t1;
+End of 5.5 tests
+#
+# Check that used memory extends if we set a variable
+#
+set @var= repeat('a',20000);
+1
+explain select @a:=max(seq) from seq_1_to_1000000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+#
+# Start of 10.3 tests
+#
+#
+# MDEV-12859 Out-of-range error for CREATE..SELECT @a:=EXTRACT(MINUTE_MICROSECOND FROM..
+#
+SET sql_mode=STRICT_ALL_TABLES;
+CREATE OR REPLACE TABLE t1 AS
+SELECT @a:=EXTRACT(MINUTE_MICROSECOND FROM '2001-01-01 11:22:33.999999') AS c1;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` bigint(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT * FROM t1;
+c1
+2233999999
+DROP TABLE t1;
+SET sql_mode=DEFAULT;
View Lorry Controller Status