MDEV-35616 adds basic optimizer support for indexes on virtual columns.

In the area of optimizer support for virtual columns, the next steps are as follows (items are independent of one another, listed in the order close to decreasing importance):

1. Handle datatype mismatch better

The rewrite of vcol_expr CMP const into vcol_column CMP const is done if the two conditions are equivalent. Current check is very close to "datatypes are identical" with some exceptions. Examine the criteria and see if they can be relaxed.

2. Support ORDER BY/GROUP BY

We need to rewrite ORDER BY vcol_expr into ORDER BY vcol_field so that the optimizer can use an index to skip sorting.
(MySQL does this, this should be easy to do)

3. Handle Duplicate virtual columns

If somebody defines multiple virtual columns like

create table t1 (

  a int,

  b int as (a+1),

  c int as (a+1),

  index(b),

  index(c)

);

and then uses

select * from t1 where a+1=2;

we will do only the first substitution, a+1=2 -> b=2. Index on c will not be used. (It doesn't matter in this case but one can construct asymmetrical cases where it would be a problem).

The solution seems to be to detect identical expressions and just use one field.

4. Make it work with "Sargable Function" optimizations

The optimizer is already able to handle several cases with func(index_field) CMP const :

• MDEV-31496: UCASE: https://github.com/mariaDB/server/commit/e987b9350cb83038c73a165922986441f345a3a9
• MDEV-8320: YEAR: https://github.com/mariaDB/server/commit/f0b665f880bb6a6864660818d926fde9db18fa3e
• MDEV-34911: LEFT and SUBSTR: (look into the MDEV for commit)

UCASE is handled by doing a rewrite:

MDEV-31496: Make optimizer handle UCASE(varchar_col)=...

...

If the properties of the used collation allow, do the following

equivalent rewrites:

1. UPPER(key_col)=expr  ->  key_col=expr

   expr=UPPER(key_col)  ->  expr=key_col

   (also rewrite both sides of the equality at the same time)

2. UPPER(key_col) IN (constant-list)  -> key_col IN (constant-list)

- Mark utf8mb{3,4}_general_ci as collations that allow this.

So is YEAR:

MDEV-8320 Allow index usage for DATE(col) <=> const and YEAR <=> const

Rewrite datetime comparison conditions into sargeable. For example,

    YEAR(col) <= val  ->  col <= YEAR_END(val)

    YEAR(col) <  val  ->  col <  YEAR_START(val)

    YEAR(col) >= val  ->  col >= YEAR_START(val)

    YEAR(col) >  val  ->  col >  YEAR_END(val)

    YEAR(col) =  val  ->  col BETWEEN YEAR_START(val) AND YEAR_END(val)

Do the same with DATE(col), for example:

    DATE(col) <= val  ->  col <= DAY_END(val)

What if there is a virtual column defined as one of sargable functions above:

alter table t1 

  add   vcol  type_def AS UPPER(key_col),

  add index(vcol);

select * from t1 where UPPER(vcol) = 'foo'; 

Currently, the rewrite will disable Vcol substitution.

With MDEV-34911 it's different. It handles LEFT() by making the range optimizer process the expression. That is, no rewrites are performed.
However, Virtual Column Substitution will disable the MDEV-34911.

5. Produce equality lookups when vcol_expr arguments are bound

Consider this

alter table t1 add vcol1 int as (col1+1),  add index idx1(vcol1);

select * from t1 WHERE col1=1;

Here, it is possible to construct index lookup on idx1: vcol1=col1+1=2.

6. Handle join conditions

Currently the rewrite is limited to conditions that are handled by the range optimizer.
It is possible to handle join conditions also, like vcol_expr = other_table.column

(Note: MySQL doesn't do that. This is doable but is this useful?)

7. Enable Index Condition Pushdown

it is not enabled in InnoDB for some reason.