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
|
--echo #
--echo # WL#14277 Deprecate user defined collations
--echo #
--echo
# Expect 1 warning due to CREATE TABLE ... (COLLATE <user_defined_collation>).
create table t (t text collate utf8mb4_test_ci);
--echo
delimiter |;
# Expect 3 warnings due to CREATE TABLE (... COLLATE <user_defined_collation>),
# DECLARE ... COLLATE <user_defined_collation>,
# and SET <val> = ... COLLATE <user_defined_collation>
create procedure p () begin
declare a varchar(1) charset utf8mb4 collate utf8mb4_test_ci;
set @a = "a" collate utf8mb4_test_ci;
create table tp (t varchar(1) collate utf8mb4_test_ci);
insert into t values (@a);
end|
--echo
# Expect 3 warnings due to CREATE FUNCTION ... RETURNS ...
# COLLATE <user_defined_collation>,
# DECLARE ... COLLATE <user_defined_collation>,
# and SET <var> = ... COLLATE <user_defined_collation>.
create function f () returns varchar(1) charset utf8mb4 collate utf8mb4_test_ci begin
declare a varchar(1) charset utf8mb4 collate utf8mb4_test_ci;
set @a = "a" collate utf8mb4_test_ci;
insert into tp values (@a);
return @a;
end|
--echo
# Expect 2 warnings due to DECLARE ... COLLATE <user_defined_collation> and
# SET <var> = ... COLLATE <user_defined_collation>.
create trigger tr after insert on t for each row begin
declare a varchar(1) charset utf8mb4 collate utf8mb4_test_ci;
set @a = "a" collate utf8mb4_test_ci;
insert into tp values (@a);
end|
--echo
delimiter ;|
# Expect 3 warnings due to procedures being parsed when called
call p();
--echo
# Expect 2 warnings
insert into t values ("a" collate utf8mb4_test_ci), ("a" collate utf8mb4_test_ci);
--echo
# Expect 3 warnings.
# The FUNCTION f() is only parsed once, which generates 3 warnings,
# even though it is called six times.
select t.*, f(), f() from t;
--echo
# Expect 9 rows in tp:
# 1 from trigger due to insert on t in p
# 2 from trigger due to insert on t in query
# 6 from trigger due to insert on t in f
select count(*) from tp;
--echo
drop table if exists tp;
drop table if exists t;
drop procedure if exists p;
drop function if exists f;
|
