[MDEV-25761] Assertion `aggr != __null' failed in sub_select_postjoin_aggr - 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: Data Manipulation - Delete
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_l2i8lmdew INTEGER NOT NULL,

c_axhvkeda INTEGER ,

primary key(c_l2i8lmdew),

unique(c_l2i8lmdew)

);

create table t_c2lhzj as

select

    ref_0.c_l2i8lmdew as c0,

    ref_0.c_l2i8lmdew as c2,

    ref_0.c_l2i8lmdew as c3

  from

    t_ykc as ref_0

  where ref_0.c_l2i8lmdew < ref_0.c_l2i8lmdew;

alter table t_ykc rename column c_l2i8lmdew to c_o2btif85c;

insert into t_ykc values

((55 << 94), (84 + 5)),

((65 / 16), 9),

(31, 76);

delete from t_ykc

where

t_ykc.c_o2btif85c = (

  select distinct

        t_ykc.c_o2btif85c as c0

      from

        (t_c2lhzj as ref_0

          cross join t_c2lhzj as ref_1

    union all

    select distinct

        52 as c0

      from

        t_c2lhzj as ref_2

      where t_ykc.c_o2btif85c >= t_ykc.c_o2btif85c);

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: 0x62b0003aa218
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 = 0x7f5164121600 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)[0x7f518fcf13c0]
sql/sql_parse.cc:0(execute_sqlcom_select(THD, TABLE_LIST))[0x17847aa]
sql/sql_lex.cc:11194(LEX::stmt_alter_procedure_start(sp_name*))[0x1656121]
sql/sql_parse.cc:6294(execute_sqlcom_select(THD, TABLE_LIST))[0x17877dd]
??:0(Item_func_trim_oracle::Item_func_trim_oracle(THD, Item, Item*))[0x1687930]
sql/item_cmpfunc.h:3452(Item_func_cursor_isopen)[0x167fc53]
sql/sql_explain.h:350(Explain_union)[0x1676897]
sql/sql_select.cc:9737(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x1c19a34]
??:0(cmp_item_int::cmp(Item*))[0x33658c1]
??:0(Item_func_in::mark_as_condition_AND_part(TABLE_LIST*))[0x32f5c99]
??:0(Item_cond_and::val_int())[0x33080b9]
sql/field.cc:5397(Field_timestamp::val_str(String, String))[0x2e73cef]
sql/field.h:3309(Field_timestamp_hires::size_of() const)[0x2f4f45a]
??:0(Field_time0::get_date(st_mysql_time*, date_mode_t))[0x2e96d3a]
sql_show.cc:0(show_create_view(THD, TABLE_LIST, String*))[0x1cca61c]
??:0(Rotate_log_event::do_update_pos(rpl_group_info*))[0x3b1d4b5]
??:0(Load_log_event::do_apply_event(st_net, rpl_group_info, bool))[0x3b136ce]
??:0(THD::THD(unsigned long long, bool))[0x1355914]
??: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)[0x7f518fce5609]
??:0(clone)[0x7f518f057293]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x62b0003b1238): delete from t_ykc
where
t_ykc.c_o2btif85c = (
select distinct
t_ykc.c_o2btif85c as c0
from
(t_c2lhzj as ref_0
cross join t_c2lhzj as ref_1
)
union all
select distinct
52 as c0
from
t_c2lhzj as ref_2
where t_ykc.c_o2btif85c >= t_ykc.c_o2btif85c)

Connection ID (thread ID): 171
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-25636 Bug report: abortion in sql/sql_parse.cc:6294

Closed

MDEV-28945 SIGSEGV in AGGR_OP::put_record and Assertion `aggr != __null' failed in sub_select_postjoin_aggr

Closed

links to

CVE-2021-46664

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2021-05-25 09:08 - edited

Thank you!
I repeated on 10.2-10.5, no failure on earlier versions

--  source include/have_innodb.inc

CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY) engine=innodb;

INSERT INTO t1 VALUES (0),(4),(31);

