Debian Codegraph @

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

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