[#MDEV-24298] SELECT_LEX::find_select_handler() fails to find MCS table running INSERT..SELECT with a single derived at top level in SELECT

As of now MDB doesn't use Select Handler for queries like:

INSERT INTO t1 SELECT * FROM (SELECT * FROM mcs1) d;

but Select Handler works for the SELECT part of the query.

The reason why this happens is that join->tables_list doesn't have TABLE_LIST* for a real table but for a derived one. Here is the gdb snippet taken in the first iteration of the loop in SELECT_LEX::find_select_handler():

(gdb) p tbl->table_name

$2 = {

  str = 0x55907a9f76c8 <internal_table_name> "*",

  length = 1

It doesn't matter if derived_merged is on or off.

(gdb) p thd->variables->optimizer_switch & OPTIMIZER_SWITCH_DERIVED_MERGE

$8 = 128

(gdb) p join->tables_list->table_name

$9 = {

  str = 0x55bee4acaa28 <internal_table_name> "*",

  length = 1

(gdb) p join->tables_list->next_global

$10 = (TABLE_LIST *) 0x0

(gdb)

Here is the code of the mentioned find_select_handler():

select_handler *SELECT_LEX::find_select_handler(THD *thd)

  if (next_select())

      return 0;

  if (master_unit()->outer_select())

    return 0;

  for (TABLE_LIST *tbl= join->tables_list; tbl; tbl= tbl->next_global)

    if (!tbl->table)

      continue;

    handlerton *ht= tbl->table->file->partition_ht();

    if (!ht->create_select)

      continue;

    select_handler *sh= ht->create_select(thd, this);

    return sh;

  return 0;

[MDEV-24298] SELECT_LEX::find_select_handler() fails to find MCS table running INSERT..SELECT with a single derived at top level in SELECT Created: 2020-11-27 Updated: 2022-05-06 Resolved: 2021-01-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.5, 10.6
Fix Version/s:	10.5.9

[MDEV-24298] SELECT_LEX::find_select_handler() fails to find MCS table running INSERT..SELECT with a single derived at top level in SELECT Created: 2020-11-27 Updated: 2022-05-06 Resolved: 2021-01-26