CREATE TABLE t2 (i int) engine=innodb;

DELETE FROM t1 WHERE t1.a =

  (SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a);

10.2 5c75ba9cadc7877e91d6b712f15
#3 <signal handler called>
#4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#5 0x00007f593603a859 in __GI_abort () at abort.c:79
#6 0x00007f593603a729 in __assert_fail_base (fmt=0x7f59361d0588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x56179f48b250 "aggr != __null", file=0x56179f488ab8 "/10.2/src/sql/sql_select.cc", line=18576, function=<optimized out>) at assert.c:92
#7 0x00007f593604bf36 in __GI___assert_fail (assertion=0x56179f48b250 "aggr != __null", file=0x56179f488ab8 "/10.2/src/sql/sql_select.cc", line=18576, function=0x56179f48b208 "enum_nested_loop_state sub_select_postjoin_aggr(JOIN, JOIN_TAB, bool)") at assert.c:101
#8 0x000056179ea4d4d5 in sub_select_postjoin_aggr (join=0x7f58d80161b0, join_tab=0x7f58d809aa28, end_of_records=true) at /10.2/src/sql/sql_select.cc:18576
#9 0x000056179ea4d875 in sub_select (join=0x7f58d80161b0, join_tab=0x7f58d809a678, end_of_records=true) at /10.2/src/sql/sql_select.cc:18825
#10 0x000056179ea4d059 in do_select (join=0x7f58d80161b0, procedure=0x0) at /10.2/src/sql/sql_select.cc:18420
#11 0x000056179ea26be1 in JOIN::exec_inner (this=0x7f58d80161b0) at /10.2/src/sql/sql_select.cc:3651
#12 0x000056179ea26088 in JOIN::exec (this=0x7f58d80161b0) at /10.2/src/sql/sql_select.cc:3446
#13 0x000056179eacd30b in st_select_lex_unit::exec (this=0x7f58d80133a8) at /10.2/src/sql/sql_union.cc:1073
#14 0x000056179ed35e99 in subselect_union_engine::exec (this=0x7f58d80156a8) at /10.2/src/sql/item_subselect.cc:4027
#15 0x000056179ed2a595 in Item_subselect::exec (this=0x7f58d8015528) at /10.2/src/sql/item_subselect.cc:770
#16 0x000056179ed2c3d1 in Item_singlerow_subselect::val_int (this=0x7f58d8015528) at /10.2/src/sql/item_subselect.cc:1391
#17 0x000056179eca23eb in Arg_comparator::compare_int_signed (this=0x7f58d80157a0) at /10.2/src/sql/item_cmpfunc.cc:970
#18 0x000056179ecb65f0 in Arg_comparator::compare (this=0x7f58d80157a0) at /10.2/src/sql/item_cmpfunc.h:87
#19 0x000056179eca4a43 in Item_func_eq::val_int (this=0x7f58d80156e0) at /10.2/src/sql/item_cmpfunc.cc:1803
#20 0x000056179ead7e61 in SQL_SELECT::skip_record (this=0x7f58d809b860, thd=0x7f58d8000d90) at /10.2/src/sql/opt_range.h:1633
#21 0x000056179edf69a8 in mysql_delete (thd=0x7f58d8000d90, table_list=0x7f58d8012878, conds=0x7f58d80156e0, order_list=0x7f58d8005338, limit=18446744073709551615, options=0, result=0x0) at /10.2/src/sql/sql_delete.cc:566
#22 0x000056179e9def30 in mysql_execute_command (thd=0x7f58d8000d90) at /10.2/src/sql/sql_parse.cc:4420
#23 0x000056179e9e9866 in mysql_parse (thd=0x7f58d8000d90, rawbuf=0x7f58d8012708 "DELETE FROM t1 WHERE t1.a =\n(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a)", length=102, parser_state=0x7f5930381560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793
#24 0x000056179e9d7ac1 in dispatch_command (command=COM_QUERY, thd=0x7f58d8000d90, packet=0x7f58d8008b61 "DELETE FROM t1 WHERE t1.a =\n(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a)", packet_length=102, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827
#25 0x000056179e9d65bc in do_command (thd=0x7f58d8000d90) at /10.2/src/sql/sql_parse.cc:1381
#26 0x000056179eb31a41 in do_handle_one_connection (connect=0x5617a18dce60) at /10.2/src/sql/sql_connect.cc:1336
#27 0x000056179eb317a6 in handle_one_connection (arg=0x5617a18dce60) at /10.2/src/sql/sql_connect.cc:1241
#28 0x000056179f35ddc8 in pfs_spawn_thread (arg=0x5617a18c0250) at /10.2/src/storage/perfschema/pfs.cc:1869
#29 0x00007f593655b609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#30 0x00007f5936137293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.5.10
210525 11:06:02 [ERROR] mysqld got signal 11 ;

