MDEV-10177 Invisible Columns and Invisible Index

Feature Definition:-

This feature adds invisible column functionality to server.
There is 4 level of "invisibility":

1. Not invisible (NOT_INVISIBLE) — Normal columns created by the user

2. A little bit invisible (USER_DEFINED_INVISIBLE) — columns that the
    user has marked invisible. They aren't shown in SELECT * and they
    don't require values in INSERT table VALUE (...). Otherwise
    they behave as normal columns.

3. More invisible (SYSTEM_INVISIBLE) — Can be queried explicitly,
    otherwise invisible from everything. Think ROWID sytem column.
    Because they're invisible from ALTER TABLE and from CREATE TABLE
    they cannot be created or dropped, they're created by the system.
    User cant not create a column name which is same as of
    SYSTEM_INVISIBLE.

4. Very invisible (COMPLETELY_INVISIBLE) — as above, but cannot be
    queried either. They can only show up in EXPLAIN EXTENDED (might
    be possible for a very invisible indexed virtual column) but
    otherwise they don't exist for the user.If user creates a columns
    which has same name as of COMPLETELY_INVISIBLE then
    COMPLETELY_INVISIBLE column is renamed again. So it is completely
    invisible from user.

Invisible Index(HA_INVISIBLE_KEY):-
   Creation of invisible columns require a new type of index which
   will be only visible to system. User cant see/alter/create/delete
   this index. If user creates a index which is same name as of
   invisible index then it will be renamed.

Syntax Details:-

  Only USER_DEFINED_INVISIBLE column can be created by user. This
  can be created by adding INVISIBLE suffix after column definition.

  Create table t1( a int invisible, b int);

Rules:-
  There are some rules/restrictions related to use of invisible columns
  1. All the columns in table cant be invisible.
     Create table t1(a int invisible); \\error
     Create table t1(a int invisible, b int invisble); \\error
  2. If you want invisible column to be NOT NULL then you have to supply
     Default value for the column.
     Create table t1(a int, b int not null); \\error
  3. If you create a view/create table with select * then this wont copy
     invisible fields. So newly created view/table wont have any invisible
     columns.
     Create table t2 as select * from t1;//t2 wont have t1 invisible column
     Create view v1 as select * from t1;//v1 wont have t1 invisible column
  4. Invisibility wont be forwarded to next table in any case of create
     table/view as select */(a,b,c) from table.
     Create table t2 as select a,b,c from t1; // t2 will have t1 invisible
                           // column(b), but this wont be invisible in t2
     Create view v1 as select a,b,c from t1; // v1 will have t1 invisible
                           // column(b), but this wont be invisible in v1

Implementation Details:-
  Parsing:- INVISIBLE_SYM is added into vcol_attribute(so its like unique
      suffix), It is also added into keyword_sp_not_data_type so that table
      can have column with name invisible.
  Implementation detail is given by each modified function/created function.
   (Some function are left as they were self explanatory)
   (m= Modified, n= Newly Created)

  mysql_prepare_create_table(m):- Extra checks for invisible columns are
  added. Also some DEBUG_EXECUTE_IF are also added for test cases.

  mysql_prepare_alter_table(m):- Now this will drop all the
  COMPLETELY_INVISIBLE column and HA_INVISIBLE_KEY index. Further
  Modifications are made to stop drop/change/delete of SYSTEM_INVISIBLE
  column.

  build_frm_image(m):- Now this allows incorporating field_visibility
  status into frm image. To remain compatible with old frms
  field_visibility info will be only written when any of the field is
  not NOT_INVISIBLE.

  extra2_write_additional_field_properties(n):- This will write field
  visibility info into buffer. We first write EXTRA2_FIELD_FLAGS into
  buffer/frm , then each next char will have field_visibility for each
  field.

  init_from_binary_frm_image(m):- Now if we get EXTRA2_FIELD_FLAGS,
  then we will read the next n(n= number of fields) chars and set the
  field_visibility. We also increment
  thd->status_var.feature_invisible_columns. One important thing to
  note if we find out that key contains a field whose visibility is
  > USER_DEFINED_INVISIBLE then , we declare this key as invisible
  key.

  sql_show.cc is changed accordingly to make show table, show keys
  correct.

  mysql_insert(m):- If we get to know that we are doing insert in
  this way insert into t1 values(1,1); without explicitly specifying
  columns, then we check for if we have invisible fields if yes then
  we reset the whole record, Why ? Because first we want hidden columns
  to get default/null value. Second thing auto_increment has property
  no default and no null which voilates invisible key rule 2, And
  because of this it was giving error. Reseting table->record[0]
  eliminates this issue. More info put breakpoint on handler::write_row
  and see auto_increment value.

  fill_record(m):- we continue loop if we find invisible column because
  this is already reseted/will get its value if it is default.

Test cases:- Since we can not directly add > USER_DEFINED_INVISIBLE
  column then I have debug_dbug to create it in mysql_prepare_create_table.

  Patch Credit:- Serg Golubchik

22 files changed, 1144 insertions, 36 deletions

