[MDEV-20557] SQL query with duplicate table aliases consistently crashes server, Assertion `thd' failed in Item_subselect::const_item Created: 2019-09-11  Updated: 2020-07-25  Resolved: 2020-07-25

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.0, 10.1, 10.1.38, 10.1.41, 10.2, 10.3, 10.4
Fix Version/s: 10.1.46, 10.2.33, 10.3.24, 10.4.14, 10.5.5

Type: Bug Priority: Major
Reporter: Mark Buranyi Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Debian GNU/Linux 9 (amd64)

Issue Links:
Duplicate
duplicates MDEV-21649 Crash when using nested EXISTS Closed
Relates
relates to MDEV-23221 A subquery causes crash Closed

 Description   

During development we've noticed that a complicated SQL query that (by mistake) uses duplicate table aliases crashes the server every time.

The query looks like this (notice how table2 and table4 are both aliased to "agu")

select 
 
 
( 
	select count(1) from table1 as com where ( 
		exists (
 
			select 1 from table2 as agu where agu.`ARTICLE_GROUP_ID` = (
 
				select agu.`ID` from `table5` as acl 
					inner join `table4` as agu on agu.`ID` = acl.`GROUPID` 
						where acl.`ARTICLEID` = art.`ID` 
			) and agu.`USER_ID` = com.`USERID`
 
		) 
	) 
) as test1
 
 
 
from table3 as art;

and tables are:

CREATE TABLE `table1` (
 
`ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
 
`USERID` int(11) NOT NULL DEFAULT '0'
 
);
 
 
 
CREATE TABLE `table2` (
 
`ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
 
`USER_ID` int(11) NOT NULL,
 
`ARTICLE_GROUP_ID` int(11) NOT NULL
 
)  ; 
 
 
CREATE TABLE `table3` (
 
`ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
 
);
 
 
 
CREATE TABLE `table4` (
 
`ID` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
 
);
 
  
CREATE TABLE `table5` (
 
`ARTICLEID` int(11) NOT NULL,
 
`GROUPID` int(11) NOT NULL
 
);

The core backtrace looks like this:

 
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
 
#1  0x0000558f375ff3f7 in handle_fatal_signal ()
 
#2  <signal handler called>
 
#3  0x0000558f37684e8b in Item_subselect::const_item() const ()
 
#4  0x0000558f37580091 in Item_in_subselect::const_item() const ()
 
#5  0x0000558f373dd2f4 in Item_func::update_used_tables() ()
 
#6  0x0000558f3745ad9f in st_select_lex::update_used_tables() ()
 
#7  0x0000558f3745c0a8 in st_select_lex::optimize_unflattened_subqueries(bool) ()
 
#8  0x0000558f374bf5e6 in JOIN::optimize_inner() ()
 
#9  0x0000558f374c0b1b in JOIN::optimize() ()
 
#10 0x0000558f374c14d8 in mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) ()
 
#11 0x0000558f374c16af in handle_select(THD*, LEX*, select_result*, unsigned long) ()
 
#12 0x0000558f373ab800 in ?? ()
 
#13 0x0000558f3746a7ca in mysql_execute_command(THD*) ()
 
#14 0x0000558f3746c4b1 in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
 
#15 0x0000558f3746fe9c in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
 
#16 0x0000558f374706d9 in do_command(THD*) ()
 
#17 0x0000558f37541c82 in do_handle_one_connection(THD*) ()
 
#18 0x0000558f37541d20 in handle_one_connection ()
 
#19 0x00007fc2903144a4 in start_thread (arg=0x7fc2905df700) at pthread_create.c:456
 
#20 0x00007fc28ef41d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Kernel log contains:
[139380.540174] mysqld[56374]: segfault at 58 ip 0000555f94691e8b sp 00007fbe668a22d0 error 4 in mysqld[555f94059000+fb4000]

I could consistently replicate this across different machines all running 10.1 MariaDB. Fixing the duplicate table alias makes to problem go away.

 Comments   