Server version: 10.5.10-MariaDB

sql/signal_handler.cc:224(handle_fatal_signal)[0x5627e7ccc4d7]
sigaction.c:0(__restore_rt)[0x7efe296513c0]
sql/sql_select.cc:29086(AGGR_OP::end_send())[0x5627e7b07fb9]
sql/sql_select.cc:20536(sub_select_postjoin_aggr(JOIN, st_join_table, bool))[0x5627e7b08261]
sql/sql_select.cc:20361(JOIN::exec_inner())[0x5627e7b23bea]
sql/sql_select.cc:4286(JOIN::exec())[0x5627e7b24033]
sql/sql_union.cc:2235(st_select_lex_unit::exec())[0x5627e7b6da6c]
sql/item_subselect.cc:4104(subselect_union_engine::exec())[0x5627e7d8549e]
sql/item_subselect.cc:839(Item_subselect::exec())[0x5627e7d8503a]
sql/item_subselect.cc:1441(Item_singlerow_subselect::val_int())[0x5627e7d8588e]
sql/item_cmpfunc.cc:943(Arg_comparator::compare_int_signed())[0x5627e7d0367b]
sql/item_cmpfunc.cc:1776(Item_func_eq::val_int())[0x5627e7d078ea]
sql/opt_range.h:1729(SQL_SELECT::skip_record(THD*))[0x5627e798d25e]
sql/sql_delete.cc:777(mysql_delete(THD, TABLE_LIST, Item, SQL_I_List<st_order>, unsigned long long, unsigned long long, select_result*))[0x5627e7e2dc64]
sql/sql_parse.cc:4875(mysql_execute_command(THD*))[0x5627e7ac638f]
sql/sql_parse.cc:8099(mysql_parse(THD, char, unsigned int, Parser_state*, bool, bool))[0x5627e7ac8efc]
sql/sql_parse.cc:1951(dispatch_command(enum_server_command, THD, char, unsigned int, bool, bool))[0x5627e7acb93a]
sql/sql_parse.cc:1375(do_command(THD*))[0x5627e7accc6f]
sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x5627e7bb9b12]
sql/sql_connect.cc:1318(handle_one_connection)[0x5627e7bb9dd4]
perfschema/pfs.cc:2204(pfs_spawn_thread)[0x5627e7f46c4d]
nptl/pthread_create.c:478(start_thread)[0x7efe29645609]
x86_64/clone.S:97(__GI___clone)[0x7efe29234293]

Query (0x7efdd4012900): DELETE FROM t1 WHERE t1.a =
(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a)

