1SET @@session.default_storage_engine = 'MyISAM'; 2# - UNIQUE KEY 3# - INDEX 4# - FULLTEXT INDEX 5# - SPATIAL INDEX (not supported) 6# - FOREIGN INDEX (partially supported) 7# - CHECK (allowed but not used) 8# UNIQUE 9create table t1 (a int, b int generated always as (a*2) virtual unique); 10show create table t1; 11Table Create Table 12t1 CREATE TABLE `t1` ( 13 `a` int(11) DEFAULT NULL, 14 `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL, 15 UNIQUE KEY `b` (`b`) 16) ENGINE=MyISAM DEFAULT CHARSET=latin1 17describe t1; 18Field Type Null Key Default Extra 19a int(11) YES NULL 20b int(11) YES UNI NULL VIRTUAL GENERATED 21drop table t1; 22create table t1 (a int, b int generated always as (a*2) stored unique); 23show create table t1; 24Table Create Table 25t1 CREATE TABLE `t1` ( 26 `a` int(11) DEFAULT NULL, 27 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, 28 UNIQUE KEY `b` (`b`) 29) ENGINE=MyISAM DEFAULT CHARSET=latin1 30describe t1; 31Field Type Null Key Default Extra 32a int(11) YES NULL 33b int(11) YES UNI NULL STORED GENERATED 34drop table t1; 35create table t1 (a int, b int generated always as (a*2) virtual, unique key (b)); 36show create table t1; 37Table Create Table 38t1 CREATE TABLE `t1` ( 39 `a` int(11) DEFAULT NULL, 40 `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL, 41 UNIQUE KEY `b` (`b`) 42) ENGINE=MyISAM DEFAULT CHARSET=latin1 43describe t1; 44Field Type Null Key Default Extra 45a int(11) YES NULL 46b int(11) YES UNI NULL VIRTUAL GENERATED 47drop table t1; 48create table t1 (a int, b int generated always as (a*2) stored, unique (b)); 49show create table t1; 50Table Create Table 51t1 CREATE TABLE `t1` ( 52 `a` int(11) DEFAULT NULL, 53 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, 54 UNIQUE KEY `b` (`b`) 55) ENGINE=MyISAM DEFAULT CHARSET=latin1 56describe t1; 57Field Type Null Key Default Extra 58a int(11) YES NULL 59b int(11) YES UNI NULL STORED GENERATED 60drop table t1; 61create table t1 (a int, b int generated always as (a*2) virtual); 62alter table t1 add unique key (b); 63drop table t1; 64create table t1 (a int, b int generated always as (a*2) stored); 65alter table t1 add unique key (b); 66drop table t1; 67# Testing data manipulation operations involving UNIQUE keys 68# on generated columns can be found in: 69# - gcol_ins_upd.inc 70# - gcol_select.inc 71# 72# INDEX 73create table t1 (a int, b int generated always as (a*2) virtual, index (b)); 74show create table t1; 75Table Create Table 76t1 CREATE TABLE `t1` ( 77 `a` int(11) DEFAULT NULL, 78 `b` int(11) GENERATED ALWAYS AS (`a` * 2) VIRTUAL, 79 KEY `b` (`b`) 80) ENGINE=MyISAM DEFAULT CHARSET=latin1 81describe t1; 82Field Type Null Key Default Extra 83a int(11) YES NULL 84b int(11) YES MUL NULL VIRTUAL GENERATED 85drop table t1; 86create table t1 (a int, b int generated always as (a*2) virtual, index (a,b)); 87drop table t1; 88create table t1 (a int, b int generated always as (a*2) stored, index (b)); 89show create table t1; 90Table Create Table 91t1 CREATE TABLE `t1` ( 92 `a` int(11) DEFAULT NULL, 93 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, 94 KEY `b` (`b`) 95) ENGINE=MyISAM DEFAULT CHARSET=latin1 96describe t1; 97Field Type Null Key Default Extra 98a int(11) YES NULL 99b int(11) YES MUL NULL STORED GENERATED 100drop table t1; 101create table t1 (a int, b int generated always as (a*2) stored, index (a,b)); 102show create table t1; 103Table Create Table 104t1 CREATE TABLE `t1` ( 105 `a` int(11) DEFAULT NULL, 106 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, 107 KEY `a` (`a`,`b`) 108) ENGINE=MyISAM DEFAULT CHARSET=latin1 109describe t1; 110Field Type Null Key Default Extra 111a int(11) YES MUL NULL 112b int(11) YES NULL STORED GENERATED 113drop table t1; 114create table t1 (a int, b int generated always as (a*2) virtual); 115alter table t1 add index (b); 116alter table t1 add index (a,b); 117drop table t1; 118create table t1 (a int, b int generated always as (a*2) stored); 119alter table t1 add index (b); 120drop table t1; 121create table t1 (a int, b int generated always as (a*2) stored); 122alter table t1 add index (a,b); 123create table t2 like t1; 124drop table t2; 125drop table t1; 126# Testing data manipulation operations involving INDEX 127# on generated columns can be found in: 128# - gcol_select.inc 129# 130# TODO: FULLTEXT INDEX 131# SPATIAL INDEX 132# Error "All parts of a SPATIAL index must be geometrical" 133create table t1 (a int, b int generated always as (a+1) stored, spatial index (b)); 134ERROR HY000: Incorrect arguments to SPATIAL INDEX 135create table t1 (a int, b int generated always as (a+1) stored); 136alter table t1 add spatial index (b); 137ERROR HY000: Incorrect arguments to SPATIAL INDEX 138drop table t1; 139# FOREIGN KEY 140# Rejected FK options. 141create table t1 (a int, b int generated always as (a+1) stored, 142foreign key (b) references t2(a) on update set null); 143ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column 144create table t1 (a int, b int generated always as (a+1) stored, 145foreign key (b) references t2(a) on update cascade); 146ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column 147create table t1 (a int, b int generated always as (a+1) stored, 148foreign key (b) references t2(a) on delete set null); 149ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column 150create table t1 (a int, b int generated always as (a+1) stored); 151alter table t1 add foreign key (b) references t2(a) on update set null; 152ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column 153alter table t1 add foreign key (b) references t2(a) on update cascade; 154ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column 155alter table t1 add foreign key (b) references t2(a) on delete set null; 156ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column 157drop table t1; 158# Allowed FK options. 159create table t2 (a int primary key, b char(5)); 160create table t1 (a int, b int generated always as (a % 10) stored, 161foreign key (b) references t2(a) on update restrict); 162drop table t1; 163create table t1 (a int, b int generated always as (a % 10) stored, 164foreign key (b) references t2(a) on update no action); 165drop table t1; 166create table t1 (a int, b int generated always as (a % 10) stored, 167foreign key (b) references t2(a) on delete restrict); 168drop table t1; 169create table t1 (a int, b int generated always as (a % 10) stored, 170foreign key (b) references t2(a) on delete cascade); 171drop table t1; 172create table t1 (a int, b int generated always as (a % 10) stored, 173foreign key (b) references t2(a) on delete no action); 174drop table t1,t2; 175# 176# Bug#20553262: WL8149: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED 177# 178CREATE TABLE c ( 179pk integer AUTO_INCREMENT, 180col_datetime_nokey DATETIME /*! NULL */, 181col_time_nokey TIME /*! NULL */, 182col_datetime_key DATETIME GENERATED ALWAYS AS 183(ADDTIME(col_datetime_nokey, col_time_nokey)), 184col_time_key TIME GENERATED ALWAYS AS 185(ADDTIME(col_datetime_nokey, col_time_nokey)), 186col_varchar_nokey VARCHAR(1) /*! NULL */, 187PRIMARY KEY (pk), 188KEY (col_time_key), 189KEY (col_datetime_key)); 190INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values 191('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'), 192('01:46:09.016386','2007-10-09 19:53:04.008332', NULL), 193('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'), 194('18:56:33.027423','2003-04-01 00:00:00', 'i'); 195Warnings: 196Note 1265 Data truncated for column 'col_time_key' at row 1 197Note 1265 Data truncated for column 'col_time_key' at row 2 198Note 1265 Data truncated for column 'col_time_key' at row 3 199Note 1265 Data truncated for column 'col_time_key' at row 4 200EXPLAIN SELECT 201outr.col_time_key AS x 202FROM c as outr 203WHERE 204outr.col_varchar_nokey in ('c', 'x', 'i') 205AND (outr.col_time_key IS NULL OR 206outr.col_datetime_key = '2009-09-27'); 207id select_type table type possible_keys key key_len ref rows Extra 2081 SIMPLE outr index_merge col_time_key,col_datetime_key col_time_key,col_datetime_key 4,6 NULL 2 x 209SELECT 210outr.col_time_key AS x 211FROM c AS outr 212WHERE 213outr.col_varchar_nokey in ('c', 'x', 'i') 214AND (outr.col_time_key IS NULL OR 215outr.col_datetime_key = '2009-09-27'); 216x 217DROP TABLE c; 218# 219# Bug#20913803: WL8149: SIG 11 IN DFIELD_DUP | 220# INNOBASE/INCLUDE/DATA0DATA.IC:253 221# 222CREATE TABLE A ( 223col_varchar_nokey TEXT , 224col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)), 225KEY (col_varchar_key(50)) 226); 227INSERT INTO A (col_varchar_nokey) VALUES (''); 228CREATE TABLE D ( 229pk INTEGER AUTO_INCREMENT, 230col_date_nokey BLOB, 231col_date_key BLOB GENERATED ALWAYS AS (REPEAT(col_date_nokey,1000)) VIRTUAL, 232col_datetime_nokey LONGBLOB, 233col_time_nokey LONGTEXT, 234col_datetime_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)), 235col_time_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_datetime_nokey, 1000)), 236col_varchar_nokey TEXT, 237col_varchar_key TEXT GENERATED ALWAYS AS (REPEAT(col_varchar_nokey, 1000)), 238PRIMARY KEY (pk), 239KEY (col_varchar_key(50)), 240KEY (col_date_key(20)), 241KEY (col_time_key(20)), 242KEY (col_datetime_key(20)), 243KEY (col_varchar_key(10), col_date_key(10), col_time_key(5), col_datetime_key(5)) 244); 245INSERT INTO D ( 246col_date_nokey, 247col_time_nokey, 248col_datetime_nokey, 249col_varchar_nokey 250) VALUES ('', '', '', ''),('', '', '', ''); 251DELETE FROM OUTR1.* USING D AS OUTR1 RIGHT JOIN A AS OUTR2 ON 252( OUTR1 . `col_varchar_nokey` = OUTR2 . `col_varchar_nokey` ); 253DROP TABLE IF EXISTS A,D; 254# 255# Bug#21024896: SIG 11 INNOBASE_ADD_ONE_VIRTUAL | 256# INNOBASE/HANDLER/HANDLER0ALTER.CC 257# 258CREATE TABLE t1 ( 259col1 int(11) DEFAULT NULL, 260col2 int(11) DEFAULT NULL, 261col3 int(11) NOT NULL, 262col4 int(11) DEFAULT NULL, 263col5 int(11) GENERATED ALWAYS AS (col2 / col2) VIRTUAL, 264col7 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, 265col8 int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL, 266col9 text, 267col6 int(11) DEFAULT NULL, 268PRIMARY KEY (`col3`), 269UNIQUE KEY uidx (`col2`), 270KEY idx (`col5`) 271); 272INSERT INTO t1(col1,col2,col3,col4,col9,col6) 273VALUES(1,1,0,1,REPEAT(col1,1000),0), (3,2,1,1,REPEAT(col1,1000),NULL); 274ALTER TABLE t1 ADD COLUMN extra INT; 275DROP TABLE t1; 276# 277# Bug#21316860: WL8149:INNODB: FAILING ASSERTION: 278# TEMPL->CLUST_REC_FIELD_NO != ULINT_UNDEFINED 279# 280CREATE TABLE t1 ( 281pk int(11) NOT NULL, 282col_int_nokey int(11), 283col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL, 284col_date_nokey date, 285col_date_key date GENERATED ALWAYS AS (col_date_nokey) VIRTUAL, 286PRIMARY KEY (pk), 287UNIQUE KEY col_int_key (col_int_key) 288); 289ALTER TABLE t1 DROP COLUMN pk; 290DROP TABLE t1; 291# Remove the impact on PK choose by index on virtual generated column 292CREATE TABLE t1 ( 293pk int(11) NOT NULL, 294col_int_nokey int(11) DEFAULT NULL, 295col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) VIRTUAL, 296UNIQUE KEY col_int_key (col_int_key) 297); 298ALTER TABLE t1 add unique index idx(pk); 299DESC t1; 300Field Type Null Key Default Extra 301pk int(11) NO PRI NULL 302col_int_nokey int(11) YES NULL 303col_int_key int(11) YES UNI NULL VIRTUAL GENERATED 304DROP TABLE t1; 305# 306# Bug#21320151 WL8149: WRONG RESULT WITH INDEX SCAN 307# 308CREATE TABLE t1 ( 309id INTEGER NOT NULL, 310b INTEGER GENERATED ALWAYS AS (id+1) VIRTUAL, 311UNIQUE KEY (b) 312); 313INSERT INTO t1 (id) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10); 314EXPLAIN SELECT b FROM t1 FORCE INDEX(b); 315id select_type table type possible_keys key key_len ref rows Extra 3161 SIMPLE t1 index NULL b 5 NULL 9 Using index 317SELECT b FROM t1 FORCE INDEX(b); 318b 3193 3204 3215 3226 3237 3248 3259 32610 32711 328EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5; 329id select_type table type possible_keys key key_len ref rows Extra 3301 SIMPLE t1 range b b 5 NULL 3 Using where; Using index 331SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5; 332b 3333 3344 3355 336DROP TABLE t1; 337 338# Testing data manipulation operations involving FOREIGN KEY 339# on generated columns can be found in: 340# - gcol_ins_upd.inc 341# - gcol_select.inc 342# 343# TODO: CHECK 344# 345# Test how optimizer picks indexes defined on a GC 346# 347CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc)); 348INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6); 349ANALYZE TABLE t1; 350Table Op Msg_type Msg_text 351test.t1 analyze status Engine-independent statistics collected 352test.t1 analyze status OK 353# Should use index 354SELECT * FROM t1 WHERE f1 + 1 > 7; 355f1 gc 3567 8 3578 9 3589 10 359EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7; 360id select_type table type possible_keys key key_len ref rows Extra 3611 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 362SELECT * FROM t1 WHERE f1 + 1 = 7; 363f1 gc 3646 7 365EXPLAIN SELECT * FROM t1 WHERE f1 + 1 = 7; 366id select_type table type possible_keys key key_len ref rows Extra 3671 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 368SELECT * FROM t1 WHERE f1 + 1 IN (7,5); 369f1 gc 3704 5 3716 7 372EXPLAIN SELECT * FROM t1 WHERE f1 + 1 IN(7,5); 373id select_type table type possible_keys key key_len ref rows Extra 3741 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 375SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7; 376f1 gc 3774 5 3785 6 3796 7 380EXPLAIN SELECT * FROM t1 WHERE f1 + 1 BETWEEN 5 AND 7; 381id select_type table type possible_keys key key_len ref rows Extra 3821 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 383# Check that expression isn't transformed for a disabled key 384SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7; 385f1 gc 3864 5 3875 6 3886 7 389EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) WHERE f1 + 1 BETWEEN 5 AND 7; 390id select_type table type possible_keys key key_len ref rows Extra 3911 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 392# Check that ORDER BY could be optimized 393SELECT * FROM t1 ORDER BY f1 + 1; 394f1 gc 3950 1 3961 2 3972 3 3983 4 3994 5 4005 6 4016 7 4027 8 4038 9 4049 10 405EXPLAIN SELECT * FROM t1 ORDER BY f1 + 1; 406id select_type table type possible_keys key key_len ref rows Extra 4071 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort 408EXPLAIN SELECT * FROM t1 IGNORE KEY (gc) ORDER BY f1 + 1; 409id select_type table type possible_keys key key_len ref rows Extra 4101 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort 411# Check that GROUP BY could be optimized 412SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1; 413f1 + 1 MAX(GC) 4141 1 4152 2 4163 3 4174 4 4185 5 4196 6 4207 7 4218 8 4229 9 42310 10 424EXPLAIN SELECT f1 + 1, MAX(GC) FROM t1 GROUP BY f1 + 1; 425id select_type table type possible_keys key key_len ref rows Extra 4261 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 427EXPLAIN SELECT f1 + 1, MAX(GC) 428FROM t1 IGNORE KEY (gc) GROUP BY f1 + 1; 429id select_type table type possible_keys key key_len ref rows Extra 4301 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort 431# Shouldn't use index 432SELECT * FROM t1 WHERE f1 + 1 > 7.0; 433f1 gc 4347 8 4358 9 4369 10 437EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 7.0; 438id select_type table type possible_keys key key_len ref rows Extra 4391 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where 440DROP TABLE t1; 441# Pick index with proper type 442CREATE TABLE t1 (f1 int, 443gc_int int AS (f1 + 1) STORED, 444gc_date DATE AS (f1 + 1) STORED, 445KEY gc_int_idx(gc_int), 446KEY gc_date_idx(gc_date)); 447INSERT INTO t1(f1) VALUES 448(030303),(040404), 449(050505),(060606), 450(010101),(020202), 451(030303),(040404), 452(050505),(060606), 453(010101),(020202), 454(090909),(101010), 455(010101),(020202), 456(070707),(080808); 457ANALYZE TABLE t1; 458Table Op Msg_type Msg_text 459test.t1 analyze status Engine-independent statistics collected 460test.t1 analyze status OK 461SELECT * FROM t1 WHERE f1 + 1 > 070707; 462f1 gc_int gc_date 463101010 101011 2010-10-11 46470707 70708 2007-07-08 46580808 80809 2008-08-09 46690909 90910 2009-09-10 467# INT column & index should be picked 468EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 070707; 469id select_type table type possible_keys key key_len ref rows Extra 4701 SIMPLE t1 ALL NULL NULL NULL NULL 18 Using where 471SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE); 472f1 gc_int gc_date 473101010 101011 2010-10-11 47470707 70708 2007-07-08 47580808 80809 2008-08-09 47690909 90910 2009-09-10 477# DATE column & index should be picked 478EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > CAST(070707 AS DATE); 479id select_type table type possible_keys key key_len ref rows Extra 4801 SIMPLE t1 ALL NULL NULL NULL NULL 18 Using where 481DROP TABLE t1; 482# 483# BUG#21229846: WL8170: SIGNAL 11 IN JOIN::MAKE_SUM_FUNC_LIST 484# 485CREATE TABLE t1 ( 486pk int primary key auto_increment, 487col_int_key INTEGER , 488col_int_gc_key INT GENERATED ALWAYS AS (col_int_key + 1) STORED, 489KEY col_int_gc_key(col_int_gc_key) 490); 491INSERT INTO t1 ( col_int_key) VALUES (7); 492ANALYZE TABLE t1; 493Table Op Msg_type Msg_text 494test.t1 analyze status Engine-independent statistics collected 495test.t1 analyze status OK 496SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2 497FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk)) 498ORDER BY field1, field2; 499field1 field2 5008 7 501EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2 502FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk)) 503ORDER BY field1, field2; 504id select_type table type possible_keys key key_len ref rows Extra 5051 SIMPLE table1 system PRIMARY NULL NULL NULL 1 5061 SIMPLE table2 system PRIMARY NULL NULL NULL 1 507SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2 508FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk)) 509GROUP BY field1, field2; 510field1 field2 5118 7 512EXPLAIN SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2 513FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk)) 514GROUP BY field1, field2; 515id select_type table type possible_keys key key_len ref rows Extra 5161 SIMPLE table1 system PRIMARY NULL NULL NULL 1 5171 SIMPLE table2 system PRIMARY NULL NULL NULL 1 518DROP TABLE t1; 519# 520# Bug#21391781 ASSERT WHEN RUNNING ALTER TABLE ON A TABLE WITH INDEX 521# ON VIRTUAL COLUMN 522# 523CREATE TABLE t1 ( 524col1 INTEGER NOT NULL, 525col2 INTEGER NOT NULL, 526gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL, 527col3 INTEGER NOT NULL, 528col4 INTEGER NOT NULL, 529col5 INTEGER DEFAULT NULL, 530col6 INTEGER DEFAULT NULL, 531col7 INTEGER DEFAULT NULL, 532col8 INTEGER DEFAULT NULL, 533col9 INTEGER DEFAULT NULL, 534col10 INTEGER DEFAULT NULL, 535col11 INTEGER DEFAULT NULL, 536col12 INTEGER DEFAULT NULL, 537col13 INTEGER DEFAULT NULL, 538col14 INTEGER DEFAULT NULL, 539col15 INTEGER DEFAULT NULL, 540col16 INTEGER DEFAULT NULL, 541col17 INTEGER DEFAULT NULL, 542col18 INTEGER DEFAULT NULL, 543col19 INTEGER DEFAULT NULL, 544col20 INTEGER DEFAULT NULL, 545col21 INTEGER DEFAULT NULL, 546col22 INTEGER DEFAULT NULL, 547col23 INTEGER DEFAULT NULL, 548col24 INTEGER DEFAULT NULL, 549col25 INTEGER DEFAULT NULL, 550col26 INTEGER DEFAULT NULL, 551col27 INTEGER DEFAULT NULL, 552col28 INTEGER DEFAULT NULL, 553col29 INTEGER DEFAULT NULL, 554col30 INTEGER DEFAULT NULL, 555col31 INTEGER DEFAULT NULL, 556col32 INTEGER DEFAULT NULL, 557col33 INTEGER DEFAULT NULL, 558col34 INTEGER DEFAULT NULL, 559col35 INTEGER DEFAULT NULL, 560col36 INTEGER DEFAULT NULL, 561col37 INTEGER DEFAULT NULL, 562col38 INTEGER DEFAULT NULL, 563col39 INTEGER DEFAULT NULL, 564col40 INTEGER DEFAULT NULL, 565col41 INTEGER DEFAULT NULL, 566col42 INTEGER DEFAULT NULL, 567col43 INTEGER DEFAULT NULL, 568col44 INTEGER DEFAULT NULL, 569col45 INTEGER DEFAULT NULL, 570col46 INTEGER DEFAULT NULL, 571col47 INTEGER DEFAULT NULL, 572col48 INTEGER DEFAULT NULL, 573col49 INTEGER DEFAULT NULL, 574col50 INTEGER DEFAULT NULL, 575col51 INTEGER DEFAULT NULL, 576col52 INTEGER DEFAULT NULL, 577col53 INTEGER DEFAULT NULL, 578col54 INTEGER DEFAULT NULL, 579col55 INTEGER DEFAULT NULL, 580col56 INTEGER DEFAULT NULL, 581col57 INTEGER DEFAULT NULL, 582col58 INTEGER DEFAULT NULL, 583col59 INTEGER DEFAULT NULL, 584col60 INTEGER DEFAULT NULL, 585col61 INTEGER DEFAULT NULL, 586col62 INTEGER DEFAULT NULL, 587col63 INTEGER DEFAULT NULL, 588col64 INTEGER DEFAULT NULL, 589col65 INTEGER DEFAULT NULL, 590gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL, 591KEY idx1 (gcol1) 592); 593INSERT INTO t1 (col1, col2, col3, col4) 594VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5); 595ALTER TABLE t1 ADD COLUMN extra INTEGER; 596SELECT gcol1 FROM t1 FORCE INDEX(idx1); 597gcol1 5982 5994 6006 6018 60210 603DROP TABLE t1; 604CREATE TABLE t1 ( 605col1 INTEGER NOT NULL, 606col2 INTEGER NOT NULL, 607gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL, 608col3 INTEGER NOT NULL, 609col4 INTEGER NOT NULL, 610col5 INTEGER DEFAULT NULL, 611col6 INTEGER DEFAULT NULL, 612col7 INTEGER DEFAULT NULL, 613col8 INTEGER DEFAULT NULL, 614col9 INTEGER DEFAULT NULL, 615col10 INTEGER DEFAULT NULL, 616col11 INTEGER DEFAULT NULL, 617col12 INTEGER DEFAULT NULL, 618col13 INTEGER DEFAULT NULL, 619col14 INTEGER DEFAULT NULL, 620col15 INTEGER DEFAULT NULL, 621col16 INTEGER DEFAULT NULL, 622col17 INTEGER DEFAULT NULL, 623col18 INTEGER DEFAULT NULL, 624col19 INTEGER DEFAULT NULL, 625col20 INTEGER DEFAULT NULL, 626col21 INTEGER DEFAULT NULL, 627col22 INTEGER DEFAULT NULL, 628col23 INTEGER DEFAULT NULL, 629col24 INTEGER DEFAULT NULL, 630col25 INTEGER DEFAULT NULL, 631col26 INTEGER DEFAULT NULL, 632col27 INTEGER DEFAULT NULL, 633col28 INTEGER DEFAULT NULL, 634col29 INTEGER DEFAULT NULL, 635col30 INTEGER DEFAULT NULL, 636col31 INTEGER DEFAULT NULL, 637col32 INTEGER DEFAULT NULL, 638col33 INTEGER DEFAULT NULL, 639col34 INTEGER DEFAULT NULL, 640col35 INTEGER DEFAULT NULL, 641col36 INTEGER DEFAULT NULL, 642col37 INTEGER DEFAULT NULL, 643col38 INTEGER DEFAULT NULL, 644col39 INTEGER DEFAULT NULL, 645col40 INTEGER DEFAULT NULL, 646col41 INTEGER DEFAULT NULL, 647col42 INTEGER DEFAULT NULL, 648col43 INTEGER DEFAULT NULL, 649col44 INTEGER DEFAULT NULL, 650col45 INTEGER DEFAULT NULL, 651col46 INTEGER DEFAULT NULL, 652col47 INTEGER DEFAULT NULL, 653col48 INTEGER DEFAULT NULL, 654col49 INTEGER DEFAULT NULL, 655col50 INTEGER DEFAULT NULL, 656col51 INTEGER DEFAULT NULL, 657col52 INTEGER DEFAULT NULL, 658col53 INTEGER DEFAULT NULL, 659col54 INTEGER DEFAULT NULL, 660col55 INTEGER DEFAULT NULL, 661col56 INTEGER DEFAULT NULL, 662col57 INTEGER DEFAULT NULL, 663col58 INTEGER DEFAULT NULL, 664col59 INTEGER DEFAULT NULL, 665col60 INTEGER DEFAULT NULL, 666col61 INTEGER DEFAULT NULL, 667col62 INTEGER DEFAULT NULL, 668col63 INTEGER DEFAULT NULL, 669col64 INTEGER DEFAULT NULL, 670col65 INTEGER DEFAULT NULL, 671gcol2 INTEGER GENERATED ALWAYS AS (col3 / col4) VIRTUAL, 672KEY idx1 (gcol2) 673); 674INSERT INTO t1 (col1, col2, col3, col4) 675VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5); 676ALTER TABLE t1 ADD COLUMN extra INTEGER; 677SELECT gcol2 FROM t1 FORCE INDEX(idx1); 678gcol2 6791 6801 6811 6821 6831 684DROP TABLE t1; 685# 686# Bug#21628161 CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN 687# 688CREATE TABLE t (a INT, 689b BOOLEAN GENERATED ALWAYS AS (a+10000) VIRTUAL, 690c BLOB GENERATED ALWAYS AS (b=2) VIRTUAL); 691INSERT INTO t(a) VALUES (1); 692SELECT * FROM t WHERE c = '0'; 693a b c 6941 127 0 695SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 696ALTER TABLE t ADD UNIQUE INDEX (c(1)); 697Warnings: 698Warning 1264 Out of range value for column 'b' at row 1 699SELECT * FROM t WHERE c = '0'; 700a b c 7011 127 0 702DROP TABLE t; 703# 704# Bug#21688115 VIRTUAL COLUMN COMPUTATION SAVE_IN_FIELD() 705# DID NOT RETURN TRUE WITH DIVIDE 0 706# 707CREATE TABLE t (a INT, b INT, h VARCHAR(10)); 708INSERT INTO t VALUES (12, 3, "ss"); 709INSERT INTO t VALUES (13, 4, "ss"); 710INSERT INTO t VALUES (14, 0, "ss"); 711SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 712ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a/b) VIRTUAL; 713CREATE INDEX idx ON t(c); 714ERROR 22012: Division by 0 715CALL mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual column values failed"); 716DROP TABLE t; 717# 718# Bug#21770798 OPTIMIZER DOES NOT USE INDEX FOR GENERATED EXPRESSIONS 719# WITH LOGICAL OPERATORS 720# 721CREATE TABLE t (a INT, b INT, 722gc_and INT GENERATED ALWAYS AS (a AND b) STORED, 723gc_or INT GENERATED ALWAYS AS (a OR b) STORED, 724gc_xor INT GENERATED ALWAYS AS (a XOR b) STORED, 725gc_not INT GENERATED ALWAYS AS (NOT a) STORED, 726gc_case INT GENERATED ALWAYS AS 727(CASE WHEN (a AND b) THEN a ELSE b END) STORED, 728INDEX(gc_and), INDEX(gc_or), INDEX(gc_xor), INDEX(gc_not), 729INDEX(gc_case)); 730INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1); 731ANALYZE TABLE t; 732Table Op Msg_type Msg_text 733test.t analyze status Engine-independent statistics collected 734test.t analyze status OK 735EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1; 736id select_type table type possible_keys key key_len ref rows Extra 7371 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 738SELECT a, b FROM t WHERE (a AND b) = 1; 739a b 7401 1 741EXPLAIN SELECT a, b FROM t WHERE 1 = (a AND b); 742id select_type table type possible_keys key key_len ref rows Extra 7431 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 744SELECT a, b FROM t WHERE 1 = (a AND b); 745a b 7461 1 747EXPLAIN SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3); 748id select_type table type possible_keys key key_len ref rows Extra 7491 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 750SELECT a, b FROM t WHERE (a AND b) IN (1, 2, 3); 751a b 7521 1 753EXPLAIN SELECT a, b FROM t WHERE (a OR b) = 1; 754id select_type table type possible_keys key key_len ref rows Extra 7551 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 756SELECT a, b FROM t WHERE (a OR b) = 1; 757a b 7580 1 7591 0 7601 1 761EXPLAIN SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10; 762id select_type table type possible_keys key key_len ref rows Extra 7631 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 764SELECT a, b FROM t WHERE (a OR b) BETWEEN 1 AND 10; 765a b 7660 1 7671 0 7681 1 769EXPLAIN SELECT a, b FROM t WHERE (a XOR b) = 1; 770id select_type table type possible_keys key key_len ref rows Extra 7711 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 772SELECT a, b FROM t WHERE (a XOR b) = 1; 773a b 7740 1 7751 0 776EXPLAIN SELECT a FROM t WHERE (NOT a) = 1; 777id select_type table type possible_keys key key_len ref rows Extra 7781 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 779SELECT a FROM t WHERE (NOT a) = 1; 780a 7810 7820 783EXPLAIN SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1; 784id select_type table type possible_keys key key_len ref rows Extra 7851 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 786SELECT a FROM t WHERE (CASE WHEN (a AND b) THEN a ELSE b END) = 1; 787a 7880 7891 790EXPLAIN SELECT a, b FROM t WHERE 1 = (b AND a); 791id select_type table type possible_keys key key_len ref rows Extra 7921 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 793SELECT a, b FROM t WHERE 1 = (b AND a); 794a b 7951 1 796EXPLAIN SELECT a, b FROM t WHERE 1 = (b OR a); 797id select_type table type possible_keys key key_len ref rows Extra 7981 SIMPLE t ALL NULL NULL NULL NULL 4 Using where 799SELECT a, b FROM t WHERE 1 = (b OR a); 800a b 8010 1 8021 0 8031 1 804DROP TABLE t; 805# 806# Bug#22810883: ASSERTION FAILED: 807# !(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE)) 808# 809CREATE TABLE t1 (a1 INTEGER GENERATED ALWAYS AS (1 AND 0) STORED, 810a2 INTEGER, KEY (a1)); 811INSERT INTO t1 VALUES (); 812CREATE TABLE t2 (b INTEGER); 813INSERT INTO t2 VALUES (1); 814ANALYZE TABLE t1, t2; 815Table Op Msg_type Msg_text 816test.t1 analyze status Engine-independent statistics collected 817test.t1 analyze status OK 818test.t2 analyze status Engine-independent statistics collected 819test.t2 analyze status OK 820# Used to choose the index on a1 and get wrong results. 821EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0; 822id select_type table type possible_keys key key_len ref rows Extra 8231 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 824SELECT * FROM t1 WHERE (a2 AND a2) = 0; 825a1 a2 826# Used to get assertion or wrong results. 827EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1; 828id select_type table type possible_keys key key_len ref rows Extra 8291 SIMPLE t1 system NULL NULL NULL NULL 1 8301 SIMPLE t2 system NULL NULL NULL NULL 1 831SELECT * FROM t1 STRAIGHT_JOIN t2 ON b WHERE (b AND b) = 1; 832a1 a2 b 8330 NULL 1 834DROP TABLE t1, t2; 835# 836# MDEV-20618 Assertion `btr_validate_index(index, 0, false)' failed 837# in row_upd_sec_index_entry 838# 839CREATE TABLE t1 (A BIT(15), VA BIT(10) GENERATED ALWAYS AS (A),PK INT, 840PRIMARY KEY (PK), UNIQUE KEY (VA)); 841INSERT IGNORE INTO t1 VALUES ( '\r1','a',1); 842Warnings: 843Warning 1906 The value specified for generated column 'VA' in table 't1' has been ignored 844Warning 1264 Out of range value for column 'VA' at row 1 845REPLACE INTO t1 (PK) VALUES (1); 846ERROR 22001: Data too long for column 'VA' at row 1 847DROP TABLE t1; 848# 849# MDEV-17890 Record in index was not found on update, server crash in 850# row_upd_build_difference_binary or 851# Assertion `0' failed in row_upd_sec_index_entry 852# 853CREATE TABLE t1 ( 854pk BIGINT AUTO_INCREMENT, 855b BIT(15), 856v BIT(10) AS (b) VIRTUAL, 857PRIMARY KEY(pk), 858UNIQUE(v) 859); 860INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101'); 861Warnings: 862Warning 1264 Out of range value for column 'v' at row 1 863SELECT pk, b INTO OUTFILE 'load.data' FROM t1; 864LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b); 865ERROR 22001: Data too long for column 'v' at row 1 866DROP TABLE t1; 867# 868# MDEV-17834 Server crashes in row_upd_build_difference_binary 869# on LOAD DATA into table with indexed virtual column 870# 871CREATE TABLE t1 ( 872pk INT, 873i TINYINT, 874ts TIMESTAMP NULL, 875vi TINYINT AS (i+1) PERSISTENT, 876vts TIMESTAMP(5) AS (ts) VIRTUAL, 877PRIMARY KEY(pk), 878UNIQUE(vts) 879); 880INSERT IGNORE INTO t1 (pk,i) VALUES (1,127); 881Warnings: 882Warning 1264 Out of range value for column 'vi' at row 1 883LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/load.data' REPLACE INTO TABLE t1 (pk,i,ts); 884ERROR 22003: Out of range value for column 'vi' at row 1 885DROP TABLE t1; 886# MDEV-19011 Assertion `file->s->base.reclength < file->s->vreclength' 887# failed in ha_myisam::setup_vcols_for_repair 888CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL); 889ALTER TABLE t1 ADD KEY (a); 890DROP TABLE t1; 891DROP VIEW IF EXISTS v1,v2; 892DROP TABLE IF EXISTS t1,t2,t3; 893DROP PROCEDURE IF EXISTS p1; 894DROP FUNCTION IF EXISTS f1; 895DROP TRIGGER IF EXISTS trg1; 896DROP TRIGGER IF EXISTS trg2; 897set sql_warnings = 0; 898