[MDEV-32717] eliminate_item_equal, sjm: SEGV at /mariadb-11.3.0/sql/sql_select.cc:18075 Created: 2023-11-07  Updated: 2023-12-04

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - CTE, Server
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: Xin Wen Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 20.04


 Description   

Run these queries in debug build:

CREATE TABLE x ( x INT ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
UPDATE x SET x = 1 WHERE x = 1 ;
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;
WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT x + 1 FROM x ) SELECT x , x FROM x GROUP BY CASE 1 WHEN 1 THEN x = ( SELECT x FROM x WHERE CASE x WHEN 1 THEN 1 ELSE x = 'x' AND CASE WHEN x > 1 THEN 1 / 1 ELSE 1 END IN ( SELECT x FROM x WHERE x = 'x' ) END != 1 IN ( x = 'x' AND x IN ( SELECT x FROM x WHERE ( ( x = 1 OR x = 1 ) ) = 'x' ) , x + 'x' ) ) WHEN 1 THEN 1 ELSE 1 / 1 END HAVING x IN ( 1 , 1 , 1 , x IN ( WITH RECURSIVE x AS ( WITH RECURSIVE x AS ( SELECT 1 ) SELECT * FROM x ) SELECT x FROM x WHERE x = 1 ^ 1 + 'x' + 1 ^ 1.000000 + 1 ^ 1.000000 + ( SELECT x FROM x AS x WHERE ( SELECT DISTINCT x WHERE x BETWEEN ( SELECT x AS x FROM x AS x GROUP BY x HAVING x ) AND 1 ) IN ( SELECT DISTINCT x FROM x WHERE x = ( x = 1 OR x > 'x' ) NOT LIKE 'x' ) AND x = 'x' GROUP BY x ) BETWEEN 1 AND 1 ) ) ORDER BY x , x ;

Will trigger Segmentation fault.
GDB info:
#0 0x00005555573368f5 in embedding_sjm (item=0x62f000013d98) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:18075
#1 0x0000555557337230 in eliminate_item_equal (thd=0x62c0001d0288, cond=0x0, upper_levels=0x0, item_equal=0x631000054060) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:18191
#2 0x0000555557338343 in substitute_for_best_equal_field (thd=0x62c0001d0288, context_tab=0x1, cond=0x631000053cc0, cond_equal=0x631000053d98, table_join_idx=0x631000054d80, do_substitution=true) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:18437
#3 0x00005555572c4538 in JOIN::optimize_stage2 (this=0x62f00000abb0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2774
#4 0x00005555572c2f34 in JOIN::optimize_inner (this=0x62f00000abb0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2650
#5 0x00005555572bbba6 in JOIN::optimize (this=0x62f00000abb0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#6 0x0000555557143851 in st_select_lex::optimize_unflattened_subqueries (this=0x629000179c30, const_only=false) at /home/wx/mariadb-11.3.0/sql/sql_lex.cc:4916
#7 0x00005555577b7509 in JOIN::optimize_unflattened_subqueries (this=0x62f0000089f8) at /home/wx/mariadb-11.3.0/sql/opt_subselect.cc:5864
#8 0x00005555572c91e7 in JOIN::optimize_stage2 (this=0x62f0000089f8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:3229
#9 0x00005555572c2f34 in JOIN::optimize_inner (this=0x62f0000089f8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2650
#10 0x00005555572bbba6 in JOIN::optimize (this=0x62f0000089f8) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#11 0x0000555557143851 in st_select_lex::optimize_unflattened_subqueries (this=0x6290000f7d30, const_only=false) at /home/wx/mariadb-11.3.0/sql/sql_lex.cc:4916
#12 0x00005555577b7509 in JOIN::optimize_unflattened_subqueries (this=0x62d000092248) at /home/wx/mariadb-11.3.0/sql/opt_subselect.cc:5864
#13 0x00005555572c91e7 in JOIN::optimize_stage2 (this=0x62d000092248) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:3229
#14 0x00005555572c2f34 in JOIN::optimize_inner (this=0x62d000092248) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:2650
#15 0x00005555572bbba6 in JOIN::optimize (this=0x62d000092248) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:1944
#16 0x00005555572dd9ba in mysql_select (thd=0x62c0001d0288, tables=0x6290000f84e8, fields=..., conds=0x0, og_num=3, order=0x62d000073a48, group=0x629000175fc0, having=0x62d000072ec0, proc_param=0x0, select_options=2165049856, result=0x62d000092218, unit=0x62c0001d46d8, select_lex=0x6290000f7d30) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:5235
#17 0x00005555572ad18a in handle_select (thd=0x62c0001d0288, lex=0x62c0001d45f8, result=0x62d000092218, setup_tables_done_option=0) at /home/wx/mariadb-11.3.0/sql/sql_select.cc:628
#18 0x00005555571ce583 in execute_sqlcom_select (thd=0x62c0001d0288, all_tables=0x6290000f84e8) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:6013
#19 0x00005555571becf6 in mysql_execute_command (thd=0x62c0001d0288, is_called_from_prepared_stmt=false) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:3912
#20 0x00005555571d95e2 in mysql_parse (thd=0x62c0001d0288, rawbuf=0x6290000f52a8 "WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT x + 1 FROM x ) SELECT x , x FROM x GROUP BY CASE 1 WHEN 1 THEN x = ( SELECT x FROM x WHERE CASE x WHEN 1 THEN 1 ELSE x = 'x' AND CASE WHEN x > 1 THEN 1 / 1 ELS"..., length=808, parser_state=0x7fffd192e870) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:7734
#21 0x00005555571b1237 in dispatch_command (command=COM_QUERY, thd=0x62c0001d0288, packet=0x6290000fa289 " WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT x + 1 FROM x ) SELECT x , x FROM x GROUP BY CASE 1 WHEN 1 THEN x = ( SELECT x FROM x WHERE CASE x WHEN 1 THEN 1 ELSE x = 'x' AND CASE WHEN x > 1 THEN 1 / 1 EL"..., packet_length=812, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1893
#22 0x00005555571adf7c in do_command (thd=0x62c0001d0288, blocking=true) at /home/wx/mariadb-11.3.0/sql/sql_parse.cc:1406
#23 0x000055555768e557 in do_handle_one_connection (connect=0x61100007d348, put_in_cache=true) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1445
#24 0x000055555768deb4 in handle_one_connection (arg=0x61100007d208) at /home/wx/mariadb-11.3.0/sql/sql_connect.cc:1347
#25 0x00005555582fa350 in pfs_spawn_thread (arg=0x618000005108) at /home/wx/mariadb-11.3.0/storage/perfschema/pfs.cc:2201
#26 0x00007ffff7115609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#27 0x00007ffff6ce8133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

 Comments   
