[#MDEV-28509] Server crash via Item_func_ne::add_key_fields in /sql/sql_bitmap.h:196, member access within null pointer of type 'struct JOIN_TAB' in add_key

[MDEV-28509] Server crash via Item_func_ne::add_key_fields in /sql/sql_bitmap.h:196, member access within null pointer of type 'struct JOIN_TAB' in add_key_field Created: 2022-05-08 Updated: 2024-02-05
Status:	In Review
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type:

Bug

Priority:

Major

Reporter:

Shihao Wen

Assignee:

Sergei Golubchik

Resolution:

Unresolved

Votes:

Labels:

UBSAN, fuzzer

Attachments:

255_stack

Issue Links:

Duplicate
is duplicated by	~~MDEV-32085~~	Server crash in add_key_field	Closed
is duplicated by	~~MDEV-32295~~	Server crashes at add_key_field	Closed
is duplicated by	~~MDEV-32422~~	Segmentation fault at /mariadb-11.3.0...	Closed
Relates
relates to	~~MDEV-22498~~	SIGSEGV in Bitmap<64u>::merge on SELECT	Closed
relates to	MDEV-29017	SIGSEGV in Bitmap<64u>::merge on SELECT	Confirmed
relates to	MDEV-22825	Server crashes in Bitmap<64u>::merge ...	Confirmed

Description

Original testcase (reduced version in comments below):

CREATE TABLE v1054 ( v1055 INT ) ;

 INSERT INTO v1054 ( v1055 ) VALUES ( 54 ) ;

 UPDATE v1054 SET v1055 = 127 WHERE v1055 = 83 ;

 INSERT INTO v1054 ( v1055 ) VALUES ( -1 ) , ( -1 ) ;

 WITH v1057 AS ( SELECT v1055 FROM ( SELECT v1055 FROM v1054 GROUP BY v1055 ) AS v1056 ) SELECT v1055 FROM v1057 WHERE v1055 BETWEEN FALSE AND ( ( ( v1055 OR NOT v1055 ) BETWEEN ( ( ( ( EXISTS ( WITH v1063 AS ( SELECT v1055 FROM ( SELECT v1055 FROM v1054 GROUP BY v1055 ) AS v1058 WINDOW v1062 AS ( PARTITION BY v1055 ORDER BY ( SELECT DISTINCT 16 FROM v1054 AS v1059 , v1054 AS v1060 , v1054 AS v1061 JOIN v1054 ) DESC RANGE BETWEEN 80808358.000000 FOLLOWING AND 82012945.000000 FOLLOWING ) ) SELECT v1055 FROM ( SELECT DISTINCT ( ( NOT ( 60914711.000000 AND v1055 = 68 ) ) = -1 AND v1055 = 17 ) % v1055 , ( v1055 = -1 OR v1055 > 'x' ) FROM v1054 WHERE v1055 = -128 AND ( v1055 = -128 OR v1055 = 0 OR v1055 = 31 ) ) AS v1064 NATURAL JOIN v1063 AS v1065 NATURAL JOIN v1063 AS v1066 NATURAL JOIN ( SELECT DISTINCT v1055 , ( v1055 = -1 OR v1055 > 'x' ) FROM v1054 ) AS v1067 NATURAL JOIN v1063 AS v1068 NATURAL JOIN v1063 WHERE v1055 != 72 GROUP BY v1055 ORDER BY v1055 ) AND v1055 = -1 ) - 2147483647 ) ) ) AND 'x' = ( 4 + 34235093.000000 <= 60 ) ) ) ;

Leads to:

10.9.0 0b14dbd45b5a1c02616d611876158d44b92b77bf (Optimized)
Core was generated by `/test/MD030522-mariadb-10.9.0-linux-x86_64-opt/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x000055daccac22da in Bitmap<64u>::merge (this=<optimized out>, map2=...)
at /test/10.9_opt/sql/sql_bitmap.h:172
[Current thread is 1 (Thread 0x14d62c400700 (LWP 357990))]
(gdb) bt
#0 0x000055daccac22da in Bitmap<64u>::merge (this=<optimized out>, map2=<optimized out>) at /test/10.9_opt/sql/sql_bitmap.h:172
#1 add_key_field (join=<optimized out>, key_fields=0x14d62c3fdd38, and_level=0, cond=0x14d5d40db680, field=0x14d5d40aef30, eq_func=<optimized out>, value=0x14d5d40db700, num_values=1, usable_tables=18446744073709551615, sargables=0x14d62c3fded8, row_col_no=0) at /test/10.9_opt/sql/sql_select.cc:6296
#2 0x000055daccac24fd in add_key_equal_fields (join=0x14d5d40a38e8, key_fields=0x14d62c3fdd38, and_level=0, cond=0x14d5d40db680, field_item=0x14d5d40db4f0, eq_func=<optimized out>, val=0x14d5d40db700, num_values=1, usable_tables=18446744073709551615, sargables=0x14d62c3fded8, row_col_no=0) at /test/10.9_opt/sql/sql_select.cc:6413
#3 0x000055daccacbd65 in Item_func_ne::add_key_fields (this=0x14d5d40db680, join=0x14d5d40a38e8, key_fields=0x14d62c3fdd38, and_level=0x14d62c3fdd34, usable_tables=18446744073709551615, sargables=0x14d62c3fded8) at /test/10.9_opt/sql/sql_select.cc:6648
#4 0x000055daccacc589 in update_ref_and_keys (thd=thd@entry=0x14d5d4000c58, keyuse=keyuse@entry=0x14d5d40a3c08, join_tab=0x14d5d40db8e0, tables=1, cond=0x14d5d40db680, normal_tables=normal_tables@entry=18446744073709551615, sargables=0x14d62c3fded8, select_lex=<optimized out>, select_lex=<optimized out>) at /test/10.9_opt/sql/sql_select.cc:7170
#5 0x000055daccb01779 in make_join_statistics (keyuse_array=0x14d5d40a3c08, tables_list=@0x14d5d405c970: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d5d40a3ea0, last = 0x14d5d40a3ea0, elements = 1}, <No data fields>}, join=0x14d5d40a38e8) at /test/10.9_opt/sql/sql_select.cc:5422
#6 JOIN::optimize_inner (this=0x14d5d40a38e8) at /test/10.9_opt/sql/sql_select.cc:2495
#7 0x000055daccb036d3 in JOIN::optimize (this=this@entry=0x14d5d40a38e8) at /test/10.9_opt/sql/sql_select.cc:1837
#8 0x000055dacca499fb in mysql_derived_optimize (thd=0x14d5d4000c58, lex=0x14d5d4004be0, derived=0x14d5d405e118) at /test/10.9_opt/sql/sql_derived.cc:1064
#9 0x000055dacca49258 in mysql_handle_single_derived (lex=0x14d5d4004be0, derived=derived@entry=0x14d5d405e118, phases=phases@entry=4) at /test/10.9_opt/sql/sql_derived.cc:200
#10 0x000055daccb0077c in JOIN::optimize_inner (this=0x14d5d40a0e50) at /test/10.9_opt/sql/sql_select.cc:2313
#11 0x000055daccb036d3 in JOIN::optimize (this=this@entry=0x14d5d40a0e50) at /test/10.9_opt/sql/sql_select.cc:1837
#12 0x000055dacca67464 in st_select_lex::optimize_unflattened_subqueries (this=0x14d5d4013c48, const_only=const_only@entry=true) at /test/10.9_opt/sql/sql_lex.cc:4916
#13 0x000055daccbe9455 in JOIN::optimize_constant_subqueries (this=this@entry=0x14d5d4072788) at /test/10.9_opt/sql/opt_subselect.cc:5622
#14 0x000055daccafff67 in JOIN::optimize_inner (this=0x14d5d4072788) at /test/10.9_opt/sql/sql_select.cc:2157
#15 0x000055daccb036d3 in JOIN::optimize (this=this@entry=0x14d5d4072788) at /test/10.9_opt/sql/sql_select.cc:1837
#16 0x000055daccb037be in mysql_select (thd=0x14d5d4000c58, tables=0x14d5d4045880, fields=@0x14d5d4013ee8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d5d40141e0, last = 0x14d5d40141e0, elements = 1}, <No data fields>}, conds=0x14d5d4059b10, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d5d4072760, unit=0x14d5d4004cb8, select_lex=0x14d5d4013c48) at /test/10.9_opt/sql/sql_select.cc:5022
#17 0x000055daccb03f57 in handle_select (thd=thd@entry=0x14d5d4000c58, lex=lex@entry=0x14d5d4004be0, result=result@entry=0x14d5d4072760, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.9_opt/sql/sql_select.cc:570
#18 0x000055dacca87a21 in execute_sqlcom_select (thd=0x14d5d4000c58, all_tables=0x14d5d4045880) at /test/10.9_opt/sql/sql_parse.cc:6271
#19 0x000055dacca95363 in mysql_execute_command (thd=0x14d5d4000c58, is_called_from_prepared_stmt=<optimized out>) at /test/10.9_opt/sql/sql_parse.cc:3961
#20 0x000055dacca82a55 in mysql_parse (rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, thd=0x14d5d4000c58) at /test/10.9_opt/sql/sql_parse.cc:8046
#21 mysql_parse (thd=0x14d5d4000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.9_opt/sql/sql_parse.cc:7968
#22 0x000055dacca8e71a in dispatch_command (command=COM_QUERY, thd=0x14d5d4000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/10.9_opt/sql/sql_class.h:1364
#23 0x000055dacca90642 in do_command (thd=0x14d5d4000c58, blocking=blocking@entry=true) at /test/10.9_opt/sql/sql_parse.cc:1408
#24 0x000055daccba55bf in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55dad0334eb8, put_in_cache=put_in_cache@entry=true) at /test/10.9_opt/sql/sql_connect.cc:1418
#25 0x000055daccba589d in handle_one_connection (arg=0x55dad0334eb8) at /test/10.9_opt/sql/sql_connect.cc:1312
#26 0x000014d645c57609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#27 0x000014d645843133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.9.0 0b14dbd45b5a1c02616d611876158d44b92b77bf (Debug)
Core was generated by `/test/MD030522-mariadb-10.9.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 Bitmap<64u>::merge (map2=..., this=<optimized out>)
at /test/10.9_dbg/sql/sql_bitmap.h:210
[Current thread is 1 (Thread 0x148c00096700 (LWP 667827))]
(gdb) bt
#0 Bitmap<64u>::merge (map2=<optimized out>, this=<optimized out>) at /test/10.9_dbg/sql/sql_bitmap.h:210
#1 add_key_field (join=join@entry=0x148b980cf378, key_fields=key_fields@entry=0x148c000942d8, and_level=and_level@entry=0, cond=cond@entry=0x148b9810ada0, field=field@entry=0x148b980dae10, eq_func=eq_func@entry=false, value=0x148b9810ae20, num_values=1, usable_tables=18446744073709551615, sargables=0x148c000943f8, row_col_no=0) at /test/10.9_dbg/sql/sql_select.cc:6296
#2 0x0000562f13415710 in add_key_equal_fields (join=join@entry=0x148b980cf378, key_fields=key_fields@entry=0x148c000942d8, and_level=0, cond=cond@entry=0x148b9810ada0, field_item=0x148b9810ac10, eq_func=eq_func@entry=false, val=0x148b9810ae20, num_values=1, usable_tables=18446744073709551615, sargables=0x148c000943f8, row_col_no=0) at /test/10.9_dbg/sql/sql_select.cc:6413
#3 0x0000562f1341f1e3 in Item_func_ne::add_key_fields (this=0x148b9810ada0, join=0x148b980cf378, key_fields=0x148c000942d8, and_level=0x148c000942d4, usable_tables=18446744073709551615, sargables=0x148c000943f8) at /test/10.9_dbg/sql/sql_select.cc:6648
#4 0x0000562f1341f93e in update_ref_and_keys (thd=thd@entry=0x148b98000db8, keyuse=keyuse@entry=0x148b980cf698, join_tab=0x148b9810b000, tables=1, cond=0x148b9810ada0, normal_tables=normal_tables@entry=18446744073709551615, select_lex=0x148b98084d30, sargables=0x148c000943f8) at /test/10.9_dbg/sql/sql_select.cc:7170
#5 0x0000562f13455b82 in make_join_statistics (join=join@entry=0x148b980cf378, tables_list=@0x148b98084f48: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x148b980cf930, last = 0x148b980cf930, elements = 1}, <No data fields>}, keyuse_array=keyuse_array@entry=0x148b980cf698) at /test/10.9_dbg/sql/sql_select.cc:5422
#6 0x0000562f1345e52c in JOIN::optimize_inner (this=this@entry=0x148b980cf378) at /test/10.9_dbg/sql/sql_select.cc:2495
#7 0x0000562f1345e96c in JOIN::optimize (this=this@entry=0x148b980cf378) at /test/10.9_dbg/sql/sql_select.cc:1837
#8 0x0000562f13381a63 in mysql_derived_optimize (thd=0x148b98000db8, lex=0x148b98004f00, derived=0x148b98086758) at /test/10.9_dbg/sql/sql_derived.cc:1064
#9 0x0000562f133811fd in mysql_handle_single_derived (lex=0x148b98004f00, derived=derived@entry=0x148b98086758, phases=phases@entry=4) at /test/10.9_dbg/sql/sql_derived.cc:200
#10 0x0000562f1345e6fd in JOIN::optimize_inner (this=this@entry=0x148b980cc840) at /test/10.9_dbg/sql/sql_select.cc:2313
#11 0x0000562f1345e96c in JOIN::optimize (this=this@entry=0x148b980cc840) at /test/10.9_dbg/sql/sql_select.cc:1837
#12 0x0000562f133a3462 in st_select_lex::optimize_unflattened_subqueries (this=0x148b98017168, const_only=const_only@entry=true) at /test/10.9_dbg/sql/sql_lex.cc:4916
#13 0x0000562f1358ff3d in JOIN::optimize_constant_subqueries (this=this@entry=0x148b9809aef0) at /test/10.9_dbg/sql/opt_subselect.cc:5622
#14 0x0000562f1345d490 in JOIN::optimize_inner (this=this@entry=0x148b9809aef0) at /test/10.9_dbg/sql/sql_select.cc:2157
#15 0x0000562f1345e96c in JOIN::optimize (this=this@entry=0x148b9809aef0) at /test/10.9_dbg/sql/sql_select.cc:1837
#16 0x0000562f1345ea5f in mysql_select (thd=thd@entry=0x148b98000db8, tables=0x148b9806dc90, fields=@0x148b98017408: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x148b98017700, last = 0x148b98017700, elements = 1}, <No data fields>}, conds=0x148b980820e8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x148b9809aec8, unit=0x148b98004fd8, select_lex=0x148b98017168) at /test/10.9_dbg/sql/sql_select.cc:5022
#17 0x0000562f1345f2a8 in handle_select (thd=thd@entry=0x148b98000db8, lex=lex@entry=0x148b98004f00, result=result@entry=0x148b9809aec8, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.9_dbg/sql/sql_select.cc:570
#18 0x0000562f133cb6c8 in execute_sqlcom_select (thd=thd@entry=0x148b98000db8, all_tables=0x148b9806dc90) at /test/10.9_dbg/sql/sql_parse.cc:6271
#19 0x0000562f133d7935 in mysql_execute_command (thd=thd@entry=0x148b98000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_parse.cc:3961
#20 0x0000562f133c567b in mysql_parse (thd=thd@entry=0x148b98000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x148c00095470) at /test/10.9_dbg/sql/sql_parse.cc:8046
#21 0x0000562f133d2f79 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x148b98000db8, packet=packet@entry=0x148b9800b699 "WITH v1057 AS ( SELECT v1055 FROM ( SELECT v1055 FROM v1054 GROUP BY v1055 ) AS v1056 ) SELECT v1055 FROM v1057 WHERE v1055 BETWEEN FALSE AND ( ( ( v1055 OR NOT v1055 ) BETWEEN ( ( ( ( EXISTS ( WITH v"..., packet_length=packet_length@entry=1048, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1364
#22 0x0000562f133d5686 in do_command (thd=0x148b98000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1408
#23 0x0000562f13532d02 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x562f171eede8, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
#24 0x0000562f1353320b in handle_one_connection (arg=0x562f171eede8) at /test/10.9_dbg/sql/sql_connect.cc:1312
#25 0x0000148c17d25609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#26 0x0000148c17911133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.3.35 (dbg), 10.3.35 (opt), 10.4.25 (dbg), 10.4.25 (opt), 10.5.16 (dbg), 10.5.16 (opt), 10.6.8 (dbg), 10.6.8 (opt), 10.7.4 (dbg), 10.7.4 (opt), 10.8.3 (dbg), 10.8.3 (opt), 10.9.0 (dbg), 10.9.0 (opt), 10.10.0 (dbg), 10.10.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.37 (dbg), 5.7.37 (opt), 8.0.28 (dbg), 8.0.28 (opt)

Comments

Comment by Alice Sherepa [ 2022-05-18 ]

CREATE TABLE t1 (i int) ;

INSERT INTO t1 VALUES (1),(2),(3) ;

WITH cte AS (SELECT i FROM (SELECT i FROM t1 GROUP BY i) dt WINDOW w AS (PARTITION BY i))

SELECT a.i

FROM cte a JOIN cte b on a.i=b.i

WHERE a.i != 5 ;

bb-10.2-release 84984b79f27399d01
220518 11:59:59 [ERROR] mysqld got signal 11 ;

Server version: 10.8.3-MariaDB-debug-log

sql/signal_handler.cc:226(handle_fatal_signal)[0x55c707662ca3]
sql/sql_bitmap.h:211(Bitmap<64u>::merge(Bitmap<64u> const&))[0x55c706a9b1dd]
sql/sql_select.cc:6282(add_key_field(JOIN, KEY_FIELD, unsigned int, Item_bool_func, Field, bool, Item, unsigned int, unsigned long long, SARGABLE_PARAM*, unsigned int))[0x55c706ec57f4]
sql/sql_select.cc:6386(add_key_equal_fields(JOIN, KEY_FIELD, unsigned int, Item_bool_func, Item, bool, Item, unsigned int, unsigned long long, SARGABLE_PARAM*, unsigned int))[0x55c706ec65a0]
sql/sql_select.cc:6621(Item_func_ne::add_key_fields(JOIN, KEY_FIELD, unsigned int, unsigned long long, SARGABLE_PARAM**))[0x55c706ec884b]
sql/sql_select.cc:7145(update_ref_and_keys(THD, st_dynamic_array, st_join_table, unsigned int, Item, unsigned long long, st_select_lex, SARGABLE_PARAM*))[0x55c706ecd7f9]
sql/sql_select.cc:5395(make_join_statistics(JOIN, List<TABLE_LIST>&, st_dynamic_array))[0x55c706ebd041]
sql/sql_select.cc:2468(JOIN::optimize_inner())[0x55c706e9f2ec]
sql/sql_select.cc:1810(JOIN::optimize())[0x55c706e98298]
sql/sql_derived.cc:1064(mysql_derived_optimize(THD, LEX, TABLE_LIST*))[0x55c706cba9d3]
sql/sql_derived.cc:200(mysql_handle_single_derived(LEX, TABLE_LIST, unsigned int))[0x55c706cb4c62]
sql/sql_select.cc:2286(JOIN::optimize_inner())[0x55c706e9d785]
sql/sql_select.cc:1810(JOIN::optimize())[0x55c706e98298]
sql/sql_select.cc:4995(mysql_select(THD, TABLE_LIST, List<Item>&, Item, unsigned int, st_order, st_order, Item, st_order, unsigned long long, select_result, st_select_lex_unit, st_select_lex))[0x55c706eb99d8]
sql/sql_select.cc:543(handle_select(THD, LEX, select_result*, unsigned long))[0x55c706e89e71]
sql/sql_parse.cc:6268(execute_sqlcom_select(THD, TABLE_LIST))[0x55c706dafac9]
sql/sql_parse.cc:3959(mysql_execute_command(THD*, bool))[0x55c706d9e6e6]
sql/sql_parse.cc:8043(mysql_parse(THD, char, unsigned int, Parser_state*))[0x55c706dbad69]
sql/sql_parse.cc:1912(dispatch_command(enum_server_command, THD, char, unsigned int, bool))[0x55c706d90fe2]
sql/sql_parse.cc:1407(do_command(THD*, bool))[0x55c706d8db46]
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x55c707238164]
sql/sql_connect.cc:1314(handle_one_connection)[0x55c7072379f0]
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55c707f0b114]
nptl/pthread_create.c:478(start_thread)[0x7f4263e08609]
addr2line: DWARF error: section .debug_info is larger than its filesize! (0x93ef57 vs 0x530ea0)
??:0(clone)[0x7f42639d9133]

Query (0x6290001092a8): WITH cte AS (SELECT i FROM (SELECT i FROM t1 GROUP BY i) dt WINDOW w AS (PARTITION BY i))
SELECT a.i
FROM cte a JOIN cte b on a.i=b.i
WHERE a.i != 5

Comment by Roel Van de Paar [ 2022-05-27 ]

WITH c AS (SELECT i FROM (SELECT i FROM (SELECT 1 AS i) AS i GROUP BY i) d WINDOW w AS (PARTITION BY i)) SELECT a.i FROM c a JOIN c b on a.i=b.i WHERE a.i!=1;

Leads to:

10.9.0 0b14dbd45b5a1c02616d611876158d44b92b77bf (Debug)
Core was generated by `/test/MD030522-mariadb-10.9.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 Bitmap<64u>::merge (map2=..., this=<optimized out>)
at /test/10.9_dbg/sql/sql_bitmap.h:210
[Current thread is 1 (Thread 0x153d241f9700 (LWP 2802637))]
(gdb) bt
#0 Bitmap<64u>::merge (map2=<optimized out>, this=<optimized out>) at /test/10.9_dbg/sql/sql_bitmap.h:210
#1 add_key_field (join=join@entry=0x153c48027070, key_fields=key_fields@entry=0x153d241f7438, and_level=and_level@entry=0, cond=cond@entry=0x153c48032790, field=field@entry=0x153c48028640, eq_func=eq_func@entry=false, value=0x153c48032810, num_values=1, usable_tables=18446744073709551615, sargables=0x153d241f7558, row_col_no=0) at /test/10.9_dbg/sql/sql_select.cc:6296
#2 0x0000561d3f7e0710 in add_key_equal_fields (join=join@entry=0x153c48027070, key_fields=key_fields@entry=0x153d241f7438, and_level=0, cond=cond@entry=0x153c48032790, field_item=0x153c48032600, eq_func=eq_func@entry=false, val=0x153c48032810, num_values=1, usable_tables=18446744073709551615, sargables=0x153d241f7558, row_col_no=0) at /test/10.9_dbg/sql/sql_select.cc:6413
#3 0x0000561d3f7ea1e3 in Item_func_ne::add_key_fields (this=0x153c48032790, join=0x153c48027070, key_fields=0x153d241f7438, and_level=0x153d241f7434, usable_tables=18446744073709551615, sargables=0x153d241f7558) at /test/10.9_dbg/sql/sql_select.cc:6648
#4 0x0000561d3f7ea93e in update_ref_and_keys (thd=thd@entry=0x153c48000db8, keyuse=keyuse@entry=0x153c48027390, join_tab=0x153c48034160, tables=1, cond=0x153c48032790, normal_tables=normal_tables@entry=18446744073709551615, select_lex=0x153c4801f660, sargables=0x153d241f7558) at /test/10.9_dbg/sql/sql_select.cc:7170
#5 0x0000561d3f820b82 in make_join_statistics (join=join@entry=0x153c48027070, tables_list=@0x153c4801f878: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153c48027628, last = 0x153c48027628, elements = 1}, <No data fields>}, keyuse_array=keyuse_array@entry=0x153c48027390) at /test/10.9_dbg/sql/sql_select.cc:5422
#6 0x0000561d3f82952c in JOIN::optimize_inner (this=this@entry=0x153c48027070) at /test/10.9_dbg/sql/sql_select.cc:2495
#7 0x0000561d3f82996c in JOIN::optimize (this=this@entry=0x153c48027070) at /test/10.9_dbg/sql/sql_select.cc:1837
#8 0x0000561d3f74ca63 in mysql_derived_optimize (thd=0x153c48000db8, lex=0x153c48004f00, derived=0x153c480237d8) at /test/10.9_dbg/sql/sql_derived.cc:1064
#9 0x0000561d3f74c1fd in mysql_handle_single_derived (lex=0x153c48004f00, derived=derived@entry=0x153c480237d8, phases=phases@entry=4) at /test/10.9_dbg/sql/sql_derived.cc:200
#10 0x0000561d3f8296fd in JOIN::optimize_inner (this=this@entry=0x153c48024da8) at /test/10.9_dbg/sql/sql_select.cc:2313
#11 0x0000561d3f82996c in JOIN::optimize (this=this@entry=0x153c48024da8) at /test/10.9_dbg/sql/sql_select.cc:1837
#12 0x0000561d3f829a5f in mysql_select (thd=thd@entry=0x153c48000db8, tables=0x153c4801ca10, fields=@0x153c4801c730: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x153c48017b48, last = 0x153c48017b48, elements = 1}, <No data fields>}, conds=0x153c4801e620, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x153c48024088, unit=0x153c48004fd8, select_lex=0x153c4801c490) at /test/10.9_dbg/sql/sql_select.cc:5022
#13 0x0000561d3f82a2a8 in handle_select (thd=thd@entry=0x153c48000db8, lex=lex@entry=0x153c48004f00, result=result@entry=0x153c48024088, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.9_dbg/sql/sql_select.cc:570
#14 0x0000561d3f7966c8 in execute_sqlcom_select (thd=thd@entry=0x153c48000db8, all_tables=0x153c4801ca10) at /test/10.9_dbg/sql/sql_parse.cc:6271
#15 0x0000561d3f7a2935 in mysql_execute_command (thd=thd@entry=0x153c48000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_parse.cc:3961
#16 0x0000561d3f79067b in mysql_parse (thd=thd@entry=0x153c48000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x153d241f8470) at /test/10.9_dbg/sql/sql_parse.cc:8046
#17 0x0000561d3f79df79 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x153c48000db8, packet=packet@entry=0x153c4800b699 "WITH c AS (SELECT i FROM (SELECT i FROM (SELECT 1 AS i) AS i GROUP BY i) d WINDOW w AS (PARTITION BY i)) SELECT a.i FROM c a JOIN c b on a.i=b.i WHERE a.i!=1", packet_length=packet_length@entry=157, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1364
#18 0x0000561d3f7a0686 in do_command (thd=0x153c48000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1408
#19 0x0000561d3f8fdd02 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x561d43255bb8, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
#20 0x0000561d3f8fe20b in handle_one_connection (arg=0x561d43255bb8) at /test/10.9_dbg/sql/sql_connect.cc:1312
#21 0x0000153d48cbf609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#22 0x0000153d488ab133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug (or feature/syntax) confirmed not present in:
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.37 (dbg), 5.7.37 (opt), 8.0.28 (dbg), 8.0.28 (opt)

Comment by Roel Van de Paar [ 2023-08-04 ]

There is also an UBSAN issue here:

WITH c AS (SELECT i FROM (SELECT i FROM (SELECT 1 AS i) AS i GROUP BY i) d WINDOW w AS (PARTITION BY i)) SELECT a.i FROM c a JOIN c b ON a.i=b.i WHERE a.i!=1;

Leads to:

11.2.0 e81fa345020ec6a067583db6a7019d6404b26f93 (Optimized)
/data/11.2_opt_san/sql/sql_select.cc:6548:15: runtime error: member access within null pointer of type 'struct JOIN_TAB'

11.2.0 e81fa345020ec6a067583db6a7019d6404b26f93 (Optimized)
#0 0x55f8e7d60b4a in add_key_field /data/11.2_opt_san/sql/sql_select.cc:6548
#1 0x55f8e7d67c2c in add_key_equal_fields /data/11.2_opt_san/sql/sql_select.cc:6669
#2 0x55f8e7de2710 in Item_func_ne::add_key_fields(JOIN, KEY_FIELD, unsigned int, unsigned long long, SARGABLE_PARAM**) /data/11.2_opt_san/sql/sql_select.cc:6904
#3 0x55f8e7de80e7 in update_ref_and_keys /data/11.2_opt_san/sql/sql_select.cc:7426
#4 0x55f8e7fbd7a7 in make_join_statistics /data/11.2_opt_san/sql/sql_select.cc:5670
#5 0x55f8e7ff2fcb in JOIN::optimize_inner() /data/11.2_opt_san/sql/sql_select.cc:2618
#6 0x55f8e7ff9225 in JOIN::optimize() /data/11.2_opt_san/sql/sql_select.cc:1944
#7 0x55f8e79386d5 in mysql_derived_optimize /data/11.2_opt_san/sql/sql_derived.cc:1037
#8 0x55f8e79348e5 in mysql_handle_single_derived(LEX, TABLE_LIST, unsigned int) /data/11.2_opt_san/sql/sql_derived.cc:200
#9 0x55f8e7ff3365 in JOIN::optimize_inner() /data/11.2_opt_san/sql/sql_select.cc:2436
#10 0x55f8e7ff9225 in JOIN::optimize() /data/11.2_opt_san/sql/sql_select.cc:1944
#11 0x55f8e7ff99c6 in mysql_select(THD, TABLE_LIST, List<Item>&, Item, unsigned int, st_order, st_order, Item, st_order, unsigned long long, select_result, st_select_lex_unit, st_select_lex) /data/11.2_opt_san/sql/sql_select.cc:5229
#12 0x55f8e7ffd890 in handle_select(THD, LEX, select_result*, unsigned long long) /data/11.2_opt_san/sql/sql_select.cc:628
#13 0x55f8e7b790a0 in execute_sqlcom_select /data/11.2_opt_san/sql/sql_parse.cc:6056
#14 0x55f8e7bdb4aa in mysql_execute_command(THD*, bool) /data/11.2_opt_san/sql/sql_parse.cc:3944
#15 0x55f8e7be9fc2 in mysql_parse(THD, char, unsigned int, Parser_state*) /data/11.2_opt_san/sql/sql_parse.cc:7800
#16 0x55f8e7bf55e5 in dispatch_command(enum_server_command, THD, char, unsigned int, bool) /data/11.2_opt_san/sql/sql_parse.cc:1892
#17 0x55f8e7c011f8 in do_command(THD*, bool) /data/11.2_opt_san/sql/sql_parse.cc:1405
#18 0x55f8e85224ac in do_handle_one_connection(CONNECT*, bool) /data/11.2_opt_san/sql/sql_connect.cc:1445
#19 0x55f8e8524aac in handle_one_connection /data/11.2_opt_san/sql/sql_connect.cc:1347
#20 0x149eff494b42 in start_thread nptl/pthread_create.c:442
#21 0x149eff5269ff (/lib/x86_64-linux-gnu/libc.so.6+0x1269ff)

Setup:

Compiled with GCC >=7.5.0 (I use GCC 11.3.0) and:

    -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_UBSAN=ON -DWITH_RAPID=OFF -DWSREP_LIB_WITH_ASAN=ON

Set before execution:

    export UBSAN_OPTIONS=print_stacktrace=1

Bug confirmed present in:
MariaDB: 10.4.31 (dbg), 10.4.31 (opt), 10.5.22 (dbg), 10.5.22 (opt), 10.6.15 (dbg), 10.6.15 (opt), 10.9.8 (dbg), 10.9.8 (opt), 10.10.6 (dbg), 10.10.6 (opt), 10.11.5 (dbg), 10.11.5 (opt), 11.0.3 (dbg), 11.0.3 (opt), 11.1.2 (dbg), 11.1.2 (opt), 11.2.0 (dbg), 11.2.0 (opt)

Comment by Alice Sherepa [ 2023-10-04 ]

test from ~~MDEV-32295~~

SELECT * FROM ( SELECT 1 x ) ss WHERE x = 1 AND x < 1 HAVING ( WITH x AS ( SELECT 1 ) SELECT x FROM x WHERE x = 1 ) ;

Version: '10.4.32-MariaDB-debug-log'

231004 11:24:33 [ERROR] mysqld got signal 11 ;

Server version: 10.4.32-MariaDB-debug-log source revision: 50a2e8b1892b6b8a276d4bd75a1a02148f9e6ff2

sql/signal_handler.cc:238(handle_fatal_signal)[0x55e63a7357e9]

sigaction.c:0(__restore_rt)[0x7ff1f94b3420]

sql/sql_bitmap.h:220(Bitmap<64u>::merge(Bitmap<64u> const&))[0x55e63a123fdd]

sql/sql_select.cc:6090(add_key_field(JOIN*, KEY_FIELD**, unsigned int, Item_bool_func*, Field*, bool, Item**, unsigned int, unsigned long long, SARGABLE_PARAM**, unsigned int))[0x55e63a05b90b]

sql/sql_select.cc:6522(Item_equal::add_key_fields(JOIN*, KEY_FIELD**, unsigned int*, unsigned long long, SARGABLE_PARAM**))[0x55e63a05fa99]

sql/sql_select.cc:6953(update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, SARGABLE_PARAM**))[0x55e63a0639af]

sql/sql_select.cc:5206(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55e63a053008]

sql/sql_select.cc:2368(JOIN::optimize_inner())[0x55e63a035c15]

sql/sql_select.cc:1711(JOIN::optimize())[0x55e63a02ec2b]

sql/sql_lex.cc:4236(st_select_lex::optimize_unflattened_subqueries(bool))[0x55e639f075f2]

sql/opt_subselect.cc:5603(JOIN::optimize_unflattened_subqueries())[0x55e63a478fa1]

sql/sql_select.cc:3165(JOIN::optimize_stage2())[0x55e63a03de38]

sql/sql_select.cc:2394(JOIN::optimize_inner())[0x55e63a035f20]

sql/sql_select.cc:1711(JOIN::optimize())[0x55e63a02ec2b]

sql/sql_select.cc:4812(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*))[0x55e63a04fc1d]

sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e63a020922]

sql/sql_parse.cc:6475(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e639f8c72c]

sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x55e639f79ea3]

sql/sql_parse.cc:8012(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e639f95c07]

sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e639f6c02d]

sql/sql_parse.cc:1378(do_command(THD*))[0x55e639f68b58]

sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55e63a3767fd]

sql/sql_connect.cc:1325(handle_one_connection)[0x55e63a3760a1]

perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55e63b02099a]

nptl/pthread_create.c:478(start_thread)[0x7ff1f94a7609]

Query (0x62b0000a1420): SELECT * FROM ( SELECT 1 x ) ss WHERE x = 1 AND x < 1 HAVING ( WITH x AS ( SELECT 1 ) SELECT x FROM x WHERE x = 1 )

Comment by Alice Sherepa [ 2023-10-04 ]

test from ~~MDEV-32085~~

CREATE TABLE t0 (id int);

SELECT t0.id

FROM t0 ra2

STRAIGHT_JOIN t0

WHERE 0

HAVING t0.id IN (SELECT t0.id FROM t0 ra4 WHERE ra4.id = t0.id) ;

Comment by Dave Gosselin [ 2023-11-01 ]

(I deleted my last comment because it was partial and I had pressed some keyboard combination by mistake which saved the comment prematurely).

The following query

SELECT * FROM ( SELECT 1 x ) ss WHERE x = 1 AND x < 1 HAVING ( WITH x AS ( SELECT 1 ) SELECT x FROM x WHERE x = 1 ) ;

would, if it weren't for the crash, return an empty result set because x cannot both be 1 and less-than 1. If we isolate the WITH clause from the HAVING clause and execute that as its own query, then we see that it has an issue:

MariaDB [(none)]> with x as (select 1) select x from x where x = 1;

ERROR 1054 (42S22): Unknown column 'x' in 'field list'

I'm still getting familiar with the code so please forgive my abuse of terms, but I suspect that we swallow some failure during the HAVING/WITH clause parsing or optimization step which leads to this crash. If we take the first query and change the WITH clause to include a column name for the constant, then we get an empty set as expected:

MariaDB [(none)]> SELECT * FROM ( SELECT 1 x ) ss WHERE x = 1 AND x < 1 HAVING ( WITH x AS ( SELECT 1 x ) SELECT x FROM x WHERE x

= 1 ) ;

Empty set (0.001 sec)

This may not be the only problem at play as other example queries in earlier comments fail yet they lack a select constant.

Comment by Sergei Petrunia [ 2023-11-09 ]

Note that in this example, the crash goes away if I disable Condition Pushdown into Derived:

-- This makes the crash go away:

set optimizer_switch='condition_pushdown_for_derived=off';

WITH cte AS (

  SELECT i

  FROM

     (SELECT i FROM t1 GROUP BY i) dt

  WINDOW w AS (PARTITION BY i)

SELECT a.i

FROM

  cte a

  JOIN cte b on a.i=b.i

WHERE a.i != 5;

+------+

| i    |

+------+

|    1 |

|    2 |

|    3 |

+------+

Comment by Sergei Petrunia [ 2023-11-09 ]

A side observation: the clause

 WINDOW w AS (PARTITION BY i)

seems to be useless. Why does removing it change the way the query is executed?

Comment by Dave Gosselin [ 2023-11-28 ]

I alerted Sergei that my fix was not correct, so I'm reassigning the bug to myself for further work.

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

[MDEV-28509] Server crash via Item_func_ne::add_key_fields in /sql/sql_bitmap.h:196, member access within null pointer of type 'struct JOIN_TAB' in add_key_field Created: 2022-05-08 Updated: 2024-02-05