Debian Codegraph @

mysql-test/suite/sys_vars/inc/explicit_defaults_for_timestamp.inc

raw
1 CREATE TABLE t1 (a TIMESTAMP);
2 SHOW CREATE TABLE t1;
3 DROP TABLE t1;
4 CREATE TABLE t1 (a TIMESTAMP NULL);
5 SHOW CREATE TABLE t1;
6 DROP TABLE t1;
7
8 CREATE TABLE t1 (a TIMESTAMP DEFAULT NULL);
9 INSERT t1 () VALUES ();
10 SHOW CREATE TABLE t1;
11 DROP TABLE t1;
12
13 CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');
14 SHOW CREATE TABLE t1;
15 DROP TABLE t1;
16 CREATE TABLE t1 (a TIMESTAMP DEFAULT '2001-01-01 10:20:30');
17 SHOW CREATE TABLE t1;
18 DROP TABLE t1;
19 CREATE TABLE t1 (a TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
20 SHOW CREATE TABLE t1;
21 DROP TABLE t1;
22
23
24 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT NULL);
25 SHOW CREATE TABLE t1;
26 DROP TABLE t1;
27 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
28 SHOW CREATE TABLE t1;
29 DROP TABLE t1;
30 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT '2001-01-01 10:20:30');
31 SHOW CREATE TABLE t1;
32 DROP TABLE t1;
33 CREATE TABLE t1 (a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
34 SHOW CREATE TABLE t1;
35 DROP TABLE t1;
36
37
38 CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
39 SHOW CREATE TABLE t1;
40 DROP TABLE t1;
41 CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT '2001-01-01 10:20:30');
42 SHOW CREATE TABLE t1;
43 DROP TABLE t1;
44 CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
45 SHOW CREATE TABLE t1;
46 DROP TABLE t1;
47
48
49 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL);
50 SHOW CREATE TABLE t1;
51 DROP TABLE t1;
52 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL);
53 SHOW CREATE TABLE t1;
54 DROP TABLE t1;
55
56
57 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT '0000-00-00 00:00:00');
58 SHOW CREATE TABLE t1;
59 DROP TABLE t1;
60 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
61 SHOW CREATE TABLE t1;
62 DROP TABLE t1;
63 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00');
64 SHOW CREATE TABLE t1;
65 DROP TABLE t1;
66
67
68 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
69 SHOW CREATE TABLE t1;
70 DROP TABLE t1;
71 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
72 SHOW CREATE TABLE t1;
73 DROP TABLE t1;
74 CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
75 SHOW CREATE TABLE t1;
76 DROP TABLE t1;
77
78
79 CREATE TABLE t1 (a TIMESTAMP) AS SELECT 1 AS i;
80 SHOW CREATE TABLE t1;
81 CREATE TABLE t2 (b TIMESTAMP) AS SELECT a FROM t1;
82 SHOW CREATE TABLE t2;
83 DROP TABLE t2;
84 DROP TABLE t1;
85
86
87 CREATE TABLE t1 (a INT);
88 ALTER TABLE t1 ADD b TIMESTAMP;
89 SHOW CREATE TABLE t1;
90 DROP TABLE t1;
91
92 --echo #
93 --echo # MDEV-10802 TIMESTAMP NOT NULL field with explicit_defaults_for_timestamp and NO_ZERO_DATE shouldn't throw error
94 --echo #
95
96 SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
97 SET sql_mode='ANSI,NO_ZERO_DATE';
98 CREATE TABLE t1 (a TIMESTAMP NOT NULL);
99 INSERT INTO t1 VALUES ();
100 SELECT * FROM t1;
101 DROP TABLE t1;
102 SET sql_mode=DEFAULT;
103 SET timestamp=DEFAULT;
104
105 --echo #
106 --echo # MDEV-29075 Changing explicit_defaults_for_timestamp within stored procedure works inconsistently
107 --echo #
108 --disable_warnings
109 set statement explicit_defaults_for_timestamp=1-@@explicit_defaults_for_timestamp for create table t1 (ts timestamp);
110 --enable_warnings
111 show create table t1;
112 drop table t1;
113
114 --delimiter $
115 create procedure pr()
116 begin
117 set explicit_defaults_for_timestamp= 1-@@explicit_defaults_for_timestamp;
118 create table t1 (ts timestamp);
119 end $
120 --delimiter ;
121
122 call pr();
123 show create table t1;
124 drop procedure pr;
125 drop table t1;
126
127 prepare stmt from 'create or replace table t1 (a timestamp)';
128 execute stmt;
129 show create table t1;
130 set explicit_defaults_for_timestamp=1-@@explicit_defaults_for_timestamp;
131 execute stmt;
132 show create table t1;
133 set explicit_defaults_for_timestamp=1-@@explicit_defaults_for_timestamp;
134 execute stmt;
135 show create table t1;
136 drop table t1;