WL#9570: Extend SHOW statements to list hidden columns and index information.

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

INFORMATION_SCHEMA tables are implemented as a views on the new
data dictionary tables in 8.0. But view definition for
COLUMNS, STATISTICS and KEY_COLUMN_USAGE do not check the hidden
column value from the dictionary tables for columns, indexes and
index elements. Hence hidden columns, indexes and index elements
are displayed by these INFORMATION_SCHEMA tables.

SHOW statements implementation uses the INFORMATION_SCHEMA tables.
So even SHOW statements displays the hidden column, index and
index elements.

The main goal of this WL are to,

  i) show columns, indexes and index element's information
     which are not hidden from the tables of INFORMATION_SCHEMA
     and SHOW statements.

 ii) Extend SHOW statements to list hidden columns and indexes
     and index elements on demand.

User Documentation
==================

* https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html
* https://dev.mysql.com/doc/refman/8.0/en/show-columns.html
* https://dev.mysql.com/doc/refman/8.0/en/show-index.html
F-1: INFORMATION_SCHEMA.COLUMNS should not show the hidden columns.

      INFORMATION_SCHEMA.COLUMNS lists all the columns of the
      table. View definition of the INFORMATION_SCHEMA.COLUMNS
      should be modified to check hidden column value from data
      dictionary table "columns" and display only columns of
      the tables which are not hidden.

      For example, the following hidden columns for the
      any table to the mysql.columns,
        DB_TRX_ID,
        DB_ROLL_PTR

      These and such hidden columns should not be listed by the
      INFORMATION_SCHEMA.COLUMNS.
  
F-2: INFORMATION_SCHEMA.STATISTICS* should not display hidden
     indexes and index elements.

      INFORMATION_SCHEMA.STATISTICS* lists all the index and
      index elements defined on the table. View definition of
      INFORMATION_SCHEMA.STATISTICS* should me modified to
      check hidden column value from the data dictionary tables 
      "indexes" and "index_column_usage" and display only
      index and index elements which are not hidden.

      For example, hidden indexes on the hidden columns
      (mentioned in F-1) in the mysql.indexes.
      Such hidden indexes should not be listed by the
      INFORMATION_SCHEMA.STATISTICS*.

F-3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE should not display hidden
     indexes and index elements.

      INFORMATION_SCHEMA.KEY_COLUMN_USAGE lists all the index and
      index elements defined on the table. View definition of
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE should be modified to
      check hidden column value from the data dictionary tables 
      "indexes" and "index_column_usage" and display only
      index and index elements which are not hidden.

      For example, creates hidden indexes on the hidden
      columns (mentioned in F-1) in the mysql.indexes.
      Such hidden indexes and its elements should not be listed by
      the INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

F-4: SHOW [ FULL ] COLUMNS should not list the hidden columns.

      SHOW [ FULL ] COLUMNS lists all the columns from the
      table. SHOW COLUMNS uses INFORMATION_SCHEMA table "COLUMNS"
      to list the COLUMNS of the table. With F-1 changes, SHOW
      COLUMNS by default lists only columns which are not hidden.

F-5: SHOW INDEX should not list the hidden index and
     index elements.

      SHOW INDEX lists all the index and index elements information
      defined on the table. 
      SHOW INDEX uses INFORMATION_SCHEMA table "STATISTICS"
      to list the index and index elements defined on the table.
      With F-2 changes, SHOW INDEX by default lists only 
      index and index elements which are hidden.

F-6: SHOW EXTENDED [ FULL ] COLUMNS should also list hidden
     columns of the table.

      By default SHOW COLUMNS should display only columns
      which are not hidden.
      To get hidden columns too, EXTENDED keyword should be
      introduced to SHOW COLUMNS statement.

F-7: SHOW EXTENDED INDEX should also list hidden index and index
     elements.

      By default SHOW INDEX should display only index and
      index elements which are not hidden. To get hidden index
      and index elements too, EXTENDED keyword should be
      introduced to SHOW INDEX statement.
      
Columns, index and index elements are marked as hidden by storing
value "true" in the column "hidden" of the data dictionary tables
"columns", "indexes" and "index_column_usage". Using this, following
modifications are done to list only columns, indexes and index 
elements which are not hidden in INFORMATION_SCHEMA queries, SHOW
statements and list hidden columns, indexes and index elements 
too with SHOW EXTENDED.

