MDEV-14785 SYSTEM_INVISIBLE behaviour not consistent

Hide INVISIBLE_SYSTEM columns from writes and from fix_vcol_expr().

5 files changed, 159 insertions, 12 deletions

diff --git a/mysql-test/r/invisible_field_debug.result b/mysql-test/r/invisible_field_debug.result
index 82966503cae..4c4ebffbdfc 100644
--- a/mysql-test/r/invisible_field_debug.result
+++ b/mysql-test/r/invisible_field_debug.result
@@ -1,7 +1,9 @@
 set @old_debug= @@debug_dbug;
+create table t_tmp(a int, b int);
 set debug_dbug= "+d,test_pseudo_invisible";
 create table t1(a int);
 set debug_dbug=@old_debug;
+insert into t1 values(1);
 desc t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
@@ -10,14 +12,76 @@ Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
-insert into t1 values(1);
+select a , invisible from t1;
+a	invisible
+1	9
+insert into t1(a, invisible) values(99,99);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+insert into t1(invisible) values(99);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+insert into t_tmp select a, invisible from t1;
+insert into t1 select * from t_tmp;
+ERROR 21S01: Column count doesn't match value count at row 1
+insert into t1(a,invisible) select * from t_tmp;
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+select a , invisible from t1;
+a	invisible
+1	9
+insert into t1 values (5), (invisible+1);
+select a , invisible from t1;
+a	invisible
+1	9
+5	9
+10	9
+delete from t1 where a > 1;
+update t1 set a = invisible where a=1;
+select a , invisible from t1;
+a	invisible
+9	9
+update t1 set a = (select invisible+100 from t1 limit 1) where a=(select a from t1 limit 1);
+select a , invisible from t1;
+a	invisible
+109	9
+update t1 set invisible = 23 where a=(select a from t1 limit 1);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+update t1 set invisible = 101 where a=(select a from t1 limit 1);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+update t1 set invisible = (select invisible+100 from t1 limit 1) where a=(select invisible from t1 limit 1);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+select a , invisible from t1;
+a	invisible
+109	9
+set @a=12;
+update t1 set invisible = (select @a from dual) where a=(select a from t1 limit 1);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+select a , invisible from t1;
+a	invisible
+109	9
+update t1 set invisible = (select invisible+100 from t1 limit 1) where a=(select a from t1 limit 1);
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+select a , invisible from t1;
+a	invisible
+109	9
+set @a=(select invisible from t1 limit 1);
+select @a from dual;
+@a
+9
+alter table t1 add constraint a check (invisible > 2);
+ERROR 42S22: Unknown column 'invisible' in 'CHECK'
+set debug_dbug= "+d,test_pseudo_invisible";
+create table t2(a int, b int as (invisible +2) virtual);
+ERROR 42S22: Unknown column 'invisible' in 'GENERATED ALWAYS AS'
+create table t2(a int , b int);
+insert into t2 values(2,2);
+insert into t2 select a, invisible from t1;
+set debug_dbug=@old_debug;
 select * from t1;
 a
-1
+109
 select invisible ,a from t1;
 invisible	a
-9	1
-drop table t1;
+9	109
+drop table t1,t2,t_tmp;
 set debug_dbug= "+d,test_completely_invisible";
 create table t1(a int);
 set debug_dbug=@old_debug;
@@ -107,9 +171,18 @@ a	invisible2
 select invisible ,a from t1;
 invisible	a
 9	1
+create trigger trg before insert on t1 for each row set new.invisible=1;
+ERROR 42S22: Unknown column 'invisible' in 'NEW'
+create trigger trg before insert on t1 for each row set @a:=new.invisible;
 drop table t1;
 set debug_dbug= "+d,test_completely_invisible";
 create table t1(a int);
+set debug_dbug=@old_debug;
+create trigger trg before insert on t1 for each row set new.invisible=1;
+ERROR 42S22: Unknown column 'invisible' in 'NEW'
+create trigger trg before insert on t1 for each row set @a:=new.invisible;
+ERROR 42S22: Unknown column 'invisible' in 'NEW'
+set debug_dbug= "+d,test_completely_invisible";
 desc t1;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
@@ -152,7 +225,6 @@ select invisible1, invisible ,a from t1;
 invisible1	invisible	a
 9	NULL	1
 ALTER table t1 add hid int default 2;
-set debug_dbug= "+d,test_completely_invisible";
 select * from t1;
 a	invisible	hid
 1	NULL	2
diff --git a/mysql-test/suite/versioning/r/insert.result b/mysql-test/suite/versioning/r/insert.result
index 411d4026ea9..cd6f300d907 100644
--- a/mysql-test/suite/versioning/r/insert.result
+++ b/mysql-test/suite/versioning/r/insert.result
@@ -298,7 +298,12 @@ select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_ti
 x	y	current
 2	2	1
 1	1	0
-create or replace table t1 (x int) with system versioning engine innodb;
+create or replace table t1 (
+x int,
+row_start timestamp(6) as row start invisible,
+row_end timestamp(6) as row end invisible,
+period for system_time (row_start, row_end)
+) with system versioning;
 insert into t1 values  (1), (2);
 insert into t1 (row_start) select row_end from t1;
 ERROR HY000: The value specified for generated column 'row_start' in table 't1' ignored
diff --git a/mysql-test/suite/versioning/t/insert.test b/mysql-test/suite/versioning/t/insert.test
index 7b861fc4b1c..b3c344d6f12 100644
--- a/mysql-test/suite/versioning/t/insert.test
+++ b/mysql-test/suite/versioning/t/insert.test
@@ -202,7 +202,12 @@ insert into t1 values (1, null);
 update t1 set x= x + 1;
 select x, y, sys_trx_end = 18446744073709551615 as current from t1 for system_time all;
 