Alice Sherepa added a comment - 2021-05-25 09:08 - edited Thank you! I repeated on 10.2-10.5, no failure on earlier versions -- source include/have_innodb.inc CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY ) engine=innodb; INSERT INTO t1 VALUES (0),(4),(31); CREATE TABLE t2 (i int ) engine=innodb; DELETE FROM t1 WHERE t1.a = ( SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a); 10.2 5c75ba9cadc7877e91d6b712f15 #3 <signal handler called> #4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 #5 0x00007f593603a859 in __GI_abort () at abort.c:79 #6 0x00007f593603a729 in __assert_fail_base (fmt=0x7f59361d0588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x56179f48b250 "aggr != __null", file=0x56179f488ab8 "/10.2/src/sql/sql_select.cc", line=18576, function=<optimized out>) at assert.c:92 #7 0x00007f593604bf36 in __GI___assert_fail (assertion=0x56179f48b250 "aggr != __null", file=0x56179f488ab8 "/10.2/src/sql/sql_select.cc", line=18576, function=0x56179f48b208 "enum_nested_loop_state sub_select_postjoin_aggr(JOIN*, JOIN_TAB*, bool)") at assert.c:101 #8 0x000056179ea4d4d5 in sub_select_postjoin_aggr (join=0x7f58d80161b0, join_tab=0x7f58d809aa28, end_of_records=true) at /10.2/src/sql/sql_select.cc:18576 #9 0x000056179ea4d875 in sub_select (join=0x7f58d80161b0, join_tab=0x7f58d809a678, end_of_records=true) at /10.2/src/sql/sql_select.cc:18825 #10 0x000056179ea4d059 in do_select (join=0x7f58d80161b0, procedure=0x0) at /10.2/src/sql/sql_select.cc:18420 #11 0x000056179ea26be1 in JOIN::exec_inner (this=0x7f58d80161b0) at /10.2/src/sql/sql_select.cc:3651 #12 0x000056179ea26088 in JOIN::exec (this=0x7f58d80161b0) at /10.2/src/sql/sql_select.cc:3446 #13 0x000056179eacd30b in st_select_lex_unit::exec (this=0x7f58d80133a8) at /10.2/src/sql/sql_union.cc:1073 #14 0x000056179ed35e99 in subselect_union_engine::exec (this=0x7f58d80156a8) at /10.2/src/sql/item_subselect.cc:4027 #15 0x000056179ed2a595 in Item_subselect::exec (this=0x7f58d8015528) at /10.2/src/sql/item_subselect.cc:770 #16 0x000056179ed2c3d1 in Item_singlerow_subselect::val_int (this=0x7f58d8015528) at /10.2/src/sql/item_subselect.cc:1391 #17 0x000056179eca23eb in Arg_comparator::compare_int_signed (this=0x7f58d80157a0) at /10.2/src/sql/item_cmpfunc.cc:970 #18 0x000056179ecb65f0 in Arg_comparator::compare (this=0x7f58d80157a0) at /10.2/src/sql/item_cmpfunc.h:87 #19 0x000056179eca4a43 in Item_func_eq::val_int (this=0x7f58d80156e0) at /10.2/src/sql/item_cmpfunc.cc:1803 #20 0x000056179ead7e61 in SQL_SELECT::skip_record (this=0x7f58d809b860, thd=0x7f58d8000d90) at /10.2/src/sql/opt_range.h:1633 #21 0x000056179edf69a8 in mysql_delete (thd=0x7f58d8000d90, table_list=0x7f58d8012878, conds=0x7f58d80156e0, order_list=0x7f58d8005338, limit=18446744073709551615, options=0, result=0x0) at /10.2/src/sql/sql_delete.cc:566 #22 0x000056179e9def30 in mysql_execute_command (thd=0x7f58d8000d90) at /10.2/src/sql/sql_parse.cc:4420 #23 0x000056179e9e9866 in mysql_parse (thd=0x7f58d8000d90, rawbuf=0x7f58d8012708 "DELETE FROM t1 WHERE t1.a =\n(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a)", length=102, parser_state=0x7f5930381560, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:7793 #24 0x000056179e9d7ac1 in dispatch_command (command=COM_QUERY, thd=0x7f58d8000d90, packet=0x7f58d8008b61 "DELETE FROM t1 WHERE t1.a =\n(SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a)", packet_length=102, is_com_multi=false, is_next_command=false) at /10.2/src/sql/sql_parse.cc:1827 #25 0x000056179e9d65bc in do_command (thd=0x7f58d8000d90) at /10.2/src/sql/sql_parse.cc:1381 #26 0x000056179eb31a41 in do_handle_one_connection (connect=0x5617a18dce60) at /10.2/src/sql/sql_connect.cc:1336 #27 0x000056179eb317a6 in handle_one_connection (arg=0x5617a18dce60) at /10.2/src/sql/sql_connect.cc:1241 #28 0x000056179f35ddc8 in pfs_spawn_thread (arg=0x5617a18c0250) at /10.2/src/storage/perfschema/pfs.cc:1869 #29 0x00007f593655b609 in start_thread (arg=<optimized out>) at pthread_create.c:477 #30 0x00007f5936137293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 10.5.10 210525 11:06:02 [ERROR] mysqld got signal 11 ; Server version: 10.5.10-MariaDB sql/signal_handler.cc:224(handle_fatal_signal)[0x5627e7ccc4d7] sigaction.c:0(__restore_rt)[0x7efe296513c0] sql/sql_select.cc:29086(AGGR_OP::end_send())[0x5627e7b07fb9] sql/sql_select.cc:20536(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))[0x5627e7b08261] sql/sql_select.cc:20361(JOIN::exec_inner())[0x5627e7b23bea] sql/sql_select.cc:4286(JOIN::exec())[0x5627e7b24033] sql/sql_union.cc:2235(st_select_lex_unit::exec())[0x5627e7b6da6c] sql/item_subselect.cc:4104(subselect_union_engine::exec())[0x5627e7d8549e] sql/item_subselect.cc:839(Item_subselect::exec())[0x5627e7d8503a] sql/item_subselect.cc:1441(Item_singlerow_subselect::val_int())[0x5627e7d8588e] sql/item_cmpfunc.cc:943(Arg_comparator::compare_int_signed())[0x5627e7d0367b] sql/item_cmpfunc.cc:1776(Item_func_eq::val_int())[0x5627e7d078ea] sql/opt_range.h:1729(SQL_SELECT::skip_record(THD*))[0x5627e798d25e] sql/sql_delete.cc:777(mysql_delete(THD*, TABLE_LIST*, Item*, SQL_I_List<st_order>*, unsigned long long, unsigned long long, select_result*))[0x5627e7e2dc64] sql/sql_parse.cc:4875(mysql_execute_command(THD*))[0x5627e7ac638f] sql/sql_parse.cc:8099(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5627e7ac8efc] sql/sql_parse.cc:1951(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5627e7acb93a] sql/sql_parse.cc:1375(do_command(THD*))[0x5627e7accc6f] sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x5627e7bb9b12] sql/sql_connect.cc:1318(handle_one_connection)[0x5627e7bb9dd4] perfschema/pfs.cc:2204(pfs_spawn_thread)[0x5627e7f46c4d] nptl/pthread_create.c:478(start_thread)[0x7efe29645609] x86_64/clone.S:97(__GI___clone)[0x7efe29234293] Query (0x7efdd4012900): DELETE FROM t1 WHERE t1.a = (SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a)

