WL#10128: Add defaults column to optimizer cost tables

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

WL#7276 added two tables, server_cost and engine_cost, to the mysql schema. 
These tables can be used to tune the cost constants that are used for the
optimizer cost model.  However, there is no way, except by reading code, to find
the default value for these cost constants.

This worklog will add a generated column that will show the default values for
the constants defined in the two tables.  Since a generated column is read-only,
one avoids any confusion around what it would imply if such a column was updated.

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

* http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
* http://dev.mysql.com/doc/refman/8.0/en/cost-model.html
F-1 Table mysql.server_cost will contain a column, default_value, which will 
    define a column that will contain the default value for each cost constant.

F-2 Table mysql.engine_cost will contain a column, default_value, which will 
    define a column that will contain the default value for each cost constant.

F-3 It should not be possible to update these columns for any user (including 
    root)

F-4 For databases created with earlier versions, the columns will be added to 
    the tables upon upgrade.
Generated column
----------------
A generated column, default_value, will be added to the two tables, server_cost
and engine cost.

The column definition will be of the form:

  default_value FLOAT GENERATED ALWAYS AS
    (CASE cost_name
       WHEN <constant_name1> THEN <default_value1>
       WHEN <constant_name2> THEN <default_value2>
       ...
       ELSE NULL
     END) VIRTUAL

Such definitions will be added to the SQL script that creates the MySQL system
tables.


Upgrade
-------
If default values for cost constants are changed in the code, the column
definition for the corresponding cost tables will need to be updated.
In addition to updating the script for creating the tables, the SQL script that
is run during upgrade will have to modify the definitions of these columns.
The update script will handle both tables that does not already contain this
column, and tables that do contain it.

Comments in the code will be added to remind developers to change these scripts
if they change the default value for a cost constant.


Testing
-------
There already exists MTR tests that verifies the content of the cost tables. 
These results files for these tests will be updated to reflect the addition of
the new column.

An MTR test case will be added to verify that the default_value column is not
null for any row of the cost tables.

An MTR test case will be added to verify that the default_value column cannot be
updated since it is a generated column.

An MTR to verify the upgrade script will also be added.


Documentation
-------------

Documentation should no longer specify defaults, but refer to the contents of
the two tables.
scripts/mysql_system_tables.sql:
================================  
Table definitions for server_cost and engine_cost are updated to included to
define the default values for all current cost constants:

CREATE TABLE IF NOT EXISTS server_cost (
  cost_name   VARCHAR(64) NOT NULL,
  cost_value  FLOAT DEFAULT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  comment     VARCHAR(1024) DEFAULT NULL,
  default_value FLOAT GENERATED ALWAYS AS
    (CASE cost_name
       WHEN 'disk_temptable_create_cost' THEN 20.0
       WHEN 'disk_temptable_row_cost' THEN 0.5
       WHEN 'key_compare_cost' THEN 0.05
       WHEN 'memory_temptable_create_cost' THEN 1.0
       WHEN 'memory_temptable_row_cost' THEN 0.1
       WHEN 'row_evaluate_cost' THEN 0.1
       ELSE NULL
     END) VIRTUAL,
  PRIMARY KEY (cost_name)
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci STATS_PERSISTENT=0;

CREATE TABLE IF NOT EXISTS engine_cost (
  engine_name VARCHAR(64) NOT NULL,
  device_type INTEGER NOT NULL,
  cost_name   VARCHAR(64) NOT NULL,
  cost_value  FLOAT DEFAULT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  comment     VARCHAR(1024) DEFAULT NULL,
  default_value FLOAT GENERATED ALWAYS AS
    (CASE cost_name
       WHEN 'io_block_read_cost' THEN 1.0
       WHEN 'memory_block_read_cost' THEN 0.25
       ELSE NULL
     END) VIRTUAL,
  PRIMARY KEY (cost_name, engine_name, device_type)
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci STATS_PERSISTENT=0;

INSERT statements for these tables have been simplified by not specifying
columns for which default column values should be used.

scripts/mysql_system_tables_fix.sql:
====================================
Add SQL code that checks if default_value columns exists and adds/modifies
column definition depending on the outcome.  (Column definition is the same as
above)

mysql-test/t/opt_costmodel_tables.test:
=======================================
In addition to updating existing test cases to reflect the new columns, add a
test case that verifies that all default values are non-NULL.

mysql-test/t/opt_costmodel_upgrade.test:
========================================
Add a test that verifies that column values are as expected after upgrade.

sql/opt_costconstantcache.cc:
=============================
A fix is needed to be able to resolve generated columns when opening tables
in a separate THD.