Currently System Invisible behaviour is inconsistent. We can not create index on them , but we can create virtual column based on them and also check constraint based on SYSTEM_INVISIBLE. This should not be allowed.
There is one more issue , We can also insert data into SYSTEM_INVISIBLE column if we use
insert like "Insert into t1(a, invisible) values(1,1)" , (invisible is sytem_invisible column). Which is wrong
Currently System Invisible behaviour is inconsistent. We can not create index on them , but we can create virtual column based on them and also check constraint based on SYSTEM_INVISIBLE. This should not be allowed.
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
Elena Stepanova
added a comment - 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
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.
Sachin Setiya (Inactive)
added a comment - - edited 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.
Currently System Invisible behaviour is inconsistent. We can not create index on them , but we can create virtual column based on them and also check constraint based on SYSTEM_INVISIBLE. This should not be allowed.
Currently System Invisible behaviour is inconsistent. We can not create index on them , but we can create virtual column based on them and also check constraint based on SYSTEM_INVISIBLE. This should not be allowed.
There is one more issue , We can also insert data into SYSTEM_INVISIBLE column if we use
insert like <<code>>Insert into t1(a, invisible) values(1,1)<</code>> , (invisible is sytem_invisible column). Which is wrong
Currently System Invisible behaviour is inconsistent. We can not create index on them , but we can create virtual column based on them and also check constraint based on SYSTEM_INVISIBLE. This should not be allowed.
There is one more issue , We can also insert data into SYSTEM_INVISIBLE column if we use
insert like <<code>>Insert into t1(a, invisible) values(1,1)<</code>> , (invisible is sytem_invisible column). Which is wrong
Currently System Invisible behaviour is inconsistent. We can not create index on them , but we can create virtual column based on them and also check constraint based on SYSTEM_INVISIBLE. This should not be allowed.
There is one more issue , We can also insert data into SYSTEM_INVISIBLE column if we use
insert like "Insert into t1(a, invisible) values(1,1)" , (invisible is sytem_invisible column). Which is wrong
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.
Elena Stepanova
added a comment -
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.
Elena Stepanova
added a comment - 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.
Bug
Major
FWIW, we've had this discussion before.