1call mtr.add_suppression("Table rebuild required"); 2# 3# Opening a Maria-10.2.26 table with a stored VARCHAR column 4# 5# Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR 6SHOW CREATE TABLE t1; 7Table Create Table 8t1 CREATE TABLE `t1` ( 9 `a` char(5) DEFAULT NULL, 10 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED 11) ENGINE=MyISAM DEFAULT CHARSET=latin1 12Warnings: 13Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 14Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 15SHOW CREATE TABLE t1; 16Table Create Table 17t1 CREATE TABLE `t1` ( 18 `a` char(5) DEFAULT NULL, 19 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED 20) ENGINE=MyISAM DEFAULT CHARSET=latin1 21FLUSH TABLES; 22SHOW CREATE TABLE t1; 23Table Create Table 24t1 CREATE TABLE `t1` ( 25 `a` char(5) DEFAULT NULL, 26 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED 27) ENGINE=MyISAM DEFAULT CHARSET=latin1 28Warnings: 29Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 30Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 31FLUSH TABLES; 32SELECT * FROM t1; 33a v 341 1 352 2 363 3 37Warnings: 38Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 39Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 40SELECT * FROM t1; 41a v 421 1 432 2 443 3 45FLUSH TABLES; 46SELECT * FROM t1; 47a v 481 1 492 2 503 3 51Warnings: 52Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 53Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 54CREATE TABLE t2 LIKE t1; 55ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 56FLUSH TABLES; 57CREATE TABLE t2 LIKE t1; 58ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 59SHOW CREATE TABLE t1; 60Table Create Table 61t1 CREATE TABLE `t1` ( 62 `a` char(5) DEFAULT NULL, 63 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED 64) ENGINE=MyISAM DEFAULT CHARSET=latin1 65Warnings: 66Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 67Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 68ALTER TABLE t1 ADD b INT DEFAULT a; 69ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 70SHOW CREATE TABLE t1; 71Table Create Table 72t1 CREATE TABLE `t1` ( 73 `a` char(5) DEFAULT NULL, 74 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED 75) ENGINE=MyISAM DEFAULT CHARSET=latin1 76SELECT * FROM t1; 77a v 781 1 792 2 803 3 81FLUSH TABLES; 82ALTER TABLE t1 ADD c INT DEFAULT a; 83ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 84SELECT * FROM t1; 85a v 861 1 872 2 883 3 89DROP TABLE t1; 90# 91# Fixing a Maria-10.2.26 table with a stored VARCHAR column 92# 93# Fixing by dropping the generated stored column 94# Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR 95ALTER TABLE t1 DROP v; 96Warnings: 97Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 98Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 99SELECT * FROM t1; 100a 1011 1022 1033 104DROP TABLE t1; 105# Fixing by altering the generation expression of the stored column 106# Copying maria100226_char_to_vchar_stored.* to MYSQLD_DATADIR 107SHOW CREATE TABLE t1; 108Table Create Table 109t1 CREATE TABLE `t1` ( 110 `a` char(5) DEFAULT NULL, 111 `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED 112) ENGINE=MyISAM DEFAULT CHARSET=latin1 113Warnings: 114Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 115Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 116ALTER TABLE t1 MODIFY v VARCHAR(5) AS (RTRIM(a)) PERSISTENT; 117SHOW CREATE TABLE t1; 118Table Create Table 119t1 CREATE TABLE `t1` ( 120 `a` char(5) DEFAULT NULL, 121 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) STORED 122) ENGINE=MyISAM DEFAULT CHARSET=latin1 123DROP TABLE t1; 124# 125# Opening a Maria-10.2.26 table with a virtual VARCHAR column 126# 127# Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR 128SHOW CREATE TABLE t1; 129Table Create Table 130t1 CREATE TABLE `t1` ( 131 `a` char(5) DEFAULT NULL, 132 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 133 KEY `v` (`v`) 134) ENGINE=MyISAM DEFAULT CHARSET=latin1 135Warnings: 136Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 137Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 138SHOW CREATE TABLE t1; 139Table Create Table 140t1 CREATE TABLE `t1` ( 141 `a` char(5) DEFAULT NULL, 142 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 143 KEY `v` (`v`) 144) ENGINE=MyISAM DEFAULT CHARSET=latin1 145FLUSH TABLES; 146SHOW CREATE TABLE t1; 147Table Create Table 148t1 CREATE TABLE `t1` ( 149 `a` char(5) DEFAULT NULL, 150 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 151 KEY `v` (`v`) 152) ENGINE=MyISAM DEFAULT CHARSET=latin1 153Warnings: 154Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 155Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 156FLUSH TABLES; 157SELECT * FROM t1; 158a v 1591 1 1602 2 1613 3 162Warnings: 163Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 164Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 165SELECT * FROM t1; 166a v 1671 1 1682 2 1693 3 170FLUSH TABLES; 171SELECT * FROM t1; 172a v 1731 1 1742 2 1753 3 176Warnings: 177Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 178Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 179CREATE TABLE t2 LIKE t1; 180ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 181FLUSH TABLES; 182CREATE TABLE t2 LIKE t1; 183ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 184SHOW CREATE TABLE t1; 185Table Create Table 186t1 CREATE TABLE `t1` ( 187 `a` char(5) DEFAULT NULL, 188 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 189 KEY `v` (`v`) 190) ENGINE=MyISAM DEFAULT CHARSET=latin1 191Warnings: 192Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 193Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 194ALTER TABLE t1 ADD b INT DEFAULT a; 195ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 196FLUSH TABLES; 197ALTER TABLE t1 ADD c INT DEFAULT a; 198ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 199SELECT * FROM t1; 200a v 2011 1 2022 2 2033 3 204DROP TABLE t1; 205# 206# Fixing a Maria-10.2.26 table with a virtual VARCHAR column 207# 208# Fixing by dropping the virtual column 209# Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR 210SHOW CREATE TABLE t1; 211Table Create Table 212t1 CREATE TABLE `t1` ( 213 `a` char(5) DEFAULT NULL, 214 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 215 KEY `v` (`v`) 216) ENGINE=MyISAM DEFAULT CHARSET=latin1 217Warnings: 218Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 219Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 220ALTER TABLE t1 DROP v; 221SELECT * FROM t1; 222a 2231 2242 2253 226DROP TABLE t1; 227# Fixing by dropping a key on a virtual column, using ALTER TABLE 228# Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR 229SHOW CREATE TABLE t1; 230Table Create Table 231t1 CREATE TABLE `t1` ( 232 `a` char(5) DEFAULT NULL, 233 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 234 KEY `v` (`v`) 235) ENGINE=MyISAM DEFAULT CHARSET=latin1 236Warnings: 237Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 238Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 239ALTER TABLE t1 DROP KEY v; 240SHOW CREATE TABLE t1; 241Table Create Table 242t1 CREATE TABLE `t1` ( 243 `a` char(5) DEFAULT NULL, 244 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL 245) ENGINE=MyISAM DEFAULT CHARSET=latin1 246SELECT * FROM t1; 247a v 2481 1 2492 2 2503 3 251DROP TABLE t1; 252# Fixing by dropping a key on a virtual column, using DROP INDEX 253# Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR 254DROP INDEX v ON t1; 255Warnings: 256Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 257Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 258SHOW CREATE TABLE t1; 259Table Create Table 260t1 CREATE TABLE `t1` ( 261 `a` char(5) DEFAULT NULL, 262 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL 263) ENGINE=MyISAM DEFAULT CHARSET=latin1 264SELECT * FROM t1; 265a v 2661 1 2672 2 2683 3 269DROP TABLE t1; 270# Fixing by altering the generation expression of a virtual column 271# Copying maria100226_char_to_vchar_virtual.* to MYSQLD_DATADIR 272SHOW CREATE TABLE t1; 273Table Create Table 274t1 CREATE TABLE `t1` ( 275 `a` char(5) DEFAULT NULL, 276 `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 277 KEY `v` (`v`) 278) ENGINE=MyISAM DEFAULT CHARSET=latin1 279Warnings: 280Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 281Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 282ALTER TABLE t1 MODIFY v VARCHAR(5) AS(RTRIM(a)) VIRTUAL; 283SHOW CREATE TABLE t1; 284Table Create Table 285t1 CREATE TABLE `t1` ( 286 `a` char(5) DEFAULT NULL, 287 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL, 288 KEY `v` (`v`) 289) ENGINE=MyISAM DEFAULT CHARSET=latin1 290SELECT * FROM t1; 291a v 2921 1 2932 2 2943 3 295CREATE TABLE t2 LIKE t1; 296SHOW CREATE TABLE t2; 297Table Create Table 298t2 CREATE TABLE `t2` ( 299 `a` char(5) DEFAULT NULL, 300 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL, 301 KEY `v` (`v`) 302) ENGINE=MyISAM DEFAULT CHARSET=latin1 303DROP TABLE t2; 304DROP TABLE t1; 305# 306# Upgrading a Maria-10.2.26 table with a stored column 307# 308# Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR 309CHECK TABLE t1 FOR UPGRADE; 310Table Op Msg_type Msg_text 311test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 312test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 313test.t1 check status OK 314FLUSH TABLES; 315CHECK TABLE t1 FOR UPGRADE; 316Table Op Msg_type Msg_text 317test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 318test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 319test.t1 check status OK 320DROP TABLE t1; 321# 322# Upgrading a Maria-10.2.26 table with a virtual column 323# 324# Copying maria100226_char_to_varchar.* to MYSQLD_DATADIR 325CHECK TABLE t1 FOR UPGRADE; 326Table Op Msg_type Msg_text 327test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 328test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 329test.t1 check status OK 330FLUSH TABLES; 331CHECK TABLE t1 FOR UPGRADE; 332Table Op Msg_type Msg_text 333test.t1 check Warning Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 334test.t1 check Warning Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 335test.t1 check status OK 336DROP TABLE t1; 337