1617
1617
CREATE TABLE help_topic (dummy int) ENGINE=innodb;
1619
1619
--echo # End of Bug#11815557
1623
--echo # BUG#17246318 - ALTER TABLE SHOULD NOT ALLOW CREATION OF TABLES
1624
--echo # WITH BOTH 5.5 AND 5.6 TEMPORALS
1627
--echo # BUG 18985760 -"FAST" ALTER TABLE CHANGE ON ENUM COLUMN
1628
--echo # TRIGGERS FULL TABLE REBUILD.
1629
--echo # Test for the case where 'avoid_temporal_upgrade' is set
1630
--echo # to the DEFAULT value(OFF).
1632
let $MYSQLD_DATADIR= `select @@datadir`;
1633
--copy_file std_data/55_temporal.frm $MYSQLD_DATADIR/test/t1.frm
1634
--copy_file std_data/55_temporal.MYD $MYSQLD_DATADIR/test/t1.MYD
1635
--copy_file std_data/55_temporal.MYI $MYSQLD_DATADIR/test/t1.MYI
1637
ALTER TABLE t1 ENGINE= INNODB;
1639
--echo #ALTER operations using INPLACE algorithm is disallowed
1640
--echo #since the table contains old temporal type.
1642
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
1643
ALTER TABLE t1 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= INPLACE;
1645
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
1646
ALTER TABLE t1 ADD COLUMN fld4 TIMESTAMP FIRST, ALGORITHM= INPLACE;
1648
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
1649
ALTER TABLE t1 ADD COLUMN fld4 TIMESTAMP AFTER f_timestamp, ALGORITHM= INPLACE;
1651
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
1652
ALTER TABLE t1 CHANGE COLUMN f_time fld4 TIMESTAMP, ALGORITHM= INPLACE;
1654
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
1655
ALTER TABLE t1 MODIFY f_datetime TIME, ALGORITHM= INPLACE;
1657
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
1658
ALTER TABLE t1 ADD INDEX index1(f_datetime), ALGORITHM= INPLACE;
1660
--echo #ALTER operations using COPY algorithm is allowed
1661
--echo #when the table contains old temporal type.
1663
--echo #Note: Timestamp encoding remains the same for the non-fractional part
1664
--echo #even in the 5.6 format. Hence there is no change in the display before
1665
--echo #and after upgrade.
1667
--echo #ADD COLUMN upgrades the old temporal type.
1668
#Setup table having old temporal type.
1669
CREATE TABLE t2 LIKE t1;
1670
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1672
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1673
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1674
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= COPY;
1675
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1676
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1679
--echo #ADD COLUMN FIRST upgrades the old temporal type.
1680
#Setup table having old temporal type.
1681
CREATE TABLE t2 LIKE t1;
1682
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1684
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1685
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1686
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP FIRST, ALGORITHM= COPY;
1687
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1688
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1691
--echo #ADD COLUMN AFTER upgrades the old temporal type.
1692
#Setup table having old temporal type.
1693
CREATE TABLE t2 LIKE t1;
1694
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1696
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1697
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1698
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP AFTER f_timestamp, ALGORITHM= COPY;
1699
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1700
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1703
--echo #CHANGE COLUMN upgrades the old temporal type.
1704
#Setup table having old temporal type.
1705
CREATE TABLE t2 LIKE t1;
1706
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1708
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1709
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1710
ALTER TABLE t2 CHANGE COLUMN f_timestamp fld4 TIMESTAMP, ALGORITHM= COPY;
1711
SELECT f_time, f_datetime, fld4, HEX(WEIGHT_STRING(f_time)),
1712
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(fld4)) FROM t2;
1715
--echo #MODIFY COLUMN upgrades the old temporal type.
1716
#Setup table having old temporal type.
1717
CREATE TABLE t2 LIKE t1;
1718
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1720
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1721
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1722
ALTER TABLE t2 MODIFY f_timestamp TIME, ALGORITHM= COPY;
1723
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1724
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1727
--echo #ADD INDEX upgrades the old temporal type.
1728
#Setup table having old temporal type.
1729
CREATE TABLE t2 LIKE t1;
1730
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1732
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1733
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1734
ALTER TABLE t2 ADD INDEX index1(f_timestamp), ALGORITHM= COPY;
1735
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1736
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1739
--echo #ALTER operations using DEFAULT algorithm is allowed
1740
--echo #when the table contains old temporal type.
1742
--echo #ADD COLUMN upgrades the old temporal type.
1743
#Setup table having old temporal type.
1744
CREATE TABLE t2 LIKE t1;
1745
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1747
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1748
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1749
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= DEFAULT;
1750
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1751
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1754
--echo #ADD COLUMN FIRST upgrades the old temporal type.
1755
#Setup table having old temporal type.
1756
CREATE TABLE t2 LIKE t1;
1757
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1759
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1760
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1761
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP FIRST, ALGORITHM= DEFAULT;
1762
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1763
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1766
--echo #ADD COLUMN AFTER upgrades the old temporal type.
1767
#Setup table having old temporal type.
1768
CREATE TABLE t2 LIKE t1;
1769
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1771
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1772
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1773
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP AFTER f_timestamp, ALGORITHM= DEFAULT;
1774
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1775
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1778
--echo #CHANGE COLUMN upgrades the old temporal type.
1779
#Setup table having old temporal type.
1780
CREATE TABLE t2 LIKE t1;
1781
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1783
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1784
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1785
ALTER TABLE t2 CHANGE COLUMN f_timestamp fld4 DATETIME, ALGORITHM= DEFAULT;
1786
SELECT f_time, f_datetime, fld4, HEX(WEIGHT_STRING(f_time)),
1787
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(fld4)) FROM t2;
1790
--echo #MODIFY COLUMN upgrades the old temporal type.
1791
#Setup table having old temporal type.
1792
CREATE TABLE t2 LIKE t1;
1793
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1795
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1796
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1797
ALTER TABLE t2 MODIFY f_timestamp TIME, ALGORITHM= DEFAULT;
1798
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1799
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1802
--echo #ADD INDEX upgrades the old temporal type.
1803
#Setup table having old temporal type.
1804
CREATE TABLE t2 LIKE t1;
1805
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1807
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1808
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1809
ALTER TABLE t2 ADD INDEX index1(f_timestamp), ALGORITHM= DEFAULT;
1810
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1811
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1814
--echo #ALTER TABLE FORCE upgrades the old temporal types.
1815
#Setup table having old temporal type.
1816
CREATE TABLE t2 LIKE t1;
1817
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1819
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1820
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1821
ALTER TABLE t2 FORCE;
1822
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1823
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1826
--echo #Examples where the NOT NULL/NULL FLAG and DEFAULT values are retained
1827
--echo #after upgrade.
1828
CREATE TABLE t2 LIKE t1;
1829
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1831
--echo #Before upgrade.
1832
SHOW CREATE TABLE t2;
1833
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= COPY;
1834
--echo #After upgrade.
1835
SHOW CREATE TABLE t2;
1837
--echo #Examples of the Alter operation which does not upgrade
1838
--echo #the temporal formats.
1840
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1841
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t1;
1843
ALTER TABLE t1 DROP COLUMN f_timestamp;
1844
SELECT f_time, f_datetime, HEX(WEIGHT_STRING(f_time)),
1845
HEX(WEIGHT_STRING(f_datetime)) FROM t1;
1847
RENAME TABLE t1 to t3;
1848
SELECT f_time, f_datetime, HEX(WEIGHT_STRING(f_time)),
1849
HEX(WEIGHT_STRING(f_datetime)) FROM t3;
1851
--echo #Once the old temporal type is upgraded to new temporal type,
1852
--echo #ADD/CHANGE COLUMN, ADD INDEX operations succeed using INPLACE
1855
ALTER TABLE t2 ADD COLUMN fld5 INT, ALGORITHM= INPLACE;
1857
ALTER TABLE t2 ADD INDEX index2(fld5), ALGORITHM= INPLACE;
1859
ALTER TABLE t2 CHANGE fld5 fld6 INT, ALGORITHM= INPLACE;
1861
ALTER TABLE t2 MODIFY fld6 INT, ALGORITHM= INPLACE;
1868
--echo # BUG 18985760 -"FAST" ALTER TABLE CHANGE ON ENUM COLUMN
1869
--echo # TRIGGERS FULL TABLE REBUILD.
1873
let $MYSQLD_DATADIR= `select @@datadir`;
1874
--copy_file std_data/55_temporal.frm $MYSQLD_DATADIR/test/t1.frm
1875
--copy_file std_data/55_temporal.MYD $MYSQLD_DATADIR/test/t1.MYD
1876
--copy_file std_data/55_temporal.MYI $MYSQLD_DATADIR/test/t1.MYI
1878
--echo # To support INPLACE ALTER table operations later in the test.
1879
ALTER TABLE t1 ENGINE= INNODB;
1881
--echo #Test cases with the global variable 'avoid_temporal_upgrade'
1884
SET @save_avoid_temporal_upgrade= @@global.avoid_temporal_upgrade;
1885
SET GLOBAL avoid_temporal_upgrade= ON;
1887
--echo #ALTER operations using INPLACE algorithm are allowed
1888
--echo #when the table contains old temporal type since
1889
--echo #the global variable 'avoid_temporal_upgrade' is
1890
--echo #enabled. The old temporal types are not upgraded.
1892
CREATE TABLE t2 LIKE t1;
1893
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1895
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1896
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1897
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= INPLACE;
1898
SELECT f_time, f_datetime, f_timestamp, fld4, HEX(WEIGHT_STRING(f_time)),
1899
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)),
1900
HEX(WEIGHT_STRING(fld4)) FROM t2;
1902
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1903
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1904
ALTER TABLE t2 ADD COLUMN fld5 TIME DEFAULT '101010' FIRST, ALGORITHM= INPLACE;
1905
SELECT f_time, f_datetime, f_timestamp, fld5, HEX(WEIGHT_STRING(f_time)),
1906
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)),
1907
HEX(WEIGHT_STRING(fld5)) FROM t2;
1909
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1910
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1911
ALTER TABLE t2 ADD COLUMN fld6 TIMESTAMP AFTER f_timestamp, ALGORITHM= INPLACE;
1912
SELECT f_time, f_datetime, f_timestamp, fld6, HEX(WEIGHT_STRING(f_time)),
1913
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)),
1914
HEX(WEIGHT_STRING(fld6)) FROM t2;
1916
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1917
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1918
ALTER TABLE t2 ADD INDEX index1(f_datetime), ALGORITHM= INPLACE;
1919
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1920
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1923
--echo #ALTER operations using COPY algorithm are allowed
1924
--echo #when the table contains old temporal type and
1925
--echo #does not upgrade the old temporal types.
1927
--echo #ADD COLUMN does not upgrade the old temporal type.
1928
#Setup table having old temporal type.
1929
CREATE TABLE t2 LIKE t1;
1930
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1932
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1933
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1934
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= COPY;
1935
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1936
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1938
--echo #ADD COLUMN FIRST does not upgrade the old temporal type.
1939
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1940
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1941
ALTER TABLE t2 ADD COLUMN fld5 TIMESTAMP FIRST, ALGORITHM= COPY;
1942
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1943
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1945
--echo #ADD COLUMN AFTER does not upgrade the old temporal type.
1946
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1947
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1948
ALTER TABLE t2 ADD COLUMN fld6 TIMESTAMP AFTER f_timestamp, ALGORITHM= COPY;
1949
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1950
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1952
--echo #CHANGE COLUMN upgrades the old temporal type only for the column which
1954
#Setup table having old temporal type.
1955
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1956
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1957
ALTER TABLE t2 CHANGE COLUMN f_datetime fld7 DATETIME, ALGORITHM= COPY;
1958
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1959
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1961
--echo #MODIFY COLUMN upgrades the old temporal type only for the column
1963
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1964
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1965
ALTER TABLE t2 MODIFY f_timestamp DATETIME, ALGORITHM= COPY;
1966
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1967
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1969
--echo #ADD INDEX does not upgrade the old temporal type.
1970
#Setup table having old temporal type.
1971
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1972
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1973
ALTER TABLE t2 ADD INDEX index1(f_time), ALGORITHM= COPY;
1974
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1975
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1978
--echo #ALTER operations using DEFAULT algorithm are allowed
1979
--echo #when the table contains old temporal type and does not
1980
--echo #upgrade the old temporal types.
1982
--echo #ADD COLUMN does not upgrade the old temporal type.
1983
#Setup table having old temporal type.
1984
CREATE TABLE t2 LIKE t1;
1985
INSERT INTO t2 VALUES ('22:22:22','2011-11-21 22:22:22','2011-11-21 22:22:22');
1987
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1988
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1989
ALTER TABLE t2 ADD COLUMN fld4 TIMESTAMP, ALGORITHM= DEFAULT;
1990
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1991
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1993
--echo #ADD COLUMN FIRST does not upgrade the old temporal type.
1994
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1995
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
1996
ALTER TABLE t2 ADD COLUMN fld5 TIMESTAMP FIRST, ALGORITHM= DEFAULT;
1997
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
1998
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2000
--echo #ADD COLUMN AFTER does not upgrade the old temporal type.
2001
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2002
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2003
ALTER TABLE t2 ADD COLUMN fld6 TIMESTAMP AFTER f_timestamp, ALGORITHM= DEFAULT;
2004
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2005
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2007
--echo #CHANGE COLUMN upgrades the old temporal type only for the column which
2009
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2010
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2011
ALTER TABLE t2 CHANGE COLUMN f_datetime fld7 DATETIME, ALGORITHM= DEFAULT;
2012
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2013
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2015
--echo #MODIFY COLUMN upgrades the old temporal type only for the column
2017
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2018
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2019
ALTER TABLE t2 MODIFY f_timestamp DATETIME, ALGORITHM= DEFAULT;
2020
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2021
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2023
--echo #ADD INDEX does not upgrade the old temporal type.
2024
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2025
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2026
ALTER TABLE t2 ADD INDEX index1(f_time), ALGORITHM= DEFAULT;
2027
SELECT f_time, fld7, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2028
HEX(WEIGHT_STRING(fld7)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2031
--echo #ALTER TABLE FORCE does not upgrade the old temporal types.
2032
#Setup table having old temporal type.
2033
CREATE TABLE t2 LIKE t1;
2034
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
ALTER TABLE t2 FORCE;
2039
SELECT f_time, f_datetime, f_timestamp, HEX(WEIGHT_STRING(f_time)),
2040
HEX(WEIGHT_STRING(f_datetime)), HEX(WEIGHT_STRING(f_timestamp)) FROM t2;
2043
SET @@global.avoid_temporal_upgrade= @save_avoid_temporal_upgrade;
2045
--echo #Test cases with the session variable 'show_old_temporals'
2049
CREATE TABLE t2(fld1 time, fld2 datetime, fld3 timestamp);
2050
SET @save_show_old_temporals= @@session.show_old_temporals;
2051
SET SESSION show_old_temporals= ON;
2053
--echo #Displays a comment to indicate that the columns are of 5.5
2054
--echo #binary format
2055
SHOW CREATE TABLE t1;
2056
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
2058
--echo #Since the temporal types are in new format, no comment is
2060
SHOW CREATE TABLE t2;
2061
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t2';
2063
--echo #Does not display the comment for table with old temporal types
2064
--echo #since the session variable 'show_old_temporals' is OFF.
2065
SET SESSION show_old_temporals= OFF;
2066
SHOW CREATE TABLE t1;
2067
SELECT COLUMN_TYPE FROM information_schema.columns WHERE table_name='t1';
2070
SET @@session.show_old_temporals= @save_show_old_temporals;