Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 644 insertions, 0 deletions

diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
new file mode 100644
index 00000000..7aa88e77
--- /dev/null
+++ b/mysql-test/main/invisible_field.result
@@ -0,0 +1,644 @@
+FLUSH STATUS;
+create table t1(abc int primary key, xyz int invisible);
+SHOW STATUS LIKE 'Feature_invisible_columns';
+Variable_name	Value
+Feature_invisible_columns	1
+desc t1;
+Field	Type	Null	Key	Default	Extra
+abc	int(11)	NO	PRI	NULL	
+xyz	int(11)	YES		NULL	INVISIBLE
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `abc` int(11) NOT NULL,
+  `xyz` int(11) INVISIBLE DEFAULT NULL,
+  PRIMARY KEY (`abc`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,EXTRA from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EXTRA
+def	test	t1	abc	
+def	test	t1	xyz	INVISIBLE
+drop table t1;
+create table t1(a1 int invisible);
+ERROR 42000: A table must have at least 1 column
+create table t1(a1 blob,invisible(a1));
+ERROR 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
+create table t1(a1 int primary key invisible ,a2 int unique invisible , a3 blob,a4 int not null invisible unique);
+ERROR HY000: Invisible column `a1` must have a default value
+create table t1(abc int not null invisible);
+ERROR 42000: A table must have at least 1 column
+MDEV-14849 CREATE + ALTER with user-invisible columns produce invalid table definition
+create or replace table t1 (pk int auto_increment primary key invisible, i int);
+alter table t1 modify pk int invisible;
+ERROR HY000: Invisible column `pk` must have a default value
+drop table t1;
+create table t1(a int invisible, b int);
+insert into t1 values(1);
+insert into t1(a) values(2);
+insert into t1(b) values(3);
+insert into t1(a,b) values(5,5);
+select * from t1;
+b
+1
+NULL
+3
+5
+select a,b from t1;
+a	b
+NULL	1
+2	NULL
+NULL	3
+5	5
+delete from t1;
+insert into t1 values(1),(2),(3),(4);
+select * from t1;
+b
+1
+2
+3
+4
+select a from t1;
+a
+NULL
+NULL
+NULL
+NULL
+drop table t1;
+#more complex case of invisible
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	INVISIBLE
+c	int(11)	NO	PRI	NULL	auto_increment, INVISIBLE
+d	blob	YES		NULL	
+e	int(11)	YES	UNI	NULL	
+f	int(11)	YES		NULL	
+insert 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);
+select * from t1;
+a	d	e	f
+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
+select a,b,c,d,e,f from t1;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+drop table t1;
+#more complex case of invisible with sql_mode=NO_AUTO_VALUE_ON_ZERO
+set sql_mode='NO_AUTO_VALUE_ON_ZERO';
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	INVISIBLE
+c	int(11)	NO	PRI	NULL	auto_increment, INVISIBLE
+d	blob	YES		NULL	
+e	int(11)	YES	UNI	NULL	
+f	int(11)	YES		NULL	
+insert 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);
+select * from t1;
+a	d	e	f
+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
+select a,b,c,d,e,f from t1;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+drop table t1;
+set sql_mode='';
+create table sdsdsd(a int , b int, invisible(a,b));
+ERROR 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
+create table t1(a int,abc int as (a mod 3) virtual invisible);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+abc	int(11)	YES		NULL	VIRTUAL GENERATED, INVISIBLE
+insert into t1 values(1,default);
+ERROR 21S01: Column count doesn't match value count at row 1
+insert into t1 values(1),(22),(233);
+select * from t1;
+a
+1
+22
+233
+select a,abc from t1;
+a	abc
+1	1
+22	1
+233	2
+drop table t1;
+create table t1(abc int primary key invisible auto_increment, a int);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+abc	int(11)	NO	PRI	NULL	auto_increment, INVISIBLE
+a	int(11)	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `abc` int(11) NOT NULL INVISIBLE AUTO_INCREMENT,
+  `a` int(11) DEFAULT NULL,
+  PRIMARY KEY (`abc`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(3);
+select * from t1;
+a
+1
+2
+3
+select abc,a from t1;
+abc	a
+1	1
+2	2
+3	3
+delete  from t1;
+insert into t1 values(1),(2),(3),(4),(6);
+select abc,a from t1;
+abc	a
+4	1
+5	2
+6	3
+7	4
+8	6
+drop table t1;
+create table t1(abc int);
+alter table t1 change abc ss int invisible;
+ERROR 42000: A table must have at least 1 column
+alter table t1 add column xyz int;
+alter table t1 modify column abc  int ;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+abc	int(11)	YES		NULL	
+xyz	int(11)	YES		NULL	
+insert into t1 values(22);
+ERROR 21S01: Column count doesn't match value count at row 1
+alter table t1 modify column abc  int invisible;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+abc	int(11)	YES		NULL	INVISIBLE
+xyz	int(11)	YES		NULL	
+insert into t1 values(12);
+drop table t1;
+#some test on copy table structure with table data;
+#table with invisible fields and unique keys;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	INVISIBLE
+c	int(11)	NO	PRI	NULL	auto_increment, INVISIBLE
+d	blob	YES		NULL	
+e	int(11)	YES	UNI	NULL	
+f	int(11)	YES		NULL	
+insert 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);
+select * from t1;
+a	d	e	f
+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
+select a,b,c,d,e,f from t1;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+#this won't copy invisible fields and keys;
+create table t2 as select * from t1;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+d	blob	YES		NULL	
+e	int(11)	YES		NULL	
+f	int(11)	YES		NULL	
+select * from t2;
+a	d	e	f
+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
+select a,b,c,d,e,f from t2;
+ERROR 42S22: Unknown column 'b' in 'field list'
+drop table t2;
+#now this will copy invisible fields
+create table t2 as select a,b,c,d,e,f from t1;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	
+c	int(11)	NO		0	
+d	blob	YES		NULL	
+e	int(11)	YES		NULL	
+f	int(11)	YES		NULL	
+select * from t2;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+select a,b,c,d,e,f from t2;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+drop table t2,t1;
+#some test related to copy of data from one table to another;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+insert 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);
+select a,b,c,d,e,f from t1;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+create table t2(a int , b int invisible , c int invisible , d blob , e int unique, f int);
+insert into t2 select * from t1;
+select a,b,c,d,e,f from t2;
+a	b	c	d	e	f
+1	NULL	NULL	d blob	1	1
+1	NULL	NULL	d blob	11	1
+1	NULL	NULL	d blob	2	1
+1	NULL	NULL	d blob	3	1
+1	NULL	NULL	d blob	41	1
+truncate t2;
+insert into t2 (a,b,c,d,e,f) select a,b,c,d,e,f from t1;
+select a,b,c,d,e,f from t2;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+truncate t2;
+drop table t1,t2;
+#some test related to creating view on table with invisible column;
+create table t1(a int , b int invisible , c int invisible auto_increment unique, d blob , e int unique, f int);
+insert 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);
+create view v as select * from t1;
+desc v;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+d	blob	YES		NULL	
+e	int(11)	YES		NULL	
+f	int(11)	YES		NULL	
+select * from v;
+a	d	e	f
+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
+#v does not have invisible column;
+select a,b,c,d,e,f from v;
+ERROR 42S22: Unknown column 'b' in 'field list'
+insert into v values(1,21,32,4);
+select * from v;
+a	d	e	f
+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
+1	21	32	4
+insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6);
+ERROR 42S22: Unknown column 'b' in 'field list'
+drop view v;
+create view v as select a,b,c,d,e,f from t1;
+desc v;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	
+c	int(11)	NO		0	
+d	blob	YES		NULL	
+e	int(11)	YES		NULL	
+f	int(11)	YES		NULL	
+select * from v;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+1	NULL	6	21	32	4
+#v does  have invisible column but they aren't invisible anymore.
+select a,b,c,d,e,f from v;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+1	NULL	6	21	32	4
+insert into v values(1,26,33,4,45,66);
+select a,b,c,d,e,f from v;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+1	NULL	6	21	32	4
+1	26	33	4	45	66
+insert into v(a,b,c,d,e,f) values(1,32,31,41,5,6);
+select a,b,c,d,e,f from v;
+a	b	c	d	e	f
+1	NULL	1	d blob	1	1
+1	NULL	2	d blob	11	1
+1	NULL	3	d blob	2	1
+1	NULL	4	d blob	3	1
+1	NULL	5	d blob	41	1
+1	NULL	6	21	32	4
+1	26	33	4	45	66
+1	32	31	41	5	6
+drop view v;
+drop table t1;
+#now invisible column in where and some join query
+create table t1 (a int unique , b int invisible unique, c int unique  invisible);
+insert into t1(a,b,c) values(1,1,1);
+insert into t1(a,b,c) values(2,2,2);
+insert into t1(a,b,c) values(3,3,3);
+insert into t1(a,b,c) values(4,4,4);
+insert into t1(a,b,c) values(21,21,26);
+insert into t1(a,b,c) values(31,31,35);
+insert into t1(a,b,c) values(41,41,45);
+insert into t1(a,b,c) values(22,22,24);
+insert into t1(a,b,c) values(32,32,33);
+insert into t1(a,b,c) values(42,42,43);
+explain select * from t1 where b=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	b	b	5	const	1	
+select * from t1 where b=3;
+a
+3
+explain select * from t1 where c=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	c	c	5	const	1	
+select * from t1 where c=3;
+a
+3
+create table t2 as select a,b,c from t1;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	
+c	int(11)	YES		NULL	
+explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	eq_ref	b,c	b	5	test.t2.c	1	Using where
+select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+a	a	b	c
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+drop table t1,t2;
+#Unhide  invisible columns
+create table t1 (a int primary key, b int invisible, c int invisible unique);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` int(11) INVISIBLE DEFAULT NULL,
+  `c` int(11) INVISIBLE DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  UNIQUE KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	NO	PRI	NULL	
+b	int(11)	YES		NULL	INVISIBLE
+c	int(11)	YES	UNI	NULL	INVISIBLE
+alter table t1 modify column b int;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	NO	PRI	NULL	
+b	int(11)	YES		NULL	
+c	int(11)	YES	UNI	NULL	INVISIBLE
+alter table t1 change column c d int;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	NO	PRI	NULL	
+b	int(11)	YES		NULL	
+d	int(11)	YES	UNI	NULL	
+drop table t1;
+SHOW STATUS LIKE 'Feature_invisible_columns';
+Variable_name	Value
+Feature_invisible_columns	52
+#invisible is non reserved
+create table t1(a int unique , invisible int invisible, c int );
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES	UNI	NULL	
+invisible	int(11)	YES		NULL	INVISIBLE
+c	int(11)	YES		NULL	
+alter table t1 change column invisible hid int invisible;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES	UNI	NULL	
+hid	int(11)	YES		NULL	INVISIBLE
+c	int(11)	YES		NULL	
+drop table t1;
+CREATE TABLE t1 (b int);
+INSERT t1 values(1);
+INSERT t1 values(2);
+INSERT t1 values(3);
+INSERT t1 values(4);
+INSERT t1 values(5);
+CREATE TABLE t2 (a int invisible) SELECT * FROM t1;
+select * from t2 order by b;
+b
+1
+2
+3
+4
+5
+select a,b from t2 order by b;
+a	b
+NULL	1
+NULL	2
+NULL	3
+NULL	4
+NULL	5
+CREATE TABLE t3 (b int, a int invisible) SELECT * FROM t1;
+select * from t3 order by b;
+b
+1
+2
+3
+4
+5
+select a,b from t3 order by b;
+a	b
+NULL	1
+NULL	2
+NULL	3
+NULL	4
+NULL	5
+CREATE TABLE t4 (b int invisible) SELECT * FROM t1;
+ERROR 42000: A table must have at least 1 column
+CREATE TABLE t5 (a int invisible) SELECT b as a FROM t1;
+ERROR 42000: A table must have at least 1 column
+drop table t1,t2,t3;
+create table t1 (a int , b int invisible default 3, c int , d int invisible default 6);
+CREATE PROCEDURE
+insert_t1(a int, b int)
+MODIFIES SQL DATA
+insert into t1 values(a,b);
+//
+call insert_t1(1,1);
+call insert_t1(2,2);
+select * from t1 order by a;
+a	c
+1	1
+2	2
+select a,b,c,d from t1 order by a;
+a	b	c	d
+1	3	1	6
+2	3	2	6
+DROP PROCEDURE insert_t1;
+delete from t1;
+prepare insert_1 from "insert into t1 values(@a,@c)";
+prepare insert_2 from "insert into t1(a,b,c) values(@a,@b,@c)";
+set @a=1, @c=1;
+execute insert_1;
+set @a=2,@b=2, @c=2;
+execute insert_2;
+select a,b,c,d from t1 order by a;
+a	b	c	d
+1	3	1	6
+2	2	2	6
+drop table t1;
+create table t1(a int default 5 invisible, b int);
+create table t2(a int default (b+11) invisible, b int);
+insert into t1 values(1);
+select a,b from t1;
+a	b
+5	1
+insert into t2 values(1);
+select a,b from t2;
+a	b
+12	1
+drop table t1,t2;
+create table t1 (a int invisible, b int, c int);
+create table t2 (a int, b int, d int);
+insert t1 (a,b,c) values (0,2,3), (10, 20, 30);
+insert t2 (a,b,d) values (1,2,4), (10, 30, 40);
+select * from t1 join t2 using (a);
+b	c	b	d
+20	30	30	40
+select * from t1 natural join t2;
+b	c	a	d
+2	3	1	4
+drop table t1, t2;
+CREATE TABLE t1 (c CHAR(3), t TIMESTAMP invisible);
+INSERT INTO t1 (c,t) VALUES ('foo','2000-01-01 00:00:00');
+CREATE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a= 1;
+INSERT INTO t1 SELECT * FROM t1;
+DROP TABLE t1;
+create or replace table t1 (a int, b int invisible);
+insert into t1 values (1),(2);
+select * from t1 into outfile 'f';
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+load data infile 'f' into table t1;
+select a,b from t1;
+a	b
+1	NULL
+2	NULL
+1	NULL
+2	NULL
+load data infile 'f' into table t1 (a,@v) SET b=@v;
+select a,b from t1;
+a	b
+1	NULL
+2	NULL
+1	NULL
+2	NULL
+1	NULL
+2	NULL
+load data infile 'f' into table t1 (a,@v) SET b=a;
+select a,b from t1;
+a	b
+1	NULL
+2	NULL
+1	NULL
+2	NULL
+1	NULL
+2	NULL
+1	1
+2	2
+truncate table t1;
+insert into t1(a,b) values (1,1),(2,2);
+select a,b from t1 into outfile 'a';
+Warnings:
+Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+load data infile 'a' into table t1(a,b);
+select a,b from t1;
+a	b
+1	1
+2	2
+1	1
+2	2
+load data infile 'a' into table t1 (a,@v) SET b=@v;
+select a,b from t1;
+a	b
+1	1
+2	2
+1	1
+2	2
+1	1
+2	2
+load data infile 'a' into table t1 (a,@v) SET b=@v+2;
+select a,b from t1;
+a	b
+1	1
+2	2
+1	1
+2	2
+1	1
+2	2
+1	3
+2	4
+drop table t1;
+#
+# MDEV-23467 SIGSEGV in fill_record/fill_record_n_invoke_before_triggers on INSERT DELAYED
+#
+create table t1 (a int, b int invisible);
+insert delayed into t1 values (1);
+drop table t1;
+#
+# MDEV-25891 Computed default for INVISIBLE column is ignored in INSERT
+#
+create table t1(
+a int,
+x int default (a),
+y int default (a) invisible,
+z int default (33) invisible);
+insert into t1 values (1, default);
+insert into t1 (a) values (2);
+select a, x, y, z from t1;
+a	x	y	z
+1	1	1	33
+2	2	2	33
+drop table t1;