diff --git a/include/my_base.h b/include/my_base.h
index 1e7cacd3426..71dca2e831a 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -282,7 +282,8 @@ enum ha_base_keytype {
   This flag can be calculated -- it's based on key lengths comparison.
 */
 #define HA_KEY_HAS_PART_KEY_SEG 65536
-
+/* Internal Flag Can be calcaluted */
+#define HA_INVISIBLE_KEY 2<<18
 	/* Automatic bits in key-flag */
 
 #define HA_SPACE_PACK_USED	 4	/* Test for if SPACE_PACK used */
diff --git a/mysql-test/r/features.result b/mysql-test/r/features.result
index c6d1a6b0bac..fc276e2f885 100644
--- a/mysql-test/r/features.result
+++ b/mysql-test/r/features.result
@@ -8,6 +8,7 @@ Feature_delay_key_write	0
 Feature_dynamic_columns	0
 Feature_fulltext	0
 Feature_gis	0
+Feature_invisible_columns	0
 Feature_locale	0
 Feature_subquery	0
 Feature_timezone	0
diff --git a/mysql-test/r/invisible_binlog.result b/mysql-test/r/invisible_binlog.result
new file mode 100644
index 00000000000..088bc858e28
--- /dev/null
+++ b/mysql-test/r/invisible_binlog.result
@@ -0,0 +1,65 @@
+include/master-slave.inc
+[connection master]
+connection master;
+create table t1(a int , b int invisible);
+insert into t1 values(1);
+insert into t1(a,b) values(2,2);
+select a,b from t1;
+a	b
+1	NULL
+2	2
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	INVISIBLE
+create table t2(a int , b int invisible default 5);
+insert into t2 values(1);
+insert into t2(a,b) values(2,2);
+select a,b from t2;
+a	b
+1	5
+2	2
+desc t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		5	INVISIBLE
+connection slave;
+select * from t1;
+a
+1
+2
+select a,b from t1;
+a	b
+1	NULL
+2	2
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	INVISIBLE
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) INVISIBLE DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+a
+1
+2
+select a,b from t2;
+a	b
+1	5
+2	2
+desc t2;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+b	int(11)	YES		5	INVISIBLE
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) INVISIBLE DEFAULT 5
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+connection master;
+drop table t1,t2;
+include/rpl_end.inc
diff --git a/mysql-test/r/invisible_field.result b/mysql-test/r/invisible_field.result
new file mode 100644
index 00000000000..22bc1063649
--- /dev/null
+++ b/mysql-test/r/invisible_field.result
@@ -0,0 +1,526 @@
+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
+select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
+def	test	t1	abc	1	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PRI		select,insert,update,references		NEVER	NULL
+def	test	t1	xyz	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)		INVISIBLE	select,insert,update,references		NEVER	NULL
+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 HY000: Invisible column `abc` must have a default value
+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
+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	
+1	SIMPLE	t1	ALL	b,c	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+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
+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	50
+#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;
diff --git a/mysql-test/t/invisible_binlog.test b/mysql-test/t/invisible_binlog.test
new file mode 100644
index 00000000000..654afcc39c3
--- /dev/null
+++ b/mysql-test/t/invisible_binlog.test
@@ -0,0 +1,32 @@
+--source include/master-slave.inc
+
+--connection master
+create table t1(a int , b int invisible);
+insert into t1 values(1);
+insert into t1(a,b) values(2,2);
+select a,b from t1;
+desc t1;
+
+create table t2(a int , b int invisible default 5);
+insert into t2 values(1);
+insert into t2(a,b) values(2,2);
+select a,b from t2;
+desc t2;
+
+
+--sync_slave_with_master
+select * from t1;
+select a,b from t1;
+desc t1;
+show create table t1;
+
+select * from t2;
+select a,b from t2;
+desc t2;
+show create table t2;
+
+
+--connection master
+drop table t1,t2;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/t/invisible_field.test b/mysql-test/t/invisible_field.test
new file mode 100644
index 00000000000..55f3463a310
--- /dev/null
+++ b/mysql-test/t/invisible_field.test
@@ -0,0 +1,216 @@
+FLUSH STATUS;
+create table t1(abc int primary key, xyz int invisible);
+SHOW STATUS LIKE 'Feature_invisible_columns';
+desc t1;
+show create table t1;
+select * from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='test' and TABLE_NAME='t1';
+drop table t1;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+create table t1(a1 int invisible);
+--error ER_PARSE_ERROR
+create table t1(a1 blob,invisible(a1));
+--error ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT
+create table t1(a1 int primary key invisible ,a2 int unique invisible , a3 blob,a4 int not null invisible unique);
+--error ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT
+create table t1(abc int not null invisible);
+create table t1(a int invisible, b int);
+#should automatically add null
+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;
+select a,b from t1;
+delete from t1;
+insert into t1 values(1),(2),(3),(4);
+select * from t1;
+select a from t1;
+drop table t1;
+
+--echo #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;
+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;
+select a,b,c,d,e,f from t1;
+drop table t1;
+
+--echo #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;
+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;
+select a,b,c,d,e,f from t1;
+drop table t1;
+set sql_mode='';
+
+--error ER_PARSE_ERROR
+create table sdsdsd(a int , b int, invisible(a,b));
+create table t1(a int,abc int as (a mod 3) virtual invisible);
+desc t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 values(1,default);
+insert into t1 values(1),(22),(233);
+select * from t1;
+select a,abc from t1;
+drop table t1;
+create table t1(abc int primary key invisible auto_increment, a int);
+desc t1;
+show create table t1;
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(3);
+select * from t1;
+select abc,a from t1;
+delete  from t1;
+insert into t1 values(1),(2),(3),(4),(6);
+select abc,a from t1;
+drop table t1;
+create table t1(abc int);
+--error ER_TABLE_MUST_HAVE_COLUMNS
+alter table t1 change abc ss int invisible;
+alter table t1 add column xyz int;
+alter table t1 modify column abc  int ;
+desc t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 values(22);
+alter table t1 modify column abc  int invisible;
+desc t1;
+insert into t1 values(12);
+drop table t1;
+--echo #some test on copy table structure with table data;
+--echo #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;
+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;
+select a,b,c,d,e,f from t1;
+--echo #this won't copy invisible fields and keys;
+create table t2 as select * from t1;
+desc t2;
+select * from t2;
+--error ER_BAD_FIELD_ERROR
+select a,b,c,d,e,f from t2;
+drop table t2;
+--echo #now this will copy invisible fields
+create table t2 as select a,b,c,d,e,f from t1;
+desc t2;
+select * from t2;
+select a,b,c,d,e,f from t2;
+drop table t2,t1;
+--echo #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;
+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;
+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;
+truncate t2;
+drop table t1,t2;
+--echo #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;
+select * from v;
+--echo #v does not have invisible column;
+--error ER_BAD_FIELD_ERROR
+select a,b,c,d,e,f from v;
+insert into v values(1,21,32,4);
+select * from v;
+--error ER_BAD_FIELD_ERROR
+insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6);
+drop view v;
+create view v as select a,b,c,d,e,f from t1;
+desc v;
+select * from v;
+--echo #v does  have invisible column but they aren't invisible anymore.
+select a,b,c,d,e,f from v;
+insert into v values(1,26,33,4,45,66);
+select a,b,c,d,e,f from v;
+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;
+drop view v;
+drop table t1;
+--echo #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;
+select * from t1 where b=3;
+explain select * from t1 where c=3;
+select * from t1 where c=3;
+create table t2 as select a,b,c from t1;
+desc t2;
+explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+drop table t1,t2;
+--echo #Unhide  invisible columns
+create table t1 (a int primary key, b int invisible, c int invisible unique);
+show create table t1;
+desc t1;
+alter table t1 modify column b int;
+desc t1;
+alter table t1 change column c d int;
+desc t1;
+drop table t1;
+SHOW STATUS LIKE 'Feature_invisible_columns';
+--echo #invisible is non reserved
+create table t1(a int unique , invisible int invisible, c int );
+desc t1;
+alter table t1 change column invisible hid int invisible;
+desc t1;
+drop table t1;
+##Internal temp table
+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;
+select a,b from t2 order by b;
+CREATE TABLE t3 (b int, a int invisible) SELECT * FROM t1;
+select * from t3 order by b;
+select a,b from t3 order by b;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+CREATE TABLE t4 (b int invisible) SELECT * FROM t1;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+CREATE TABLE t5 (a int invisible) SELECT b as a FROM t1;
+drop table t1,t2,t3;
+
+create table t1 (a int , b int invisible default 3, c int , d int invisible default 6);
+DELIMITER //;
+CREATE PROCEDURE
+insert_t1(a int, b int)
+MODIFIES SQL DATA
+insert into t1 values(a,b);
+//
+DELIMITER ;//
+call insert_t1(1,1);
+call insert_t1(2,2);
+select * from t1 order by a;
+select a,b,c,d from t1 order by a;
+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;
+drop table t1;
\ No newline at end of file
diff --git a/sql/field.cc b/sql/field.cc
index 7d8f8a9af20..5ad1981b752 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -1603,7 +1603,8 @@ String *Field::val_int_as_str(String *val_buffer, bool unsigned_val)
 Field::Field(uchar *ptr_arg,uint32 length_arg,uchar *null_ptr_arg,
 	     uchar null_bit_arg,
 	     utype unireg_check_arg, const LEX_CSTRING *field_name_arg)
