WL#8227: Support SEs to create index on virtual generated columns

Status: Complete   —   Priority: Medium

Server will provide info on the GC, both virtual and stored, in several
1) Info about GC's in general is stored in the Field::gcol_info.
GC's expression as string is available in Field::gcol_info::expr_str.
GC's expression as parsed and ready for execution Item tree is available at
When handler::open is called, SE can check whether all functions used in the
GCs expressions are supported by SE. If not, SE should return an error
indicating that an expression in a GC isn't supported and mentioning the GC
in question.

The item tree is configured to use TABLE::record[0] as the data source.
In order to evaluate the expression SE should call 
  table->field->expr_item->save_in_field(table->field, false)
and check return value and thd->is_error(). If either is non-zero/true then
an error occur and should be propagated up to the server. Otherwise
evaluation was successful.

2) When record data is written to SE, e.g on INSERT/REPLACE/UPDATE, server
will calculate values of all GCs in the table and save them in the record
buffer. This is intended to be the main way of getting data for GCs into the

3) For command like on-line ALTER TABLE (i.e commands that are executed
inside SE and don't have direct access to the server) server will provide a
callback that would allow to calculate values of GCs.


  @param[in] db     table's db 
  @param[in] name   table's name
  @param[in] fields table's fields needs to be calculated
  @param[in] buf    source and result buffer

    true   an error occur, values of GCs are undefined
    false GCs values successfully calculated 
my_eval_gcolumn_exprs(const char *db, const char *name, ulonglong fields,
                      const char *record);

Following limitations are applicable:
1) GCs which index is beyond 64 can't be calculated this way. Effectively
this means that on-line indexes on GCs beyond this limit aren't supported.
2) The thread calling this callback should be running one of
INSERT/REPLACE/UPDATE/ALTER/SELECT commands, error is returned otherwise.
3) Table should identify a single table that is opened already by the thread.
The callback relies on this and wouldn't check tables in e.g. the SELECT part
of INSERT ..SELECT, neither it will open another table.
4) Source buffer should be same size as TABLE::record[0] and hold
data in the MySQL record format.
5) Result of GCs calculations will be saved in the source and result buffer.
In order to obtain a value of a particular field the SE would have to call
e.g field->val_int() after calculation is done, or read from the buffer

Pseudo code for this function could look like following
my_eval_gcolumn_exprs(const char *db, const char *name, ulonglong fields,
                      const char *record)
  THD *thd= current_thd;
  TABLE *table;
  char *old_buf;
  bool res= false;

  if (non_applicable_command(thd))
    return true;    // Wrong command
  if ((table= find_table_in_thd(thd, db, name))
    return true;    // Table not found
  old_buf= repoint_fields_to_record(table, fields, record);
  for (Field *fld= *table->fields; fld; fld++)
    if (! ((fld->field_index - 1) & fields))
      continue;     // Skip unneeded fields
    if (fld->gcol_info->expr_item->save_in_field(fld, false)
        || thd->is_error())
      res= true;
  repoint_fields_to_record(table, fields, old_buf);
  return res;

Fuunction Requirements
FR1:Don't support create index on virtual GC combined with other normal columns 
and stored GC.
FR2: Don't support mixture of multiple alter....table statements.
FR3: Don't support FK on virtual GC.
FR4: Don't support to create fulltext and spatial index on virtual GCs
FR5: Don't support ICP or MRR for virtual GCs
FR6: Support inplace alter on virtual GCs.
FR7: Don't allow auto-increment column as the base column. This is because the 
value of auto-increment column is filled by storage engine. Before write into 
storage engine, the generated expression cannot be evaluated correctly if it 
depends on an auto-increment column.
After this WL, we can create index on virtual GC. We can take use of index 
optimizations except some special limitations due to limited time. About the 
constrains, pls see the belows.

There is no special syntax for CREATE/ALTER index, but there are some constraints 
on these operations.
  1) Create-index statment on virtual GC doesn't support to create an index on 
mixture of virtual GC and other kinds of columns.(Time matters)
  1) Support add/drop/rename virtual GC inplace.
  2) Don't allow multiple ALTER statements if one GC is involved(Time matters). 
  3) Don't support add virtual GC index combined with other non-virtual-GCs.

Limitation of index type on virtual GC
1. Don't support Fulltext index
2. Don't support Spatial index
3. Don't support Foreign key
4. Don't support Primary key

Constraints on Index optimization
1. Don't support ICP for virtual-GC index.
2. Don't support MRR and DS_MRR
1) Limitation on CREATE INDEX statement
  Add one flag ALTER_VIRTUAL_GCOLUMN in Alter_info structure, which is used to 
mark whether the index is related   with virtual-GC. 
  In order to block this constraint, one enum is introduced in 
  The logic is as the blows:
  List_iterator<Create_field> it(alter_info->create_list);
  while (sql_field= it++)
	if (status == ADDED_NONE)
		if (sql_field->gcol_info && !sql_field->stored_in_db)
			status= ADDED_VGC;
			status= ADDED_OTHER;
	else if (status == ADDED_VGC)
		(sql_field->gcol_info && sql_field->stored_in_db))
                   "Virtual generated column combines with other columns "
                   "to be indexed together");
        if (sql_field->gcol_info && !sql_field->stored_in_db)
                   "Virtual generated column combines with other columns "
                   "to be indexed together");

  1)) Support add/drop/rename virtual GC inplace.
  There is not too much to do here, except support drop virtual-GC inplace. 
  In order to let SE knows which virtual-GC is dropped, we use a tricky way to 
keep it in alter_info->drop_list but not clear it after it's used.
  2)) Don't allow multiple ALTER statements if one GC is involved(Time matters). 
  One function named block_multiple_alter_stmts is added. The logic is described 
as the below.
  static bool block_multiple_alter_stmts(Alter_info *alter_info)
	// At first, we need clean up some noises which are flags used for some 
other uses but not for a distinguish on statement type.
	// Check only one flag is set for Alter statement, which means only one 
kind of statements is in ALTER statement.
	if (alter_info->flags & Alter_info::ALTER_VIRTUAL_GCOLUMN)
		if (only one flag is set)
			return ok;
			report_error("Multiple alter statements including 
virtual generated column");
  3)) Don't support add virtual GC index combined with other non-virtual-GCs.
  The same as constraint on CREATE index part.

Limitation of index type on virtual GC
1. One function "block_vgcol_unsupported_key_type" is added for blocking 
Fulltext and Spatial index on virtual GCs.
2. Foreign key on virtual-GC is blocked in Foreign_key::validate function which 
is added by WL#411.
3. Primary key on virtual-GC is blocked in mysql_prepare_create_table.

Constraints on Index optimization
1. In QEP_TAB::push_index_cond function, scan the key parts of possible keys to 
see whether a virtual-GC index is used. If it's used, return directly.
2. In test_quick_select, add code to block MRR and DS_MRR same as ICP.

Support to create index on virtual GCs
1) In order to support to recovery, Innodb has to know the base columns for one 
GC so that its value can be reevaluated. So one new function 
register_base_columns is added in table.cc. Only the regular base columns(non-
virtual or non-stored GCs) are needed.

2) Callback function for on-line ALTER TABLE
Currently, the callback function is implemented as what's described in 
Server_part 3) of HLD.