Sergei Petrunia added a comment - 2021-07-04 12:48

SELECT statement also crashes...

Playing, around, I see that this statement crashes:

SELECT * FROM t1 WHERE t1.a =   ( SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a>0);

while this doesn't:

SELECT * FROM t1 WHERE t1.a =   (SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a);

Sergei Petrunia added a comment - 2021-07-04 12:48 SELECT statement also crashes... Playing, around, I see that this statement crashes: SELECT * FROM t1 WHERE t1.a = ( SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a>0); while this doesn't: SELECT * FROM t1 WHERE t1.a = ( SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2 r WHERE t1.a = t1.a);

Sergei Petrunia added a comment - 2021-07-04 13:00

So, it crashes in sub_select_postjoin_aggr(), because aggr==NULL.

It was set to NULL here:

  #0  JOIN::cleanup (this=0x7fff7c0176e8, full=true) at /home/psergey/dev-git/10.2/sql/sql_select.cc:12420

  #1  0x0000555555ba2b63 in JOIN::join_free (this=0x7fff7c0176e8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:12305

  #2  0x0000555555bb2156 in do_select (join=0x7fff7c0176e8, procedure=0x0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18485

  #3  0x0000555555b8bb24 in JOIN::exec_inner (this=0x7fff7c0176e8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3651

  #4  0x0000555555b8afdc in JOIN::exec (this=0x7fff7c0176e8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3446

  #5  0x0000555555c30bdd in st_select_lex_unit::exec (this=0x7fff7c0141c8) at /home/psergey/dev-git/10.2/sql/sql_union.cc:1073

  #6  0x0000555555e8f04d in subselect_union_engine::exec (this=0x7fff7c0164c8) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:4027

  #7  0x0000555555e8390d in Item_subselect::exec (this=0x7fff7c016348) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:770

  #8  0x0000555555e85690 in Item_singlerow_subselect::val_int (this=0x7fff7c016348) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:1391

  #9  0x0000555555a6a9cf in Item::val_int_result (this=0x7fff7c016348) at /home/psergey/dev-git/10.2/sql/item.h:1274

  #10 0x0000555555deee38 in Item_cache_int::cache_value (this=0x7fff7c03f408) at /home/psergey/dev-git/10.2/sql/item.cc:9703

  #11 0x0000555555df901c in Item_cache_wrapper::cache (this=0x7fff7c03f350) at /home/psergey/dev-git/10.2/sql/item.cc:8364

  #12 0x0000555555deae86 in Item_cache_wrapper::val_int (this=0x7fff7c03f350) at /home/psergey/dev-git/10.2/sql/item.cc:8418

  #13 0x0000555555dff43b in Arg_comparator::compare_int_signed (this=0x7fff7c0165c0) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.cc:970

  #14 0x0000555555e1309c in Arg_comparator::compare (this=0x7fff7c0165c0) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.h:87

  #15 0x0000555555e01a1f in Item_func_eq::val_int (this=0x7fff7c016500) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.cc:1806

  #16 0x0000555555bb2db9 in evaluate_join_record (join=0x7fff7c016760, join_tab=0x7fff7c03b508, error=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18990

  #17 0x0000555555bb29e0 in sub_select (join=0x7fff7c016760, join_tab=0x7fff7c03b508, end_of_records=false) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18895

  #18 0x0000555555bb1f53 in do_select (join=0x7fff7c016760, procedure=0x0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18439

  #19 0x0000555555b8bb24 in JOIN::exec_inner (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3651

  #20 0x0000555555b8afdc in JOIN::exec (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3446

  #21 0x0000555555b8c1ae in mysql_select (thd=0x7fff7c000d50, tables=0x7fff7c013688, wild_num=1, fields=..., conds=0x7fff7c016500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c016740, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3849

  #22 0x0000555555b80303 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c004888, result=0x7fff7c016740, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:361

  #23 0x0000555555b4b5d9 in execute_sqlcom_select (thd=0x7fff7c000d50, all_tables=0x7fff7c013688) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6271

  #24 0x0000555555b420cb in mysql_execute_command (thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:3582

  #25 0x0000555555b4f350 in mysql_parse (thd=0x7fff7c000d50, rawbuf=0x7fff7c0133c8 "SELECT * \nFROM t1 \nWHERE \n  t1.a = (SELECT t1.a FROM t2 \n          UNION \n          SELECT DISTINCT 52 FROM t2a r WHERE t1.a = t1.a)", length=132, parser_state=0x7ffff41c0630, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:7793

