1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
set @v1a="Hello";
set @v2a="World";
set @v3a=65;
set @v4a=NULL;
select @v1a, @v2a, @v3a, @v4a, @v_unknown;
@v1a @v2a @v3a @v4a @v_unknown
Hello World 65 NULL NULL
select VARIABLE_NAME, VARIABLE_VALUE
from performance_schema.user_variables_by_thread
order by VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
v1a Hello
v2a World
v3a 65
v4a NULL
set @v1b="Salut";
set @v2b="La compagnie";
set @v3b=12;
set @v4b=NULL;
select @v1b, @v2b, @v3b, @v4b, @v_unknown;
@v1b @v2b @v3b @v4b @v_unknown
Salut La compagnie 12 NULL NULL
select VARIABLE_NAME, VARIABLE_VALUE
from performance_schema.user_variables_by_thread
order by VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
v1a Hello
v1b Salut
v2a World
v2b La compagnie
v3a 65
v3b 12
v4a NULL
v4b NULL
select VARIABLE_NAME, VARIABLE_VALUE
from performance_schema.user_variables_by_thread
order by VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
v1a Hello
v2a World
v3a 65
v4a NULL
set @blob_under_16_bits=repeat("a", 65000);
set @blob_under_24_bits=repeat("a", 1000000);
select length(@blob_under_16_bits);
length(@blob_under_16_bits)
65000
select length(@blob_under_24_bits);
length(@blob_under_24_bits)
1000000
select variable_name, length(variable_value)
from performance_schema.user_variables_by_thread
where VARIABLE_NAME like "blob%"
order by VARIABLE_NAME;
variable_name length(variable_value)
blob_under_16_bits 65000
blob_under_24_bits 1000000
set @a=10;
CREATE procedure test_user_val
(IN input INT,OUT output INT)
BEGIN
SELECT input + input
INTO output;
END|
CALL test_user_val(@a , @total);
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread
where variable_name in ('a','total') order by VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
a 10
total 20
create table t1 (c1 int);
CREATE TRIGGER test_uvar_trigger
AFTER INSERT ON t1
FOR EACH ROW BEGIN
set @a=@a+@a;
END|
insert into t1 values(1);
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread
where variable_name ='a' order by VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
a 20
insert into t1 values(1);
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread
where variable_name ='a' order by VARIABLE_NAME;
VARIABLE_NAME VARIABLE_VALUE
a 40
drop table t1;
drop procedure test_user_val;
#
# Bug#35781732 5.7 vs 8.0 user variables differences output in performance_schema.user_variables_by_thread
#
SET @bc=123.656;
SET @cde='123.656';
SELECT @bc,@cde,variable_name,variable_value,CAST(variable_value AS CHAR) val_char,CAST(variable_value AS DEC(9,4)) val_num
FROM performance_schema.user_variables_by_thread
WHERE (variable_name='bc' OR variable_name='cde')
ORDER BY variable_name;
@bc @cde variable_name variable_value val_char val_num
123.656 123.656 bc 123.656 123.656 123.6560
123.656 123.656 cde 123.656 123.656 123.6560
|
