CREATE TABLE t1 ( id int);

CREATE VIEW v1 AS

	SELECT b AS a, b AS b 

	FROM (SELECT id AS b FROM t1) AS dt

	ORDER BY a,b;

WITH cte AS (SELECT dt.b FROM ((SELECT 11 AS b FROM v1) dt JOIN v1 ON 1))

SELECT 5 ;

CREATE TABLE t1 ( id int);

CREATE VIEW v1 AS

	SELECT b AS a, b AS b 

	FROM (SELECT id AS b FROM t1) AS dt

	ORDER BY a,b;

SELECT 5 FROM

(SELECT dt.b FROM ((SELECT 11 AS b FROM v1) dt JOIN v1 ON 1)) dt2;

#2  0x000055817b4add4b in handle_fatal_signal (sig=11) at /10.2/src/sql/signal_handler.cc:355

#3  <signal handler called>

#4  0x000055817b1be846 in find_field_in_tables (thd=0x7ff5a8000d90, item=0x7ff5a809b3e8, first_table=0x7ff5a8014e78, last_table=0x0, ref=0x7ff60058cd58, report_error=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /10.2/src/sql/sql_base.cc:6013

#5  0x000055817b2a4da2 in find_order_in_list (thd=0x7ff5a8000d90, ref_pointer_array=..., tables=0x7ff5a8014e78, order=0x7ff5a80a2c88, fields=..., all_fields=..., is_group_field=false, add_to_all_fields=true, from_window_spec=false) at /10.2/src/sql/sql_select.cc:22604

#6  0x000055817b2a5208 in setup_order (thd=0x7ff5a8000d90, ref_pointer_array=..., tables=0x7ff5a8014e78, fields=..., all_fields=..., order=0x7ff5a80a2c88, from_window_spec=false) at /10.2/src/sql/sql_select.cc:22724

#7  0x000055817b269eb4 in setup_without_group (thd=0x7ff5a8000d90, ref_pointer_array=..., tables=0x7ff5a8014e78, leaves=..., fields=..., all_fields=..., conds=0x7ff5a8191648, order=0x7ff5a80a2b10, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7ff5a8191527, reserved=0x7ff5a8012b8c) at /10.2/src/sql/sql_select.cc:665

#8  0x000055817b26a835 in JOIN::prepare (this=0x7ff5a8191240, tables_init=0x7ff5a8014e78, wild_num=0, conds_init=0x0, og_num=2, order_init=0x7ff5a80a2b10, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7ff5a80128c8, unit_arg=0x7ff5a8012cc0) at /10.2/src/sql/sql_select.cc:812

#9  0x000055817b319416 in st_select_lex_unit::prepare (this=0x7ff5a8012cc0, thd_arg=0x7ff5a8000d90, sel_result=0x0, additional_options=0) at /10.2/src/sql/sql_union.cc:596

#10 0x000055817b4063c0 in With_element::prepare_unreferenced (this=0x7ff5a80162d8, thd=0x7ff5a8000d90) at /10.2/src/sql/sql_cte.cc:1223

#11 0x000055817b405c01 in With_clause::prepare_unreferenced_elements (this=0x7ff5a8012828, thd=0x7ff5a8000d90) at /10.2/src/sql/sql_cte.cc:914

#12 0x000055817b26ae45 in JOIN::prepare (this=0x7ff5a8190c48, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7ff5a80050d8, unit_arg=0x7ff5a8004988) at /10.2/src/sql/sql_select.cc:903

#13 0x000055817b2751b0 in mysql_select (thd=0x7ff5a8000d90, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff5a8190c28, unit=0x7ff5a8004988, select_lex=0x7ff5a80050d8) at /10.2/src/sql/sql_select.cc:3827

#14 0x000055817b2693b6 in handle_select (thd=0x7ff5a8000d90, lex=0x7ff5a80048c8, result=0x7ff5a8190c28, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361

#15 0x000055817b233aaa in execute_sqlcom_select (thd=0x7ff5a8000d90, all_tables=0x7ff5a8014808) at /10.2/src/sql/sql_parse.cc:6271

#16 0x000055817b22a61e in mysql_execute_command (thd=0x7ff5a8000d90) at /10.2/src/sql/sql_parse.cc:3582

#17 0x000055817b237866 in mysql_parse (thd=0x7ff5a8000d90, rawbuf=0x7ff5a8012708 "WITH cte AS (SELECT dt.b FROM ((SELECT 11 AS b FROM v1) dt JOIN v1 ON 1))\nSELECT 5", length=82, parser_state=0x7ff60058e560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793