Sergei Petrunia added a comment - 2021-07-04 13:00 So, it crashes in sub_select_postjoin_aggr(), because aggr==NULL. It was set to NULL here: #0 JOIN::cleanup (this=0x7fff7c0176e8, full=true) at /home/psergey/dev-git/10.2/sql/sql_select.cc:12420 #1 0x0000555555ba2b63 in JOIN::join_free (this=0x7fff7c0176e8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:12305 #2 0x0000555555bb2156 in do_select (join=0x7fff7c0176e8, procedure=0x0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18485 #3 0x0000555555b8bb24 in JOIN::exec_inner (this=0x7fff7c0176e8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3651 #4 0x0000555555b8afdc in JOIN::exec (this=0x7fff7c0176e8) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3446 #5 0x0000555555c30bdd in st_select_lex_unit::exec (this=0x7fff7c0141c8) at /home/psergey/dev-git/10.2/sql/sql_union.cc:1073 #6 0x0000555555e8f04d in subselect_union_engine::exec (this=0x7fff7c0164c8) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:4027 #7 0x0000555555e8390d in Item_subselect::exec (this=0x7fff7c016348) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:770 #8 0x0000555555e85690 in Item_singlerow_subselect::val_int (this=0x7fff7c016348) at /home/psergey/dev-git/10.2/sql/item_subselect.cc:1391 #9 0x0000555555a6a9cf in Item::val_int_result (this=0x7fff7c016348) at /home/psergey/dev-git/10.2/sql/item.h:1274 #10 0x0000555555deee38 in Item_cache_int::cache_value (this=0x7fff7c03f408) at /home/psergey/dev-git/10.2/sql/item.cc:9703 #11 0x0000555555df901c in Item_cache_wrapper::cache (this=0x7fff7c03f350) at /home/psergey/dev-git/10.2/sql/item.cc:8364 #12 0x0000555555deae86 in Item_cache_wrapper::val_int (this=0x7fff7c03f350) at /home/psergey/dev-git/10.2/sql/item.cc:8418 #13 0x0000555555dff43b in Arg_comparator::compare_int_signed (this=0x7fff7c0165c0) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.cc:970 #14 0x0000555555e1309c in Arg_comparator::compare (this=0x7fff7c0165c0) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.h:87 #15 0x0000555555e01a1f in Item_func_eq::val_int (this=0x7fff7c016500) at /home/psergey/dev-git/10.2/sql/item_cmpfunc.cc:1806 #16 0x0000555555bb2db9 in evaluate_join_record (join=0x7fff7c016760, join_tab=0x7fff7c03b508, error=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18990 #17 0x0000555555bb29e0 in sub_select (join=0x7fff7c016760, join_tab=0x7fff7c03b508, end_of_records=false) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18895 #18 0x0000555555bb1f53 in do_select (join=0x7fff7c016760, procedure=0x0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:18439 #19 0x0000555555b8bb24 in JOIN::exec_inner (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3651 #20 0x0000555555b8afdc in JOIN::exec (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3446 #21 0x0000555555b8c1ae in mysql_select (thd=0x7fff7c000d50, tables=0x7fff7c013688, wild_num=1, fields=..., conds=0x7fff7c016500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c016740, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3849 #22 0x0000555555b80303 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c004888, result=0x7fff7c016740, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:361 #23 0x0000555555b4b5d9 in execute_sqlcom_select (thd=0x7fff7c000d50, all_tables=0x7fff7c013688) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:6271 #24 0x0000555555b420cb in mysql_execute_command (thd=0x7fff7c000d50) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:3582 #25 0x0000555555b4f350 in mysql_parse (thd=0x7fff7c000d50, rawbuf=0x7fff7c0133c8 "SELECT * \nFROM t1 \nWHERE \n t1.a = (SELECT t1.a FROM t2 \n UNION \n SELECT DISTINCT 52 FROM t2a r WHERE t1.a = t1.a)", length=132, parser_state=0x7ffff41c0630, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2/sql/sql_parse.cc:7793

