1FLUSH STATUS; 2create table t1(abc int primary key, xyz int invisible); 3SHOW STATUS LIKE 'Feature_invisible_columns'; 4Variable_name Value 5Feature_invisible_columns 1 6desc t1; 7Field Type Null Key Default Extra 8abc int(11) NO PRI NULL 9xyz int(11) YES NULL INVISIBLE 10show create table t1; 11Table Create Table 12t1 CREATE TABLE `t1` ( 13 `abc` int(11) NOT NULL, 14 `xyz` int(11) INVISIBLE DEFAULT NULL, 15 PRIMARY KEY (`abc`) 16) ENGINE=MyISAM DEFAULT CHARSET=latin1 17select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,EXTRA from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1'; 18TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME EXTRA 19def test t1 abc 20def test t1 xyz INVISIBLE 21drop table t1; 22create table t1(a1 int invisible); 23ERROR 42000: A table must have at least 1 column 24create table t1(a1 blob,invisible(a1)); 25ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(a1))' at line 1 26create table t1(a1 int primary key invisible ,a2 int unique invisible , a3 blob,a4 int not null invisible unique); 27ERROR HY000: Invisible column `a1` must have a default value 28create table t1(abc int not null invisible); 29ERROR 42000: A table must have at least 1 column 30MDEV-14849 CREATE + ALTER with user-invisible columns produce invalid table definition 31create or replace table t1 (pk int auto_increment primary key invisible, i int); 32alter table t1 modify pk int invisible; 33ERROR HY000: Invisible column `pk` must have a default value 34drop table t1; 35create table t1(a int invisible, b int); 36insert into t1 values(1); 37insert into t1(a) values(2); 38insert into t1(b) values(3); 39insert into t1(a,b) values(5,5); 40select * from t1; 41b 421 43NULL 443 455 46select a,b from t1; 47a b 48NULL 1 492 NULL 50NULL 3 515 5 52delete from t1; 53insert into t1 values(1),(2),(3),(4); 54select * from t1; 55b 561 572 583 594 60select a from t1; 61a 62NULL 63NULL 64NULL 65NULL 66drop table t1; 67#more complex case of invisible 68create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int); 69desc t1; 70Field Type Null Key Default Extra 71a int(11) YES NULL 72b int(11) YES NULL INVISIBLE 73c int(11) NO PRI NULL auto_increment, INVISIBLE 74d blob YES NULL 75e int(11) YES UNI NULL 76f int(11) YES NULL 77insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1); 78select * from t1; 79a d e f 801 d blob 1 1 811 d blob 11 1 821 d blob 2 1 831 d blob 3 1 841 d blob 41 1 85select a,b,c,d,e,f from t1; 86a b c d e f 871 NULL 1 d blob 1 1 881 NULL 2 d blob 11 1 891 NULL 3 d blob 2 1 901 NULL 4 d blob 3 1 911 NULL 5 d blob 41 1 92drop table t1; 93#more complex case of invisible with sql_mode=NO_AUTO_VALUE_ON_ZERO 94set sql_mode='NO_AUTO_VALUE_ON_ZERO'; 95create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int); 96desc t1; 97Field Type Null Key Default Extra 98a int(11) YES NULL 99b int(11) YES NULL INVISIBLE 100c int(11) NO PRI NULL auto_increment, INVISIBLE 101d blob YES NULL 102e int(11) YES UNI NULL 103f int(11) YES NULL 104insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1); 105select * from t1; 106a d e f 1071 d blob 1 1 1081 d blob 11 1 1091 d blob 2 1 1101 d blob 3 1 1111 d blob 41 1 112select a,b,c,d,e,f from t1; 113a b c d e f 1141 NULL 1 d blob 1 1 1151 NULL 2 d blob 11 1 1161 NULL 3 d blob 2 1 1171 NULL 4 d blob 3 1 1181 NULL 5 d blob 41 1 119drop table t1; 120set sql_mode=''; 121create table sdsdsd(a int , b int, invisible(a,b)); 122ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(a,b))' at line 1 123create table t1(a int,abc int as (a mod 3) virtual invisible); 124desc t1; 125Field Type Null Key Default Extra 126a int(11) YES NULL 127abc int(11) YES NULL VIRTUAL GENERATED, INVISIBLE 128insert into t1 values(1,default); 129ERROR 21S01: Column count doesn't match value count at row 1 130insert into t1 values(1),(22),(233); 131select * from t1; 132a 1331 13422 135233 136select a,abc from t1; 137a abc 1381 1 13922 1 140233 2 141drop table t1; 142create table t1(abc int primary key invisible auto_increment, a int); 143desc t1; 144Field Type Null Key Default Extra 145abc int(11) NO PRI NULL auto_increment, INVISIBLE 146a int(11) YES NULL 147show create table t1; 148Table Create Table 149t1 CREATE TABLE `t1` ( 150 `abc` int(11) NOT NULL INVISIBLE AUTO_INCREMENT, 151 `a` int(11) DEFAULT NULL, 152 PRIMARY KEY (`abc`) 153) ENGINE=MyISAM DEFAULT CHARSET=latin1 154insert into t1 values(1); 155insert into t1 values(2); 156insert into t1 values(3); 157select * from t1; 158a 1591 1602 1613 162select abc,a from t1; 163abc a 1641 1 1652 2 1663 3 167delete from t1; 168insert into t1 values(1),(2),(3),(4),(6); 169select abc,a from t1; 170abc a 1714 1 1725 2 1736 3 1747 4 1758 6 176drop table t1; 177create table t1(abc int); 178alter table t1 change abc ss int invisible; 179ERROR 42000: A table must have at least 1 column 180alter table t1 add column xyz int; 181alter table t1 modify column abc int ; 182desc t1; 183Field Type Null Key Default Extra 184abc int(11) YES NULL 185xyz int(11) YES NULL 186insert into t1 values(22); 187ERROR 21S01: Column count doesn't match value count at row 1 188alter table t1 modify column abc int invisible; 189desc t1; 190Field Type Null Key Default Extra 191abc int(11) YES NULL INVISIBLE 192xyz int(11) YES NULL 193insert into t1 values(12); 194drop table t1; 195#some test on copy table structure with table data; 196#table with invisible fields and unique keys; 197create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int); 198desc t1; 199Field Type Null Key Default Extra 200a int(11) YES NULL 201b int(11) YES NULL INVISIBLE 202c int(11) NO PRI NULL auto_increment, INVISIBLE 203d blob YES NULL 204e int(11) YES UNI NULL 205f int(11) YES NULL 206insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1); 207select * from t1; 208a d e f 2091 d blob 1 1 2101 d blob 11 1 2111 d blob 2 1 2121 d blob 3 1 2131 d blob 41 1 214select a,b,c,d,e,f from t1; 215a b c d e f 2161 NULL 1 d blob 1 1 2171 NULL 2 d blob 11 1 2181 NULL 3 d blob 2 1 2191 NULL 4 d blob 3 1 2201 NULL 5 d blob 41 1 221#this won't copy invisible fields and keys; 222create table t2 as select * from t1; 223desc t2; 224Field Type Null Key Default Extra 225a int(11) YES NULL 226d blob YES NULL 227e int(11) YES NULL 228f int(11) YES NULL 229select * from t2; 230a d e f 2311 d blob 1 1 2321 d blob 11 1 2331 d blob 2 1 2341 d blob 3 1 2351 d blob 41 1 236select a,b,c,d,e,f from t2; 237ERROR 42S22: Unknown column 'b' in 'field list' 238drop table t2; 239#now this will copy invisible fields 240create table t2 as select a,b,c,d,e,f from t1; 241desc t2; 242Field Type Null Key Default Extra 243a int(11) YES NULL 244b int(11) YES NULL 245c int(11) NO 0 246d blob YES NULL 247e int(11) YES NULL 248f int(11) YES NULL 249select * from t2; 250a b c d e f 2511 NULL 1 d blob 1 1 2521 NULL 2 d blob 11 1 2531 NULL 3 d blob 2 1 2541 NULL 4 d blob 3 1 2551 NULL 5 d blob 41 1 256select a,b,c,d,e,f from t2; 257a b c d e f 2581 NULL 1 d blob 1 1 2591 NULL 2 d blob 11 1 2601 NULL 3 d blob 2 1 2611 NULL 4 d blob 3 1 2621 NULL 5 d blob 41 1 263drop table t2,t1; 264#some test related to copy of data from one table to another; 265create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int); 266insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1); 267select a,b,c,d,e,f from t1; 268a b c d e f 2691 NULL 1 d blob 1 1 2701 NULL 2 d blob 11 1 2711 NULL 3 d blob 2 1 2721 NULL 4 d blob 3 1 2731 NULL 5 d blob 41 1 274create table t2(a int , b int invisible , c int invisible , d blob , e int unique, f int); 275insert into t2 select * from t1; 276select a,b,c,d,e,f from t2; 277a b c d e f 2781 NULL NULL d blob 1 1 2791 NULL NULL d blob 11 1 2801 NULL NULL d blob 2 1 2811 NULL NULL d blob 3 1 2821 NULL NULL d blob 41 1 283truncate t2; 284insert into t2 (a,b,c,d,e,f) select a,b,c,d,e,f from t1; 285select a,b,c,d,e,f from t2; 286a b c d e f 2871 NULL 1 d blob 1 1 2881 NULL 2 d blob 11 1 2891 NULL 3 d blob 2 1 2901 NULL 4 d blob 3 1 2911 NULL 5 d blob 41 1 292truncate t2; 293drop table t1,t2; 294#some test related to creating view on table with invisible column; 295create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int); 296insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1); 297create view v as select * from t1; 298desc v; 299Field Type Null Key Default Extra 300a int(11) YES NULL 301d blob YES NULL 302e int(11) YES NULL 303f int(11) YES NULL 304select * from v; 305a d e f 3061 d blob 1 1 3071 d blob 11 1 3081 d blob 2 1 3091 d blob 3 1 3101 d blob 41 1 311#v does not have invisible column; 312select a,b,c,d,e,f from v; 313ERROR 42S22: Unknown column 'b' in 'field list' 314insert into v values(1,21,32,4); 315select * from v; 316a d e f 3171 d blob 1 1 3181 d blob 11 1 3191 d blob 2 1 3201 d blob 3 1 3211 d blob 41 1 3221 21 32 4 323insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6); 324ERROR 42S22: Unknown column 'b' in 'field list' 325drop view v; 326create view v as select a,b,c,d,e,f from t1; 327desc v; 328Field Type Null Key Default Extra 329a int(11) YES NULL 330b int(11) YES NULL 331c int(11) NO 0 332d blob YES NULL 333e int(11) YES NULL 334f int(11) YES NULL 335select * from v; 336a b c d e f 3371 NULL 1 d blob 1 1 3381 NULL 2 d blob 11 1 3391 NULL 3 d blob 2 1 3401 NULL 4 d blob 3 1 3411 NULL 5 d blob 41 1 3421 NULL 6 21 32 4 343#v does have invisible column but they aren't invisible anymore. 344select a,b,c,d,e,f from v; 345a b c d e f 3461 NULL 1 d blob 1 1 3471 NULL 2 d blob 11 1 3481 NULL 3 d blob 2 1 3491 NULL 4 d blob 3 1 3501 NULL 5 d blob 41 1 3511 NULL 6 21 32 4 352insert into v values(1,26,33,4,45,66); 353select a,b,c,d,e,f from v; 354a b c d e f 3551 NULL 1 d blob 1 1 3561 NULL 2 d blob 11 1 3571 NULL 3 d blob 2 1 3581 NULL 4 d blob 3 1 3591 NULL 5 d blob 41 1 3601 NULL 6 21 32 4 3611 26 33 4 45 66 362insert into v(a,b,c,d,e,f) values(1,32,31,41,5,6); 363select a,b,c,d,e,f from v; 364a b c d e f 3651 NULL 1 d blob 1 1 3661 NULL 2 d blob 11 1 3671 NULL 3 d blob 2 1 3681 NULL 4 d blob 3 1 3691 NULL 5 d blob 41 1 3701 NULL 6 21 32 4 3711 26 33 4 45 66 3721 32 31 41 5 6 373drop view v; 374drop table t1; 375#now invisible column in where and some join query 376create table t1 (a int unique , b int invisible unique, c int unique invisible); 377insert into t1(a,b,c) values(1,1,1); 378insert into t1(a,b,c) values(2,2,2); 379insert into t1(a,b,c) values(3,3,3); 380insert into t1(a,b,c) values(4,4,4); 381insert into t1(a,b,c) values(21,21,26); 382insert into t1(a,b,c) values(31,31,35); 383insert into t1(a,b,c) values(41,41,45); 384insert into t1(a,b,c) values(22,22,24); 385insert into t1(a,b,c) values(32,32,33); 386insert into t1(a,b,c) values(42,42,43); 387explain select * from t1 where b=3; 388id select_type table type possible_keys key key_len ref rows Extra 3891 SIMPLE t1 const b b 5 const 1 390select * from t1 where b=3; 391a 3923 393explain select * from t1 where c=3; 394id select_type table type possible_keys key key_len ref rows Extra 3951 SIMPLE t1 const c c 5 const 1 396select * from t1 where c=3; 397a 3983 399create table t2 as select a,b,c from t1; 400desc t2; 401Field Type Null Key Default Extra 402a int(11) YES NULL 403b int(11) YES NULL 404c int(11) YES NULL 405explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b; 406id select_type table type possible_keys key key_len ref rows Extra 4071 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where 4081 SIMPLE t1 eq_ref b,c b 5 test.t2.c 1 Using where 409select * from t1,t2 where t1.b = t2.c and t1.c = t2.b; 410a a b c 4111 1 1 1 4122 2 2 2 4133 3 3 3 4144 4 4 4 415drop table t1,t2; 416#Unhide invisible columns 417create table t1 (a int primary key, b int invisible, c int invisible unique); 418show create table t1; 419Table Create Table 420t1 CREATE TABLE `t1` ( 421 `a` int(11) NOT NULL, 422 `b` int(11) INVISIBLE DEFAULT NULL, 423 `c` int(11) INVISIBLE DEFAULT NULL, 424 PRIMARY KEY (`a`), 425 UNIQUE KEY `c` (`c`) 426) ENGINE=MyISAM DEFAULT CHARSET=latin1 427desc t1; 428Field Type Null Key Default Extra 429a int(11) NO PRI NULL 430b int(11) YES NULL INVISIBLE 431c int(11) YES UNI NULL INVISIBLE 432alter table t1 modify column b int; 433desc t1; 434Field Type Null Key Default Extra 435a int(11) NO PRI NULL 436b int(11) YES NULL 437c int(11) YES UNI NULL INVISIBLE 438alter table t1 change column c d int; 439desc t1; 440Field Type Null Key Default Extra 441a int(11) NO PRI NULL 442b int(11) YES NULL 443d int(11) YES UNI NULL 444drop table t1; 445SHOW STATUS LIKE 'Feature_invisible_columns'; 446Variable_name Value 447Feature_invisible_columns 52 448#invisible is non reserved 449create table t1(a int unique , invisible int invisible, c int ); 450desc t1; 451Field Type Null Key Default Extra 452a int(11) YES UNI NULL 453invisible int(11) YES NULL INVISIBLE 454c int(11) YES NULL 455alter table t1 change column invisible hid int invisible; 456desc t1; 457Field Type Null Key Default Extra 458a int(11) YES UNI NULL 459hid int(11) YES NULL INVISIBLE 460c int(11) YES NULL 461drop table t1; 462CREATE TABLE t1 (b int); 463INSERT t1 values(1); 464INSERT t1 values(2); 465INSERT t1 values(3); 466INSERT t1 values(4); 467INSERT t1 values(5); 468CREATE TABLE t2 (a int invisible) SELECT * FROM t1; 469select * from t2 order by b; 470b 4711 4722 4733 4744 4755 476select a,b from t2 order by b; 477a b 478NULL 1 479NULL 2 480NULL 3 481NULL 4 482NULL 5 483CREATE TABLE t3 (b int, a int invisible) SELECT * FROM t1; 484select * from t3 order by b; 485b 4861 4872 4883 4894 4905 491select a,b from t3 order by b; 492a b 493NULL 1 494NULL 2 495NULL 3 496NULL 4 497NULL 5 498CREATE TABLE t4 (b int invisible) SELECT * FROM t1; 499ERROR 42000: A table must have at least 1 column 500CREATE TABLE t5 (a int invisible) SELECT b as a FROM t1; 501ERROR 42000: A table must have at least 1 column 502drop table t1,t2,t3; 503create table t1 (a int , b int invisible default 3, c int , d int invisible default 6); 504CREATE PROCEDURE 505insert_t1(a int, b int) 506MODIFIES SQL DATA 507insert into t1 values(a,b); 508// 509call insert_t1(1,1); 510call insert_t1(2,2); 511select * from t1 order by a; 512a c 5131 1 5142 2 515select a,b,c,d from t1 order by a; 516a b c d 5171 3 1 6 5182 3 2 6 519DROP PROCEDURE insert_t1; 520delete from t1; 521prepare insert_1 from "insert into t1 values(@a,@c)"; 522prepare insert_2 from "insert into t1(a,b,c) values(@a,@b,@c)"; 523set @a=1, @c=1; 524execute insert_1; 525set @a=2,@b=2, @c=2; 526execute insert_2; 527select a,b,c,d from t1 order by a; 528a b c d 5291 3 1 6 5302 2 2 6 531drop table t1; 532create table t1(a int default 5 invisible, b int); 533create table t2(a int default (b+11) invisible, b int); 534insert into t1 values(1); 535select a,b from t1; 536a b 5375 1 538insert into t2 values(1); 539select a,b from t2; 540a b 54112 1 542drop table t1,t2; 543create table t1 (a int invisible, b int, c int); 544create table t2 (a int, b int, d int); 545insert t1 (a,b,c) values (0,2,3), (10, 20, 30); 546insert t2 (a,b,d) values (1,2,4), (10, 30, 40); 547select * from t1 join t2 using (a); 548b c b d 54920 30 30 40 550select * from t1 natural join t2; 551b c a d 5522 3 1 4 553drop table t1, t2; 554CREATE TABLE t1 (c CHAR(3), t TIMESTAMP invisible); 555INSERT INTO t1 (c,t) VALUES ('foo','2000-01-01 00:00:00'); 556CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a= 1; 557INSERT INTO t1 SELECT * FROM t1; 558DROP TABLE t1; 559create or replace table t1 (a int, b int invisible); 560insert into t1 values (1),(2); 561select * from t1 into outfile 'f'; 562Warnings: 563Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 564load data infile 'f' into table t1; 565select a,b from t1; 566a b 5671 NULL 5682 NULL 5691 NULL 5702 NULL 571load data infile 'f' into table t1 (a,@v) SET b=@v; 572select a,b from t1; 573a b 5741 NULL 5752 NULL 5761 NULL 5772 NULL 5781 NULL 5792 NULL 580load data infile 'f' into table t1 (a,@v) SET b=a; 581select a,b from t1; 582a b 5831 NULL 5842 NULL 5851 NULL 5862 NULL 5871 NULL 5882 NULL 5891 1 5902 2 591truncate table t1; 592insert into t1(a,b) values (1,1),(2,2); 593select a,b from t1 into outfile 'a'; 594Warnings: 595Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 596load data infile 'a' into table t1(a,b); 597select a,b from t1; 598a b 5991 1 6002 2 6011 1 6022 2 603load data infile 'a' into table t1 (a,@v) SET b=@v; 604select a,b from t1; 605a b 6061 1 6072 2 6081 1 6092 2 6101 1 6112 2 612load data infile 'a' into table t1 (a,@v) SET b=@v+2; 613select a,b from t1; 614a b 6151 1 6162 2 6171 1 6182 2 6191 1 6202 2 6211 3 6222 4 623drop table t1; 624# 625# MDEV-23467 SIGSEGV in fill_record/fill_record_n_invoke_before_triggers on INSERT DELAYED 626# 627create table t1 (a int, b int invisible); 628insert delayed into t1 values (1); 629drop table t1; 630# 631# MDEV-25891 Computed default for INVISIBLE column is ignored in INSERT 632# 633create table t1( 634a int, 635x int default (a), 636y int default (a) invisible, 637z int default (33) invisible); 638insert into t1 values (1, default); 639insert into t1 (a) values (2); 640select a, x, y, z from t1; 641a x y z 6421 1 1 33 6432 2 2 33 644drop table t1; 645