Debian Codegraph @

mysql-test/suite/funcs_1/r/myisam_storedproc_07.result

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
4 --------------------------------------------------------------------------------
5 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
6
7 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
8 --------------------------------------------------------------------------------
9 DROP DATABASE IF EXISTS db_storedproc;
10 DROP DATABASE IF EXISTS db_storedproc_1;
11 CREATE DATABASE db_storedproc charset utf8mb4;
12 CREATE DATABASE db_storedproc_1 charset utf8mb4;
13 USE db_storedproc;
14 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
15 engine = <engine_to_be_tested>;
16 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
17 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
18 engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
20 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
21 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
22 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
23 engine = <engine_to_be_tested>;
24 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
25 USE db_storedproc_1;
26 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
27 engine = <engine_to_be_tested>;
28 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
29 USE db_storedproc;
30 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
31 engine = <engine_to_be_tested>;
32 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
33 Warnings:
34 Warning 1265 Data truncated for column 'f3' at row 1
35 Warning 1265 Data truncated for column 'f3' at row 2
36 Warning 1265 Data truncated for column 'f3' at row 3
37 Warning 1265 Data truncated for column 'f3' at row 4
38 Warning 1265 Data truncated for column 'f3' at row 5
39 Warning 1265 Data truncated for column 'f3' at row 6
40 Warning 1265 Data truncated for column 'f3' at row 7
41 Warning 1265 Data truncated for column 'f3' at row 8
42 Warning 1265 Data truncated for column 'f3' at row 9
43 Warning 1265 Data truncated for column 'f3' at row 10
44 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
45 engine = <engine_to_be_tested>;
46 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
47 Warnings:
48 Warning 1265 Data truncated for column 'f3' at row 1
49 Warning 1265 Data truncated for column 'f3' at row 2
50 Warning 1265 Data truncated for column 'f3' at row 3
51 Warning 1265 Data truncated for column 'f3' at row 4
52 Warning 1265 Data truncated for column 'f3' at row 5
53 Warning 1265 Data truncated for column 'f3' at row 6
54 Warning 1265 Data truncated for column 'f3' at row 7
55 Warning 1265 Data truncated for column 'f3' at row 8
56 Warning 1265 Data truncated for column 'f3' at row 9
57 Warning 1265 Data truncated for column 'f3' at row 10
58 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
59 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
60 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
61 engine = <engine_to_be_tested>;
62 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
63 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
64 engine = <engine_to_be_tested>;
65 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
66 SET sql_mode = default;
67 Warnings:
68 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
69
70 Section 3.1.7 - SQL mode checks:
71 --------------------------------------------------------------------------------
72 Warnings:
73 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
74 USE db_storedproc;
75 Warnings:
76 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
77
78 Testcase 3.1.7.1:
79 -----------------
80 Ensure that the sql_mode setting in effect at the time a stored procedure is
81 created is the same setting under which the stored procedure runs when it is
82 called/executed.
83 --------------------------------------------------------------------------------
84 Warnings:
85 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
86 DROP PROCEDURE IF EXISTS sp1;
87 DROP TABLE IF EXISTS temp_tbl;
88 DROP TABLE IF EXISTS result;
89 CREATE TABLE temp_tbl (f1 tinyint);
90 CREATE TABLE result (f1 text(200), f2 char(20));
91 set @@sql_mode='traditional';
92 SHOW VARIABLES LIKE 'sql_mode';
93 Variable_name Value
94 sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
95 CREATE PROCEDURE sp1()
96 BEGIN
97 declare a tinyint;
98 declare count_ int default 1;
99 declare continue handler for sqlstate '22003' set count_=1000;
100 SHOW VARIABLES LIKE 'sql_mode';
101 SELECT @@sql_mode into @cur_val_sql_mode;
102 insert into temp_tbl values (1000);
103 if count_ = 1000 THEN
104 INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
105 ELSE
106 INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
107 END if;
108 END//
109 SHOW CREATE PROCEDURE sp1;
110 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
111 sp1 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`()
112 BEGIN
113 declare a tinyint;
114 declare count_ int default 1;
115 declare continue handler for sqlstate '22003' set count_=1000;
116 SHOW VARIABLES LIKE 'sql_mode';
117 SELECT @@sql_mode into @cur_val_sql_mode;
118 insert into temp_tbl values (1000);
119 if count_ = 1000 THEN
120 INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
121 ELSE
122 INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
123 END if;
124 END utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
125 set @@sql_mode='';
126 SHOW VARIABLES LIKE 'sql_mode';
127 Variable_name Value
128 sql_mode
129 CALL sp1();
130 Variable_name Value
131 sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
132 SELECT * from result;
133 f1 f2
134 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION value restored
135 SHOW VARIABLES LIKE 'sql_mode';
136 Variable_name Value
137 sql_mode
138 SELECT @@sql_mode;
139 @@sql_mode
140
141 SET @@sql_mode='TRADITIONAL';
142 DROP PROCEDURE sp1;
143 DROP TABLE temp_tbl;
144 DROP TABLE result;
145 Warnings:
146 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
147
148 Testcase 3.1.7.2:
149 -----------------
150 Ensure that if the sql_mode setting is changed when a stored procedure is run,
151 that the original setting is restored as soon as the stored procedure execution
152 is complete.
153 --------------------------------------------------------------------------------
154 Warnings:
155 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
156 DROP PROCEDURE IF EXISTS sp2;
157 ... show initial value
158 SHOW VARIABLES LIKE 'sql_mode';
159 Variable_name Value
160 sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
161 CREATE PROCEDURE sp2()
162 BEGIN
163 SET @@sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';
164 SHOW VARIABLES LIKE 'sql_mode';
165 END//
166 SHOW CREATE PROCEDURE sp2;
167 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
168 sp2 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `sp2`()
169 BEGIN
170 SET @@sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';
171 SHOW VARIABLES LIKE 'sql_mode';
172 END utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
173 ... show value prior calling procedure
174 SHOW VARIABLES LIKE 'sql_mode';
175 Variable_name Value
176 sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
177 ... call procedure that changes sql_mode
178 CALL sp2();
179 Variable_name Value
180 sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE
181 ... check whether old value is re-set
182 SHOW VARIABLES LIKE 'sql_mode';
183 Variable_name Value
184 sql_mode STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
185 DROP PROCEDURE sp2;
186
187 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
188 --------------------------------------------------------------------------------
189 DROP DATABASE IF EXISTS db_storedproc;
190 DROP DATABASE IF EXISTS db_storedproc_1;
191 Warnings:
192 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
193
194 . +++ END OF SCRIPT +++
195 --------------------------------------------------------------------------------
196 Warnings:
197 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.