Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10177

Invisible column And Invisible Index

    Details

      Description

      This feature adds invisible column functionality to server.
      There is 4 level of "invisibility":

      1. Not invisible (NOT_INVISIBLE) — Normal columns created by the user

      2. A little bit invisible (USER_DEFINED_INVISIBLE) — columns that the
      user has marked invisible. They aren't shown in SELECT * and they
      don't require values in INSERT table VALUE (...). Otherwise
      they behave as normal columns.

      3. More invisible (SYSTEM_INVISIBLE) — Can be queried explicitly,
      otherwise invisible from everything. Think ROWID sytem column.
      Because they're invisible from ALTER TABLE and from CREATE TABLE
      they cannot be created or dropped, they're created by the system.
      User cant not create a column name which is same as of
      SYSTEM_INVISIBLE.

      4. Very invisible (COMPLETELY_INVISIBLE) — as above, but cannot be
      queried either. They can only show up in EXPLAIN EXTENDED (might
      be possible for a very invisible indexed virtual column) but
      otherwise they don't exist for the user.If user creates a columns
      which has same name as of COMPLETELY_INVISIBLE then
      COMPLETELY_INVISIBLE column is renamed again. So it is completely
      invisible from user.

      Invisible Index(HA_INVISIBLE_KEY):-
      Creation of invisible columns require a new type of index which
      will be only visible to system. User cant see/alter/create/delete
      this index. If user creates a index which is same name as of
      invisible index then it will be renamed.

      Syntax Details:-

      Only USER_DEFINED_INVISIBLE column can be created by user. This
      can be created by adding INVISIBLE suffix after column definition.

      Create table t1( a int invisible, b int);

      Rules:-
      There are some rules/restrictions related to use of invisible columns
      1. All the columns in table cant be invisible.
      Create table t1(a int invisible);
      error
      Create table t1(a int invisible, b int invisble);
      error
      2. If you want invisible column to be NOT NULL then you have to supply
      Default value for the column.
      Create table t1(a int, b int not null);
      error
      3. If you create a view/create table with select * then this wont copy
      invisible fields. So newly created view/table wont have any invisible
      columns.
      Create table t2 as select * from t1;//t2 wont have t1 invisible column
      Create view v1 as select * from t1;//v1 wont have t1 invisible column
      4. Invisibility wont be forwarded to next table in any case of create
      table/view as select */(a,b,c) from table.
      Create table t2 as select a,b,c from t1; // t2 will have t1 invisible
      // column(b), but this wont be invisible in t2
      Create view v1 as select a,b,c from t1; // v1 will have t1 invisible
      // column(b), but this wont be invisible in v1

      See also Invisible Columns in Oracle and Hidden Columns in DB2.

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                serg Sergei Golubchik
              • Votes:
                3 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated: