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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.5.9, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.2.43, 10.3.34, 10.4.24, 10.5.15, 10.6.7, 10.7.3
Component/s: Optimizer - CTE
Labels:
- crash
Environment:
Ubuntu 18.04
MariaDB 10.5.9

Description

I used my fuzzing tool to test Mariadb , and found a bug that can result in an abortion.

Mariadb installation：
1) cd mariadb-10.5.9
2) mkdir build; cd build
3) cmake -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_DEBUG=ON ../
4) make -j8 && sudo make install

How to Repeat:
export ASAN_OPTIONS=detect_leaks=0
/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot -p123456(your password)
MariaDB> drop database if exists test_db;
MariaDB> create database test_db;
MariaDB> source fuzz.sql;

I have simplified the content of fuzz.sql, and I hope fuzz.sql can help you reproduce the bug and fix it. In addition, I attach the abortion report (which has its stack trace).

--fuzz.sql

create table t_ykc (

c_l2i8lidec INTEGER ,

c_iv TEXT ,

c_mp TEXT ,

primary key(c_l2i8lidec),

unique(c_l2i8lidec),

check((t_ykc.c_l2i8lidec <= case when t_ykc.c_l2i8lidec in (

        t_ykc.c_l2i8lidec, t_ykc.c_l2i8lidec) then t_ykc.c_l2i8lidec else t_ykc.c_l2i8lidec end

  or (t_ykc.c_iv like 'cfirh%e' AND t_ykc.c_l2i8lidec < t_ykc.c_l2i8lidec))

);

create view t_wk as

select

    subq_0.c2 as c1,

    subq_0.c4 as c2,

    subq_0.c1 as c3,

    subq_0.c4 as c4,

    subq_0.c0 as c5,

    11 as c6,

    subq_0.c3 as c7,

    (subq_0.c0 / subq_0.c1) as c8,

    subq_0.c2 as c9,

    (subq_0.c0 + subq_0.c3) as c10

  from

    (select

          ref_0.c_l2i8lidec as c0,

          ref_0.c_l2i8lidec as c1,

          ref_0.c_iv as c2,

          ref_0.c_l2i8lidec as c3,

          ref_0.c_l2i8lidec as c4,

          ref_0.c_l2i8lidec as c5

        from

          t_ykc as ref_0

        where ((select min(c_l2i8lidec) from t_ykc)

               between ref_0.c_l2i8lidec and ref_0.c_l2i8lidec)

          or (ref_0.c_l2i8lidec < ref_0.c_l2i8lidec)

        order by c0, c1, c2, c3, -c4, c5 asc) as subq_0

  where subq_0.c2 not like 'rpch%4go'

  order by c1, c2, c3, c4, c5, -c6, c7, c8, c9, c10 desc;

WITH

jennifer_0 AS (select

    subq_0.c4 as c6

  from

    ((select

            11 as c4

          from

            t_wk as ref_0

     ) as subq_0

      left outer join t_wk as ref_2

      on (11 <> 19))

select

    subq_2.c2 as c4

  from

    (select distinct

          61 as c2

        from

          t_ykc as ref_13

        ) as subq_2

abortion_report.txt
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.5.9-MariaDB
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=2
max_threads=153
thread_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467864 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x62b00007e218
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f26a2226600 thread_stack 0x5fc00
/usr/local/mysql/bin/mariadbd(__interceptor_backtrace+0x5b)[0x917bfb]
:0(mi_state_info_read)[0x6ad5ad8]
sql/sql_yacc.cc:46457(MYSQLparse(THD*))[0x2beef02]
sigaction.c:0(__restore_rt)[0x7f26c1a133c0]
sql/protocol.h:0(Protocol_text)[0xe8530c]
sql/sql_lex.cc:1164(Lex_input_stream::add_digest_token(unsigned int, YYSTYPE*))[0x1567533]
sql/sql_parse.cc:8709(st_select_lex::add_cross_joined_table(TABLE_LIST, TABLE_LIST, bool))[0x17aeebc]
??:0(Lex_input_stream::body_utf8_append(char const, char const))[0x1564878]
sql/sql_lex.cc:120(sp_expr_lex::case_stmt_action_when(bool))[0x155372f]
sql_select.cc:0(change_cond_ref_to_const(THD, I_List<COND_CMP>, Item, Item, Item_bool_func2, Item, Item*))[0x1c45226]
sql_select.cc:0(trace_table_dependencies(THD, st_join_table, unsigned int))[0x1c05a9e]
??:0(JOIN_CACHE::create_remaining_fields())[0x260f8eb]
??:0(JOIN_CACHE::create_remaining_fields())[0x260ee4d]
??:0(LEX::create_item_for_sp_var(Lex_ident_cli_st const, sp_variable))[0x15571b6]
??:0(select_create::abort_result_set())[0x1536385]
sql/sql_insert.cc:5068(select_create::abort_result_set())[0x1534614]
sql/sql_class.cc:2550(THD::make_string_literal_charset(Lex_string_with_metadata_st const&, charset_info_st const*))[0x1386f2f]
sql/sql_cache.cc:4324(Query_cache::move_by_type(unsigned char, Query_cache_block, unsigned long, Query_cache_block))[0x133cf2d]
??:0(Query_cache::store_query(THD, TABLE_LIST))[0x1308593]
??:0(Query_cache::lock_and_suspend())[0x12f7243]
??:0(Query_cache::is_cacheable(THD, LEX, TABLE_LIST, unsigned char))[0x130cd19]
??:0(st_select_lex_unit::cleanup())[0x2023c1d]
??:0(st_select_lex_unit::cleanup())[0x202215d]
maria/ma_write.c:402(maria_write)[0x46e90f3]
nptl/pthread_create.c:478(start_thread)[0x7f26c1a07609]
??:0(clone)[0x7f26c0d79293]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x62b000085238): WITH
jennifer_0 AS (select
subq_0.c4 as c6
from
((select
11 as c4
from
t_wk as ref_0
) as subq_0
left outer join t_wk as ref_2
on (11 <> 19))
)
select
subq_2.c2 as c4
from
(select distinct
61 as c2
from
t_ykc as ref_13
) as subq_2

Connection ID (thread ID): 637
Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off

The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
information that should help you find out what is causing the crash.
Writing a core file...
Working directory at /usr/local/mysql/data
Resource Limits:
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size 0 0 bytes
Max resident set unlimited unlimited bytes
Max processes 79624 79624 processes
Max open files 1048576 1048576 files
Max locked memory 67108864 67108864 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 79624 79624 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Core pattern: core

Attachments

Issue Links

relates to

MDEV-25565 Crash on 2-nd execution of SP/PS for query calculating window functions from view

Closed

links to

CVE-2021-46661

Activity

Ascending order - Click to sort in descending order

View 4 older comments

Igor Babaev (Inactive) added a comment - 2022-02-17 02:56

Analysis:
1.
When the view v1 is opened for ref_2 the function mysql_make_view() is called. It comes to the following code:

      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);