Comment by Alice Sherepa [ 2019-09-11 ]

Thank you for the report! I repeated the crash on 10.0-10.4:

create table t1 (id int, id2 int);
 
create table t2 (id int, id2 int, a int);
 
create table t3 (id int);
 
create table t4 (id int);
 
 
 
select (select 1 from t1 where (exists 
	(select 1 from t2  
	where t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt
 
from t3;
 
 
 
drop table t1,t2,t3,t4;

also repeatable with EXPLAIN ..

10.0 15b9d04bdc66f4d

 
#3  <signal handler called>
 
#4  0x00007f6985333428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007f698533502a in __GI_abort () at abort.c:89
 
#6  0x00007f698532bbd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x100a8ae "thd", file=file@entry=0x100a5a0 "/10.0/sql/item_subselect.cc", line=line@entry=881, function=function@entry=0x100bde0 <Item_subselect::const_item() const::__PRETTY_FUNCTION__> "virtual bool Item_subselect::const_item() const") at assert.c:92
 
#7  0x00007f698532bc82 in __GI___assert_fail (assertion=0x100a8ae "thd", file=0x100a5a0 "/10.0/sql/item_subselect.cc", line=881, function=0x100bde0 <Item_subselect::const_item() const::__PRETTY_FUNCTION__> "virtual bool Item_subselect::const_item() const") at assert.c:101
 
#8  0x00000000009107fd in Item_subselect::const_item (this=0x7f69719487a8) at /10.0/sql/item_subselect.cc:881
 
#9  0x00000000009207ca in Item_in_subselect::const_item (this=0x7f69719487a8) at /10.0/sql/item_subselect.h:614
 
#10 0x00000000008d1b19 in Item_func::update_used_tables (this=0x7f6971b64380) at /10.0/sql/item_func.cc:450
 
#11 0x00000000006511ff in st_select_lex::update_used_tables (this=0x7f69718f8248) at /10.0/sql/sql_lex.cc:3928
 
#12 0x0000000000650535 in st_select_lex::optimize_unflattened_subqueries (this=0x7f6979e71100, const_only=false) at /10.0/sql/sql_lex.cc:3555
 
#13 0x00000000007e2366 in JOIN::optimize_unflattened_subqueries (this=0x7f6971b615e8) at /10.0/sql/opt_subselect.cc:5312
 
#14 0x000000000069874d in JOIN::optimize_inner (this=0x7f6971b615e8) at /10.0/sql/sql_select.cc:1822
 
#15 0x0000000000695966 in JOIN::optimize (this=0x7f6971b615e8) at /10.0/sql/sql_select.cc:1042
 
#16 0x000000000069dea8 in mysql_select (thd=0x7f6979e6d070, rref_pointer_array=0x7f6979e713a8, tables=0x7f6971b60768, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f6971b60e20, unit=0x7f6979e70a08, select_lex=0x7f6979e71100) at /10.0/sql/sql_select.cc:3336
 
#17 0x00000000006d514a in mysql_explain_union (thd=0x7f6979e6d070, unit=0x7f6979e70a08, result=0x7f6971b60e20) at /10.0/sql/sql_select.cc:24347
 
#18 0x0000000000663c05 in execute_sqlcom_select (thd=0x7f6979e6d070, all_tables=0x7f6971b60768) at /10.0/sql/sql_parse.cc:5276
 
#19 0x000000000065be1e in mysql_execute_command (thd=0x7f6979e6d070) at /10.0/sql/sql_parse.cc:2555
 
#20 0x0000000000667014 in mysql_parse (thd=0x7f6979e6d070, rawbuf=0x7f69718f8088 "explain extended\nselect (select 1 from t1 where (exists \n(select 1 from t2  \nwhere t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt\nfrom t3", length=166, parser_state=0x7f698725a660) at /10.0/sql/sql_parse.cc:6649
 
#21 0x0000000000658eb2 in dispatch_command (command=COM_QUERY, thd=0x7f6979e6d070, packet=0x7f697f173071 "", packet_length=166) at /10.0/sql/sql_parse.cc:1301
 
#22 0x0000000000658119 in do_command (thd=0x7f6979e6d070) at /10.0/sql/sql_parse.cc:1003
 
#23 0x000000000078ffb2 in do_handle_one_connection (thd_arg=0x7f6979e6d070) at /10.0/sql/sql_connect.cc:1377
 
#24 0x000000000078fd00 in handle_one_connection (arg=0x7f6979e6d070) at /10.0/sql/sql_connect.cc:1292
 
#25 0x0000000000e5046e in pfs_spawn_thread (arg=0x7f6979bf6670) at /10.0/storage/perfschema/pfs.cc:1861
 
#26 0x00007f698660c6ba in start_thread (arg=0x7f698725b700) at pthread_create.c:333
 
#27 0x00007f698540541d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

on 10.4 71c57bcf8f87078772:

#4  0x00007f26591c7428 in __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:54
 
#5  0x00007f26591c902a in __GI_abort () at abort.c:89
 
#6  0x00007f26591bfbd7 in __assert_fail_base (fmt=<optimized out>, assertion=assertion@entry=0x563370160a76 "thd", file=file@entry=0x563370160748 "/10.4/sql/item_subselect.cc", line=line@entry=952, function=function@entry=0x563370162620 <Item_subselect::const_item() const::__PRETTY_FUNCTION__> "virtual bool Item_subselect::const_item() const") at assert.c:92
 
#7  0x00007f26591bfc82 in __GI___assert_fail (assertion=0x563370160a76 "thd", file=0x563370160748 "/10.4/sql/item_subselect.cc", line=952, function=0x563370162620 <Item_subselect::const_item() const::__PRETTY_FUNCTION__> "virtual bool Item_subselect::const_item() const") at assert.c:101
 
#8  0x000056336f6c2cfc in Item_subselect::const_item (this=0x7f260414f580) at /10.4/sql/item_subselect.cc:952
 
#9  0x000056336f6d7612 in Item_in_subselect::const_item (this=0x7f260414f580) at /10.4/sql/item_subselect.h:642
 
#10 0x000056336f1e5338 in Used_tables_and_const_cache::used_tables_and_const_cache_join (this=0x7f260414ddb8, item=0x7f260414f580) at /10.4/sql/item.h:4966
 
#11 0x000056336f1e5396 in Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join (this=0x7f260414ddb8, item=0x7f260414f580) at /10.4/sql/item.h:4971
 
#12 0x000056336f1e53e0 in Used_tables_and_const_cache::used_tables_and_const_cache_update_and_join (this=0x7f260414ddb8, argc=2, argv=0x7f260414dda0) at /10.4/sql/item.h:4982
 
#13 0x000056336f1e6a24 in Item_func::update_used_tables (this=0x7f260414dd10) at /10.4/sql/item_func.h:160
 
#14 0x000056336f246030 in st_select_lex::update_used_tables (this=0x7f2604013710) at /10.4/sql/sql_lex.cc:4585
 
#15 0x000056336f245344 in st_select_lex::optimize_unflattened_subqueries (this=0x7f26040132a8, const_only=false) at /10.4/sql/sql_lex.cc:4189
 
#16 0x000056336f46a37e in JOIN::optimize_unflattened_subqueries (this=0x7f260414c0c0) at /10.4/sql/opt_subselect.cc:5480
 
#17 0x000056336f2c6fa3 in JOIN::optimize_stage2 (this=0x7f260414c0c0) at /10.4/sql/sql_select.cc:2966
 
#18 0x000056336f2c45ee in JOIN::optimize_inner (this=0x7f260414c0c0) at /10.4/sql/sql_select.cc:2218
 
#19 0x000056336f2c20b6 in JOIN::optimize (this=0x7f260414c0c0) at /10.4/sql/sql_select.cc:1561
 
#20 0x000056336f2cd2fa in mysql_select (thd=0x7f2604000b00, tables=0x7f2604018710, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2604018fa8, unit=0x7f2604004a30, select_lex=0x7f26040132a8) at /10.4/sql/sql_select.cc:4590
 
#21 0x000056336f2bd14b in handle_select (thd=0x7f2604000b00, lex=0x7f2604004968, result=0x7f2604018fa8, setup_tables_done_option=0) at /10.4/sql/sql_select.cc:424
 
#22 0x000056336f2831c3 in execute_sqlcom_select (thd=0x7f2604000b00, all_tables=0x7f2604018710) at /10.4/sql/sql_parse.cc:6357
 
#23 0x000056336f278774 in mysql_execute_command (thd=0x7f2604000b00) at /10.4/sql/sql_parse.cc:3899
 
#24 0x000056336f287351 in mysql_parse (thd=0x7f2604000b00, rawbuf=0x7f2604013118 "select (select 1 from t1 where (exists \n(select 1 from t2  \nwhere t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt\nfrom t3", length=149, parser_state=0x7f2651742ff0, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:7909
 
#25 0x000056336f272318 in dispatch_command (command=COM_QUERY, thd=0x7f2604000b00, packet=0x7f260419dc31 "select (select 1 from t1 where (exists \n(select 1 from t2  \nwhere t2.a = (select t4.id from t4 where t4.id = t3.id) and t2.id2 = t1.id2))) dt\nfrom t3", packet_length=149, is_com_multi=false, is_next_command=false) at /10.4/sql/sql_parse.cc:1843
 
#26 0x000056336f270936 in do_command (thd=0x7f2604000b00) at /10.4/sql/sql_parse.cc:1360
 
#27 0x000056336f3fb4af in do_handle_one_connection (connect=0x563372603e30) at /10.4/sql/sql_connect.cc:1412
 
#28 0x000056336f3fb1d8 in handle_one_connection (arg=0x563372603e30) at /10.4/sql/sql_connect.cc:1316
 
#29 0x000056336fe32941 in pfs_spawn_thread (arg=0x5633725825c0) at /10.4/storage/perfschema/pfs.cc:1862
 
#30 0x00007f265a6b66ba in start_thread (arg=0x7f2651744700) at pthread_create.c:333
 
#31 0x00007f265929941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Comment by Elena Stepanova [ 2020-02-03 ]

Test case from MDEV-21649 which produces the same or very similar stack traces:

CREATE TABLE storage_storagemedium (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
 
CREATE TABLE storage_storageobject (id INT NOT NULL AUTO_INCREMENT, ip_id INT, PRIMARY KEY(id));
 
CREATE TABLE storage_storagemethodtargetrelation (id INT NOT NULL AUTO_INCREMENT, storage_method_id INT, storage_target_id INT, PRIMARY KEY(id));
 
 
 
SELECT
 
    W0.`id`
 
FROM
 
    `storage_storagemedium` W0
 
WHERE (
 
    EXISTS(
 
        SELECT
 
            V0.`id`
 
        FROM
 
            `storage_storageobject` V0
 
        WHERE (
 
            EXISTS(
 
                SELECT
 
                    U0.`id`
 
                FROM
 
                    `storage_storageobject` U0
 
                    INNER JOIN `storage_storagemethodtargetrelation` U4 ON (U0.`id` = U4.`storage_target_id`)
 
                WHERE (
 
                        U0.`ip_id` = V0.`ip_id`
 
                        AND U4.`storage_method_id` = (
 
                            SELECT
 
                                U5.`storage_method_id`
 
                            FROM
 
                                `storage_storagemethodtargetrelation` U5
 
                            WHERE
 
                                U5.`storage_target_id` = V0.`id`
 
                            LIMIT
 
                                1
 
                        )
 
                )
 
            )
 
        )
 
    )
 
);

(reproducible with at least MyISAM and InnoDB).

Comment by Sergei Petrunia [ 2020-07-25 ]

Fixed by the fix for MDEV-23221. Added the testcase from this MDEV, too.

Generated at Thu Feb 08 09:00:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.