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

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

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.

StackExchange Database Administrators Q#212227, answer score: 4