Sergei Petrunia added a comment - 2021-07-04 15:12

Making the query readable:

create table t2a like t2;

insert into t2a select * from t2;

SELECT *

FROM t1

WHERE

  t1.a = (SELECT t1.a FROM t2

          UNION

          SELECT DISTINCT 52 FROM t2a r WHERE t1.a = t1.a);

The select with id=3 initially has select_lex->uncacheable=3, but then it becomes 0 here:

(gdb) wher

  #0  st_select_lex::update_correlated_cache (this=0x7fff7c0150a8) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:4345

  #1  0x0000555555b3270f in st_select_lex::optimize_unflattened_subqueries (this=0x7fff7c005098, const_only=false) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:3872

  #2  0x0000555555cf246c in JOIN::optimize_unflattened_subqueries (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/opt_subselect.cc:5326

  #3  0x0000555555b86370 in JOIN::optimize_inner (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:2089

  #4  0x0000555555b82bcc in JOIN::optimize (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1127

  #5  0x0000555555b8c11e in mysql_select (thd=0x7fff7c000d50, tables=0x7fff7c013688, wild_num=1, fields=..., conds=0x7fff7c016500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c016740, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3835

  #6  0x0000555555b80303 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c004888, result=0x7fff7c016740, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:361

Sergei Petrunia added a comment - 2021-07-04 15:12 Making the query readable: create table t2a like t2; insert into t2a select * from t2; SELECT * FROM t1 WHERE t1.a = ( SELECT t1.a FROM t2 UNION SELECT DISTINCT 52 FROM t2a r WHERE t1.a = t1.a); The select with id=3 initially has select_lex->uncacheable=3, but then it becomes 0 here: (gdb) wher #0 st_select_lex::update_correlated_cache (this=0x7fff7c0150a8) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:4345 #1 0x0000555555b3270f in st_select_lex::optimize_unflattened_subqueries (this=0x7fff7c005098, const_only=false) at /home/psergey/dev-git/10.2/sql/sql_lex.cc:3872 #2 0x0000555555cf246c in JOIN::optimize_unflattened_subqueries (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/opt_subselect.cc:5326 #3 0x0000555555b86370 in JOIN::optimize_inner (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:2089 #4 0x0000555555b82bcc in JOIN::optimize (this=0x7fff7c016760) at /home/psergey/dev-git/10.2/sql/sql_select.cc:1127 #5 0x0000555555b8c11e in mysql_select (thd=0x7fff7c000d50, tables=0x7fff7c013688, wild_num=1, fields=..., conds=0x7fff7c016500, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff7c016740, unit=0x7fff7c004948, select_lex=0x7fff7c005098) at /home/psergey/dev-git/10.2/sql/sql_select.cc:3835 #6 0x0000555555b80303 in handle_select (thd=0x7fff7c000d50, lex=0x7fff7c004888, result=0x7fff7c016740, setup_tables_done_option=0) at /home/psergey/dev-git/10.2/sql/sql_select.cc:361

