The server might need to add columns to the table, for various internal purposes. These columns should be marked hidden — the user shouldn't see them. Depending on the application there can be different levels of "hiddeness":
- Not hidden — normal columns created by the user
- A little bit hidden — columns that the user has marked hiden. They aren't shown in SELECT * and they don't require values in INSERT table VALUE (...). Otherwise they behave as normal columns.
- More hidden — Can be queried explicitly, otherwise hidden from everything. Think ROWID pseudo-column. Because they're hidden from ALTER TABLE and from CREATE TABLE they cannot be created or dropped, they're created by the system.
- Very hidden — as above, but cannot be queried either. They can only show up in EXPLAIN EXTENDED (might be possible for a very hidden indexed virtual column) but otherwise they don't exist for the user.
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.