Debian Codegraph @

mysql-test/suite/sys_vars/r/explicit_defaults_for_timestamp_off.result

raw
1 set @@explicit_defaults_for_timestamp=0;
2 Warnings:
3 Warning 1287 'explicit_defaults_for_timestamp=0' is deprecated and will be removed in a future release
4 CREATE TABLE t1 (a TIMESTAMP);
5 SHOW CREATE TABLE t1;
6 Table Create Table
7 t1 CREATE TABLE `t1` (
8 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
9 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
10 DROP TABLE t1;
11 CREATE TABLE t1 (a TIMESTAMP NULL);
12 SHOW CREATE TABLE t1;
13 Table Create Table
14 t1 CREATE TABLE `t1` (
15 `a` timestamp NULL DEFAULT NULL
16 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
17 DROP TABLE t1;
18 CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL);
19 INSERT t1 () VALUES ();
20 SHOW CREATE TABLE t1;
21 Table Create Table
22 t1 CREATE TABLE `t1` (
23 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
24 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
25 DROP TABLE t1;
26 CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');
27 SHOW CREATE TABLE t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
31 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
32 DROP TABLE t1;
33 CREATE TABLE t1 (a TIMESTAMP DEFAULT '2001-01-01 10:20:30');
34 SHOW CREATE TABLE t1;
35 Table Create Table
36 t1 CREATE TABLE `t1` (
37 `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30'
38 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
39 DROP TABLE t1;
40 CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
41 SHOW CREATE TABLE t1;
42 Table Create Table
43 t1 CREATE TABLE `t1` (
44 `a` timestamp NOT NULL DEFAULT current_timestamp()
45 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
46 DROP TABLE t1;
47 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT NULL);
48 SHOW CREATE TABLE t1;
49 Table Create Table
50 t1 CREATE TABLE `t1` (
51 `a` timestamp NULL DEFAULT NULL
52 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
53 DROP TABLE t1;
54 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
55 SHOW CREATE TABLE t1;
56 Table Create Table
57 t1 CREATE TABLE `t1` (
58 `a` timestamp NULL DEFAULT '0000-00-00 00:00:00'
59 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
60 DROP TABLE t1;
61 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '2001-01-01 10:20:30');
62 SHOW CREATE TABLE t1;
63 Table Create Table
64 t1 CREATE TABLE `t1` (
65 `a` timestamp NULL DEFAULT '2001-01-01 10:20:30'
66 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
67 DROP TABLE t1;
68 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
69 SHOW CREATE TABLE t1;
70 Table Create Table
71 t1 CREATE TABLE `t1` (
72 `a` timestamp NULL DEFAULT current_timestamp()
73 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
74 DROP TABLE t1;
75 CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
76 SHOW CREATE TABLE t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
80 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
81 DROP TABLE t1;
82 CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30');
83 SHOW CREATE TABLE t1;
84 Table Create Table
85 t1 CREATE TABLE `t1` (
86 `a` timestamp NOT NULL DEFAULT '2001-01-01 10:20:30'
87 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
88 DROP TABLE t1;
89 CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
90 SHOW CREATE TABLE t1;
91 Table Create Table
92 t1 CREATE TABLE `t1` (
93 `a` timestamp NOT NULL DEFAULT current_timestamp()
94 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
95 DROP TABLE t1;
96 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL);
97 SHOW CREATE TABLE t1;
98 Table Create Table
99 t1 CREATE TABLE `t1` (
100 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
101 `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
102 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
103 DROP TABLE t1;
104 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL);
105 SHOW CREATE TABLE t1;
106 Table Create Table
107 t1 CREATE TABLE `t1` (
108 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
109 `b` timestamp NULL DEFAULT NULL
110 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
111 DROP TABLE t1;
112 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT '0000-00-00 00:00:00');
113 SHOW CREATE TABLE t1;
114 Table Create Table
115 t1 CREATE TABLE `t1` (
116 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
117 `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
118 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
119 DROP TABLE t1;
120 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
121 SHOW CREATE TABLE t1;
122 Table Create Table
123 t1 CREATE TABLE `t1` (
124 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
125 `b` timestamp NULL DEFAULT '0000-00-00 00:00:00'
126 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
127 DROP TABLE t1;
128 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
129 SHOW CREATE TABLE t1;
130 Table Create Table
131 t1 CREATE TABLE `t1` (
132 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
133 `b` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
134 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
135 DROP TABLE t1;
136 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
137 SHOW CREATE TABLE t1;
138 Table Create Table
139 t1 CREATE TABLE `t1` (
140 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
141 `b` timestamp NOT NULL DEFAULT current_timestamp()
142 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
143 DROP TABLE t1;
144 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
145 SHOW CREATE TABLE t1;
146 Table Create Table
147 t1 CREATE TABLE `t1` (
148 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
149 `b` timestamp NULL DEFAULT current_timestamp()
150 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
151 DROP TABLE t1;
152 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
153 SHOW CREATE TABLE t1;
154 Table Create Table
155 t1 CREATE TABLE `t1` (
156 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
157 `b` timestamp NOT NULL DEFAULT current_timestamp()
158 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
159 DROP TABLE t1;
160 CREATE TABLE t1 (a TIMESTAMP) AS SELECT 1 AS i;
161 SHOW CREATE TABLE t1;
162 Table Create Table
163 t1 CREATE TABLE `t1` (
164 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
165 `i` int(1) NOT NULL
166 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
167 CREATE TABLE t2 (b TIMESTAMP) AS SELECT a FROM t1;
168 SHOW CREATE TABLE t2;
169 Table Create Table
170 t2 CREATE TABLE `t2` (
171 `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
172 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
173 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
174 DROP TABLE t2;
175 DROP TABLE t1;
176 CREATE TABLE t1 (a INT);
177 ALTER TABLE t1 ADD b TIMESTAMP;
178 SHOW CREATE TABLE t1;
179 Table Create Table
180 t1 CREATE TABLE `t1` (
181 `a` int(11) DEFAULT NULL,
182 `b` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
183 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
184 DROP TABLE t1;
185 #
186 # MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error
187 #
188 SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
189 SET sql_mode='ANSI,NO_ZERO_DATE';
190 CREATE TABLE t1 (a TIMESTAMP NOT NULL);
191 INSERT INTO t1 VALUES ();
192 SELECT * FROM t1;
193 a
194 2001-01-01 10:20:30
195 DROP TABLE t1;
196 SET sql_mode=DEFAULT;
197 SET timestamp=DEFAULT;
198 #
199 # MDEV-29075 Changing explicit_defaults_for_timestamp within stored procedure works inconsistently
200 #
201 set statement explicit_defaults_for_timestamp=1-@@explicit_defaults_for_timestamp for create table t1 (ts timestamp);
202 show create table t1;
203 Table Create Table
204 t1 CREATE TABLE `t1` (
205 `ts` timestamp NULL DEFAULT NULL
206 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
207 drop table t1;
208 create procedure pr()
209 begin
210 set explicit_defaults_for_timestamp= 1-@@explicit_defaults_for_timestamp;
211 create table t1 (ts timestamp);
212 end $
213 call pr();
214 show create table t1;
215 Table Create Table
216 t1 CREATE TABLE `t1` (
217 `ts` timestamp NULL DEFAULT NULL
218 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
219 drop procedure pr;
220 drop table t1;
221 prepare stmt from 'create or replace table t1 (a timestamp)';
222 execute stmt;
223 show create table t1;
224 Table Create Table
225 t1 CREATE TABLE `t1` (
226 `a` timestamp NULL DEFAULT NULL
227 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
228 set explicit_defaults_for_timestamp=1-@@explicit_defaults_for_timestamp;
229 Warnings:
230 Warning 1287 'explicit_defaults_for_timestamp=0' is deprecated and will be removed in a future release
231 execute stmt;
232 show create table t1;
233 Table Create Table
234 t1 CREATE TABLE `t1` (
235 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
236 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
237 set explicit_defaults_for_timestamp=1-@@explicit_defaults_for_timestamp;
238 execute stmt;
239 show create table t1;
240 Table Create Table
241 t1 CREATE TABLE `t1` (
242 `a` timestamp NULL DEFAULT NULL
243 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
244 drop table t1;