1SET @@session.default_storage_engine = 'InnoDB'; 2create table t1 (a int, 3b int as (-a), 4c int as (-a) persistent); 5set sql_warnings = 1; 6# 7# *** INSERT *** 8# 9# INSERT INTO tbl_name VALUES... DEFAULT is specified against vcols 10insert into t1 values (1,default,default); 11select * from t1; 12a b c 131 -1 -1 14delete from t1; 15select * from t1; 16a b c 17# INSERT INTO tbl_name VALUES... NULL is specified against vcols 18insert into t1 values (1,null,null); 19select * from t1; 20a b c 211 -1 -1 22delete from t1; 23select * from t1; 24a b c 25# INSERT INTO tbl_name VALUES... a non-NULL value is specified against vcols 26insert ignore into t1 values (1,2,3); 27Warnings: 28Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored 29Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored 30select * from t1; 31a b c 321 -1 -1 33delete from t1; 34select * from t1; 35a b c 36# INSERT INTO tbl_name (<non_vcol_list>) VALUES... 37insert into t1 (a) values (1), (2); 38select * from t1; 39a b c 401 -1 -1 412 -2 -2 42delete from t1; 43select * from t1; 44a b c 45# INSERT INTO tbl_name (<normal+vcols>) VALUES... DEFAULT is specified 46# against vcols 47insert into t1 (a,b) values (1,default), (2,default); 48select * from t1; 49a b c 501 -1 -1 512 -2 -2 52delete from t1; 53select * from t1; 54a b c 55# INSERT INTO tbl_name (<normal+vcols>) VALUES... NULL is specified against vcols 56insert into t1 (a,b) values (1,null), (2,null); 57select * from t1; 58a b c 591 -1 -1 602 -2 -2 61delete from t1; 62select * from t1; 63a b c 64# INSERT INTO tbl_name (<normal+vcols>) VALUES... a non-NULL value is specified 65# against vcols 66insert ignore into t1 (a,b) values (1,3), (2,4); 67Warnings: 68Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored 69Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored 70select * from t1; 71a b c 721 -1 -1 732 -2 -2 74delete from t1; 75select * from t1; 76a b c 77drop table t1; 78# Table with UNIQUE non-vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE 79# KEY UPDATE <non_vcol>=expr, <vcol>=expr 80create table t1 (a int unique, 81b int as (-a), 82c int as (-a) persistent); 83insert into t1 values (1,default,default); 84insert into t1 values (1,default,default) 85on duplicate key update a=2, b=default; 86select a,b,c from t1; 87a b c 882 -2 -2 89delete from t1 where b in (1,2); 90select * from t1; 91a b c 922 -2 -2 93drop table t1; 94# Table with UNIQUE vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE 95# KEY UPDATE <non_vcol>=expr, <vcol>=expr 96create table t1 (a int, 97b int as (-a), 98c int as (-a) persistent unique); 99insert into t1 values (1,default,default); 100insert into t1 values (1,default,default) 101on duplicate key update a=2, b=default; 102select a,b,c from t1; 103a b c 1042 -2 -2 105# CREATE new_table ... LIKE old_table 106# INSERT INTO new_table SELECT * from old_table 107create table t2 like t1; 108insert ignore into t2 select * from t1; 109Warnings: 110Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored 111Warning 1906 The value specified for generated column 'c' in table 't2' has been ignored 112select * from t1; 113a b c 1142 -2 -2 115drop table t2; 116# CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-vcols>, <vcols>) 117# SELECT <non-vcols>, <vcols> from old_table 118insert into t1 values (1,default,default); 119select * from t1; 120a b c 1212 -2 -2 1221 -1 -1 123create table t2 like t1; 124insert ignore into t2 (a,b) select a,b from t1; 125Warnings: 126Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored 127Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored 128select * from t2; 129a b c 1302 -2 -2 1311 -1 -1 132drop table t2; 133drop table t1; 134# 135# *** UPDATE *** 136# 137# UPDATE tbl_name SET non-vcol=expr WHERE non-vcol=expr 138create table t1 (a int, 139b int as (-a), 140c int as (-a) persistent); 141insert into t1 (a) values (1), (2); 142select * from t1; 143a b c 1441 -1 -1 1452 -2 -2 146update t1 set a=3 where a=2; 147select * from t1; 148a b c 1491 -1 -1 1503 -3 -3 151delete from t1; 152select * from t1; 153a b c 154# UPDATE tbl_name SET vcol=expr WHERE non-vcol=expr 155insert into t1 (a) values (1), (2); 156select * from t1; 157a b c 1581 -1 -1 1592 -2 -2 160update ignore t1 set c=3 where a=2; 161Warnings: 162Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored 163select * from t1; 164a b c 1651 -1 -1 1662 -2 -2 167delete from t1; 168select * from t1; 169a b c 170# UPDATE tbl_name SET non-vcol=expr WHERE vcol=expr 171insert into t1 (a) values (1), (2); 172select * from t1; 173a b c 1741 -1 -1 1752 -2 -2 176update t1 set a=3 where b=-2; 177select * from t1; 178a b c 1791 -1 -1 1803 -3 -3 181delete from t1; 182select * from t1; 183a b c 184# UPDATE tbl_name SET vcol=expr WHERE vcol=expr 185insert into t1 (a) values (1), (2); 186select * from t1; 187a b c 1881 -1 -1 1892 -2 -2 190update ignore t1 set c=3 where b=-2; 191Warnings: 192Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored 193select * from t1; 194a b c 1951 -1 -1 1962 -2 -2 197delete from t1; 198select * from t1; 199a b c 200drop table t1; 201# INDEX created on vcol 202# UPDATE tbl_name SET non-vcol=expr WHERE vcol=const 203create table t1 (a int, 204b int as (-a), 205c int as (-a) persistent unique); 206insert into t1 (a) values (1), (2); 207select * from t1; 208a b c 2091 -1 -1 2102 -2 -2 211update t1 set a=3 where c=-2; 212select * from t1; 213a b c 2141 -1 -1 2153 -3 -3 216delete from t1; 217select * from t1; 218a b c 219# INDEX created on vcol 220# UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2 221insert into t1 (a) values (1), (2); 222select * from t1; 223a b c 2241 -1 -1 2252 -2 -2 226update t1 set a=3 where c between -3 and -2; 227select * from t1; 228a b c 2291 -1 -1 2303 -3 -3 231delete from t1; 232select * from t1; 233a b c 234# No INDEX created on vcol 235# UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2 236insert into t1 (a) values (1), (2); 237select * from t1; 238a b c 2391 -1 -1 2402 -2 -2 241update t1 set a=3 where b between -3 and -2; 242select * from t1; 243a b c 2441 -1 -1 2453 -3 -3 246delete from t1; 247select * from t1; 248a b c 249# INDEX created on vcol 250# UPDATE tbl_name SET non-vcol=expr 251# WHERE vcol=between const1 and const2 ORDER BY vcol 252insert into t1 (a) values (1), (2), (3), (4), (5); 253select * from t1; 254a b c 2551 -1 -1 2562 -2 -2 2573 -3 -3 2584 -4 -4 2595 -5 -5 260update t1 set a=6 where c between -1 and 0 261order by c; 262select * from t1; 263a b c 2646 -6 -6 2652 -2 -2 2663 -3 -3 2674 -4 -4 2685 -5 -5 269delete from t1 where c between -6 and 0; 270select * from t1; 271a b c 272# INDEX created on vcol 273# UPDATE tbl_name SET non-vcol=expr 274# WHERE vcol=between const1 and const2 ORDER BY vcol LIMIT 2 275insert into t1 (a) values (1), (2), (3), (4), (5); 276select * from t1; 277a b c 2781 -1 -1 2792 -2 -2 2803 -3 -3 2814 -4 -4 2825 -5 -5 283update t1 set a=6 where c between -1 and 0 284order by c limit 2; 285select * from t1; 286a b c 2876 -6 -6 2882 -2 -2 2893 -3 -3 2904 -4 -4 2915 -5 -5 292delete from t1 where c between -2 and 0 order by c; 293select * from t1; 294a b c 2956 -6 -6 2963 -3 -3 2974 -4 -4 2985 -5 -5 299delete from t1; 300# INDEX created on vcol 301# UPDATE tbl_name SET non-vcol=expr 302# WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3 303insert into t1 (a) values (1), (2), (3), (4), (5); 304select * from t1; 305a b c 3061 -1 -1 3072 -2 -2 3083 -3 -3 3094 -4 -4 3105 -5 -5 311update t1 set a=6 where (c between -2 and 0) and (b=-1); 312select * from t1; 313a b c 3146 -6 -6 3152 -2 -2 3163 -3 -3 3174 -4 -4 3185 -5 -5 319delete from t1; 320# INDEX created on vcol 321# UPDATE tbl_name SET non-vcol=expr 322# WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3 323# ORDER BY indexed vcol 324insert into t1 (a) values (1), (2), (3), (4), (5); 325select * from t1; 326a b c 3271 -1 -1 3282 -2 -2 3293 -3 -3 3304 -4 -4 3315 -5 -5 332update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c; 333select * from t1; 334a b c 3356 -6 -6 3362 -2 -2 3373 -3 -3 3384 -4 -4 3395 -5 -5 340delete from t1; 341drop table t1; 342# 343# Verify ON UPDATE/DELETE actions of FOREIGN KEYs 344create table t2 (a int primary key, name varchar(10)); 345create table t1 (a int primary key, b int as (a % 10) persistent); 346insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3'); 347insert into t1 (a) values (1),(2),(3); 348select * from t1; 349a b 3501 1 3512 2 3523 3 353select * from t2; 354a name 3551 value1 3562 value2 3573 value3 358select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a; 359a b name 3601 1 value1 3612 2 value2 3623 3 value3 363# - ON UPDATE RESTRICT 364alter table t1 add foreign key (b) references t2(a) on update restrict; 365insert into t1 (a) values (4); 366ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`)) 367update t2 set a=4 where a=3; 368ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`)) 369select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a; 370a b name 3711 1 value1 3722 2 value2 3733 3 value3 374alter table t1 drop foreign key t1_ibfk_1; 375# - ON DELETE RESTRICT 376alter table t1 add foreign key (b) references t2(a) on delete restrict; 377delete from t2 where a=3; 378ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`)) 379select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a; 380a b name 3811 1 value1 3822 2 value2 3833 3 value3 384select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a); 385a b name 3861 1 value1 3872 2 value2 3883 3 value3 389alter table t1 drop foreign key t1_ibfk_1; 390# - ON DELETE CASCADE 391alter table t1 add foreign key (b) references t2(a) on delete cascade; 392delete from t2 where a=3; 393select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a; 394a b name 3951 1 value1 3962 2 value2 397select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a); 398a b name 3991 1 value1 4002 2 value2 401alter table t1 drop foreign key t1_ibfk_1; 402drop table t1; 403drop table t2; 404# 405# *** REPLACE *** 406# 407# UNIQUE INDEX on vcol 408# REPLACE tbl_name (non-vcols) VALUES (non-vcols); 409create table t1 (a int, 410b int as (-a), 411c int as (-a) persistent unique, 412d varchar(16)); 413insert into t1 (a,d) values (1,'a'), (2,'b'); 414select * from t1; 415a b c d 4161 -1 -1 a 4172 -2 -2 b 418replace t1 (a,d) values (1,'c'); 419select * from t1; 420a b c d 4211 -1 -1 c 4222 -2 -2 b 423delete from t1; 424select * from t1; 425a b c d 426set sql_warnings = 0; 427drop table t1; 428# 429# MDEV-9093: Persistent computed column is not updated when 430# update query contains join 431# 432CREATE TABLE `t1` ( 433`id` bigint(20) NOT NULL, 434`name` varchar(254) DEFAULT NULL, 435`name_hash` varchar(64) AS (sha1(name)) PERSISTENT, 436PRIMARY KEY (`id`) 437); 438insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3'); 439create table t2 (id bigint); 440insert into t2 values (2050),(2051),(2041); 441select * from t1; 442id name name_hash 4432041 name3 1aefcd1b0f39da45fa1fd7236f683c907c15ef82 4442050 name1 9b46b0dd3a8083c070c3b9953bb5f3f95c5ab4da 4452051 name2 39ea84acf1fef629fef18a9c6f5799bba32ecc25 446update t1 join t2 using(id) set name = concat(name, 447'+1') where t1.id in (2051,2041); 448select * from t1; 449id name name_hash 4502041 name3+1 93c9096df48221428de46e146abc9f4f94bf7d2e 4512050 name1 9b46b0dd3a8083c070c3b9953bb5f3f95c5ab4da 4522051 name2+1 fd4f236320db3956a5ec073c5ec39707d7f05708 453drop table t1,t2; 454# 455# Test error handling with virtual columns 456# 457CREATE TABLE IF NOT EXISTS t1 ( 458f1 DOUBLE, 459f2 DOUBLE NOT NULL DEFAULT '0', 460f3 DOUBLE, 461f4 DOUBLE NOT NULL DEFAULT '0', 462v1 DOUBLE AS ( ( f1 DIV ( f1 ) ) <= f2 ) VIRTUAL, 463v2 DOUBLE AS ( ( f2 DIV ( f2 ) ) <= f2 ) VIRTUAL, 464KEY (v2) 465); 466set sql_mode='strict_all_tables,error_for_division_by_zero'; 467INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0); 468ERROR 22012: Division by 0 469INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1); 470INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1); 471ERROR 22012: Division by 0 472INSERT IGNORE INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1); 473Warnings: 474Warning 1365 Division by 0 475INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1); 476select v1 from t1; 477v1 4781 4790 480NULL 481Warnings: 482Warning 1365 Division by 0 483INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2); 484ERROR 22012: Division by 0 485INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4); 486INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0); 487ERROR 22012: Division by 0 488INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0); 489INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1; 490ERROR 22012: Division by 0 491select count(*) from t1; 492count(*) 4936 494DELETE FROM t1 WHERE v2 != f1 and f1 < 5; 495Warnings: 496Warning 1365 Division by 0 497Warning 1365 Division by 0 498select count(*) from t1; 499count(*) 5003 501select * from t1; 502f1 f2 f3 f4 v1 v2 5031 1 1 1 1 1 5041 0 1 1 0 NULL 5056 6 0 0 1 1 506Warnings: 507Warning 1365 Division by 0 508INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1; 509ERROR 23000: Column 'f2' cannot be null 510INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1 where f2 !=0; 511Warnings: 512Warning 1365 Division by 0 513UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null; 514Warnings: 515Warning 1365 Division by 0 516SELECT * FROM t1; 517f1 f2 f3 f4 v1 v2 5181 1 1 1 1 1 5191 0 1 1 0 NULL 5206 6 0 0 1 1 5211 1 10 10 1 1 5221 1 10 10 1 1 523Warnings: 524Warning 1365 Division by 0 525TRUNCATE TABLE t1; 526set sql_mode='error_for_division_by_zero'; 527INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0); 528Warnings: 529Warning 1365 Division by 0 530INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1); 531INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1); 532Warnings: 533Warning 1365 Division by 0 534INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1); 535select v1 from t1; 536v1 537NULL 5381 5390 540NULL 541Warnings: 542Warning 1365 Division by 0 543Warning 1365 Division by 0 544INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2); 545Warnings: 546Warning 1365 Division by 0 547INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4); 548INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0); 549Warnings: 550Warning 1365 Division by 0 551INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0); 552INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1; 553Warnings: 554Warning 1365 Division by 0 555Warning 1365 Division by 0 556Warning 1365 Division by 0 557Warning 1365 Division by 0 558select count(*) from t1; 559count(*) 56022 561DELETE FROM t1 WHERE v2 != f1 and f1 < 5; 562Warnings: 563Warning 1365 Division by 0 564Warning 1365 Division by 0 565Warning 1365 Division by 0 566Warning 1365 Division by 0 567Warning 1365 Division by 0 568Warning 1365 Division by 0 569Warning 1365 Division by 0 570Warning 1365 Division by 0 571Warning 1365 Division by 0 572Warning 1365 Division by 0 573Warning 1365 Division by 0 574Warning 1365 Division by 0 575Warning 1365 Division by 0 576Warning 1365 Division by 0 577Warning 1365 Division by 0 578Warning 1365 Division by 0 579select count(*) from t1; 580count(*) 58115 582select * from t1; 583f1 f2 f3 f4 v1 v2 5840 0 0 0 NULL NULL 5851 1 1 1 1 1 5861 0 1 1 0 NULL 5870 0 0 0 NULL NULL 5885 5 5 5 1 1 5891 0 0 0 0 NULL 5906 6 0 0 1 1 5910 0 0 0 NULL NULL 5921 1 1 1 1 1 5931 1 1 1 1 1 5941 1 1 1 1 1 5950 0 0 0 NULL NULL 5965 5 5 5 1 1 5970 0 0 0 NULL NULL 5980 0 0 0 NULL NULL 599Warnings: 600Warning 1365 Division by 0 601Warning 1365 Division by 0 602Warning 1365 Division by 0 603Warning 1365 Division by 0 604Warning 1365 Division by 0 605Warning 1365 Division by 0 606Warning 1365 Division by 0 607Warning 1365 Division by 0 608Warning 1365 Division by 0 609Warning 1365 Division by 0 610Warning 1365 Division by 0 611Warning 1365 Division by 0 612Warning 1365 Division by 0 613Warning 1365 Division by 0 614INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1; 615Warnings: 616Warning 1365 Division by 0 617Warning 1365 Division by 0 618Warning 1365 Division by 0 619Warning 1365 Division by 0 620Warning 1365 Division by 0 621Warning 1365 Division by 0 622Warning 1365 Division by 0 623Warning 1365 Division by 0 624Warning 1365 Division by 0 625Warning 1365 Division by 0 626Warning 1365 Division by 0 627Warning 1365 Division by 0 628Warning 1365 Division by 0 629Warning 1365 Division by 0 630Warning 1048 Column 'f2' cannot be null 631Warning 1365 Division by 0 632Warning 1048 Column 'f2' cannot be null 633Warning 1365 Division by 0 634Warning 1048 Column 'f2' cannot be null 635Warning 1365 Division by 0 636Warning 1048 Column 'f2' cannot be null 637Warning 1365 Division by 0 638Warning 1048 Column 'f2' cannot be null 639Warning 1365 Division by 0 640Warning 1048 Column 'f2' cannot be null 641Warning 1365 Division by 0 642Warning 1048 Column 'f2' cannot be null 643Warning 1365 Division by 0 644Warning 1048 Column 'f2' cannot be null 645Warning 1365 Division by 0 646UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null; 647Warnings: 648Warning 1365 Division by 0 649Warning 1365 Division by 0 650Warning 1365 Division by 0 651Warning 1365 Division by 0 652Warning 1365 Division by 0 653Warning 1365 Division by 0 654Warning 1365 Division by 0 655Warning 1365 Division by 0 656Warning 1365 Division by 0 657Warning 1365 Division by 0 658Warning 1365 Division by 0 659Warning 1365 Division by 0 660Warning 1365 Division by 0 661Warning 1365 Division by 0 662Warning 1365 Division by 0 663Warning 1365 Division by 0 664Warning 1365 Division by 0 665Warning 1365 Division by 0 666Warning 1365 Division by 0 667Warning 1365 Division by 0 668Warning 1365 Division by 0 669Warning 1365 Division by 0 670Warning 1365 Division by 0 671Warning 1365 Division by 0 672drop table t1; 673set sql_mode=@@global.sql_mode; 674