#18 0x000055817b225ac1 in dispatch_command (command=COM_QUERY, thd=0x7ff5a8000d90, packet=0x7ff5a8008b61 "WITH cte AS (SELECT dt.b FROM ((SELECT 11 AS b FROM v1) dt JOIN v1 ON 1))\nSELECT 5 ", packet_length=83, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827

#19 0x000055817b2245bc in do_command (thd=0x7ff5a8000d90) at /10.2/src/sql/sql_parse.cc:1381

#20 0x000055817b37fa41 in do_handle_one_connection (connect=0x55817f4b2e90) at /10.2/src/sql/sql_connect.cc:1336

#21 0x000055817b37f7a6 in handle_one_connection (arg=0x55817f4b2e90) at /10.2/src/sql/sql_connect.cc:1241

#22 0x000055817bbabdc8 in pfs_spawn_thread (arg=0x55817f496280) at /10.2/src/storage/perfschema/pfs.cc:1869

#23 0x00007ff605f08609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#24 0x00007ff605ae4293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

#2  0x0000557674300d4b in handle_fatal_signal (sig=11) at /10.2/src/sql/signal_handler.cc:355

#3  <signal handler called>

#4  0x0000557674011846 in find_field_in_tables (thd=0x7f1894000d90, item=0x7f189409b330, first_table=0x7f1894014e70, last_table=0x0, ref=0x7f18ec4c8be8, report_error=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /10.2/src/sql/sql_base.cc:6013

#5  0x00005576740f7da2 in find_order_in_list (thd=0x7f1894000d90, ref_pointer_array=..., tables=0x7f1894014e70, order=0x7f18941909d8, fields=..., all_fields=..., is_group_field=false, add_to_all_fields=true, from_window_spec=false) at /10.2/src/sql/sql_select.cc:22604

#6  0x00005576740f8208 in setup_order (thd=0x7f1894000d90, ref_pointer_array=..., tables=0x7f1894014e70, fields=..., all_fields=..., order=0x7f18941909d8, from_window_spec=false) at /10.2/src/sql/sql_select.cc:22724

#7  0x00005576740bceb4 in setup_without_group (thd=0x7f1894000d90, ref_pointer_array=..., tables=0x7f1894014e70, leaves=..., fields=..., all_fields=..., conds=0x7f189409bd78, order=0x7f1894190860, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7f189409bc57, reserved=0x7f1894012b7c) at /10.2/src/sql/sql_select.cc:665

#8  0x00005576740bd835 in JOIN::prepare (this=0x7f189409b970, tables_init=0x7f1894014e70, wild_num=0, conds_init=0x0, og_num=2, order_init=0x7f1894190860, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f18940128b8, unit_arg=0x7f1894012cb0) at /10.2/src/sql/sql_select.cc:812

#9  0x000055767416c416 in st_select_lex_unit::prepare (this=0x7f1894012cb0, thd_arg=0x7f1894000d90, sel_result=0x7f189409b8a0, additional_options=0) at /10.2/src/sql/sql_union.cc:596

#10 0x000055767404d47a in mysql_derived_prepare (thd=0x7f1894000d90, lex=0x7f18940048c8, derived=0x7f1894016318) at /10.2/src/sql/sql_derived.cc:764

#11 0x000055767404c245 in mysql_handle_single_derived (lex=0x7f18940048c8, derived=0x7f1894016318, phases=2) at /10.2/src/sql/sql_derived.cc:192

#12 0x0000557674196a8e in TABLE_LIST::handle_derived (this=0x7f1894016318, lex=0x7f18940048c8, phases=2) at /10.2/src/sql/table.cc:8124

#13 0x0000557674063168 in LEX::handle_list_of_derived (this=0x7f18940048c8, table_list=0x7f1894016318, phases=2) at /10.2/src/sql/sql_lex.h:3223

#14 0x000055767406db5e in st_select_lex::handle_derived (this=0x7f18940050d8, lex=0x7f18940048c8, phases=2) at /10.2/src/sql/sql_lex.cc:3927

#15 0x00005576740bd211 in JOIN::prepare (this=0x7f1894191078, tables_init=0x7f1894016318, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f18940050d8, unit_arg=0x7f1894004988) at /10.2/src/sql/sql_select.cc:725

#16 0x00005576740c81b0 in mysql_select (thd=0x7f1894000d90, tables=0x7f1894016318, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1894191058, unit=0x7f1894004988, select_lex=0x7f18940050d8) at /10.2/src/sql/sql_select.cc:3827