Comment by Alice Sherepa [ 2023-11-07 ]

Thanks!
I repeated as described on 10.4-11.2

Version: '10.4.32-MariaDB-debug-log'  
231107 18:01:13 [ERROR] mysqld got signal 11 ;
 
 
 
 
 
Server version: 10.4.32-MariaDB-debug-log source revision: e5a5573f782723e40c372f38346a60b1ccc644d6
 
 
 
sql/signal_handler.cc:235(handle_fatal_signal)[0x563f5f2a6615]
 
sigaction.c:0(__restore_rt)[0x7f2f38f96420]
 
sql/sql_select.cc:15894(embedding_sjm(Item*))[0x563f5ec0d692]
 
sql/sql_select.cc:16010(eliminate_item_equal(THD*, Item*, COND_EQUAL*, Item_equal*))[0x563f5ec0e008]
 
sql/sql_select.cc:16256(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x563f5ec0f1e9]
 
sql/sql_select.cc:2533(JOIN::optimize_stage2())[0x563f5eba6a7e]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x563f5eba53c2]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x563f5eb9e0cd]
 
sql/sql_lex.cc:4233(st_select_lex::optimize_unflattened_subqueries(bool))[0x563f5ea75c76]
 
sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x563f5efea595]
 
sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x563f5ebab44b]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x563f5eba53c2]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x563f5eb9e0cd]
 
sql/sql_lex.cc:4233(st_select_lex::optimize_unflattened_subqueries(bool))[0x563f5ea75c76]
 
sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x563f5efea595]
 
sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x563f5ebab44b]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x563f5eba53c2]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x563f5eb9e0cd]
 
sql/sql_select.cc:4832(mysql_select(THD*, 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*))[0x563f5ebbf0b7]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x563f5eb8fb1e]
 
sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x563f5eafb886]
 
sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x563f5eae8ffd]
 
sql/sql_parse.cc:8013(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x563f5eb04dc5]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x563f5eadb187]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x563f5ead7cb2]
 
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x563f5eee75b5]
 
sql/sql_connect.cc:1325(handle_one_connection)[0x563f5eee6e59]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x563f5fb8347e]
 
nptl/pthread_create.c:478(start_thread)[0x7f2f38f8a609]
 
 
 