-create or replace table t1 (x int) with system versioning engine innodb;
+create or replace table t1 (
+  x int,
+  row_start timestamp(6) as row start invisible,
+  row_end timestamp(6) as row end invisible,
+  period for system_time (row_start, row_end)
+) with system versioning;
 insert into t1 values  (1), (2);
 --error ER_WARNING_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
 insert into t1 (row_start) select row_end from t1;
diff --git a/mysql-test/t/invisible_field_debug.test b/mysql-test/t/invisible_field_debug.test
index 0eaef68802a..8674620e055 100644
--- a/mysql-test/t/invisible_field_debug.test
+++ b/mysql-test/t/invisible_field_debug.test
@@ -1,16 +1,67 @@
 --source include/have_debug.inc
 ##TEST for invisible coloumn level 2
 set @old_debug= @@debug_dbug;
+create table t_tmp(a int, b int);
 set debug_dbug= "+d,test_pseudo_invisible";
 create table t1(a int);
 set debug_dbug=@old_debug;
-
+insert into t1 values(1);
 desc t1;
 show create table t1;
-insert into t1 values(1);
+select a , invisible from t1;
+##field should not be resolved in fill_record
+--error ER_BAD_FIELD_ERROR
+insert into t1(a, invisible) values(99,99);
+--error ER_BAD_FIELD_ERROR
+insert into t1(invisible) values(99);
+insert into t_tmp select a, invisible from t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 select * from t_tmp;
+--error ER_BAD_FIELD_ERROR
+insert into t1(a,invisible) select * from t_tmp;
+
+select a , invisible from t1;
+insert into t1 values (5), (invisible+1);
+select a , invisible from t1;
+delete from t1 where a > 1;
+
+##Update
+update t1 set a = invisible where a=1;
+select a , invisible from t1;
+update t1 set a = (select invisible+100 from t1 limit 1) where a=(select a from t1 limit 1);
+select a , invisible from t1;
+
+--error ER_BAD_FIELD_ERROR
+update t1 set invisible = 23 where a=(select a from t1 limit 1);
+--error ER_BAD_FIELD_ERROR
+update t1 set invisible = 101 where a=(select a from t1 limit 1);
+--error ER_BAD_FIELD_ERROR
+update t1 set invisible = (select invisible+100 from t1 limit 1) where a=(select invisible from t1 limit 1);
+select a , invisible from t1;
+##if changed then error
+set @a=12;
+--error ER_BAD_FIELD_ERROR
+update t1 set invisible = (select @a from dual) where a=(select a from t1 limit 1);
+select a , invisible from t1;
+--error ER_BAD_FIELD_ERROR
+update t1 set invisible = (select invisible+100 from t1 limit 1) where a=(select a from t1 limit 1);
+select a , invisible from t1;
+
+##set
+set @a=(select invisible from t1 limit 1);
+select @a from dual;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add constraint a check (invisible > 2);
+set debug_dbug= "+d,test_pseudo_invisible";
+--error ER_BAD_FIELD_ERROR
+create table t2(a int, b int as (invisible +2) virtual);
+create table t2(a int , b int);
+insert into t2 values(2,2);
+insert into t2 select a, invisible from t1;
+set debug_dbug=@old_debug;
 select * from t1;
 select invisible ,a from t1;
-drop table t1;
+drop table t1,t2,t_tmp;
 
 ##TEST for invisible coloumn level 3
 
@@ -79,6 +130,10 @@ ALTER table t1 add invisible2 int default 2;
 select * from t1;
 select invisible ,a from t1;
 
+--error ER_BAD_FIELD_ERROR
+create trigger trg before insert on t1 for each row set new.invisible=1;
+create trigger trg before insert on t1 for each row set @a:=new.invisible;
+
 drop table t1;
 
 ##TEST for Alter table for invisibleness level 3
@@ -86,6 +141,13 @@ drop table t1;
 set debug_dbug= "+d,test_completely_invisible";
 create table t1(a int);
 
+set debug_dbug=@old_debug;
+--error ER_BAD_FIELD_ERROR
+create trigger trg before insert on t1 for each row set new.invisible=1;
+--error ER_BAD_FIELD_ERROR
+create trigger trg before insert on t1 for each row set @a:=new.invisible;
+set debug_dbug= "+d,test_completely_invisible";
+
 desc t1;
 insert into t1 values(1);
 select * from t1;
@@ -112,9 +174,7 @@ ALTER table t1 add invisible int;
 select * from t1;
 select invisible1, invisible ,a from t1;
 
-#set debug_dbug=@old_debug;
 ALTER table t1 add hid int default 2;
-set debug_dbug= "+d,test_completely_invisible";
 select * from t1;
 select invisible ,a from t1;
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 8f6e1427dbc..c46a825f9d7 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5648,6 +5648,11 @@ find_field_in_table(THD *thd, TABLE *table, const char *name, size_t length,
     if (field->invisible == INVISIBLE_FULL &&
         DBUG_EVALUATE_IF("test_completely_invisible", 0, 1))
       DBUG_RETURN((Field*)0);
+
+    if (field->invisible == INVISIBLE_SYSTEM &&
+        thd->column_usage != MARK_COLUMNS_READ &&
+        thd->column_usage != COLUMNS_READ)
+      DBUG_RETURN((Field*)0);
   }
   else
   {