#17 0x00005576740bc3b6 in handle_select (thd=0x7f1894000d90, lex=0x7f18940048c8, result=0x7f1894191058, setup_tables_done_option=0) at /10.2/src/sql/sql_select.cc:361

#18 0x0000557674086aaa in execute_sqlcom_select (thd=0x7f1894000d90, all_tables=0x7f1894016318) at /10.2/src/sql/sql_parse.cc:6271

#19 0x000055767407d61e in mysql_execute_command (thd=0x7f1894000d90) at /10.2/src/sql/sql_parse.cc:3582

#20 0x000055767408a866 in mysql_parse (thd=0x7f1894000d90, rawbuf=0x7f1894012708 "SELECT 5 FROM\n(SELECT dt.b FROM ((SELECT 11 AS b FROM v1) dt JOIN v1 ON 1)) dt2", length=79, parser_state=0x7f18ec4ca560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793

#21 0x0000557674078ac1 in dispatch_command (command=COM_QUERY, thd=0x7f1894000d90, packet=0x7f1894008b61 "SELECT 5 FROM\n(SELECT dt.b FROM ((SELECT 11 AS b FROM v1) dt JOIN v1 ON 1)) dt2", packet_length=79, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827

#22 0x00005576740775bc in do_command (thd=0x7f1894000d90) at /10.2/src/sql/sql_parse.cc:1381

#23 0x00005576741d2a41 in do_handle_one_connection (connect=0x557676332e60) at /10.2/src/sql/sql_connect.cc:1336

#24 0x00005576741d27a6 in handle_one_connection (arg=0x557676332e60) at /10.2/src/sql/sql_connect.cc:1241

#25 0x00005576749fedc8 in pfs_spawn_thread (arg=0x557676316250) at /10.2/src/storage/perfschema/pfs.cc:1869

#26 0x00007f18f26a4609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#27 0x00007f18f2280293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

create table t1 (f1 INTEGER);

create view v1 as

select

    subq_0.c4 as c2,

    subq_0.c4 as c4

  from

    (select

          ref_0.f1 as c4

        from

          t1 as ref_0

        where (select 1)

        ) as subq_0

  order by c2, c4 desc;

WITH

unused_with AS (select

    subq_0.c4 as c6

  from

    (select

            11 as c4

          from

            v1 as ref_0

     ) as subq_0,

     v1 as ref_2

)

select 1 ;

drop view v1;

drop table t1;

commit d667a01d0f74780907a27d26292f066efa405b68 (HEAD -> bb-10.2-MDEV-25766, origin/bb-10.2-MDEV-25766)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Wed Sep 15 16:06:02 2021 +0200

    MDEV-25766 Unused CTE lead to a crash in find_field_in_tables/find_order_in_list

    Do not assume that subquery Item always present.

      if (!table->select_lex->master_unit()->is_union() &&

          table->select_lex->order_list.elements == 0)

        table->select_lex->order_list.push_back(&lex->select_lex.order_list);

(gdb) p table->alias

$27 = 0x7fff8c01dd10 "ref_2"

(gdb) p dbug_print_select(table->select_lex)

$28 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c6 from (select 11 AS c4 from test.v1 ref_0 order by subq_0.c4,subq_0.c4 desc) subq_0 join test.v1 ref_2"

gdb) p dbug_print_select(&lex->select_lex)

