FWIW, we've had this discussion before.

elenst
@sachin: MDEV-10177 says for system-invisible: "Can be queried explicitly, otherwise invisible from everything". However, they can still be used in virtual columns, check constraints and such (at least those created under test_pseudo_invisible can). Is it intentional?

sachin
I think yes, we can think of SYSTEM_INVISBLE as ID columns , So user might use them , I am not sure If it is right or wrong

serg
yeah, I think it's ok. a user cannot drop/modify them, but virtual columns and check constraints just reading, like selects

But I think after this serg said we need a more homogeneous behaviour.

elenst
okay then. It seemed kind of odd that you can use it in a check constraint but not in an index, but apart from "odd", i don't have any grounds for objections.

sachin
I think check constraint should not be allowed , user should not put a rule on column which is created by system (edited)

serg
I don't have a strong opinion, either way is fine, whatever is easier to do. Not allowing them anywhere is ok, allowing them in indexes is also ok.

marko
if we allow them in unique indexes or FOREIGN KEY, we should allow them in CHECK constraints too, for the sake of consistency.

a.patch

But I think after this serg said we need a more homogeneous behaviour.

Okay, thanks, good, I must have missed or forgotten that part. I am certainly for consistency – was then and still am.

sachin.setiya.007, please also consider the following in regard to system-invisible columns – maybe some changes are due here as well:

• show fields in does not show them, but show index in does
• EITS collects stats for them and stores in stat tables
• if they form a constraint together with normal columns, those normal columns can't be dropped

The above is based on the assumption that the current variant of system versioning without explicit versioning columns exhibits the typical behavior of system-invisible columns.