Display non-hidden columns, index and index elements:
--------------------------------------------------------
Following modifications are done to display only columns,
index and index elements which are not hidden by checking the hidden
column values from the dictionary table(s).

  1) Modification to Native methods:
  ----------------------------------
    Native methods CAN_ACCESS_COLUMN() and CAN_ACCESS_TABLE() are
    modified to either skip or display rows. New parameter is
    introduced to these methods to skip rows for INFORMATION_SCHEMA
    query. Row is skipped if value of this parameter is true.

    New signature of these native methods are as below,

      1.a) CAN_ACCESS_TABLE:
	    Existing: 
	      int CAN_ACCCESS_TABLE(schema_name, table_name);

	    New:
	      int CAN_ACCCESS_TABLE(schema_name, table_name,
				    skip_table);
   
      1.b) CAN_ACCESS_COLUMN:
	    Existing:
	      int CAN_ACCCESS_COLUMN(schema_name,
				     table_name,
				     field_name);
	    New:
	      int CAN_ACCCESS_COLUMN(schema_name,
				     table_name,
				     field_name,
				     skip_column);



  2) Modification INFORMATION_SCHEMA tables:
  ------------------------------------------
    View definition of INFORMATION_SCHEMA tables are modified
    to skip hidden columns, index and index elements by using
    the new parameter introduced for CAN_ACCCESS_TABLE and
    CAN_ACCESS_COLUMN method.

    View definition of INFORMATION_SCHEMA tables is modified as
    below,

    2.a) INFORMATION_SCHEMA.COLUMNS:
    ================================
      COLUMNS view definition is modified as below to invoke
      native function CAN_ACCESS_COLUMN() with columns.hidden
      column value as below,

      CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
	 information_schema.COLUMNS AS
       ...
       WHERE INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name, tbl.name,
	       tbl.type, tbl.options) AND
	     CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name,
	       col.hidden)
       ...


    2.b) INFORMATION_SCHMEA.STATISTICS*:
    =======================================
      STATISTICS* view definition is modified as below to invoke
      native function CAN_ACCESS_TABLE() with index.hidden and
      index_column_usage.hidden column  values as below,

	CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
	  information_schema.STATISTICS_BASE
	...
	WHERE CAN_ACCESS_TABLE(sch.name, tbl.name,
		idx.hidden OR icu.hidden)
        ...


    2.c) INFORMATION_SCHEMA.KEY_COLUMN_USAGE:
    =========================================
      KEY_COLUMN_USAGE view definition is modified as below to invoke
      native function CAN_ACCESS_COLUMN() with columns.hidden,
      indexes.hidden and index_column_usage.hidden column values
      as below,

	CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
	  information_schema.KEY_COLUMN_USAGE
	...  
	WHERE CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name,
				col.hidden OR idx.hidden OR
                                icu.hidden) AND
	...
	UNION
	(SELECT cat.name ", @collate_tolower, " AS CONSTRAINT_CATALOG,
	 ...
	 WHERE CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name,
				 col.hidden OR idx.hidden OR
                                 icu.hidden)
	 ...

    2.d) INFORMATION_SCHEMA.TABLE_CONSTRAINTS:
    =========================================== 
      TABLE_CONSTRAINTS view definition is modified as below to
      invoke native function CAN_ACCESS_TABLE() with indexes.hidden
      column values as below,

      CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
	information_schema.TABLE_CONSTRAINTS
      ...
      WHERE CAN_ACCESS_TABLE(sch.name, tbl.name, idx.hidden)
      ...

  In other view definitions of INFORMATION_SCHEMA tables 
  value FALSE is passed to the new parameter.

  With this change only non-hidden columns, indexes and index
  elements are displayed by the INFORMATION_SCHEMA tables and
  by SHOW operations (as it uses the modified INFORMATION_SCHEMA
  tables).
	
Display hidden columns, index and index elements on Demand:
-----------------------------------------------------------
By default INFORMATION_SCHEMA tables and SHOW statements does not
list the hidden columns, index and index elements. But these 
information can be listed on demand from the SHOW statements.

New optional keyword EXTENDED is introduced to the SHOW COLUMNS
and INDEX to even list the hidden columns, index and index elements.

  1 SHOW COLUMNS:
  ===============
    SHOW COLUMNS with EXTENDED option displays the hidden columns
    from the table.

    New Syntax of SHOW COLUMNS:
    
      SHOW [EXTENDED] [FULL] COLUMNS {FROM | IN} tbl_name
		      [{FROM | IN} db_name]
		      [LIKE 'pattern' | WHERE expr]


  2 SHOW INDEX:
  ===============
    SHOW INDEX with EXTENDED option displays the hidden index and
    index elements of the table.

    New Syntax of SHOW INDEX:

      SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
		      {FROM | IN} tbl_name
		      [{FROM | IN} db_name]
		      [WHERE expr]

New member m_extended_show of bool type in the LEX is set to
true when EXTENDED is used with SHOW COLUMNS and SHOW INDEX.

EXTENDED keyword(LEX::m_extended_show) overrides the skip_column
and skip_table  parameter value in Native methods CAN_ACCESS_COLUMN
and CAN_ACCESS_TABLE respectively.