Here we have:

(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"

We see that table->select_lex points to the specification of the CTE 'unused_with' while &lex->select_lex points to the specification of v1 that is used for ref_2.
After execution of

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

the specification of the CTE 'unused_with' becomes:

(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"

We see that the clause 'order by subq_0.c4,subq_0.c4' has been added to the specification.
It should be noted that the context for both elements from the added order by list remains as it used to be. For example for the first element we have:

(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"

Igor Babaev (Inactive) added a comment - 2022-02-17 02:56 Analysis: 1. When the view v1 is opened for ref_2 the function mysql_make_view() is called. It comes to the following code: 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); Here we have: (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" We see that table->select_lex points to the specification of the CTE 'unused_with' while &lex->select_lex points to the specification of v1 that is used for ref_2. After execution of table->select_lex->order_list.push_back(&lex->select_lex.order_list); the specification of the CTE 'unused_with' becomes: (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" We see that the clause 'order by subq_0.c4,subq_0.c4' has been added to the specification. It should be noted that the context for both elements from the added order by list remains as it used to be. For example for the first element we have: (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"

Igor Babaev (Inactive) added a comment - 2022-02-18 23:49 - edited

Analysis (cont)
2.
When we comes to JOIN::prepare for the specification of the CTE 'unused_with' we first call mysql_derived_prepare() for the views / derived tables used in the specification. So first we call this function for the derived table subq_0 used in the instance of v1 for ref_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)"

Then we call mysql_derived_prepare() for he instance of v1 for ref_0:

(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"

Note that nobody removed order by list from this select and outer select contains the same order by list

(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"

When executing JOIN::prepare() for ref_0 we come to the call of setup_order for the used order by list.
At this moment the select for the instance of v1 aliased as ref_0 looks like this:

(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"

Reference to ref_0 above is a reference to ref_0 used in the specification of v1.
Now we try to resolve the first element of the order by list subq_0.c4. First we try to resolve it against the select list employing the function find_item_in_list().
Here subq_0.c4 is successfully resolved against the first element in the select list. It looks confusing that subq_0.c4 is resolved against ref_0.f1, but the typr this element is actually Item::REF_ITEM:

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

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

The second element of the order by list is resolved against the same element of the select list and after the call of setup_table for the instance of v1 aliased as ref_0 we have

(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"

Here all occurrences of ref_0.f1 in the order by list and select list of outer select should be considered as subq_0.c4.
If we look at the outer select for the above select we see

(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"

Thus the order by list here has changed simultaneously. Again ref_0.f1 there actually represents subq_0.c4.

Igor Babaev (Inactive) added a comment - 2022-02-18 23:49 - edited Analysis (cont) 2. When we comes to JOIN::prepare for the specification of the CTE 'unused_with' we first call mysql_derived_prepare() for the views / derived tables used in the specification. So first we call this function for the derived table subq_0 used in the instance of v1 for ref_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)" Then we call mysql_derived_prepare() for he instance of v1 for ref_0: (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" Note that nobody removed order by list from this select and outer select contains the same order by list (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" When executing JOIN::prepare() for ref_0 we come to the call of setup_order for the used order by list. At this moment the select for the instance of v1 aliased as ref_0 looks like this: (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" Reference to ref_0 above is a reference to ref_0 used in the specification of v1. Now we try to resolve the first element of the order by list subq_0.c4. First we try to resolve it against the select list employing the function find_item_in_list(). Here subq_0.c4 is successfully resolved against the first element in the select list. It looks confusing that subq_0.c4 is resolved against ref_0.f1, but the typr this element is actually Item::REF_ITEM: (item->type() == Item::REF_ITEM) && (((Item_ref *)item)->ref_type() == Item_ref::VIEW_REF) The second element of the order by list is resolved against the same element of the select list and after the call of setup_table for the instance of v1 aliased as ref_0 we have (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" Here all occurrences of ref_0.f1 in the order by list and select list of outer select should be considered as subq_0.c4. If we look at the outer select for the above select we see (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" Thus the order by list here has changed simultaneously. Again ref_0.f1 there actually represents subq_0.c4.

Igor Babaev (Inactive) added a comment - 2022-02-19 06:20

Analysis (cont)
3.
Let's go to JOIN::prepare() for the select

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

and the to setup_order() for this select.
The select list contains only '11' so the order list element subq_0.c4 (ref_0.f1) cannot be resolved against the select list. As this element is fixed the only thing we have to do is to add the pointer to this element to the ref_pointer_array for the given select.

Igor Babaev (Inactive) added a comment - 2022-02-19 06:20 Analysis (cont) 3. Let's go to JOIN::prepare() for the select select 11 AS c4 from test.v1 ref_0 order by ref_0.f1,ref_0.f1 desc and the to setup_order() for this select. The select list contains only '11' so the order list element subq_0.c4 (ref_0.f1) cannot be resolved against the select list. As this element is fixed the only thing we have to do is to add the pointer to this element to the ref_pointer_array for the given select.

Igor Babaev (Inactive) added a comment - 2022-02-19 08:48

Analysis (cont)
4.
Now let's come to the call of mysql_derived_prepare() for the second instance of v1 aliased as ref_2.
The select for this instance can be seen below:

(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"

JOIN::prepare for this instance works in the same way as for the first instance and at the end we have

(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"

Here again instead of all occurrences of ref_0.f1 in the select list and order by list we should consider subq_0.c4.
The outer select for the select for ref_2 looks like this:

(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"

When setup_order is called for this select the first elements in the order list is resolved against the first element in the select list. Yet it is also resolved against the the first column of subq_0. As a result we come to the code:

    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;

Here table_ref->alias == subq_0. subq_0 does not belong neither to a view nor to a derived_table. We also have

(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"

Igor Babaev (Inactive) added a comment - 2022-02-19 08:48 Analysis (cont) 4. Now let's come to the call of mysql_derived_prepare() for the second instance of v1 aliased as ref_2. The select for this instance can be seen below: (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" JOIN::prepare for this instance works in the same way as for the first instance and at the end we have (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" Here again instead of all occurrences of ref_0.f1 in the select list and order by list we should consider subq_0.c4. The outer select for the select for ref_2 looks like this: (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" When setup_order is called for this select the first elements in the order list is resolved against the first element in the select list. Yet it is also resolved against the the first column of subq_0. As a result we come to the code: 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; } Here table_ref->alias == subq_0. subq_0 does not belong neither to a view nor to a derived_table. We also have (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"

Igor Babaev (Inactive) added a comment - 2022-02-22 22:41

This a simplest test case that causes the same problem:

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;

As we can see it is does not matter whether the used CTE is hanging or not: the same bug is reprodiced in both cases. Moreover if we replace the usage of cte with the usage of its specification as a derived table

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

we see the same problem.
To reproduce the problem it is important to use derived tables with the same name in the view and the main query ('dt' in our test case) and whose only exposed column has the same name for both tables (column 'c' in our test case). It looks like that usage of two elements on order by list referring to the same columns of the derived table is also important.

Igor Babaev (Inactive) added a comment - 2022-02-22 22:41 This a simplest test case that causes the same problem: 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; As we can see it is does not matter whether the used CTE is hanging or not: the same bug is reprodiced in both cases. Moreover if we replace the usage of cte with the usage of its specification as a derived table select * from ( select dt.c from ( select t1.f as c from t1) dt, v1) t; we see the same problem. To reproduce the problem it is important to use derived tables with the same name in the view and the main query ('dt' in our test case) and whose only exposed column has the same name for both tables (column 'c' in our test case). It looks like that usage of two elements on order by list referring to the same columns of the derived table is also important.

MariaDB Server

Unused CTE lead to a crash in find_field_in_tables/find_order_in_list

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration