Invisible columns

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

See also Invisible Columns in Oracle and Hidden Columns in DB2.

Q: What happens in one declares a hidden column that is NOT NULL and has no default value? Oracle returns an error at INSERT time if the column value was not provided. DB2 does not allow to create such a column.
A: We'll do the latter, an error at CREATE TABLE time.
Q: The syntax. Oracle uses the INVISIBLE keyword, DB2 uses IMPLICITLY HIDDEN.
A: We'll use INVISIBLE.
Q: Visibility of hidden columns in SHOW commands, in the INFORMATION_SCHEMA tables, in ALTER, etc. Oracle hides invisible columns in the informational commands too (and has a special view that shows them). DB2 hides them too, unless one uses a special form of the DESCRIBE} statement.
A: Fully visible everywhere. They're only hidden from SELECT * and INSERT that does not specify the column list.
Q: Replication. Are hidden columns written to binlog?
A: User-hidden columns — absolutely. But others? ROWID pseudo-column should not be replicated, that's what "pseudo" mean. Hidden automatic primary key should be. On the other hand, ROWID is engine-dependent, engine knows what to do with it. So we can replicate all hidden columns just normally. The engine will just not write down ROWID, if it feels like it.
Q: Indexes on hidden columns, are they hidden? What about indexes on a mix of hidden/visible columns?
A: On user-visible columns, no and no. On more hidden — yes and impossible.