What are "Invisible Columns" and how are they used?

Question

I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature?

Columns can be given an INVISIBLE attribute in a CREATE TABLE or ALTER TABLE statement. These columns will then not be listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement, unless INSERT explicitly mentions them by name.

Since SELECT * does not return the invisible columns, new tables or views created in this manner will have no trace of the invisible columns. If specifically referenced in the SELECT statement, the columns will be brought into the view/new table, but the INVISIBLE attribute will not.

Invisible columns can be declared as NOT NULL, but then require a DEFAULT value

Answer 1

This is of course not standard SQL, and generally goofy. Instead follow best practices,

1. Never use INVISIBLE columns.
2. Never use SELECT * in production code.
3. Always secure columns that you don't want other people to get access to with encryption, or SQL-Standard column-level permissions.

MariaDB's INVISIBLE Columns

It seems that the intention is to permit MariaDB users to continue to use SELECT * which is almost always against a DBA's best practices. So now you can use SELECT *, and then add to the table without mutating the result returned by SELECT *.

CREATE TABLE users ( id INT, username varchar(255) );

And you have an export that dumps users with SELECT *. Later if you wish to add a column, you can mark it as INVISIBLE and maintain the result

ALERT TABLE users
  ADD COLUMN password varchar(255) INVISIBLE;

Post- ADD COLUMN you're still hilariously safe without learning that no one in their right mind would use SELECT * for in that case.

Though only one type of invisibility is exposed to the user, MariaDB internally has four different types of invisibility. For more information see, MDEV-10177 Invisible columns. Invisibility also effects INSERT, find more information on the ticket

Other Databases

As an aside, Oracle calls this feature INVISIBLE, and DB2 calls it IMPLICITLY HIDDEN. In other dialects of SQL where this isn't supported you would just create a VIEW,

CREATE user_view AS SELECT id, username FROM users;

Answer 2

Here's an actual use-case that came up just days after I learned about the invisible columns feature in MariaDB 10.3:

Following a new requirement, I had added a new column in the middle of one of our big tables. (This is accessed by multiple different applications.) While this is the kind of menial task that you wouldn't imagine could result in a problem even in your worst nightmares, this is exactly what happened.

Unbeknownst to me, we had some "innovative" legacy code that was executing SELECT * against that particular table (and several others, it turns out), and then populating a set of variables based on the column names and column order that existed at the time. The end result was that the wrong values were displayed in the user interface, causing much confusion with the users.

I could have fixed the issue in minutes by making the column "invisible", but since we were still running 10.2 that wasn't an option, so we decided to go the hard route by patching and re-deploying the software. Had we not had access to the source code or for other reasons not been able to modify the code, an upgrade to 10.3 could have been our only option.

So while this feature is not covered by the SQL Standard, it can nevertheless be useful in some rare cases.