-  :ptr(ptr_arg), null_ptr(null_ptr_arg), table(0), orig_table(0),
+  :ptr(ptr_arg), field_visibility(NOT_INVISIBLE),
+  null_ptr(null_ptr_arg), table(0), orig_table(0),
   table_name(0), field_name(*field_name_arg), option_list(0),
   option_struct(0), key_start(0), part_of_key(0),
   part_of_key_not_clustered(0), part_of_sortkey(0),
@@ -2195,6 +2196,7 @@ Field *Field::make_new_field(MEM_ROOT *root, TABLE *new_table,
   tmp->flags&= (NOT_NULL_FLAG | BLOB_FLAG | UNSIGNED_FLAG |
                 ZEROFILL_FLAG | BINARY_FLAG | ENUM_FLAG | SET_FLAG);
   tmp->reset_fields();
+  tmp->field_visibility= NOT_INVISIBLE;
   return tmp;
 }
 
@@ -10645,6 +10647,7 @@ Column_definition::Column_definition(THD *thd, Field *old_field,
   option_list= old_field->option_list;
   pack_flag= 0;
   compression_method_ptr= 0;
+  field_visibility= old_field->field_visibility;
 
   if (orig_field)
   {
@@ -10782,6 +10785,7 @@ Column_definition::redefine_stage1_common(const Column_definition *dup_field,
   flags=        dup_field->flags;
   interval=     dup_field->interval;
   vcol_info=    dup_field->vcol_info;
+  field_visibility= dup_field->field_visibility;
 }
 
 
diff --git a/sql/field.h b/sql/field.h
index 624921b628e..7338a8ff679 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -674,6 +674,8 @@ public:
   { DBUG_ASSERT(0); }
 
   uchar		*ptr;			// Position to field in record
+
+  field_visible_type field_visibility;
   /**
      Byte where the @c NULL bit is stored inside a record. If this Field is a
      @c NOT @c NULL field, this member is @c NULL.
@@ -4046,6 +4048,7 @@ public:
     max number of characters. 
   */
   ulonglong length;
+  field_visible_type field_visibility;
   /*
     The value of `length' as set by parser: is the number of characters
     for most of the types, or of bytes for BLOBs or numeric types.
@@ -4076,7 +4079,7 @@ public:
    :Type_handler_hybrid_field_type(&type_handler_null),
     compression_method_ptr(0),
     comment(null_clex_str),
-    on_update(NULL), length(0), decimals(0),
+    on_update(NULL), length(0),field_visibility(NOT_INVISIBLE), decimals(0),
     flags(0), pack_length(0), key_length(0), unireg_check(Field::NONE),
     interval(0), charset(&my_charset_bin),
     srid(0), geom_type(Field::GEOM_GEOMETRY),
@@ -4553,5 +4556,6 @@ bool check_expression(Virtual_column_info *vcol, LEX_CSTRING *name,
 #define f_no_default(x)		((x) & FIELDFLAG_NO_DEFAULT)
 #define f_bit_as_char(x)        ((x) & FIELDFLAG_TREAT_BIT_AS_CHAR)
 #define f_is_hex_escape(x)      ((x) & FIELDFLAG_HEX_ESCAPE)
+#define f_visibility(x)         (static_cast<field_visible_type> ((x) & 3))
 
 #endif /* FIELD_INCLUDED */
diff --git a/sql/lex.h b/sql/lex.h
index a0068069660..da985ad26a6 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -273,6 +273,7 @@ static SYMBOL symbols[] = {
   { "HAVING",		SYM(HAVING)},
   { "HELP",		SYM(HELP_SYM)},
   { "HIGH_PRIORITY",	SYM(HIGH_PRIORITY)},
+  { "INVISIBLE",        SYM(INVISIBLE_SYM)},
   { "HOST",		SYM(HOST_SYM)},
   { "HOSTS",		SYM(HOSTS_SYM)},
   { "HOUR",		SYM(HOUR_SYM)},
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 1f679ee6e9e..f3cb39959a7 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8490,6 +8490,7 @@ SHOW_VAR status_vars[]= {
   {"Feature_dynamic_columns",  (char*) offsetof(STATUS_VAR, feature_dynamic_columns), SHOW_LONG_STATUS},
   {"Feature_fulltext",         (char*) offsetof(STATUS_VAR, feature_fulltext), SHOW_LONG_STATUS},
   {"Feature_gis",              (char*) offsetof(STATUS_VAR, feature_gis), SHOW_LONG_STATUS},
+  {"Feature_invisible_columns",   (char*) offsetof(STATUS_VAR, feature_invisible_columns), SHOW_LONG_STATUS},
   {"Feature_locale",           (char*) offsetof(STATUS_VAR, feature_locale), SHOW_LONG_STATUS},
   {"Feature_subquery",         (char*) offsetof(STATUS_VAR, feature_subquery), SHOW_LONG_STATUS},
   {"Feature_timezone",         (char*) offsetof(STATUS_VAR, feature_timezone), SHOW_LONG_STATUS},
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 1b7696bc056..949e3bd9d17 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7810,3 +7810,5 @@ ER_INVALID_AGGREGATE_FUNCTION
         eng "Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function"
 ER_INVALID_VALUE_TO_LIMIT
         eng "Limit only accepts integer values"
+ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT
+        eng "Invisible column %`s must have a default value"
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index a08078c5cff..ef8d4afdd3c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5528,7 +5528,10 @@ find_field_in_table(THD *thd, TABLE *table, const char *name, uint length,
 
   if (field_ptr && *field_ptr)
   {
-    *cached_field_index_ptr= (uint)(field_ptr - table->field);
+    if ((*field_ptr)->field_visibility == COMPLETELY_INVISIBLE)
+      DBUG_RETURN((Field*)0);
+
+    *cached_field_index_ptr= field_ptr - table->field;
     field= *field_ptr;
   }
   else
@@ -7607,6 +7610,14 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
 
     for (; !field_iterator.end_of_fields(); field_iterator.next())
     {
+      /*
+        field() is always NULL for views (see, e.g. Field_iterator_view or
+        Field_iterator_natural_join).
+        But view fields can never be invisible.
+      */
+      if ((field= field_iterator.field()) &&
+          field->field_visibility != NOT_INVISIBLE)
+        continue;
       Item *item;
 
       if (!(item= field_iterator.create_item(thd)))
@@ -8213,7 +8224,6 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List<Item> &values,
                         ? table->next_number_field->field_index
                         : ~0U;
   DBUG_ENTER("fill_record");
-
   if (!*ptr)
   {
     /* No fields to update, quite strange!*/
@@ -8236,7 +8246,10 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List<Item> &values,
     /* Ensure that all fields are from the same table */
     DBUG_ASSERT(field->table == table);
 
-    value=v++;
+    if (field->field_visibility != NOT_INVISIBLE)
+      continue;
+    else
+      value=v++;
     if (field->field_index == autoinc_index)
       table->auto_increment_field_not_null= TRUE;
     if (field->vcol_info)
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 46dea27dc7a..4249bc6bb5b 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -304,22 +304,25 @@ public:
   LEX_CSTRING name;
   engine_option_value *option_list;
   bool generated;
+  bool invisible;
 
   Key(enum Keytype type_par, const LEX_CSTRING *name_arg,
       ha_key_alg algorithm_arg, bool generated_arg, DDL_options_st ddl_options)
     :DDL_options(ddl_options),
      type(type_par), key_create_info(default_key_create_info),
-    name(*name_arg), option_list(NULL), generated(generated_arg)
+    name(*name_arg), option_list(NULL), generated(generated_arg),
+    invisible(false)
   {
     key_create_info.algorithm= algorithm_arg;
-  } 
+  }
   Key(enum Keytype type_par, const LEX_CSTRING *name_arg,
       KEY_CREATE_INFO *key_info_arg,
       bool generated_arg, List<Key_part_spec> *cols,
       engine_option_value *create_opt, DDL_options_st ddl_options)
     :DDL_options(ddl_options),
      type(type_par), key_create_info(*key_info_arg), columns(*cols),
-    name(*name_arg), option_list(create_opt), generated(generated_arg)
+    name(*name_arg), option_list(create_opt), generated(generated_arg),
+    invisible(false)
   {}
   Key(const Key &rhs, MEM_ROOT *mem_root);
   virtual ~Key() {}
@@ -791,6 +794,7 @@ typedef struct system_status_var
   ulong feature_dynamic_columns;    /* +1 when creating a dynamic column */
   ulong feature_fulltext;	    /* +1 when MATCH is used */
   ulong feature_gis;                /* +1 opening a table with GIS features */
+  ulong feature_invisible_columns;     /* +1 opening a table with invisible column */
   ulong feature_locale;		    /* +1 when LOCALE is set */
   ulong feature_subquery;	    /* +1 when subqueries are used */
   ulong feature_timezone;	    /* +1 when XPATH is used */
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 51c6c80fd66..0cf121fdd59 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -222,7 +222,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
                table_list->view_db.str, table_list->view_name.str);
       DBUG_RETURN(-1);
     }
-    if (values.elements != table->s->fields)
+    if (values.elements != table->s->visible_fields)
     {
       my_error(ER_WRONG_VALUE_COUNT_ON_ROW, MYF(0), 1L);
       DBUG_RETURN(-1);
@@ -980,7 +980,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
           No field list, all fields are set explicitly:
           INSERT INTO t1 VALUES (values)
         */
-        if (thd->lex->used_tables)		      // Column used in values()
+        if (thd->lex->used_tables || // Column used in values()
+                table->s->visible_fields != table->s->fields)
 	  restore_record(table,s->default_values);	// Get empty record
         else
         {
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 8ac472bd626..d0a0293bf1b 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2162,13 +2162,18 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
   */
   old_map= tmp_use_all_columns(table, table->read_set);
 
+  bool not_the_first_field= false;
   for (ptr=table->field ; (field= *ptr); ptr++)
   {
+
     uint flags = field->flags;
 
-    if (ptr != table->field)
+    if (field->field_visibility > USER_DEFINED_INVISIBLE)
+       continue;
+    if (not_the_first_field)
       packet->append(STRING_WITH_LEN(",\n"));
 
+    not_the_first_field= true;
     packet->append(STRING_WITH_LEN("  "));
     append_identifier(thd,packet,field->field_name.str,
                       field->field_name.length);
@@ -2221,6 +2226,10 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
         packet->append(STRING_WITH_LEN(" NULL"));
       }
 
+      if (field->field_visibility == USER_DEFINED_INVISIBLE)
+      {
+        packet->append(STRING_WITH_LEN(" INVISIBLE"));
+      }
       def_value.set(def_value_buf, sizeof(def_value_buf), system_charset_info);
       if (get_field_default_value(thd, field, &def_value, 1))
       {
@@ -2262,6 +2271,8 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
 
   for (uint i=0 ; i < share->keys ; i++,key_info++)
   {
+    if (key_info->flags & HA_INVISIBLE_KEY)
+      continue;
     KEY_PART_INFO *key_part= key_info->key_part;
     bool found_primary=0;
     packet->append(STRING_WITH_LEN(",\n  "));
@@ -5711,6 +5722,8 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
 
   for (; (field= *ptr) ; ptr++)
   {
+    if(field->field_visibility > USER_DEFINED_INVISIBLE)
+      continue;
     uchar *pos;
     char tmp[MAX_FIELD_WIDTH];
     String type(tmp,sizeof(tmp), system_charset_info);
@@ -5768,11 +5781,11 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
     table->field[16]->store((const char*) pos,
                             strlen((const char*) pos), cs);
 
+    StringBuffer<256> buf;
     if (field->unireg_check == Field::NEXT_NUMBER)
-      table->field[17]->store(STRING_WITH_LEN("auto_increment"), cs);
+        buf.set(STRING_WITH_LEN("auto_increment"),cs);
     if (print_on_update_clause(field, &type, true))
-      table->field[17]->store(type.ptr(), type.length(), cs);
-
+        buf.set(type.ptr(), type.length(),cs);
     if (field->vcol_info)
     {
       String gen_s(tmp,sizeof(tmp), system_charset_info);
@@ -5783,13 +5796,20 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
       table->field[20]->store(STRING_WITH_LEN("ALWAYS"), cs);
 
       if (field->vcol_info->stored_in_db)
-        table->field[17]->store(STRING_WITH_LEN("STORED GENERATED"), cs);
+        buf.set(STRING_WITH_LEN("STORED GENERATED"), cs);
       else
-        table->field[17]->store(STRING_WITH_LEN("VIRTUAL GENERATED"), cs);
+        buf.set(STRING_WITH_LEN("VIRTUAL GENERATED"), cs);
     }
     else
       table->field[20]->store(STRING_WITH_LEN("NEVER"), cs);
-
+    /*Invisible can coexist with auto_increment and virtual */
+    if (field->field_visibility == USER_DEFINED_INVISIBLE)
+    {
+      if (buf.length())
+        buf.append(STRING_WITH_LEN(", "));
+      buf.append(STRING_WITH_LEN("INVISIBLE"),cs);
+    }
+    table->field[17]->store(buf.ptr(), buf.length(), cs);
     table->field[19]->store(field->comment.str, field->comment.length, cs);
     if (schema_table_store_record(thd, table))
       DBUG_RETURN(1);
@@ -6369,6 +6389,8 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
     }
     for (uint i=0 ; i < show_table->s->keys ; i++,key_info++)
     {
+      if ((key_info->flags & HA_INVISIBLE_KEY))
+        continue;
       KEY_PART_INFO *key_part= key_info->key_part;
       LEX_CSTRING *str;
       LEX_CSTRING unknown= {STRING_WITH_LEN("?unknown field?") };
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index dbf198d722a..44774d5fcff 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -62,12 +62,16 @@
 
 const char *primary_key_name="PRIMARY";
 
-static bool check_if_keyname_exists(const char *name,KEY *start, KEY *end);
+static int check_if_keyname_exists(const char *name,KEY *start, KEY *end);
 static char *make_unique_key_name(THD *thd, const char *field_name, KEY *start,
                                   KEY *end);
 static void make_unique_constraint_name(THD *thd, LEX_CSTRING *name,
                                         List<Virtual_column_info> *vcol,
                                         uint *nr);
+static const
+char * make_unique_invisible_field_name(THD *thd, const char *field_name,
+                        List<Create_field> *fields);
+
 static int copy_data_between_tables(THD *thd, TABLE *from,TABLE *to,
                                     List<Create_field> &create, bool ignore,
 				    uint order_num, ORDER *order,
@@ -3263,8 +3267,70 @@ bool Column_definition::prepare_stage1_check_typelib_default()
   }
   return false;
 }
+/*
+   This function adds a invisible field to field_list
+   SYNOPSIS
+    mysql_add_invisible_field()
+      thd                      Thread Object
+      field_list               list of all table fields
+      field_name               name/prefix of invisible field
+                               ( Prefix in the case when it is
+                                *COMPLETELY_INVISIBLE*
+                               and given name is duplicate)
+      type_handler             field data type
+      field_visibility
+      default value
+    RETURN VALUE
+      Create_field pointer
+*/
+int mysql_add_invisible_field(THD *thd, List<Create_field> * field_list,
+        const char *field_name, Type_handler *type_handler,
+        field_visible_type field_visibility, Item* default_value)
+{
+  Create_field *fld= new(thd->mem_root)Create_field();
+  const char *new_name= NULL;
+  /* Get unique field name if field_visibility == COMPLETELY_INVISIBLE */
+  if (field_visibility == COMPLETELY_INVISIBLE)
+  {
+    if ((new_name= make_unique_invisible_field_name(thd, field_name,
+                                                     field_list)))
+    {
+      fld->field_name.str= new_name;
+      fld->field_name.length= strlen(new_name);
+    }
+    else
+      return 1;  //Should not happen
+  }
+  else
+  {
+    fld->field_name.str= thd->strmake(field_name, strlen(field_name));
+    fld->field_name.length= strlen(field_name);
+  }
+  fld->set_handler(type_handler);
+  fld->field_visibility= field_visibility;
+  if (default_value)
+  {
+    Virtual_column_info *v= new (thd->mem_root) Virtual_column_info();
+    v->expr= default_value;
+    v->utf8= 0;
+    fld->default_value= v;
+  }
+  field_list->push_front(fld, thd->mem_root);
+  return 0;
+}
 
-
+Key *
+mysql_add_invisible_index(THD *thd, List<Key> *key_list,
+        LEX_CSTRING* field_name, enum Key::Keytype type)
+{
+  Key *key= NULL;
+  key= new (thd->mem_root) Key(type, &null_clex_str, HA_KEY_ALG_UNDEF,
+         false, DDL_options(DDL_options::OPT_NONE));
+  key->columns.push_back(new(thd->mem_root) Key_part_spec(field_name, 0),
+          thd->mem_root);
+  key_list->push_back(key, thd->mem_root);
+  return key;
+}
 /*
   Preparation for table creation
 
@@ -3440,7 +3506,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
   while ((sql_field=it++))
   {
     DBUG_ASSERT(sql_field->charset != 0);
-
     if (sql_field->prepare_stage2(file, file->ha_table_flags()))
       DBUG_RETURN(TRUE);
     if (sql_field->real_field_type() == MYSQL_TYPE_VARCHAR)
@@ -3460,8 +3525,18 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
     */
     if (sql_field->stored_in_db())
       record_offset+= sql_field->pack_length;
+    if (sql_field->field_visibility == USER_DEFINED_INVISIBLE &&
+        sql_field->flags & NOT_NULL_FLAG &&
+        sql_field->flags & NO_DEFAULT_VALUE_FLAG)
+    {
+      my_error(ER_INVISIBLE_NOT_NULL_WITHOUT_DEFAULT, MYF(0),
+                          sql_field->field_name.str);
+      DBUG_RETURN(TRUE);
+    }
   }
-  /* Update virtual fields' offset*/
+  /* Update virtual fields' offset and give error if
+     All fields are invisible */
+  bool is_all_invisible= true;
   it.rewind();
   while ((sql_field=it++))
   {
@@ -3470,6 +3545,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
       sql_field->offset= record_offset;
       record_offset+= sql_field->pack_length;
     }
+    if (sql_field->field_visibility == NOT_INVISIBLE)
+      is_all_invisible= false;
+  }
+  if (is_all_invisible)
+  {
+    my_error(ER_TABLE_MUST_HAVE_COLUMNS, MYF(0));
+    DBUG_RETURN(TRUE);
   }
   if (auto_increment > 1)
   {
@@ -3720,11 +3802,21 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
                             &column->field_name,
                             &sql_field->field_name))
 	field++;
+      /*
+         Either field is not present or field visibility is >
+         USER_DEFINED_INVISIBLE
+      */
       if (!sql_field)
       {
 	my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name.str);
 	DBUG_RETURN(TRUE);
       }
+      if (sql_field->field_visibility > USER_DEFINED_INVISIBLE &&
+          !key->invisible)
+      {
+        my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name.str);
+        DBUG_RETURN(TRUE);
+      }
       while ((dup_column= cols2++) != column)
       {
         if (!lex_string_cmp(system_charset_info,
@@ -5034,17 +5126,36 @@ err:
 
 /*
 ** Give the key name after the first field with an optional '_#' after
+   @returns
+    0        if keyname does not exists
+    [1..)    index + 1 of duplicate key name
 **/
 
-static bool
+static int
 check_if_keyname_exists(const char *name, KEY *start, KEY *end)
 {
-  for (KEY *key=start ; key != end ; key++)
+  uint i= 1;
+  for (KEY *key=start; key != end ; key++, i++)
     if (!my_strcasecmp(system_charset_info, name, key->name.str))
-      return 1;
+      return i;
   return 0;
 }
 
+/**
+ Returns 1 if field name exists otherwise 0
+*/
+static bool
+check_if_field_name_exists(const char *name, List<Create_field> * fields)
+{
+  Create_field *fld;
+  List_iterator<Create_field>it(*fields);
+  while ((fld = it++))
+  {
+    if (!my_strcasecmp(system_charset_info, fld->field_name.str, name))
+      return 1;
+  }
+  return 0;
+}
 
 static char *
 make_unique_key_name(THD *thd, const char *field_name,KEY *start,KEY *end)
@@ -5102,6 +5213,33 @@ static void make_unique_constraint_name(THD *thd, LEX_CSTRING *name,
   }
 }
 
+/**
+  COMPLETELY_INVISIBLE are internally created. They are completely invisible
+  to Alter command (Opposite of SYSTEM_INVISIBLE which throws an
+  error when same name column is added by Alter). So in the case of when
+  user added a same column name as of COMPLETELY_INVISIBLE , we change
+  COMPLETELY_INVISIBLE column name.
+*/
+static const
+char * make_unique_invisible_field_name(THD *thd, const char *field_name,
+                        List<Create_field> *fields)
+{
+  if (!check_if_field_name_exists(field_name, fields))
+    return field_name;
+  char buff[MAX_FIELD_NAME], *buff_end;
+  buff_end= strmake_buf(buff, field_name);
+  if (buff_end - buff < 5)
+    return NULL; // Should not happen
+
+  for (uint i=1 ; i < 10000; i++)
+  {
+    char *real_end= int10_to_str(i, buff_end, 10);
+    if (check_if_field_name_exists(buff, fields))
+      continue;
+    return (const char *)thd->strmake(buff, real_end - buff);
+  }
+  return NULL; //Should not happen
+}
 
 /****************************************************************************
 ** Alter a table definition
@@ -7530,6 +7668,8 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   bitmap_clear_all(&table->tmp_set);
   for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
   {
+    if (field->field_visibility == COMPLETELY_INVISIBLE)
+        continue;
     Alter_drop *drop;
     if (field->type() == MYSQL_TYPE_VARCHAR)
       create_info->varchar= TRUE;
@@ -7541,7 +7681,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
           !my_strcasecmp(system_charset_info,field->field_name.str, drop->name))
         break;
     }
-    if (drop)
+    if (drop && field->field_visibility < SYSTEM_INVISIBLE)
     {
       /* Reset auto_increment value if it was dropped */
       if (MTYP_TYPENR(field->unireg_check) == Field::NEXT_NUMBER &&
@@ -7566,7 +7706,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
                           &def->change))
 	break;
     }
-    if (def)
+    if (def && field->field_visibility < SYSTEM_INVISIBLE)
     {						// Field is changed
       def->field=field;
       /*
@@ -7621,12 +7761,12 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   def_it.rewind();
   while ((def=def_it++))			// Add new columns
   {
+    Create_field *find;
     if (def->change.str && ! def->field)
     {
       /*
         Check if there is modify for newly added field.
       */
-      Create_field *find;
       find_it.rewind();
       while((find=find_it++))
       {
@@ -7666,7 +7806,6 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       new_create_list.push_back(def, thd->mem_root);
     else
     {
-      Create_field *find;
       if (def->change.str)
       {
         find_it.rewind();
@@ -7753,9 +7892,11 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
     Collect all keys which isn't in drop list. Add only those
     for which some fields exists.
   */
- 
+
   for (uint i=0 ; i < table->s->keys ; i++,key_info++)
   {
+    if (key_info->flags & HA_INVISIBLE_KEY)
+      continue;
     const char *key_name= key_info->name.str;
     Alter_drop *drop;
     drop_it.rewind();
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index b2227bdbbbf..2e51cb6da11 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1129,6 +1129,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  HEX_NUM
 %token  HEX_STRING
 %token  HIGH_PRIORITY
+%token  INVISIBLE_SYM
 %token  HOST_SYM
 %token  HOSTS_SYM
 %token  HOUR_MICROSECOND_SYM
@@ -6428,6 +6429,10 @@ vcol_attribute:
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
           }
         | COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment= $2; }
+        | INVISIBLE_SYM
+          {
+              Lex->last_field->field_visibility= USER_DEFINED_INVISIBLE;
+          }
         ;
 
 parse_vcol_expr:
@@ -6812,7 +6817,7 @@ attribute:
           }
         | AUTO_INC { Lex->last_field->flags|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG; }
         | SERIAL_SYM DEFAULT VALUE_SYM
-          { 
+          {
             LEX *lex=Lex;
             lex->last_field->flags|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG | UNIQUE_KEY_FLAG;
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
@@ -15166,6 +15171,7 @@ keyword_sp_not_data_type:
         | GOTO_SYM                 {}
         | HASH_SYM                 {}
         | HARD_SYM                 {}
+        | INVISIBLE_SYM            {}
         | HOSTS_SYM                {}
         | HOUR_SYM                 {}
         | ID_SYM                   {}
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 54c27783b0a..1de303baf08 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -537,6 +537,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  HEX_NUM
 %token  HEX_STRING
 %token  HIGH_PRIORITY
+%token  INVISIBLE_SYM
 %token  HOST_SYM
 %token  HOSTS_SYM
 %token  HOUR_MICROSECOND_SYM
@@ -6125,6 +6126,10 @@ vcol_attribute:
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
           }
         | COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment= $2; }
+        | INVISIBLE_SYM
+          {
+            Lex->last_field->field_visibility= USER_DEFINED_INVISIBLE;
+          }
         ;
 
 parse_vcol_expr:
@@ -15046,6 +15051,7 @@ keyword_sp_not_data_type:
         | GLOBAL_SYM               {}
         | HASH_SYM                 {}
         | HARD_SYM                 {}
+        | INVISIBLE_SYM            {}
         | HOSTS_SYM                {}
         | HOUR_SYM                 {}
         | ID_SYM                   {}
diff --git a/sql/table.cc b/sql/table.cc
index e8343903d96..f65d7fa123d 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -43,6 +43,7 @@
 #include "rpl_filter.h"
 #include "sql_cte.h"
 #include "ha_sequence.h"
+#include "sql_show.h"
 
 /* For MySQL 5.7 virtual fields */
 #define MYSQL57_GENERATED_FIELD 128
@@ -1161,13 +1162,15 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
   uint db_create_options, keys, key_parts, n_length;
   uint com_length, null_bit_pos, mysql57_vcol_null_bit_pos, bitmap_count;
   uint i;
+  uint field_additional_property_length= 0;
   bool use_hash, mysql57_null_bits= 0;
   char *keynames, *names, *comment_pos;
   const uchar *forminfo, *extra2;
   const uchar *frm_image_end = frm_image + frm_length;
   uchar *record, *null_flags, *null_pos, *mysql57_vcol_null_pos= 0;
   const uchar *disk_buff, *strpos;
-  ulong pos, record_offset; 
+  const uchar *field_properties= NULL;
+  ulong pos, record_offset;
   ulong rec_buff_length;
   handler *handler_file= 0;
   KEY	*keyinfo;
@@ -1279,6 +1282,10 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
         }
 #endif /*HAVE_SPATIAL*/
         break;
+      case EXTRA2_FIELD_FLAGS:
+         field_properties = extra2;
+         field_additional_property_length= length;
+        break;
       default:
         /* abort frm parsing if it's an unknown but important extra2 value */
         if (type >= EXTRA2_ENGINE_IMPORTANT)
@@ -1595,8 +1602,9 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
   memcpy(record, frm_image + record_offset, share->reclength);
 
   disk_buff= frm_image + pos + FRM_FORMINFO_SIZE;
-
   share->fields= uint2korr(forminfo+258);
+  if (field_properties && field_additional_property_length != share->fields)
+    goto err;
   pos= uint2korr(forminfo+260);   /* Length of all screens */
   n_length= uint2korr(forminfo+268);
   interval_count= uint2korr(forminfo+270);
@@ -1607,6 +1615,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
   vcol_screen_length= uint2korr(forminfo+286);
   share->virtual_fields= share->default_expressions=
     share->field_check_constraints= share->default_fields= 0;
+  share->visible_fields= 0;
   share->stored_fields= share->fields;
   if (forminfo[46] != (uchar)255)
   {
@@ -1978,6 +1987,15 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
     reg_field->field_index= i;
     reg_field->comment=comment;
     reg_field->vcol_info= vcol_info;
+    if(field_properties!=NULL)
+    {
+      uint temp= *field_properties++;
+      reg_field->field_visibility= f_visibility(temp);
+    }
+    if (reg_field->field_visibility == USER_DEFINED_INVISIBLE)
+      status_var_increment(thd->status_var.feature_invisible_columns);
+    if (reg_field->field_visibility == NOT_INVISIBLE)
+      share->visible_fields++;
     if (field_type == MYSQL_TYPE_BIT && !f_bit_as_char(pack_flag))
     {
       null_bits_are_used= 1;
@@ -2229,6 +2247,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
 
         field= key_part->field= share->field[key_part->fieldnr-1];
         key_part->type= field->key_type();
+        if (field->field_visibility > USER_DEFINED_INVISIBLE)
+          keyinfo->flags |= HA_INVISIBLE_KEY;
         if (field->null_ptr)
         {
           key_part->null_offset=(uint) ((uchar*) field->null_ptr -
@@ -5107,7 +5127,6 @@ int TABLE::verify_constraints(bool ignore_failure)
   return(VIEW_CHECK_OK);
 }
 
-
 /*
   Find table in underlying tables by mask and check that only this
   table belong to given mask
diff --git a/sql/table.h b/sql/table.h
index aded1930a6b..72e70bf0312 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -335,6 +335,16 @@ enum enum_vcol_update_mode
   VCOL_UPDATE_FOR_REPLACE
 };
 
+/* Field visibility enums */
+
+enum  field_visible_type{
+	NOT_INVISIBLE= 0,
+	USER_DEFINED_INVISIBLE,
+    /* automatically added by the server. Can be queried explicitly
+      in SELECT, otherwise invisible from anything" */
+	SYSTEM_INVISIBLE,
+	COMPLETELY_INVISIBLE
+};
 
 /**
   Category of table found in the table share.
@@ -668,6 +678,7 @@ struct TABLE_SHARE
   uint blob_fields;                     /* number of blob fields */
   uint varchar_fields;                  /* number of varchar fields */
   uint default_fields;                  /* number of default fields */
+  uint visible_fields;                  /* number of visible fields */
 
   uint default_expressions;
   uint table_check_constraints, field_check_constraints;
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 1ca0233552e..a0f42f68bf0 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -87,6 +87,21 @@ static uchar *extra2_write(uchar *pos, enum extra2_frm_value_type type,
   return extra2_write(pos, type, reinterpret_cast<LEX_CSTRING *>(str));
 }
 
+static uchar *extra2_write_additional_field_properties(uchar *pos,
+                   int number_of_fields,List_iterator<Create_field> * it)
+{
+  *pos++=EXTRA2_FIELD_FLAGS;
+  /*
+   always first 2  for field visibility
+  */
+  pos= extra2_write_len(pos, number_of_fields);
+  Create_field *cf;
+  while((cf=(*it)++))
+    *pos++= cf->field_visibility;
+  it->rewind();
+  return pos;
+}
+
 /**
   Create a frm (table definition) file
 
@@ -121,6 +136,19 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
   StringBuffer<MAX_FIELD_WIDTH> vcols;
   DBUG_ENTER("build_frm_image");
 
+  List_iterator<Create_field> it(create_fields);
+  Create_field *field;
+  bool have_additional_field_properties= false;
+  while ((field=it++))
+  {
+    if (field->field_visibility != NOT_INVISIBLE)
+    {
+      have_additional_field_properties= true;
+      break;
+    }
+  }
+  it.rewind();
+
  /* If fixed row records, we need one bit to check for deleted rows */
   if (!(create_info->table_options & HA_OPTION_PACK_RECORD))
     create_info->null_bits++;
@@ -218,7 +246,9 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
 
   if (gis_extra2_len)
     extra2_size+= 1 + (gis_extra2_len > 255 ? 3 : 1) + gis_extra2_len;
-
+  if(have_additional_field_properties)
+    extra2_size+=1 + (create_fields.elements > 255 ? 3 : 1) +
+        create_fields.elements;
 
   key_buff_length= uint4korr(fileinfo+47);
 
@@ -274,7 +304,8 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
     pos+= gis_field_options_image(pos, create_fields);
   }
 #endif /*HAVE_SPATIAL*/
-
+  if (have_additional_field_properties)
+    pos=extra2_write_additional_field_properties(pos,create_fields.elements,&it);
   int4store(pos, filepos); // end of the extra2 segment
   pos+= 4;
 
diff --git a/sql/unireg.h b/sql/unireg.h
index b0cfb3841ef..36f985ee1bb 100644
--- a/sql/unireg.h
+++ b/sql/unireg.h
@@ -176,6 +176,7 @@ enum extra2_frm_value_type {
 #define EXTRA2_ENGINE_IMPORTANT 128
 
   EXTRA2_ENGINE_TABLEOPTS=128,
+  EXTRA2_FIELD_FLAGS=129
 };
 
 int rea_create_table(THD *thd, LEX_CUSTRING *frm,