SELECT * FROM mysql.server_cost;
cost_name	cost_value	last_update	comment	default_value
disk_temptable_create_cost	NULL	#	NULL	20
disk_temptable_row_cost	NULL	#	NULL	0.5
key_compare_cost	NULL	#	NULL	0.05
memory_temptable_create_cost	NULL	#	NULL	1
memory_temptable_row_cost	NULL	#	NULL	0.1
row_evaluate_cost	NULL	#	NULL	0.1
SHOW INDEX FROM mysql.server_cost;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
server_cost	0	PRIMARY	1	cost_name	A	#	NULL	NULL		BTREE			YES	NULL
SELECT * FROM mysql.engine_cost;
engine_name	device_type	cost_name	cost_value	last_update	comment	default_value
default	0	io_block_read_cost	NULL	#	NULL	1
default	0	memory_block_read_cost	NULL	#	NULL	0.25
SHOW INDEX FROM mysql.engine_cost;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
engine_cost	0	PRIMARY	1	cost_name	A	#	NULL	NULL		BTREE			YES	NULL
engine_cost	0	PRIMARY	2	engine_name	A	#	NULL	NULL		BTREE			YES	NULL
engine_cost	0	PRIMARY	3	device_type	A	#	NULL	NULL		BTREE			YES	NULL
UPDATE mysql.server_cost
SET cost_value=0.1
WHERE cost_name="row_evaluate_cost";
SELECT *
FROM mysql.server_cost
WHERE cost_name="row_evaluate_cost";
cost_name	cost_value	last_update	comment	default_value
row_evaluate_cost	0.1	#	NULL	0.1
UPDATE mysql.server_cost
SET cost_value=DEFAULT
WHERE cost_name="row_evaluate_cost";
SELECT *
FROM mysql.server_cost
WHERE cost_name="row_evaluate_cost";
cost_name	cost_value	last_update	comment	default_value
row_evaluate_cost	NULL	#	NULL	0.1
INSERT INTO mysql.server_cost
VALUES ("lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch is important", DEFAULT);
SELECT * FROM mysql.server_cost;
cost_name	cost_value	last_update	comment	default_value
disk_temptable_create_cost	NULL	#	NULL	20
disk_temptable_row_cost	NULL	#	NULL	0.5
key_compare_cost	NULL	#	NULL	0.05
lunch_cost	NULL	#	Lunch is important	NULL
memory_temptable_create_cost	NULL	#	NULL	1
memory_temptable_row_cost	NULL	#	NULL	0.1
row_evaluate_cost	NULL	#	NULL	0.1
DELETE FROM mysql.server_cost
WHERE cost_name="lunch_cost";
INSERT INTO mysql.server_cost
VALUES ("row_evaluate_cost", DEFAULT, CURRENT_TIMESTAMP, "Faster CPU", DEFAULT);
ERROR 23000: Duplicate entry 'row_evaluate_cost' for key 'PRIMARY'
INSERT INTO mysql.server_cost
VALUES ("ROW_EVALUATE_COST", DEFAULT, CURRENT_TIMESTAMP, "Faster CPU", DEFAULT);
ERROR 23000: Duplicate entry 'ROW_EVALUATE_COST' for key 'PRIMARY'
SELECT * FROM mysql.server_cost;
cost_name	cost_value	last_update	comment	default_value
disk_temptable_create_cost	NULL	#	NULL	20
disk_temptable_row_cost	NULL	#	NULL	0.5
key_compare_cost	NULL	#	NULL	0.05
memory_temptable_create_cost	NULL	#	NULL	1
memory_temptable_row_cost	NULL	#	NULL	0.1
row_evaluate_cost	NULL	#	NULL	0.1
UPDATE mysql.engine_cost
SET cost_value=0.1
WHERE cost_name="io_block_read_cost";
SELECT *
FROM mysql.engine_cost
WHERE cost_name="io_block_read_cost";
engine_name	device_type	cost_name	cost_value	last_update	comment	default_value
default	0	io_block_read_cost	0.1	#	NULL	1
UPDATE mysql.engine_cost
SET cost_value=DEFAULT
WHERE cost_name="io_block_read_cost";
SELECT *
FROM mysql.engine_cost
WHERE cost_name="io_block_read_cost";
engine_name	device_type	cost_name	cost_value	last_update	comment	default_value
default	0	io_block_read_cost	NULL	#	NULL	1
INSERT INTO mysql.engine_cost
VALUES ("InnoDB", 2, "lunch_cost1", DEFAULT, CURRENT_TIMESTAMP, "Lunch 1", DEFAULT),
("InnoDB", 2, "lunch_cost2", DEFAULT, CURRENT_TIMESTAMP, "Lunch 2", DEFAULT);
SELECT * FROM mysql.engine_cost;
engine_name	device_type	cost_name	cost_value	last_update	comment	default_value
default	0	io_block_read_cost	NULL	#	NULL	1
InnoDB	2	lunch_cost1	NULL	#	Lunch 1	NULL
InnoDB	2	lunch_cost2	NULL	#	Lunch 2	NULL
default	0	memory_block_read_cost	NULL	#	NULL	0.25
DELETE FROM mysql.engine_cost
WHERE cost_name LIKE "lunch_cost%";
INSERT INTO mysql.engine_cost
VALUES ("default", 0, "lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch", DEFAULT);
INSERT INTO mysql.engine_cost
VALUES ("default", 0, "lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch", DEFAULT);
ERROR 23000: Duplicate entry 'lunch_cost-default-0' for key 'PRIMARY'
SELECT * FROM mysql.engine_cost;
engine_name	device_type	cost_name	cost_value	last_update	comment	default_value
default	0	io_block_read_cost	NULL	#	NULL	1
default	0	lunch_cost	NULL	#	Lunch	NULL
default	0	memory_block_read_cost	NULL	#	NULL	0.25
DELETE FROM mysql.engine_cost
WHERE cost_name="lunch_cost";
INSERT INTO mysql.engine_cost
VALUES ("default", 0, "IO_BLOCK_READ_COST", DEFAULT, CURRENT_TIMESTAMP,
"Lunch", DEFAULT);
ERROR 23000: Duplicate entry 'IO_BLOCK_READ_COST-default-0' for key 'PRIMARY'
SELECT * FROM mysql.engine_cost;
engine_name	device_type	cost_name	cost_value	last_update	comment	default_value
default	0	io_block_read_cost	NULL	#	NULL	1
default	0	memory_block_read_cost	NULL	#	NULL	0.25
CREATE TABLE server_cost_tmp (
cost_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP
);
CREATE TABLE engine_cost_tmp (
cost_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP
);
INSERT INTO server_cost_tmp
SELECT cost_name, last_update FROM mysql.server_cost;
INSERT INTO engine_cost_tmp
SELECT cost_name, last_update FROM mysql.engine_cost;
UPDATE mysql.server_cost 
SET cost_value=0.1
WHERE cost_name="row_evaluate_cost";
SELECT mysql.server_cost.cost_name
FROM mysql.server_cost JOIN server_cost_tmp 
ON mysql.server_cost.cost_name = server_cost_tmp.cost_name
WHERE mysql.server_cost.last_update > server_cost_tmp.last_update;
cost_name
row_evaluate_cost
UPDATE mysql.server_cost 
SET cost_value=DEFAULT
WHERE cost_name="row_evaluate_cost";
UPDATE mysql.engine_cost 
SET cost_value=2.0
WHERE cost_name="io_block_read_cost";
SELECT mysql.engine_cost.cost_name
FROM mysql.engine_cost JOIN engine_cost_tmp 
ON mysql.engine_cost.cost_name = engine_cost_tmp.cost_name
WHERE mysql.engine_cost.last_update > engine_cost_tmp.last_update;
cost_name
io_block_read_cost
UPDATE mysql.engine_cost 
SET cost_value=DEFAULT
WHERE cost_name="io_block_read_cost";
DROP TABLE server_cost_tmp, engine_cost_tmp;

WL#10128:  Add defaults column to optimizer cost tables

Verify that default values are defined for all cost constants
SELECT COUNT(*) FROM mysql.server_cost WHERE default_value IS NULL;
COUNT(*)
0
SELECT COUNT(*) FROM mysql.engine_cost WHERE default_value IS NULL;
COUNT(*)
0
Verify that default_value columns can not be updated
UPDATE mysql.server_cost SET default_value = 1.0;
ERROR HY000: The value specified for generated column 'default_value' in table 'server_cost' is not allowed.
UPDATE mysql.engine_cost SET default_value = 1.0;
ERROR HY000: The value specified for generated column 'default_value' in table 'engine_cost' is not allowed.