Query (0x62b0000a1290): WITH x ( x ) AS ( SELECT 1 EXCEPT SELECT x + 1 FROM x ) SELECT x , x FROM x GROUP BY CASE 1 WHEN 1 THEN x = ( SELECT x FROM x WHERE CASE x WHEN 1 THEN 1 ELSE x = 'x' AND CASE WHEN x > 1 THEN 1 / 1 ELSE 1 END IN ( SELECT x FROM x WHERE x = 'x' ) END != 1 IN ( x = 'x' AND x IN ( SELECT x FROM x WHERE ( ( x = 1 OR x = 1 ) ) = 'x' ) , x + 'x' ) ) WHEN 1 THEN 1 ELSE 1 / 1 END HAVING x IN ( 1 , 1 , 1 , x IN ( WITH RECURSIVE x AS ( WITH RECURSIVE x AS ( SELECT 1 ) SELECT * FROM x ) SELECT x FROM x WHERE x = 1 ^ 1 + 'x' + 1 ^ 1.000000 + 1 ^ 1.000000 + ( SELECT x FROM x AS x WHERE ( SELECT DISTINCT x WHERE x BETWEEN ( SELECT x AS x FROM x AS x GROUP BY x HAVING x ) AND 1 ) IN ( SELECT DISTINCT x FROM x WHERE x = ( x = 1 OR x > 'x' ) NOT LIKE 'x' ) AND x = 'x' GROUP BY x ) BETWEEN 1 AND 1 ) ) ORDER BY x , x


CREATE TABLE x ( x INT ) ;
 
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) , ( 1 ) ;
 
 
 
WITH x AS ( SELECT 1 x union SELECT x + 1 FROM x )
 
 
 
SELECT x FROM x 
HAVING x IN  ( WITH x AS ( SELECT 1 ) 
 	SELECT x FROM x WHERE x =  ( SELECT x FROM x WHERE  x in ( SELECT x FROM x WHERE x LIKE 'x' ) AND x = 'x' ) )  ;


231204 15:10:05 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.4.33-MariaDB-debug-log source revision: d8e6bb00888b1f82c031938f4c8ac5d97f6874c3
 
 
 
sql/signal_handler.cc:235(handle_fatal_signal)[0x55b136ad2fd1]
 
sigaction.c:0(__restore_rt)[0x7f90695f9420]
 
sql/sql_select.cc:15894(embedding_sjm(Item*))[0x55b136438c6c]
 
sql/sql_select.cc:16010(eliminate_item_equal(THD*, Item*, COND_EQUAL*, Item_equal*))[0x55b1364395e2]
 
sql/sql_select.cc:16256(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x55b13643a7c3]
 
sql/sql_select.cc:2533(JOIN::optimize_stage2())[0x55b1363d2058]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55b1363d099c]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x55b1363c96a7]
 
sql/sql_lex.cc:4344(st_select_lex::optimize_unflattened_subqueries(bool))[0x55b13629a6b8]
 
sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x55b1368162d3]
 
sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x55b1363d6a25]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55b1363d099c]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x55b1363c96a7]
 
sql/sql_lex.cc:4344(st_select_lex::optimize_unflattened_subqueries(bool))[0x55b13629a6b8]
 
sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x55b1368162d3]
 
sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x55b1363d6a25]
 
sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55b1363d099c]
 
sql/sql_select.cc:1731(JOIN::optimize())[0x55b1363c96a7]
 
sql/sql_select.cc:4832(mysql_select(THD*, 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*))[0x55b1363ea691]
 
sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55b1363bb0f8]
 
sql/sql_parse.cc:6523(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55b1363220fd]
 
sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55b13630f71d]
 
sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55b13632b679]
 
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55b136301825]
 
sql/sql_parse.cc:1378(do_command(THD*))[0x55b1362fe350]
 
sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55b136713420]
 
sql/sql_connect.cc:1324(handle_one_connection)[0x55b136712cc4]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55b1373b12d4]
 
nptl/pthread_create.c:478(start_thread)[0x7f90695ed609]
 
 
 
Query (0x62b000062290): WITH x AS ( SELECT 1 x union SELECT x + 1 FROM x )
 
SELECT x FROM x 
HAVING x IN  ( WITH x AS ( SELECT 1 ) 
SELECT x FROM x WHERE x =  ( SELECT x FROM x WHERE  x in ( SELECT x FROM x WHERE x LIKE 'x' ) AND x = 'x' ) )

Generated at Thu Feb 08 10:33:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.