Sergei Petrunia added a comment - 2021-07-04 15:14

... but select_lex_unit->uncacheable remains =1, because the first branch of the UNION remains correlated.

Sergei Petrunia added a comment - 2021-07-04 15:14 ... but select_lex_unit->uncacheable remains =1, because the first branch of the UNION remains correlated.

Sergei Petrunia added a comment - 2021-07-04 15:22

This is closely related to ~~MDEV-25636~~

Sergei Petrunia added a comment - 2021-07-04 15:22 This is closely related to MDEV-25636

Sergei Petrunia added a comment - 2022-02-10 18:11

Fixed by fix for ~~MDEV-25636~~. Testcase added.

Sergei Petrunia added a comment - 2022-02-10 18:11 Fixed by fix for MDEV-25636 . Testcase added.

Roel Van de Paar added a comment - 2022-06-25 03:31

See ~~MDEV-28945~~ Assertion `aggr != __null' failed in sub_select_postjoin_aggr

Roel Van de Paar added a comment - 2022-06-25 03:31 See MDEV-28945 Assertion `aggr != __null' failed in sub_select_postjoin_aggr

People

Assignee:: Sergei Petrunia

Reporter:: Zuming Jiang

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2021-05-24 09:46

Updated:: 2023-10-09 13:27

Resolved:: 2022-02-10 18:11

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server