1987
1987
ERROR HY000: Storage engine 'InnoDB' does not support system tables. [mysql.help_topic]
1989
1989
# End of Bug#11815557
1991
# BUG#17246318 - ALTER TABLE SHOULD NOT ALLOW CREATION OF TABLES
1992
# WITH BOTH 5.5 AND 5.6 TEMPORALS
1994
# BUG 18985760 -"FAST" ALTER TABLE CHANGE ON ENUM COLUMN
1995
# TRIGGERS FULL TABLE REBUILD.
1996
# Test for the case where 'avoid_temporal_upgrade' is set
1997
# to the DEFAULT value(OFF).
1998
ALTER TABLE t1 ENGINE= INNODB;
1999
#ALTER operations using INPLACE algorithm is disallowed
2000
#since the table contains old temporal type.
2001
ALTER TABLE t1 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= INPLACE;
2002
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2003
ALTER TABLE t1 ADD COLUMN fld4 TIMESTAMP FIRST, ALGORITHM= INPLACE;
2004
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2005
ALTER TABLE t1 ADD COLUMN fld4 TIMESTAMP AFTER f_timestamp, ALGORITHM= INPLACE;
2006
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2007
ALTER TABLE t1 CHANGE COLUMN f_time fld4 TIMESTAMP, ALGORITHM= INPLACE;
2008
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2009
ALTER TABLE t1 MODIFY f_datetime TIME, ALGORITHM= INPLACE;
2010
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2011
ALTER TABLE t1 ADD INDEX index1(f_datetime), ALGORITHM= INPLACE;
2012
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2013
#ALTER operations using COPY algorithm is allowed
2014
#when the table contains old temporal type.
2015
#Note: Timestamp encoding remains the same for the non-fractional part
2016
#even in the 5.6 format. Hence there is no change in the display before
2018
#ADD COLUMN upgrades the old temporal type.
2019
CREATE TABLE t2 LIKE t1;
2020
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2021
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2022
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2023
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2024
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2025
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= COPY;
2027
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2028
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2029
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2030
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2031
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2033
#ADD COLUMN FIRST upgrades the old temporal type.
2034
CREATE TABLE t2 LIKE t1;
2035
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2036
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2037
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2038
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2039
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2040
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP FIRST, ALGORITHM= COPY;
2042
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2043
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2044
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2045
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2046
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2048
#ADD COLUMN AFTER upgrades the old temporal type.
2049
CREATE TABLE t2 LIKE t1;
2050
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2051
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2052
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2053
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2054
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2055
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP AFTER f_timestamp, ALGORITHM= COPY;
2057
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2058
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2059
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2060
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2061
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2063
#CHANGE COLUMN upgrades the old temporal type.
2064
CREATE TABLE t2 LIKE t1;
2065
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2066
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2067
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2068
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2069
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2070
ALTER TABLE t2 CHANGE COLUMN f_timestamp fld4 TIMESTAMP, ALGORITHM= COPY;
2072
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2073
SELECT f_time, f_datetime, fld4, HEX(WEIGHT_STRING(f_time)),
2074
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(fld4)) FROM t2;
2075
f_time f_datetime fld4 HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(fld4))
2076
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2078
#MODIFY COLUMN upgrades the old temporal type.
2079
CREATE TABLE t2 LIKE t1;
2080
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2081
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2082
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2083
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2084
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2085
ALTER TABLE t2 MODIFY f_timestamp TIME, ALGORITHM= COPY;
2087
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2088
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2089
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2090
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2091
22:22:22 2011-11-21 22:22:22 22:22:22 816596 998AAB6596 816596
2093
#ADD INDEX upgrades the old temporal type.
2094
CREATE TABLE t2 LIKE t1;
2095
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2096
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2097
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2098
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2099
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2100
ALTER TABLE t2 ADD INDEX index1(f_timestamp), ALGORITHM= COPY;
2102
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2103
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2104
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2105
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2106
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2108
#ALTER operations using DEFAULT algorithm is allowed
2109
#when the table contains old temporal type.
2110
#ADD COLUMN upgrades the old temporal type.
2111
CREATE TABLE t2 LIKE t1;
2112
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2113
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2114
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2115
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2116
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2117
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= DEFAULT;
2119
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2120
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2121
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2122
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2123
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2125
#ADD COLUMN FIRST upgrades the old temporal type.
2126
CREATE TABLE t2 LIKE t1;
2127
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2128
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2129
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2130
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2131
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2132
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP FIRST, ALGORITHM= DEFAULT;
2134
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2135
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2136
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2137
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2138
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2140
#ADD COLUMN AFTER upgrades the old temporal type.
2141
CREATE TABLE t2 LIKE t1;
2142
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2143
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2144
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2145
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2146
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2147
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP AFTER f_timestamp, ALGORITHM= DEFAULT;
2149
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2150
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2151
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2152
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2153
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2155
#CHANGE COLUMN upgrades the old temporal type.
2156
CREATE TABLE t2 LIKE t1;
2157
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2158
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2159
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2160
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2161
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2162
ALTER TABLE t2 CHANGE COLUMN f_timestamp fld4 DATETIME, ALGORITHM= DEFAULT;
2164
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2165
SELECT f_time, f_datetime, fld4, HEX(WEIGHT_STRING(f_time)),
2166
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(fld4)) FROM t2;
2167
f_time f_datetime fld4 HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(fld4))
2168
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 998AAB6596
2170
#MODIFY COLUMN upgrades the old temporal type.
2171
CREATE TABLE t2 LIKE t1;
2172
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2173
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2174
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2175
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2176
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2177
ALTER TABLE t2 MODIFY f_timestamp TIME, ALGORITHM= DEFAULT;
2179
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2180
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2181
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2182
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2183
22:22:22 2011-11-21 22:22:22 22:22:22 816596 998AAB6596 816596
2185
#ADD INDEX upgrades the old temporal type.
2186
CREATE TABLE t2 LIKE t1;
2187
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2188
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2189
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2190
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2191
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2192
ALTER TABLE t2 ADD INDEX index1(f_timestamp), ALGORITHM= DEFAULT;
2194
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2195
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2196
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2197
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2198
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2200
#ALTER TABLE FORCE upgrades the old temporal types.
2201
CREATE TABLE t2 LIKE t1;
2202
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2203
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2204
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2205
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2206
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2207
ALTER TABLE t2 FORCE;
2209
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2210
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2211
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2212
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2213
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 816596 998AAB6596 4ECAA4EE
2215
#Examples where the NOT NULL/NULL FLAG and DEFAULT values are retained
2217
CREATE TABLE t2 LIKE t1;
2218
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2220
SHOW CREATE TABLE t2;
2222
t2 CREATE TABLE `t2` (
2223
`f_time` time DEFAULT NULL,
2224
`f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2225
`f_datetime` datetime DEFAULT NULL
2226
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2227
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= COPY;
2229
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
2231
SHOW CREATE TABLE t2;
2233
t2 CREATE TABLE `t2` (
2234
`f_time` time DEFAULT NULL,
2235
`f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2236
`f_datetime` datetime DEFAULT NULL,
2237
`fld4` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
2238
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2239
#Examples of the Alter operation which does not upgrade
2240
#the temporal formats.
2241
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2242
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
2243
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2244
10:10:10 2010-10-10 10:10:10 2031-11-21 17:11:01 818A92 0000124821911312 7468F975
2245
00:00:00 2000-01-01 01:01:01 2001-01-21 18:11:01 800000 00001230A2EA8AB5 3A6AFC05
2246
01:01:10 2020-01-01 01:01:01 1980-11-21 18:06:01 80277E 0000125F33D85AB5 147BF1D9
2247
NULL 2000-01-01 01:01:01 2015-11-21 17:11:01 NULL 00001230A2EA8AB5 56507B75
2248
00:00:00 NULL 2011-11-21 17:11:01 800000 NULL 4ECA5BF5
2249
ALTER TABLE t1 DROP COLUMN f_timestamp;
2250
SELECT f_time, f_datetime, HEX(WEIGHT_STRING(f_time)),
2251
HEX(WEIGHT_STRING(f_datetime)) FROM t1;
2252
f_time f_datetime HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime))
2253
10:10:10 2010-10-10 10:10:10 818A92 0000124821911312
2254
00:00:00 2000-01-01 01:01:01 800000 00001230A2EA8AB5
2255
01:01:10 2020-01-01 01:01:01 80277E 0000125F33D85AB5
2256
NULL 2000-01-01 01:01:01 NULL 00001230A2EA8AB5
2257
00:00:00 NULL 800000 NULL
2258
RENAME TABLE t1 to t3;
2259
SELECT f_time, f_datetime, HEX(WEIGHT_STRING(f_time)),
2260
HEX(WEIGHT_STRING(f_datetime)) FROM t3;
2261
f_time f_datetime HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime))
2262
10:10:10 2010-10-10 10:10:10 818A92 0000124821911312
2263
00:00:00 2000-01-01 01:01:01 800000 00001230A2EA8AB5
2264
01:01:10 2020-01-01 01:01:01 80277E 0000125F33D85AB5
2265
NULL 2000-01-01 01:01:01 NULL 00001230A2EA8AB5
2266
00:00:00 NULL 800000 NULL
2267
#Once the old temporal type is upgraded to new temporal type,
2268
#ADD/CHANGE COLUMN, ADD INDEX operations succeed using INPLACE
2270
ALTER TABLE t2 ADD COLUMN fld5 INT, ALGORITHM= INPLACE;
2271
ALTER TABLE t2 ADD INDEX index2(fld5), ALGORITHM= INPLACE;
2272
ALTER TABLE t2 CHANGE fld5 fld6 INT, ALGORITHM= INPLACE;
2273
ALTER TABLE t2 MODIFY fld6 INT, ALGORITHM= INPLACE;
2277
# BUG 18985760 -"FAST" ALTER TABLE CHANGE ON ENUM COLUMN
2278
# TRIGGERS FULL TABLE REBUILD.
2280
# To support INPLACE ALTER table operations later in the test.
2281
ALTER TABLE t1 ENGINE= INNODB;
2282
#Test cases with the global variable 'avoid_temporal_upgrade'
2284
SET @save_avoid_temporal_upgrade= @@global.avoid_temporal_upgrade;
2286
Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
2287
SET GLOBAL avoid_temporal_upgrade= ON;
2289
Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
2290
#ALTER operations using INPLACE algorithm are allowed
2291
#when the table contains old temporal type since
2292
#the global variable 'avoid_temporal_upgrade' is
2293
#enabled. The old temporal types are not upgraded.
2294
CREATE TABLE t2 LIKE t1;
2295
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2296
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2297
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2298
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2299
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2300
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= INPLACE;
2301
SELECT f_time, f_datetime, f_timestamp, fld4, HEX(WEIGHT_STRING(f_time)),
2302
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)),
2303
HEX(WEIGHT_STRING(fld4)) FROM t2;
2304
f_time f_datetime f_timestamp fld4 HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) HEX(WEIGHT_STRING(fld4))
2305
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 0000-00-00 00:00:00 83640E 0000124A7C3C8A4E 4ECAA4EE 00000000
2306
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2307
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2308
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2309
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2310
ALTER TABLE t2 ADD COLUMN fld5 TIME DEFAULT '101010' FIRST, ALGORITHM= INPLACE;
2311
SELECT f_time, f_datetime, f_timestamp, fld5, HEX(WEIGHT_STRING(f_time)),
2312
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)),
2313
HEX(WEIGHT_STRING(fld5)) FROM t2;
2314
f_time f_datetime f_timestamp fld5 HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) HEX(WEIGHT_STRING(fld5))
2315
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 10:10:10 83640E 0000124A7C3C8A4E 4ECAA4EE 80A28A
2316
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2317
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2318
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2319
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2320
ALTER TABLE t2 ADD COLUMN fld6 TIMESTAMP AFTER f_timestamp, ALGORITHM= INPLACE;
2321
SELECT f_time, f_datetime, f_timestamp, fld6, HEX(WEIGHT_STRING(f_time)),
2322
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)),
2323
HEX(WEIGHT_STRING(fld6)) FROM t2;
2324
f_time f_datetime f_timestamp fld6 HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp)) HEX(WEIGHT_STRING(fld6))
2325
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 0000-00-00 00:00:00 83640E 0000124A7C3C8A4E 4ECAA4EE 00000000
2326
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2327
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2328
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2329
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2330
ALTER TABLE t2 ADD INDEX index1(f_datetime), ALGORITHM= INPLACE;
2331
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2332
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2333
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2334
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2336
#ALTER operations using COPY algorithm are allowed
2337
#when the table contains old temporal type and
2338
#does not upgrade the old temporal types.
2339
#ADD COLUMN does not upgrade the old temporal type.
2340
CREATE TABLE t2 LIKE t1;
2341
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2342
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2343
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2344
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2345
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2346
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= COPY;
2347
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2348
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2349
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2350
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2351
#ADD COLUMN FIRST does not upgrade the old temporal type.
2352
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2353
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2354
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2355
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2356
ALTER TABLE t2 ADD COLUMN fld5 TIMESTAMP FIRST, ALGORITHM= COPY;
2357
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2358
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2359
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2360
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2361
#ADD COLUMN AFTER does not upgrade the old temporal type.
2362
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2363
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2364
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2365
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2366
ALTER TABLE t2 ADD COLUMN fld6 TIMESTAMP AFTER f_timestamp, ALGORITHM= COPY;
2367
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2368
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2369
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2370
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2371
#CHANGE COLUMN upgrades the old temporal type only for the column which
2373
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2374
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2375
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2376
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2377
ALTER TABLE t2 CHANGE COLUMN f_datetime fld7 DATETIME, ALGORITHM= COPY;
2378
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2379
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2380
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2381
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 4ECAA4EE
2382
#MODIFY COLUMN upgrades the old temporal type only for the column
2384
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2385
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2386
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2387
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 4ECAA4EE
2388
ALTER TABLE t2 MODIFY f_timestamp DATETIME, ALGORITHM= COPY;
2389
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2390
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2391
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2392
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 998AAB6596
2393
#ADD INDEX does not upgrade the old temporal type.
2394
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2395
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2396
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2397
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 998AAB6596
2398
ALTER TABLE t2 ADD INDEX index1(f_time), ALGORITHM= COPY;
2399
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2400
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2401
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2402
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 998AAB6596
2404
#ALTER operations using DEFAULT algorithm are allowed
2405
#when the table contains old temporal type and does not
2406
#upgrade the old temporal types.
2407
#ADD COLUMN does not upgrade the old temporal type.
2408
CREATE TABLE t2 LIKE t1;
2409
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2410
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2411
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2412
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2413
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2414
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= DEFAULT;
2415
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2416
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2417
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2418
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2419
#ADD COLUMN FIRST does not upgrade the old temporal type.
2420
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2421
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2422
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2423
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2424
ALTER TABLE t2 ADD COLUMN fld5 TIMESTAMP FIRST, ALGORITHM= DEFAULT;
2425
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2426
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2427
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2428
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2429
#ADD COLUMN AFTER does not upgrade the old temporal type.
2430
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2431
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2432
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2433
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2434
ALTER TABLE t2 ADD COLUMN fld6 TIMESTAMP AFTER f_timestamp, ALGORITHM= DEFAULT;
2435
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2436
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2437
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2438
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2439
#CHANGE COLUMN upgrades the old temporal type only for the column which
2441
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2442
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2443
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2444
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2445
ALTER TABLE t2 CHANGE COLUMN f_datetime fld7 DATETIME, ALGORITHM= DEFAULT;
2446
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2447
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2448
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2449
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 4ECAA4EE
2450
#MODIFY COLUMN upgrades the old temporal type only for the column
2452
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2453
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2454
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2455
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 4ECAA4EE
2456
ALTER TABLE t2 MODIFY f_timestamp DATETIME, ALGORITHM= DEFAULT;
2457
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2458
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2459
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2460
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 998AAB6596
2461
#ADD INDEX does not upgrade the old temporal type.
2462
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2463
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2464
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2465
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 998AAB6596
2466
ALTER TABLE t2 ADD INDEX index1(f_time), ALGORITHM= DEFAULT;
2467
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2468
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2469
f_time fld7 f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(fld7)) HEX(WEIGHT_STRING(f_timestamp))
2470
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 998AAB6596 998AAB6596
2472
#ALTER TABLE FORCE does not upgrade the old temporal types.
2473
CREATE TABLE t2 LIKE t1;
2474
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
2475
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2476
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2477
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2478
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2479
ALTER TABLE t2 FORCE;
2480
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2481
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2482
f_time f_datetime f_timestamp HEX(WEIGHT_STRING(f_time)) HEX(WEIGHT_STRING(f_datetime)) HEX(WEIGHT_STRING(f_timestamp))
2483
22:22:22 2011-11-21 22:22:22 2011-11-21 22:22:22 83640E 0000124A7C3C8A4E 4ECAA4EE
2485
SET @@global.avoid_temporal_upgrade= @save_avoid_temporal_upgrade;
2487
Warning 1287 '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
2488
#Test cases with the session variable 'show_old_temporals'
2491
CREATE TABLE t2(fld1 time, fld2 datetime, fld3 timestamp);
2492
SET @save_show_old_temporals= @@session.show_old_temporals;
2494
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
2495
SET SESSION show_old_temporals= ON;
2497
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
2498
#Displays a comment to indicate that the columns are of 5.5
2500
SHOW CREATE TABLE t1;
2502
t1 CREATE TABLE `t1` (
2503
`f_time` time /* 5.5 binary format */ DEFAULT NULL,
2504
`f_timestamp` timestamp /* 5.5 binary format */ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2505
`f_datetime` datetime /* 5.5 binary format */ DEFAULT NULL
2506
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2507
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
2509
time /* 5.5 binary format */
2510
timestamp /* 5.5 binary format */
2511
datetime /* 5.5 binary format */
2512
#Since the temporal types are in new format, no comment is
2514
SHOW CREATE TABLE t2;
2516
t2 CREATE TABLE `t2` (
2517
`fld1` time DEFAULT NULL,
2518
`fld2` datetime DEFAULT NULL,
2519
`fld3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
2520
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2521
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t2';
2526
#Does not display the comment for table with old temporal types
2527
#since the session variable 'show_old_temporals' is OFF.
2528
SET SESSION show_old_temporals= OFF;
2530
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.
2531
SHOW CREATE TABLE t1;
2533
t1 CREATE TABLE `t1` (
2534
`f_time` time DEFAULT NULL,
2535
`f_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
2536
`f_datetime` datetime DEFAULT NULL
2537
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2538
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
2544
SET @@session.show_old_temporals= @save_show_old_temporals;
2546
Warning 1287 '@@show_old_temporals' is deprecated and will be removed in a future release.