WL#6599: New Data Dictionary and I_S integration

Affects: Server-8.0   —   Status: Complete

Introduction:
-------------

The INFORMATION_SCHEMA tables on 5.7 are temporary tables that
are created and filled during execution of the query. The
meta-data is read from .FRM files, storage engines and server in
memory data structures. Reading data from .FRM files involves
preparation of TABLE_SHARE object for each and every table and
this is time consuming. 

With the introduction of data dictionary in 8.0, most of
INFORMATION_SCHEMA tables meta-data can be read from data
dictionary tables. The goal of this WL is,

1.  To re-implement the possible INFORMATION_SCHEMA tables to pick
  meta-data from data-dictionary tables and avoid creation of
  temporary tables and preparation of TABLE_SHARE object upon every
  query execution that contains INFORMATION_SCHEMA table.

2.  The implementation would also provide following modes,

  2.1 Support retrieval of dynamic table or index statistics from a
  new DD table where the statistics are already stored. The DD table
  would be updated using ANALYZE TABLE command. This allows a
  INFORMATION_SCHEMA system view to present the dynamic statistics to
  the users faster. It is understood that the statistics presented
  this way would be from the time when user ran ANALYZE TABLE and are
  not latest statistics.

  2.2 Support retrieval of dynamic table or index statistics from
  storage engine. This allows a INFORMATION_SCHEMA system view to
  present latest dynamic statistics to the users. The design would
  also involve developing customer storage engine API's that would
  help get the latest table statistics without running open_table()
  on a given table.

  2.3 Provide support for user to switch between behaviour 2.1 and
      2.2 based on a session variable.

3.  With the re-implementation of several INFORMATION_SCHEMA
  tables, there will be compatibility issues. We must try to
  support the current behaviour as much of possible. However few
  specific cases are,

  - The current behavior is that the order of row's returned from
    I_S query is fixed as of now. After this WL implementation the
    rows returned from every execution of INFORMATION_SCHEMA system
    view might depend on optimizer decisions and hence the order might
    change.

  - On 5.7, I_S tables are temporary tables and then the 'CREATE
    TABLE LIKE' and 'HANDLER COLUMNS OPEN' command would work on I_S
    tables.  On 8.0, few I_S tables are system views and these two
    command expect the input to be a BASE table and not a view. This
    brings in a limitation.

  - The --ignore-db-dir option was mostly added to avoid SQL server
    to read physical directories like lost+found. Now that 8.0 server
    do not depend of file-system folder to find databases, this option
    is removed.

  - Upon execution of I_S system views, the resulting column names
    are show in capital. This seems to be result of how views are
    merged.

  There could be other compatibility issues, that should be
  investigated by the WL.

4.  Discuss possible solution and outline a plan for implementation.