WL#10905: Support for INVISIBLE columns
Affects: Server-8.0
—
Status: Complete
The aim of the WL is to implement INVISIBLE COLUMNS feature.
The user defined columns are always visible in the MySQL. But if user
wants to hide some column(s) then the INVISIBLE COLUMNS feature is
useful. A SQL statement uses invisible column by explicitly referencing
it.
For example:
------------
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
INSERT INTO t1 VALUES (1), (2);
SELECT * FROM t1;
+------+
| f2 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
SELECT f1, f2 FROM t1;
+------+------+
| f1 | f2 |
+------+------+
| NULL | 1 |
| NULL | 2 |
+------+------+
2 rows in set (0.00 sec)
Use case:
-----------
a) Application database has a table with two columns. For a new
version of application new columns are added to the table. But to
make old application work as before without any changes, new columns
are marked as INVISIBLE columns. New version of application uses
columns by explicitly referring them in a query.
Invisible columns are useful during application migrations.
If table is created without primary key then WL13784 automatically
generates on the INVISIBLE column. WL13784 is dependent on this feature.
F-1: A user must be allowed to create INVISIBLE columns.
CREATE TABLE / ALTER TABLE statement must support column
visibility attribute in the column definition.
CREATE TABLE t1 (f1 INT,
f2 INT VISIBLE,
f3 INT INVISIBLE);
ALTER TABLE t1 ADD COLUMN f4 INT,
ADD COLUMN f5 INT VISIBLE,
ADD COLUMN f6 INT INVISIBLE;
F-2: A user must be allowed to set visibility attribute to generated
columns.
CREATE TABLE t1 (f1 INT,
f2 INT AS (f1 * 10) INVISIBLE);
F-3: CHANGE clause of ALTER TABLE statement must support column
visibility attribute.
ALTER TABLE t1 CHANGE f1 f1 INT INVISIBLE;
Existing column's visibility attribute can be altered with this
support.
F-4: MODIFY clause of ALTER TABLE statement must support column
visibility attribute.
ALTER TABLE t1 MODIFY f1 INT INVISIBLE;
Existing column's visibility attribute can be altered with this
support.
F-5: ALTER clause of ALTER TABLE statement must support column
visibility attribute.
ALTER clause supports column rename, set and drop default
value operations. This clause must be extended to set
column visibility attribute.
ALTER TABLE t1 ALTER COLUMN f1 SET VISIBLE;
ALTER TABLE t1 ALTER COLUMN f1 SET INVISIBLE;
Existing column's visibility attribute can be altered with this
support.
F-6: A user must be allowed to change column visibility of columns.
A user must have ALTER privilege on table to change column
visibility.
F-7: Table privileges must be applicable to invisible columns too.
Table privileges are applicable to all columns in a given
table. Table privileges must be applicable to even
invisible columns.
F-8: A table must have at least one visible column.
Error must be reported if table definition doesn't have at least
one visible column.
F-9: If invisible column is not explictly referenced in the column
list of a SELECT statement then it must not be listed.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
INSERT INTO t1 VALUES (1), (2);
SELECT * FROM t1;
+------+
| f2 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
F-10: SELECT must list INVISIBLE columns only when they are referenced
explicitly.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
INSERT INTO t1 VALUES (1), (2);
SELECT * FROM t1;
+------+
| f2 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
SELECT f1, f2 FROM t1;
+------+------+
| f1 | f2 |
+------+------+
| NULL | 1 |
| NULL | 2 |
+------+------+
2 rows in set (0.00 sec)
Invisible column f1 is listed when referenced explictly .
F-11: INSERT without column list must insert values to only visible
columns.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
INSERT INTO t1 VALUES (1), (2);
SELECT f1, f2 FROM t1;
+------+------+
| f1 | f2 |
+------+------+
| NULL | 1 |
| NULL | 2 |
+------+------+
2 rows in set (0.00 sec)
F-12: To insert value to invisible column, invisible column must
be included in the column list.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
INSERT INTO t1(f1, f2) VALUES (10, 3);
SELECT f1, f2 FROM t1;
+------+------+
| f1 | f2 |
+------+------+
| 10 | 3 |
+------+------+
F-13: A user must be allowed to set column visibility attribute to
AUTO_INCREMENT column.
CREATE TABLE t1 (f1 INT PRIMARY KEY AUTO_INCREMENT INVISIBLE,
f2 INT);
F-14: A user must be allowed to set NOT NULL attribute to INVISIBLE
column.
CREATE TABLE t1 (f1 INT NOT NULL INVISIBLE, f2 INT);
F-15: A user must be allowed to create primary key on an INVISIBLE
column.
CREATE TABLE t1 (f1 INT INVISIBLE PRIMARY KEY, f2 INT);
F-16: A user must be allowed to create a unique key on an INVISIBLE
column.
CREATE TABLE t1 (f1 INT INVISIBLE UNIQUE, f2 INT);
F-17: A user must be allowed to create a secondary key on an INVISIBLE
column.
CREATE TABLE t1 (f1 INT PRIMARY KEY,
f2 INT INVISIBLE KEY,
f3 INT);
F-18: A user must be allowed to reference an INVISIBLE column in Foreign
key constraint.
CREATE TABLE t1 (f1 INT INVISIBLE PRIMARY KEY,
f2 INT);
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT,
FOREIGN KEY (f1) REFERENCES t1(f1));
F-19: A user must be allowed to create a foreign key constraint on an
INVISIBLE column.
CREATE TABLE t1 (f1 INT PRIMARY KEY,
f2 INT);
CREATE TABLE t2 (f1 INT INVISIBLE PRIMARY KEY, f2 INT,
FOREIGN KEY (f1) REFERENCES t1(f1));
F-20: A user must be allowed to define Check constraint on INVISIBLE
column.
CREATE TABLE t1 (f1 INT INVISIBLE CHECK (f1 < 10),
f2 INT,
CHECK ((f1 + f2) < 100));
F-21: INSERT/UPDATE/REPLACE/LOAD/ALTER operations should fail on
violation of constraints defined on INVISIBLE column.
F-22: REPLACE statement must replace row if value for key defined on
INVISIBLE columns is same.
F-23: INSERT ... ON DUPLICATE KEY statement must update row if
value for key defined on INVISIBLE column is same.
F-24: A user must be allowed to update INVISIBLE column.
UPDATE t1 SET f1 = f1 + 10;
F-25: A user must be allowed to create view over an INVISIBLE column(s).
F-26: LOAD DATA INFILE without column list must store values for only
visible columns.
F-27: LOAD DATA INFILE with INVISIBLE column in column list must
store value for INVISIBLE columns.
F-28: LOAD XML INFILE without column list must store values for only
visible columns.
F-29: LOAD XML INFILE with INVISIBLE column in column list must
store value for INVISIBLE columns.
F-30: SHOW CREATE TABLE must list INVISIBLE columns.
F-31: mysqldump must include INVISIBLE columns definitions (SHOW CREATE
TABLE o/p) and their values in the dump output.
F-32: mysqlpump must include INVISIBLE columns definitions (SHOW CREATE
TABLE o/p) and column values in the dump output.
F-33: SHOW COLUMNS from table must list INVISIBLE columns. EXTRA
column must contain INVISIBLE column information.
F-34: INFORMATION_SCHEMA.COLUMNS must list INVISIBLE columns.
INFORMATION_SCHEMA.COLUMN.EXTRA column must contain visibility
information for INVISIBLE columns.
F-35: INVISIBLE attribute for column should be written to binary log
when creating table with INVISIBLE column.
This is helpful when applying DML SQL statements (with /
without explicit reference to INVISIBLE columns).
F-36: When creating binlog row events, invisible columns should not
be treated differently from any other columns. Invisible columns
shall be included in the event if needed according to
binlog_row_image.
F-37: Invisible columns should not be treated differently from visible
columns when applying row events: in particular, when choosing
the algorithm and index to use according to
slave_rows_search_algorithms.
F-38: Invisible columns should not be treated differently from visible
columns when computing writesets. In particular, indexes defined
on invisible columns should be included in the writeset.
F-39: mysqlbinlog must print column visibility in the metadata.
NF-1: This feature would affect performance.
Feature adds extra check to skip invisible column for
statements,
* SELECT * FROM tbl_name...
* INSERT without column list
* INSERT INTO TABLE tbl_name SELECT * FROM...
* REPLACE without column list
* REPLACE INTO TABLE tbl_name SELECT * FROM...
* LOAD DATA without column list
* LOAD XML without column list
Extra check would impact performance but it should be
still with in acceptable range.
NF-2: Data-dictionary table is modified to store the INVISIBLE
column information. In-place upgrade should automatically
upgrade data dictionary tables.
NF-3: INFORMATION_SCHEMA.COLUMNS system view is modified to list
the visibility attribute for invisible columns. In-place upgrade
should automatically upgrade COLUMNS system view definition.
MySQL has options to hide system columns. System columns are added
by MySQL or Storage Engines to the table for internal use. The columns
DB_ROW_ID, DB_TRX_ID and DB_ROLL_PTR created by the InnoDB storage
engine are hidden from the user. Columns created for functional indexes
by MySQL are hidden from the user. But MySQL did not provide options
to hide user defined columns. WL implements this. INVISIBLE columns
is a non-standard SQL feature.
After this WL a table can have,
a) Visible columns - Columns visible to user.
b) Hidden columns - Columns which are not visible to user.
b.1) Invisible columns - Columns hidden by user using
INVISIBLE keyword.
These columns are accessible on request.
b.2) System columns - Columns hidden by the system.
These columns are not accessible to
user.
There are two types:
b.2.i) Columns hidden by SQL layer.
b.2.ii) Columns hidden by SE. These
columns are not even seen by
MySQL.
this feature is supported for all the SEs.
This feature is supported by the ORACLE, MS SQL, DB2 and MARIADB
DBMS'es. WL follows ORACLE DBMS's INVISIBLE column feature and
makes feature suitable for the MySQL.
A) INVISIBLE columns feature in ORACLE DBMS:
---------------------------------------------
(Following is from Oracle Documentation)
Use this clause to specify whether column is VISIBLE or INVISIBLE. The
default is VISIBLE.
INVISIBLE columns are user-specified hidden columns. To display or
assign a value to an INVISIBLE column, you must specify its name
explicitly. For example:
*) The SELECT * syntax will not display an INVISIBLE column. However,
if you include an INVISIBLE column in the select list of a SELECT
statement, then the column will be displayed.
*) You cannot implicitly specify a value for an INVISIBLE column in
the VALUES clause of an INSERT statement. You must specify the
INVISIBLE column in the column list.
*) You must explicitly specify an INVISIBLE column in Oracle Call
Interface (OCI) describes.
The following notes apply to VISIBLE and INVISIBLE columns:
*) An INVISIBLE column can be used as a partitioning key when
specified as part of CREATE TABLE.
*) You can specify INVISIBLE columns in a column_expression.
*) A virtual column can be an INVISIBLE column.
The following restrictions apply to VISIBLE and INVISIBLE columns:
*) INVISIBLE columns are not supported in external tables, cluster tables,
or temporary tables.
*) You cannot make a system-generated hidden column visible.
A.1 Creating INVISIBLE columns in Oracle:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE tab1 (
id NUMBER,
description VARCHAR2(50) INVISIBLE
);
Oracle uses column attribute values { VISIBLE | INVISIBLE } to set
column attribute. Using similar column attribute in MySQL too for invisible
columns.
A.2 Altering column visibility attribute:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER TABLE tab1 MODIFY description VISIBLE;
ALTER TABLE tab1 MODIFY id INVISIBLE;
A.3 Listing invisible columns using DESCRIBE command:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> DESC tab1
Name Null? Type
----------------------------------------------------- -------- ------------
ID NUMBER
DESCRIPTION (INVISIBLE) VARCHAR2(50)
INVISIBLE column is listed with column visibility attribute by the DESCRIBE
command.
A.4 Accessing invisible columns:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
INVISIBLE columns is not listed by the 'SELECT *' and natural join.
CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE);
CREATE TABLE t2 (f3 INT, f2 INT INVISIBLE);
-- Invisible column must be listed in column list to insert values.
INSERT INTO t1 VALUES(10);
SELECT f1, f2 FROM t1;
f1 f2
---------- ----------
10 null
INSERT INTO t1(f1, f2) VALUES(1, 1);
SELECT f1, f2 FROM t1;
f1 f2
---------- ----------
10 null
1 1
TRUNCATE TABLE t1;
INSERT INTO t1(f1, f2) VALUES (1,1);
INSERT INTO t1(f1, f2) VALUES (2,2);
INSERT INTO t2(f3, f2) VALUES (3,1);
INSERT INTO t2(f3, f2) VALUES (4,2);
-- Invisible column is not listed.
SELECT * FROM t1;
f1
----------
1
2
-- Invisible column is not listed.
SELECT * FROM t1 WHERE f2 > 1;
f1
----------
2
-- Invisible column is listed on referring explicitly.
SELECT f1, f2 FROM t1;
f1 f2
---------- ----------
1 1
2 2
-- Invisible column is listed on referring explicitly.
SELECT f1, f2 FROM t1 WHERE f2 > 1;
f1 f2
---------- ----------
2 2
-- Invisible columns are not listed in table JOIN.
SELECT * FROM t1 NATURAL JOIN t2;
f1 f2
---------- ----------
1 3
1 4
2 3
2 4
-- USING clause in JOIN with invisible column uses invisible clause as
common column.
SELECT * FROM t1 LEFT JOIN t2 USING (f2);
f2 f1 f3
---------- ---------- ----------
1 1 3
2 2 4
-- Table join using search condition does not list invisible columns.
SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2;
f1 f3
---------- ----------
1 3
2 4
A.5 Views over Invisible columns:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 'SELECT *' in view query does not expand invisible columns.
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM v1;
f1
----------
1
2
-- To create view over invisible column, it must be included in the column
-- list of view query.
CREATE VIEW v2 AS SELECT f1, f2 FROM t1;
SELECT * FROM v2;
f1 f2
---------- ----------
1 1
2 2
-- Altering base table column visibility does not change existing view's
-- definition (attribute is not propagated to existing view).
ALTER TABLE t1 MODIFY f2 VISIBLE;
SELECT * FROM t1;
f1 f2
---------- ----------
1 1
2 2
SELECT * FROM v1;
f1
----------
1
2
A.6 NOT NULL constraint for Invisible columns:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE NOT NULL);
-- Error is reported for inserting null value to invisible column.
INSERT INTO t1 VALUES(1);
ORA-01400: cannot insert NULL into ("DB"."T1"."F2")
A.7 PRIMARY KEY constraint for Invisible columns:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE PRIMARY KEY);
INSERT INTO t1(f1, f2) VALUES(1, 1);
INSERT INTO t1(f1, f2) VALUES(1, 1);
ORA-00001: unique constraint () violated.
A.8 Foreign Key constraint referencing Invisible columns:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE PRIMARY KEY);
INSERT INTO t1(f1, f2) VALUES(1, 1);
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT, FOREIGN KEY (f1) REFERENCES
t1(f2));
INSERT INTO t2 VALUES(2, 1);
ORA-02291: integrity constraint () violated - parent key not found
A.9 CHECK constraint on invisible columns:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE CHECK(f2 > 10));
INSERT INTO t1(f1, f2) VALUES(1, 1);
ORA-02290: check constraint () violated.
B) Syntax to create INVISIBLE columns:
---------------------------------------
A column visibility attribute is added to the column definition of
CREATE TABLE and ALTER TABLE statements.
Syntax:
column_definition: column_name data_type [NOT NULL | NULL ]
[DEFAULT default_value] ...............
[VISIBLE | INVISIBLE]
^^^^^^^^^^^^^^^^^^^^^^^
Example:
CREATE TABLE t1 (f1 INT VISIBLE, f2 INT INVISIBLE);
ALTER TABLE t1 ADD COLUMN f3 INT VISIBLE,
ADD COLUMN f4 INT INVISIBLE;
Columns are VISIBLE by default. The user is not allowed to declare all
columns as INVISIBLE, a table must have at least one VISIBLE column.
C) Accessing INVISIBLE columns:
--------------------------------
If of INSERT, REPLACE or LOAD statements is empty
then while preparing a column list invisible columns are *not* included.
A default, generated or NULL value is stored for the invisible column
in this case. Invisible column should be explicitly referenced in the
of INSERT, REPLACE and LOAD statement to insert
a value.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
-- Value 1 is stored in column f2 and NULL in INVISIBLE column.
INSERT INTO t1 VALUES (1);
-- Value 3 is stored in INVISIBLE column and 4 in f2.
INSERT INTO t1(f1, f2) VALUES (3, 4);
-- Value 5 is stored in column f2 and NULL in INVISIBLE column.
REPLACE INTO t1 VALUES (5);
-- Value 6 is stored in INVISIBLE column and 7 in f2.
REPLACE INTO t1(f1, f2) VALUES (6, 7);
-- Value is stored in both the columns.
LOAD DATA INFILE ... INTO TABLE t1 (f1, f2);
Note: A user is allowed to define NOT NULL, UNIQUE, PRIMARY KEY,
FOREIGN KEY and CHECK constraints on the INVISIBLE columns.
Error is reported on violation of constraint.
C.1 Expanding wild card in SELECT statement:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Invisible columns are not included in column list while expanding
wild card "*" in the SELECT query or natural join.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT);
INSERT INTO t1 VALUES (1) (2);
SELECT * FROM t1;
+------+
| f2 |
+------+
| 1 |
| 2 |
+------+
Invisible column f1 is not included. Only column f2 is included
while expanding wildcard.
SELECT t.f2 FROM (SELECT * FROM t1) as t;
+------+
| t.f2 |
+------+
| 1 |
| 2 |
+------+
SELECT t.f1 FROM (SELECT * FROM t1) as t;
ERROR 1054 (42S22): Unknown column 't.f1' in 'field list'
Invisible column f1 is not included. Only column f2 is included
while expanding wildcard in subquery.
Invisible columns are accessible when explicitly referenced in the
query.
CREATE TABLE t1 (f1 INT INVISIBLE, f2 INT, f3 INT);
INSERT INTO t1 VALUES (1, 3), (2, 4);
SELECT f1, f2 FROM t1;
+------+------+
| f1 | f2 |
+------+------+
| NULL | 1 |
| NULL | 2 |
+------+------+
Invisible column f1 is explicitly referenced in the statement.
SELECT *, f1 FROM t1;
+------+------+------+
| f2 | f3 | f1 |
+------+------+------+
| 1 | 3 | NULL |
| 2 | 4 | NULL |
+------+------+------+
Invisible column f1 is explicitly referenced in the statement.
SELECT t.f1 FROM (SELECT f1 FROM t1) AS t;
+------+
| f1 |
+------+
| NULL |
| NULL |
+------+
Invisible column f1 is explicitly referenced in the subquery.
C.1.a) INVISIBLE columns with JOIN:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Invisible columns are not included in the column list while
expanding wild card '*' in a table JOIN operation.
mysql> CREATE TABLE t1(f1 INT, f2 INT INVISIBLE);
mysql> CREATE TABLE t2(f3 INT, f2 INT INVISIBLE);
mysql> INSERT INTO t1(f1, f2) VALUES (1, 1), (2, 2);
mysql> INSERT INTO t2(f3, f2) VALUES (3, 1), (4, 2);
mysql> SELECT * FROM t1 JOIN t2;
+------+------+
| f1 | f3 |
+------+------+
| 2 | 3 |
| 1 | 3 |
| 2 | 4 |
| 1 | 4 |
+------+------+
Invisible columns t1.f2 and t2.f2 are not listed by the 'SELECT *'.
mysql> SELECT t1.f2, t2.f3 FROM t1 JOIN t2;
+------+------+
| f2 | f3 |
+------+------+
| 2 | 3 |
| 1 | 3 |
| 2 | 4 |
| 1 | 4 |
+------+------+
Invisible column t1.f2 is listed on explicit reference.
mysql> SELECT t1.*, t2.f2 as t2_f2 FROM t1 JOIN t2;
+------+-------+
| f1 | t2_f2 |
+------+-------+
| 2 | 1 |
| 1 | 1 |
| 2 | 2 |
| 1 | 2 |
+------+-------+
Invisible column t2.f2 is listed on explicit reference.
Invisible columns are not considered as common columns for the
NATURAL join.
mysql> SELECT * FROM t1 NATURAL JOIN t2;
+------+------+
| f1 | f3 |
+------+------+
| 2 | 3 |
| 1 | 3 |
| 2 | 4 |
| 1 | 4 |
+------+------+
This produces cartesian product between the specified tables.
USING clause with the invisible columns in a join columns list then
JOIN compares invisible column(s) from the both the tables.
mysql> SELECT * FROM t1 JOIN t2 USING(f2);
+------+------+------+
| f2 | f1 | f3 |
+------+------+------+
| 1 | 1 | 3 |
| 2 | 2 | 4 |
+------+------+------+
D) Changes to CREATE TABLE:
----------------------------
MySQL already has code to handle column visibility as it is used
by the InnoDB Storage engine and Functional index feature. All user
columns are stored as VISIBLE in the current code.
D.1) CREATE TABLE LIKE behavior: Column visibility attribute is
copied from the source table column(s).
mysql> create table t1 (f1 INT, f2 INT INVISIBLE);
mysql> create table t2 like t1;
mysql> show create table t2\G
...
Create Table: CREATE TABLE `t2` (
`f1` int DEFAULT NULL,
`f2` int DEFAULT NULL /*!800XX INVISIBLE */
);
D.2) CREATE TABLE ... SELECT behavior: An invisible column can be
explicitly referenced in the SELECT of a CREATE TABLE ... SELECT
statement. Column is created as a visible column in the
target table.
mysql> create table t1 (f1 INT, f2 INT INVISIBLE);
mysql> create table t3 as select f1, f2 from t1;
mysql> show create table t3\G
...
Create Table: CREATE TABLE `t3` (
`f1` int DEFAULT NULL,
`f2` int DEFAULT NULL
);
The user can still change column visibility by setting visibility
attribute in the column list of CREATE TABLE ... SELECT
statement.
E) Changes to ALTER TABLE:
---------------------------
Apart from supporting visibility attribute in the column definition for
ALTER TABLE ... ADD COLUMN, ALTER TABLE ... MODIFY COLUMN and
ALTER TABLE ... CHANGE COLUMN statements, visibility clause is added
in the ALTER TABLE ... ALTER [COLUMN] statement.
E.1 To alter column visibility:
alter_option: {
...
ALTER [COLUMN] col_name
{ SET DEFAULT ... | DROP DEFAULT ....
SET VISIBLE | SET INVISIBLE }
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
...
}
ALTER TABLE t1 ALTER [COLUMN] column_name
{ SET VISIBLE | SET INVISIBLE };
F) Changes to Data-Dictionary tables:
--------------------------------------
The mysql.columns.field_hidden column is modified to represent column
marked as INVISIBLE by user. Data-dictionary version is bumped because
of this change.
Data-dictionary layer is also modified to represent the new hidden
type. New type is added to the dd::Columns::enum_hidden_type.
G) Changes to SHOW CREATE TABLE:
---------------------------------
SHOW CREATE TABLE is adjusted to list the user columns marked as
INVISIBLE with version specific comment.
mysql> SHOW CREATE TABLE t\G
...
Create Table: CREATE TABLE `t` (
`f1` int DEFAULT NULL,
`f2` int DEFAULT NULL /*!800XX INVISIBLE */
);
Visibility attribute is not listed for the visible columns.
Columns are treated as visible columns if show output is used to
create table in lower versions of MySQL.
H) Changes to INFORMATION_SCHEMA tables:
-----------------------------------------
"INVISIBLE" value is stored in the INFORMATION_SCHEMA.COLUMNS.EXTRA
column for invisible column.
mysql> select TABLE_NAME, COLUMN_NAME, EXTRA from INFORMATION_SCHEMA.COLUMNS;
+------------+-------------+----------------------------+
| TABLE_NAME | COLUMN_NAME | EXTRA |
+------------+-------------+----------------------------+
| t1 | f1 | |
| t1 | f2 | INVISIBLE |
| t1 | f3 | VIRTUAL GENERATED INVISIBLE|
+------------+-------------+----------------------------+
3 rows in set (0.01 sec)
INFORMATION_SCHEMA version is bumped because of this change.
I) Changes to SHOW COLUMNS:
----------------------------
The SHOW COLUMNS code is adjusted to list user invisible columns. The
invisible attribute is stored in the EXTRA column for invisible
columns.
mysql> SHOW COLUMNS FROM t1;
+-------+------+------+-----+---------+----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------------------+
| f1 | int | YES | | NULL | |
| f2 | int | YES | | NULL | INVISIBLE |
| f3 | int | YES | | NULL | VIRTUAL GENERATED INVISIBLE|
+-------+------+------+-----+---------+----------------------------+
3 rows in set (0.01 sec)
Note: INVISIBLE in EXTRA column is listed only for user columns.
Hidden columns listed in EXTENDED output will not have this value.
J) Views over invisible columns:
---------------------------------
The user is allowed to create views over INVISIBLE column(s) by explicitly
referencing column(s) in the SELECT query. Any invisible column
referenced explicitly in the view query is treated as a normal column
in the view.
mysql> CREATE TABLE t1 (f1 INT, f2 INT INVISIBLE);
mysql> INSERT INTO t1(f1, f2) VALUES (10, 20), (20, 30);
mysql> CREATE VIEW v1 AS SELECT f2 FROM t1;
mysql> SELECT * FROM v1;
+------+
| f2 |
+------+
| 20 |
| 30 |
+------+
2 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM v1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| f2 | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
K) Logical dump:
-----------------
The mysqldump and mysqlpump tools are adjusted to dump table definition
with INVISIBLE columns (i.e CREATE TABLE output) and dump INSERT
statements with column list when table has INVISIBLE column(s).
When a dump is applied on a version supporting INVISIBLE columns,
invisible columns are created and value for the invisible columns are
populated. But when a dump is applied on a version without INVISIBLE
columns support, INVISIBLE columns are treated as VISIBLE columns.
L) Upgrade:
------------
Hidden by type 'User' is added at the end in DD column mysql.columns.
hidden (of type ENUM). Upgrade from 5.7 to 8.0 and 8.0.x (version without
INVISIBLE columns support) to 8.0.y (version supporting INVISIBLE columns)
should work without any changes to metadata.
M) Replication:
----------------
DDL statements to CREATE TABLE with INVISIBLE columns are logged with
the INVISIBLE attribute. Table is created with INVISIBLE columns when
binlog statements are applied or when applying statements through
the replication applier thread(s) in the replica.
DML statements are logged (without column list or with column list when
invisible columns are included in column list to store value) to
binlog in statement based replication mode. DEFAULT, NULL or specified
value for INVISIBLE columns are stored when SQL statement is applied
by the replication applied thread(s) in the replica.
M.1) show_binlog_events.inc output with STATEMENT binlog format:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Query # # use `test`; CREATE TABLE t1 (f1 INT PRIMARY KEY
AUTO_INCREMENT INVISIBLE,
f2 INT INVISIBLE, f3 INT)
Query # # BEGIN
Intvar # # INSERT_ID=1
Query # # use `test`; INSERT INTO t1 VALUES (10), (20)
Xid # # COMMIT /* XID */
Query # # BEGIN
Query # # use `test`; INSERT INTO t1(f1, f2, f3) VALUES (3, 6, 30)
M.2) show_binlog_events.inc output with ROW binlog format:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When writing and applying row events, invisible columns are not
treated differently from the normal columns.
Query # # use `test`; CREATE TABLE t1 (f1 INT PRIMARY KEY
AUTO_INCREMENT INVISIBLE,
f2 INT INVISIBLE, f3 INT)
Query # # BEGIN
Table_map # # table_id: # (test.t1)
Write_rows # # table_id: # flags: STMT_END_F
Xid # # COMMIT /* XID */
Query # # BEGIN
Table_map # # table_id: # (test.t1)
Write_rows # # table_id: # flags: STMT_END_F
Xid # # COMMIT /* XID */
M.3) Changes to Table_map_log_event:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Table_map_log_event is extended to contain the column visibility
metadata. Column visibility metadata is written to the binlog.
mysqlbinlog tool is extended to decode the column visibility metadata
and print "INVISIBLE" clause for column marked as invisible.
M.4) Upgrade:
--------------
Replication upgrade from OLD to NEW server is unaffected.
Replication from NEW to OLD is affected as DML operations on table
with INVISIBLE column would fails on OLD server due to column list
mismatch. But replication from NEW to OLD is not supported.
N) Privileges:
---------------
No extra privilege checks are required.
Table privileges are equally applicable to invisible columns. To
grant or revoke invisible column privileges, the column must be
included in the column list of the statement.
mysql> CREATE TABLE t1 (f1 INT INVISIBLE DEFAULT 10, f2 INT);
-- Granting column level privileges to user1.
mysql> GRANT INSERT(f1), SELECT(f1, f2) ON t1 TO user1@localhost;
-- Revoking column level privileges from user1.
mysql> REVOKE INSERT(f1), SELECT(f1) ON t1 FROM user1@localhost;
If user does not have privileges to access column then error is
reported. OTOH 'SELECT *' which failed before because of insufficient
column privileges, passes if column is marked as INVISIBLE.
mysql> CREATE TABLE t1 (f1 INT, f2 INT);
-- Granting SELECT privilege on only column f1 to user2.
mysql> GRANT SELECT(f1) ON t1 TO user2@localhost;
$mysql -u user2
mysql> SELECT * FROM t1;
ERROR 1142 (42000): SELECT command denied to user 'user2'@'localhost' for
table 't1'
mysql> SELECT f1 FROM t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
$mysql -u root
mysql> ALTER TABLE t1 ALTER COLUMN f2 SET INVISIBLE;
$mysql -u user2
mysql> SELECT * FROM t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
-- Error is reported on invisible column with insufficient
-- privileges access.
mysql> SELECT f2 FROM t1;
ERROR 1143 (42000): SELECT command denied to user 'user2'@'localhost' for
column 'f2' in table 't1'
A) Changes to CREATE TABLE:
----------------------------
Currently parser creates Create_field instances as VISIBLE columns.
To support the INVISIBLE columns feature, the parser code is adjusted
to Create_field instance with visibility attributes.
B) Changes to ALTER TABLE:
---------------------------
B.1 Parser prepares list of
B.1.a) Create_field instance(s) for newly added columns with
visibility attribute.
B.1.b) Create_field instance(s) for columns Modified with MODIFY
clause.
B.1.c) Create_field instance(s) for columns Changed with
CHANGE clause.
B.1.d) Alter_column instance list for columns Altered with
ALTER column clause.
i) Alter_column::Type::SET_COLUMN_VISIBLE and
Alter_column::Type::SET_COLUMN_INVISIBLE types
are added to Alter_column::Type to represent
column visibility an alter operation.
B.2 While preparing fields for alter table.
B.2.a) The Alter_column list is traversed to alter existing
column's visibility.
B.2.b) The Create_field list is traversed to alter existing
column's visibility.
C. Changes to Data-dictionary tables:
--------------------------------------
Current definition of field_hidden column:
...
m_target_def.add_field(FIELD_HIDDEN, "FIELD_HIDDEN",
"hidden ENUM('Visible', 'SE', 'SQL') NOT NULL");
...
New definition of field_hidden column:
...
m_target_def.add_field(FIELD_HIDDEN, "FIELD_HIDDEN",
"hidden ENUM('Visible', 'SE',
'SQL_INTERNAL',
'SQL',
'User') NOT NULL");
...
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.