$29 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c2,subq_0.c4 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where (subquery#9)) subq_0 order by subq_0.c4,subq_0.c4 desc"

 table->select_lex->order_list.push_back(&lex->select_lex.order_list);

(gdb) p dbug_print_select(table->select_lex)

$32 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c6 from (select 11 AS c4 from test.v1 ref_0 order by subq_0.c4,subq_0.c4 desc) subq_0 join test.v1 ref_2 order by subq_0.c4,subq_0.c4 desc"

(gdb) p ((Item_field *)(*table->select_lex->order_list.first->item))->context

$35 = (Name_resolution_context *) 0x7fff840225c0

(gdb) p dbug_print_select(((Name_resolution_context *) 0x7fff840225c0)->select_lex)

$37 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c2,subq_0.c4 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where (subquery#9)) subq_0 order by subq_0.c4,subq_0.c4 desc"

(gdb) p ((Name_resolution_context *) 0x7fff840225c0)->first_name_resolution_table->alias

$38 = 0x7fff84026100 "subq_0"

(gdb) p derived->alias

$1 = 0x7fff84020680 "subq_0"

(gdb) p dbug_print_unit(unit)

$2 = 0x555557158c00 <dbug_item_print_buf> "select ref_0.f1 AS c4 from test.t1 ref_0 where (subquery#6)"

(gdb) p derived->alias

$3 = 0x7fff84014850 "ref_0"

(gdb) p dbug_print_unit(unit)

$4 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c2,subq_0.c4 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where 1) subq_0 order by subq_0.c4,subq_0.c4 desc"

(gdb) p dbug_print_select(unit->first_select()->outer_select())

$5 = 0x555557158c00 <dbug_item_print_buf> "select 11 AS c4 from test.v1 ref_0 order by subq_0.c4,subq_0.c4 desc"

(gdb) p dbug_print_select(select)

$12 = 0x555557158c00 <dbug_item_print_buf> "select ref_0.f1 AS c2,ref_0.f1 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where 1) subq_0 order by subq_0.c4,subq_0.c4 desc"

(item->type() == Item::REF_ITEM) &&

 (((Item_ref *)item)->ref_type() == Item_ref::VIEW_REF)

(gdb) p dbug_print_select(thd->lex->current_select)

$20 = 0x555557158c00 <dbug_item_print_buf> "select ref_0.f1 AS c2,ref_0.f1 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where 1) subq_0 order by ref_0.f1,ref_0.f1 desc"

(gdb) p dbug_print_select(thd->lex->current_select->outer_select())

$21 = 0x555557158c00 <dbug_item_print_buf> "select 11 AS c4 from test.v1 ref_0 order by ref_0.f1,ref_0.f1 desc"

select 11 AS c4 from test.v1 ref_0 order by ref_0.f1,ref_0.f1 desc

(gdb) p derived->alias

$37 = 0x7fff8c023f70 "ref_2"

(gdb) p dbug_print_unit(unit)

$38 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c2,subq_0.c4 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where 1) subq_0 order by subq_0.c4,subq_0.c4 desc"

(gdb) p dbug_print_select(thd->lex->current_select)

$42 = 0x555557158c00 <dbug_item_print_buf> "select ref_0.f1 AS c2,ref_0.f1 AS c4 from (select ref_0.f1 AS c4 from test.t1 ref_0 where 1) subq_0 order by ref_0.f1,ref_0.f1 desc"

(gdb) p dbug_print_select(thd->lex->current_select->outer_select())

$43 = 0x555557158c00 <dbug_item_print_buf> "select subq_0.c4 AS c6 from (select 11 AS c4 from test.v1 ref_0 order by ref_0.f1,ref_0.f1 desc) subq_0 join test.v1 ref_2 order by ref_0.f1,ref_0.f1 desc"

    if (found)

    {

      if (found == WRONG_GRANT)

	return (Field*) 0;

      /*

        Only views fields should be marked as dependent, not an underlying

        fields.

      */

      if (!table_ref->belong_to_view &&

          !table_ref->belong_to_derived)

      {

        SELECT_LEX *current_sel= item->context->select_lex;

        SELECT_LEX *last_select= table_ref->select_lex;

        bool all_merged= TRUE;

        for (SELECT_LEX *sl= current_sel; sl && sl!=last_select;

             sl=sl->outer_select())

        {

          Item *subs= sl->master_unit()->item;

          if (subs->type() == Item::SUBSELECT_ITEM && 

              ((Item_subselect*)subs)->substype() == Item_subselect::IN_SUBS &&

              ((Item_in_subselect*)subs)->test_strategy(SUBS_SEMI_JOIN))

          {

            continue;

          }

          all_merged= FALSE;

          break;

        }

(gdb) p dbug_print_select(item->context->select_lex)

$56 = 0x555557158c00 <dbug_item_print_buf> "select ref_0.f1 AS c4 from test.t1 ref_0 where 1"

(gdb) p dbug_print_select(table_ref->select_lex)

$57 = 0x555557158c00 <dbug_item_print_buf> "select 11 AS c6 from (select 11 AS c6 from test.v1 ref_0 order by ref_0.f1,ref_0.f1 desc) subq_0 join test.v1 ref_2 order by 11,ref_0.f1 desc"

create table t1 (f int);

create view v1 as select dt.c as c1 from (select t1.f as c from t1) as dt order by dt.c, dt.c;   

with cte as (select dt.c from (select t1.f as c from t1) dt, v1) select * from cte;

select * from (select dt.c from (select t1.f as c from t1) dt, v1) t;