1# 2# Start of 10.2 tests 3# 4# 5# MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH 6# 7# 8# PAD_CHAR_TO_FULL_LENGTH + various virtual column data types 9# 10CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v)); 11SHOW CREATE TABLE t1; 12Table Create Table 13t1 CREATE TABLE `t1` ( 14 `a` char(5) DEFAULT NULL, 15 `v` char(5) GENERATED ALWAYS AS (`a`) VIRTUAL, 16 KEY `v` (`v`) 17) ENGINE=MyISAM DEFAULT CHARSET=latin1 18DROP TABLE t1; 19CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v)); 20SHOW CREATE TABLE t1; 21Table Create Table 22t1 CREATE TABLE `t1` ( 23 `a` char(5) DEFAULT NULL, 24 `v` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL, 25 KEY `v` (`v`) 26) ENGINE=MyISAM DEFAULT CHARSET=latin1 27DROP TABLE t1; 28CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); 29ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 30CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); 31ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` 32SHOW WARNINGS; 33Level Code Message 34Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` 35Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 36CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v)); 37ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 38SHOW WARNINGS; 39Level Code Message 40Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 41Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 42CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v)); 43ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 44SHOW WARNINGS; 45Level Code Message 46Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 47Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 48CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100))); 49ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 50SHOW WARNINGS; 51Level Code Message 52Error 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 54# PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency 55CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v)); 56SHOW CREATE TABLE t1; 57Table Create Table 58t1 CREATE TABLE `t1` ( 59 `a` char(5) DEFAULT NULL, 60 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL, 61 KEY `v` (`v`) 62) ENGINE=MyISAM DEFAULT CHARSET=latin1 63DROP TABLE t1; 64CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100))); 65SHOW CREATE TABLE t1; 66Table Create Table 67t1 CREATE TABLE `t1` ( 68 `a` char(5) DEFAULT NULL, 69 `v` text GENERATED ALWAYS AS (rtrim(`a`)) VIRTUAL, 70 KEY `v` (`v`(100)) 71) ENGINE=MyISAM DEFAULT CHARSET=latin1 72DROP TABLE t1; 73CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); 74SHOW CREATE TABLE t1; 75Table Create Table 76t1 CREATE TABLE `t1` ( 77 `a` char(5) DEFAULT NULL, 78 `v` varchar(5) GENERATED ALWAYS AS (trim(trailing ' ' from `a`)) VIRTUAL, 79 KEY `v` (`v`) 80) ENGINE=MyISAM DEFAULT CHARSET=latin1 81DROP TABLE t1; 82CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100))); 83SHOW CREATE TABLE t1; 84Table Create Table 85t1 CREATE TABLE `t1` ( 86 `a` char(5) DEFAULT NULL, 87 `v` text GENERATED ALWAYS AS (trim(trailing ' ' from `a`)) VIRTUAL, 88 KEY `v` (`v`(100)) 89) ENGINE=MyISAM DEFAULT CHARSET=latin1 90DROP TABLE t1; 91CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); 92SHOW CREATE TABLE t1; 93Table Create Table 94t1 CREATE TABLE `t1` ( 95 `a` char(5) DEFAULT NULL, 96 `v` varchar(5) GENERATED ALWAYS AS (trim(both ' ' from `a`)) VIRTUAL, 97 KEY `v` (`v`) 98) ENGINE=MyISAM DEFAULT CHARSET=latin1 99DROP TABLE t1; 100CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100))); 101SHOW CREATE TABLE t1; 102Table Create Table 103t1 CREATE TABLE `t1` ( 104 `a` char(5) DEFAULT NULL, 105 `v` text GENERATED ALWAYS AS (trim(both ' ' from `a`)) VIRTUAL, 106 KEY `v` (`v`(100)) 107) ENGINE=MyISAM DEFAULT CHARSET=latin1 108DROP TABLE t1; 109CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v)); 110SHOW CREATE TABLE t1; 111Table Create Table 112t1 CREATE TABLE `t1` ( 113 `a` char(5) DEFAULT NULL, 114 `v` varchar(5) GENERATED ALWAYS AS (trim(trailing NULL from `a`)) VIRTUAL, 115 KEY `v` (`v`) 116) ENGINE=MyISAM DEFAULT CHARSET=latin1 117DROP TABLE t1; 118CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v)); 119SHOW CREATE TABLE t1; 120Table Create Table 121t1 CREATE TABLE `t1` ( 122 `a` char(5) DEFAULT NULL, 123 `v` varchar(5) GENERATED ALWAYS AS (trim(both NULL from `a`)) VIRTUAL, 124 KEY `v` (`v`) 125) ENGINE=MyISAM DEFAULT CHARSET=latin1 126DROP TABLE t1; 127# PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency 128CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v)); 129ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 130SHOW WARNINGS; 131Level Code Message 132Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 133Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 134CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100))); 135ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 136SHOW WARNINGS; 137Level Code Message 138Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 139Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 140CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v)); 141ERROR HY000: Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 142SHOW WARNINGS; 143Level Code Message 144Error 1901 Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 145Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 146CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v)); 147ERROR HY000: Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 148SHOW WARNINGS; 149Level Code Message 150Error 1901 Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 151Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 152CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v)); 153ERROR HY000: Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 154SHOW WARNINGS; 155Level Code Message 156Error 1901 Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 157Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 158CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v)); 159ERROR HY000: Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 160SHOW WARNINGS; 161Level Code Message 162Error 1901 Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 163Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 164CREATE TABLE t1 ( 165a CHAR(5), 166v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); 167ERROR HY000: Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 168SHOW WARNINGS; 169Level Code Message 170Error 1901 Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 171Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 172CREATE TABLE t1 ( 173a CHAR(5), 174v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); 175ERROR HY000: Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 176SHOW WARNINGS; 177Level Code Message 178Error 1901 Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 179Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 180# PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a) 181CREATE TABLE t1 ( 182a CHAR(5), 183b CHAR(5), 184v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100))); 185ERROR HY000: Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 186SHOW WARNINGS; 187Level Code Message 188Error 1901 Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 189Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 190# PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency 191CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v)); 192SHOW CREATE TABLE t1; 193Table Create Table 194t1 CREATE TABLE `t1` ( 195 `a` char(5) DEFAULT NULL, 196 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,5,' ')) VIRTUAL, 197 KEY `v` (`v`) 198) ENGINE=MyISAM DEFAULT CHARSET=latin1 199DROP TABLE t1; 200CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v)); 201SHOW CREATE TABLE t1; 202Table Create Table 203t1 CREATE TABLE `t1` ( 204 `a` char(5) DEFAULT NULL, 205 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,6,' ')) VIRTUAL, 206 KEY `v` (`v`) 207) ENGINE=MyISAM DEFAULT CHARSET=latin1 208DROP TABLE t1; 209CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v)); 210SHOW CREATE TABLE t1; 211Table Create Table 212t1 CREATE TABLE `t1` ( 213 `a` char(5) DEFAULT NULL, 214 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,6,NULL)) VIRTUAL, 215 KEY `v` (`v`) 216) ENGINE=MyISAM DEFAULT CHARSET=latin1 217DROP TABLE t1; 218CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v)); 219SHOW CREATE TABLE t1; 220Table Create Table 221t1 CREATE TABLE `t1` ( 222 `a` char(5) DEFAULT NULL, 223 `v` varchar(5) GENERATED ALWAYS AS (rpad(`a`,NULL,' ')) VIRTUAL, 224 KEY `v` (`v`) 225) ENGINE=MyISAM DEFAULT CHARSET=latin1 226DROP TABLE t1; 227# PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency 228CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v)); 229ERROR HY000: Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` 230SHOW WARNINGS; 231Level Code Message 232Error 1901 Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` 233Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 234CREATE TABLE t1 ( 235a CHAR(5), 236b CHAR(5), 237v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL, 238KEY(v) 239); 240ERROR HY000: Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 241SHOW WARNINGS; 242Level Code Message 243Error 1901 Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 244Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 245# PAD_CHAR_TO_FULL_LENGTH + comparison 246CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v)); 247SHOW CREATE TABLE t1; 248Table Create Table 249t1 CREATE TABLE `t1` ( 250 `a` char(5) DEFAULT NULL, 251 `v` int(11) GENERATED ALWAYS AS (`a` = 'a') VIRTUAL, 252 KEY `v` (`v`) 253) ENGINE=MyISAM DEFAULT CHARSET=latin1 254DROP TABLE t1; 255CREATE TABLE t1 ( 256a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin, 257v INT AS (a='a') VIRTUAL, KEY(v) 258); 259ERROR HY000: Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` 260SHOW WARNINGS; 261Level Code Message 262Error 1901 Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` 263Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 264# PAD_CHAR_TO_FULL_LENGTH + LIKE 265CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v)); 266SHOW CREATE TABLE t1; 267Table Create Table 268t1 CREATE TABLE `t1` ( 269 `a` char(5) DEFAULT NULL, 270 `v` int(11) GENERATED ALWAYS AS (`a` like 'a%') VIRTUAL, 271 KEY `v` (`v`) 272) ENGINE=MyISAM DEFAULT CHARSET=latin1 273DROP TABLE t1; 274CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v)); 275SHOW CREATE TABLE t1; 276Table Create Table 277t1 CREATE TABLE `t1` ( 278 `a` char(5) DEFAULT NULL, 279 `v` int(11) GENERATED ALWAYS AS (`a` like NULL) VIRTUAL, 280 KEY `v` (`v`) 281) ENGINE=MyISAM DEFAULT CHARSET=latin1 282DROP TABLE t1; 283CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v)); 284ERROR HY000: Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` 285SHOW WARNINGS; 286Level Code Message 287Error 1901 Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` 288Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 289# PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency 290CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v)); 291ERROR HY000: Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 292SHOW WARNINGS; 293Level Code Message 294Error 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` 295Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 296# 297# Testing NO_UNSIGNED_SUBTRACTION 298# 299CREATE TABLE t1 ( 300a INT UNSIGNED, 301b INT UNSIGNED, 302c INT GENERATED ALWAYS AS (a-b) VIRTUAL, 303KEY (c) 304); 305ERROR HY000: Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` 306SHOW WARNINGS; 307Level Code Message 308Error 1901 Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` 309Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION 310CREATE TABLE t1 ( 311a INT UNSIGNED, 312b INT UNSIGNED, 313c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL, 314KEY (c) 315); 316ERROR HY000: Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` 317SHOW WARNINGS; 318Level Code Message 319Error 1901 Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` 320Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION 321CREATE TABLE t1 ( 322a INT UNSIGNED, 323b INT UNSIGNED, 324c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL, 325KEY (c) 326); 327ERROR HY000: Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` 328SHOW WARNINGS; 329Level Code Message 330Error 1901 Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` 331Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION 332CREATE TABLE t1 ( 333a INT UNSIGNED, 334b INT UNSIGNED, 335c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-CAST(b AS SIGNED)) VIRTUAL, 336KEY (c) 337); 338SHOW CREATE TABLE t1; 339Table Create Table 340t1 CREATE TABLE `t1` ( 341 `a` int(10) unsigned DEFAULT NULL, 342 `b` int(10) unsigned DEFAULT NULL, 343 `c` int(11) GENERATED ALWAYS AS (cast(`a` as signed) - cast(`b` as signed)) VIRTUAL, 344 KEY `c` (`c`) 345) ENGINE=MyISAM DEFAULT CHARSET=latin1 346DROP TABLE t1; 347CREATE TABLE t1 ( 348a INT UNSIGNED, 349b INT UNSIGNED, 350c INT GENERATED ALWAYS AS (CAST(a AS DECIMAL(20,0))-CAST(b AS DECIMAL(20,0))) VIRTUAL, 351KEY (c) 352); 353SHOW CREATE TABLE t1; 354Table Create Table 355t1 CREATE TABLE `t1` ( 356 `a` int(10) unsigned DEFAULT NULL, 357 `b` int(10) unsigned DEFAULT NULL, 358 `c` int(11) GENERATED ALWAYS AS (cast(`a` as decimal(20,0)) - cast(`b` as decimal(20,0))) VIRTUAL, 359 KEY `c` (`c`) 360) ENGINE=MyISAM DEFAULT CHARSET=latin1 361DROP TABLE t1; 362# 363# Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION 364# 365CREATE TABLE t1 ( 366a INT UNSIGNED, 367b INT UNSIGNED, 368c CHAR(5), 369v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL, 370KEY (v) 371); 372ERROR HY000: Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` 373SHOW WARNINGS; 374Level Code Message 375Error 1901 Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` 376Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION 377Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 378CREATE TABLE t1 ( 379a INT UNSIGNED, 380b INT UNSIGNED, 381c CHAR(5), 382v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL, 383KEY (v) 384); 385ERROR HY000: Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` 386SHOW WARNINGS; 387Level Code Message 388Error 1901 Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` 389Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 390# ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error 391CREATE TABLE t1 ( 392a INT UNSIGNED, 393b INT UNSIGNED, 394c CHAR(5), 395v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL 396); 397ALTER TABLE t1 ADD KEY(v); 398ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` 399SHOW WARNINGS; 400Level Code Message 401Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` 402Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 403CREATE INDEX v ON t1 (v); 404ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` 405SHOW WARNINGS; 406Level Code Message 407Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` 408Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 409DROP TABLE t1; 410# A virtual column on the second position in an index - cannot depend on sql_mode 411CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100))); 412ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 413SHOW WARNINGS; 414Level Code Message 415Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 416Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 417# A persisten virtual column cannot depend on sql_mode 418CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT); 419ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 420SHOW WARNINGS; 421Level Code Message 422Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` 423Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH 424CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT); 425SHOW CREATE TABLE t1; 426Table Create Table 427t1 CREATE TABLE `t1` ( 428 `a` char(5) DEFAULT NULL, 429 `v` varchar(5) GENERATED ALWAYS AS (rtrim(`a`)) STORED 430) ENGINE=MyISAM DEFAULT CHARSET=latin1 431DROP TABLE t1; 432# 433# End of 10.2 tests 434# 435