Roel Van de Paar added a comment - 2022-08-12 22:30 psergei Hi! Can you please check if this issue is in optimizer code or in name resolution? Thank you

Roel Van de Paar added a comment - 2022-12-17 03:39 Additional stack (in optimized builds) seen with this testcase: CREATE TABLE t (c TEXT) ENGINE=InnoDB; SET SESSION query_alloc_block_size=1; SELECT * FROM t WHERE c=1 AND (3=0 OR ( SELECT c=1 OR ( SELECT 3 WHERE c=c)=3)); Leads to: 10.11.2 c194db34d93d8d94bd52b17349063fa401e3f942 (Optimized) Core was generated by `/test/MD171222-mariadb-10.11.2-linux-x86_64-opt/bin/mysqld --no-defaults --core'. Program terminated with signal SIGSEGV, Segmentation fault. #0 0x000055871cc03b10 in Item_cond::remove_eq_conds (this=0x14f0380139b0, thd= 0x14f038000c58, cond_value=0x14f038048210, top_level_arg=<optimized out>) at /test/10.11_opt/sql/sql_select.cc:18634 18634 equality->upper_levels= cond_equal->upper_levels; [Current thread is 1 (Thread 0x14f06ebbc700 (LWP 1376172))] (gdb) bt #0 0x000055871cc03b10 in Item_cond::remove_eq_conds (this=0x14f0380139b0, thd=0x14f038000c58, cond_value=0x14f038048210, top_level_arg=<optimized out>) at /test/10.11_opt/sql/sql_select.cc:18634 #1 0x000055871cbee889 in optimize_cond (join=<optimized out>, conds=0x14f0380139b0, join_list=0x14f038017428, ignore_on_conds=<optimized out>, cond_value=0x14f038048210, cond_equal=0x14f038048338, flags=1) at /test/10.11_opt/sql/sql_select.cc:18239 #2 0x000055871cc26aa8 in JOIN::optimize_inner (this=0x14f038047e90) at /test/10.11_opt/sql/sql_select.cc:2261 #3 0x000055871cc29fd0 in JOIN::optimize (this=this@entry=0x14f038047e90) at /test/10.11_opt/sql/sql_select.cc:1870 #4 0x000055871cc2a0be in mysql_select (thd=0x14f038000c58, tables=0x14f038017720, fields=@0x14f038017510: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14f03801ae70, last = 0x14f03801ae70, elements = 1}, <No data fields>}, conds=0x14f0380139b0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14f038013c10, unit=0x14f038004cd8, select_lex=0x14f038017270) at /test/10.11_opt/sql/sql_select.cc:5066 #5 0x000055871cc2a857 in handle_select (thd=thd@entry=0x14f038000c58, lex=lex@entry=0x14f038004c00, result=result@entry=0x14f038013c10, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.11_opt/sql/sql_select.cc:581 #6 0x000055871cbabbe1 in execute_sqlcom_select (thd=0x14f038000c58, all_tables=0x14f038017720) at /test/10.11_opt/sql/sql_parse.cc:6265 #7 0x000055871cbb9748 in mysql_execute_command (thd=0x14f038000c58, is_called_from_prepared_stmt=<optimized out>) at /test/10.11_opt/sql/sql_parse.cc:3949 #8 0x000055871cba6c25 in mysql_parse (rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, thd=0x14f038000c58) at /test/10.11_opt/sql/sql_parse.cc:8000 #9 mysql_parse (thd=0x14f038000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.11_opt/sql/sql_parse.cc:7922 #10 0x000055871cbb296a in dispatch_command (command=COM_QUERY, thd=0x14f038000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/10.11_opt/sql/sql_class.h:1346 #11 0x000055871cbb4752 in do_command (thd=0x14f038000c58, blocking=blocking@entry=true) at /test/10.11_opt/sql/sql_parse.cc:1407 #12 0x000055871cccdf7f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55871f7062b8, put_in_cache=put_in_cache@entry=true) at /test/10.11_opt/sql/sql_connect.cc:1416 #13 0x000055871ccce25d in handle_one_connection (arg=0x55871f7062b8) at /test/10.11_opt/sql/sql_connect.cc:1318 #14 0x000014f08b835609 in start_thread (arg=<optimized out>) at pthread_create.c:477 #15 0x000014f08b421133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 Present in 10.4 to 10.11. This testcase also produces an downcast of address UBSAN error in Item_cond::remove_eq_conds : 10.11.0 fe1f8f2c6b6f3b8e3383168225f9ae7853028947 (Optimized, UBASAN) /test/10.11_opt_san/sql/sql_select.cc:18463:33: runtime error: downcast of address 0x61d0000538f8 which does not point to an object of type 'Item_cond_and' 0x61d0000538f8: note: object is of type 'Item_cond_or' be be be be 18 e9 fd 8b ae 55 00 00 01 00 00 00 00 00 00 be 20 49 5b 93 ae 55 00 00 05 00 00 00 ^~~~~~~~~~~~~~~~~~~~~~~ vptr for 'Item_cond_or' #0 0x55ae872070d0 in Item_cond::remove_eq_conds(THD*, Item::cond_result*, bool) /test/10.11_opt_san/sql/sql_select.cc:18463 #1 0x55ae87182da7 in optimize_cond /test/10.11_opt_san/sql/sql_select.cc:18161 #2 0x55ae8733bc28 in JOIN::optimize_inner() /test/10.11_opt_san/sql/sql_select.cc:2251 #3 0x55ae87367f6f in JOIN::optimize() /test/10.11_opt_san/sql/sql_select.cc:1863 #4 0x55ae873791fa 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*) /test/10.11_opt_san/sql/sql_select.cc:5056 #5 0x55ae8737d093 in handle_select(THD*, LEX*, select_result*, unsigned long) /test/10.11_opt_san/sql/sql_select.cc:581 #6 0x55ae86f8ac2f in execute_sqlcom_select /test/10.11_opt_san/sql/sql_parse.cc:6261 #7 0x55ae86fdb53b in mysql_execute_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:3945 #8 0x55ae86f5b500 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /test/10.11_opt_san/sql/sql_parse.cc:8035 #9 0x55ae86fb00ff in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /test/10.11_opt_san/sql/sql_parse.cc:1894 #10 0x55ae86fbb3fd in do_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:1407 #11 0x55ae878a44cd in do_handle_one_connection(CONNECT*, bool) /test/10.11_opt_san/sql/sql_connect.cc:1418 #12 0x55ae878a6b3c in handle_one_connection /test/10.11_opt_san/sql/sql_connect.cc:1312 #13 0x15359a867608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477 #14 0x153599adc132 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x11f132) As well as ASAN use-after-poison in base_list::operator error: 10.11.0 fe1f8f2c6b6f3b8e3383168225f9ae7853028947 (Optimized, UBASAN) ==1592160==ERROR: AddressSanitizer: use-after-poison on address 0x61d0000539e0 at pc 0x55ae872064ee bp 0x153577857fc0 sp 0x153577857fb0 READ of size 8 at 0x61d0000539e0 thread T15 #0 0x55ae872064ed in base_list::operator=(base_list const&) /test/10.11_opt_san/sql/sql_list.h:155 #1 0x55ae872064ed in base_list::append(base_list*) /test/10.11_opt_san/sql/sql_list.h:235 #2 0x55ae872064ed in List<Item_equal>::append(List<Item_equal>*) /test/10.11_opt_san/sql/sql_list.h:512 #3 0x55ae872064ed in Item_cond::remove_eq_conds(THD*, Item::cond_result*, bool) /test/10.11_opt_san/sql/sql_select.cc:18473 #4 0x55ae87182da7 in optimize_cond /test/10.11_opt_san/sql/sql_select.cc:18161 #5 0x55ae8733bc28 in JOIN::optimize_inner() /test/10.11_opt_san/sql/sql_select.cc:2251 #6 0x55ae87367f6f in JOIN::optimize() /test/10.11_opt_san/sql/sql_select.cc:1863 #7 0x55ae873791fa 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*) /test/10.11_opt_san/sql/sql_select.cc:5056 #8 0x55ae8737d093 in handle_select(THD*, LEX*, select_result*, unsigned long) /test/10.11_opt_san/sql/sql_select.cc:581 #9 0x55ae86f8ac2f in execute_sqlcom_select /test/10.11_opt_san/sql/sql_parse.cc:6261 #10 0x55ae86fdb53b in mysql_execute_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:3945 #11 0x55ae86f5b500 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /test/10.11_opt_san/sql/sql_parse.cc:8035 #12 0x55ae86fb00ff in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /test/10.11_opt_san/sql/sql_parse.cc:1894 #13 0x55ae86fbb3fd in do_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:1407 #14 0x55ae878a44cd in do_handle_one_connection(CONNECT*, bool) /test/10.11_opt_san/sql/sql_connect.cc:1418 #15 0x55ae878a6b3c in handle_one_connection /test/10.11_opt_san/sql/sql_connect.cc:1312 #16 0x15359a867608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477 #17 0x153599adc132 in __clone (/lib/x86_64-linux-gnu/libc.so.6+0x11f132) 0x61d0000539e0 is located 1888 bytes inside of 2064-byte region [0x61d000053280,0x61d000053a90) allocated by thread T15 here: #0 0x55ae8672fa08 in __interceptor_malloc (/test/UBASAN_MD010922-mariadb-10.11.0-linux-x86_64-opt/bin/mariadbd+0x79c3a08) #1 0x55ae8ac1f824 in my_malloc /test/10.11_opt_san/mysys/my_malloc.c:90 #2 0x55ae8abfc070 in root_alloc /test/10.11_opt_san/mysys/my_alloc.c:66 #3 0x55ae8abfc070 in alloc_root /test/10.11_opt_san/mysys/my_alloc.c:332 #4 0x55ae867a89eb in Item::operator new(unsigned long, st_mem_root*) /test/10.11_opt_san/sql/item.h:854 #5 0x55ae886fd9fe in Eq_creator::create(THD*, Item*, Item*) const /test/10.11_opt_san/sql/item_cmpfunc.cc:7485 #6 0x55ae87f919cf in MYSQLparse(THD*) /test/10.11_opt_san/sql/sql_yacc.yy:9190 #7 0x55ae86f84074 in parse_sql(THD*, Parser_state*, Object_creation_ctx*, bool) /test/10.11_opt_san/sql/sql_parse.cc:10426 #8 0x55ae86f5ac6e in mysql_parse(THD*, char*, unsigned int, Parser_state*) /test/10.11_opt_san/sql/sql_parse.cc:7987 #9 0x55ae86fb00ff in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /test/10.11_opt_san/sql/sql_parse.cc:1894 #10 0x55ae86fbb3fd in do_command(THD*, bool) /test/10.11_opt_san/sql/sql_parse.cc:1407 #11 0x55ae878a44cd in do_handle_one_connection(CONNECT*, bool) /test/10.11_opt_san/sql/sql_connect.cc:1418 #12 0x55ae878a6b3c in handle_one_connection /test/10.11_opt_san/sql/sql_connect.cc:1312 #13 0x15359a867608 in start_thread /build/glibc-SzIz7B/glibc-2.31/nptl/pthread_create.c:477   Thread T15 created by T0 here: #0 0x55ae8665ca45 in pthread_create (/test/UBASAN_MD010922-mariadb-10.11.0-linux-x86_64-opt/bin/mariadbd+0x78f0a45) #1 0x55ae8677ff83 in create_thread_to_handle_connection(CONNECT*) /test/10.11_opt_san/sql/mysqld.cc:6018 #2 0x55ae8679100f in handle_accepted_socket(st_mysql_socket, st_mysql_socket) /test/10.11_opt_san/sql/mysqld.cc:6139 #3 0x55ae86792037 in handle_connections_sockets() /test/10.11_opt_san/sql/mysqld.cc:6263 #4 0x55ae86794f94 in mysqld_main(int, char**) /test/10.11_opt_san/sql/mysqld.cc:5913 #5 0x1535999e1082 in __libc_start_main ../csu/libc-start.c:308   SUMMARY: AddressSanitizer: use-after-poison /test/10.11_opt_san/sql/sql_list.h:155 in base_list::operator=(base_list const&) Shadow bytes around the buggy address: 0x0c3a800026e0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0x0c3a800026f0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0x0c3a80002700: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0x0c3a80002710: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 f7 00 0x0c3a80002720: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 =>0x0c3a80002730: 00 00 00 00 00 00 00 00 00 f7 00 00[f7]00 00 00 0x0c3a80002740: 00 00 00 00 00 00 00 00 f7 00 00 f7 00 00 00 00 0x0c3a80002750: 00 00 fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c3a80002760: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c3a80002770: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa 0x0c3a80002780: fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa fa Shadow byte legend (one shadow byte represents 8 application bytes): Addressable: 00 Partially addressable: 01 02 03 04 05 06 07 Heap left redzone: fa Freed heap region: fd Stack left redzone: f1 Stack mid redzone: f2 Stack right redzone: f3 Stack after return: f5 Stack use after scope: f8 Global redzone: f9 Global init order: f6 Poisoned by user: f7 Container overflow: fc Array cookie: ac Intra object redzone: bb ASan internal: fe Left alloca redzone: ca Right alloca redzone: cb Shadow gap: cc ==1592160==ABORTING

Roel Van de Paar added a comment - 2022-12-17 03:46 The original testcase also produces the same UBSAN + ASAN issues.

Oleg Smirnov added a comment - 2023-01-10 14:12 - edited Item_cond::remove_eq_conds() while ((item=li++)) { Item *new_item= item->remove_eq_conds(thd, &tmp_cond_value, false); if (!new_item) { /* This can happen only when item is converted to TRUE or FALSE */ li.remove(); } else if (item != new_item) { if (new_item->type() == Item::COND_ITEM && item->type() == Item::COND_ITEM) { DBUG_ASSERT(functype() == ((Item_cond *) new_item)->functype()); List<Item> *new_item_arg_list= ((Item_cond *) new_item)->argument_list(); if (and_level) { /* If new_item is an AND formula then multiple equalities of new_item_arg_list must merged into multiple equalities of cond_arg_list. */ DBUG_ASSERT(functype() == ((Item_cond *) new_item)->functype()) was initially introduced with the commit: commit fa7f677218ae5ed38ef37b7b61140a069c1f8a44 (HEAD) Author: Igor Babaev <igor@askmonty.org> Date: Thu Aug 15 14:16:16 2013 -0700   Fixed bug mdev-4355. This patch almost totally revised the patch for bug mdev-4177. The latter had too many defects. In particular, it did not propagate multiple equalities formed when merging a degenerate disjunct into underlying AND formula. The purpose of this assert is not clear since Item_cond (this) and new_item may have different functype()'s: Item_cond_and and Item_cond_or (or vice versa). It looks more like a condition for merging new_item into the Item_cond's arguments. igor , can you review the fix in branch bb-10.4- MDEV-29294 ? It was suggested by Monty during the optimizer team call

Sergei Petrunia added a comment - 2023-01-13 11:49 Putting a breakpoint in optimize_cond(), this call: => conds= conds->remove_eq_conds(thd, cond_value, true ); Inside this call: (gdb) p dbug_print_item(this) $58 = 0x555557a549e0 <dbug_item_print_buf> "(3 = 0 or multiple equal(1, t.c) or (subquery#3) = 3) and multiple equal(1, t.c)" Looks like "(x OR y OR z) AND multiple-equal" but it is not: (gdb) p this->list.elem(0) $103 = (Item_cond_or *) 0x7fffb801bbc0   (gdb) p this->list.elem(1) $104 = (Item_equal *) 0x7fffb8987658 Ok so far ... (gdb) p dbug_print_item(this->list.elem(0)) $105 = 0x555557a549e0 <dbug_item_print_buf> "3 = 0 or multiple equal(1, t.c) or (subquery#3) = 3" Is this a 3-way OR? Nope: (gdb) p this->list.elem(0)->list.elements $106 = 2 (gdb) p this->list.elem(0)->list.elem(0) $107 = (Item_func_eq *) 0x7fffb8018fc8 (gdb) p this->list.elem(0)->list.elem(1) $108 = (Item_cond_or *) 0x7fffb801b050 It's a two-way OR. Which has another OR inside it. Which is not allowed! More details: (gdb) p dbug_print_item(this->list.elem(0)->list.elem(0)) $109 = 0x555557a549e0 <dbug_item_print_buf> "3 = 0" (gdb) p dbug_print_item(this->list.elem(0)->list.elem(1)) $110 = 0x555557a549e0 <dbug_item_print_buf> "multiple equal(1, t.c) or (subquery#3) = 3" That is, what we actually have is: ( 3 = 0 OR ( multiple equal(1, t.c) OR (subquery#3) = 3 ) )

Sergei Petrunia added a comment - 2023-01-13 12:13 Looking at the original query: WHERE c = 1 AND ( 3 = 0 OR (SELECT c = 1 OR (SELECT 3 WHERE c = c ) = 3)); The WHERE has form: " c=1 AND (...) ". The right part of that condition is: 3 = 0 OR (SELECT c = 1 OR (SELECT 3 WHERE c = c ) = 3) Line #3 is a degenerate subquery in form "(SELECT foo)" which gets rewritten to just "foo": 3 = 0 OR ( c = 1 OR (SELECT 3 WHERE c = c ) = 3 ) And this is how we get OR-inside-another-OR. Could it be that this bug is caused by the code that does the "(SELECT foo)" -> "foo" rewrite? It does the rewrite and accidentally creates OR-inside-OR...

Sergei Petrunia added a comment - 2023-01-13 12:37 The rewrite in question happens in Item_singlerow_subselect::select_transformer(). Item_subselect::fix_fields( ..., Item *ref) puts an Item_cond_or into *ref. Then, here: #0 Item_cond::fix_fields (this=0x7fff8401a8f0, thd=0x7fff84000d78, ref=0x7fff8401ab00) at /home/psergey/dev-git2/10.6-fix/sql/item_cmpfunc.cc:4974 #1 0x0000555555d8c196 in Item::fix_fields_if_needed (this=0x7fff8401a8f0, thd=0x7fff84000d78, ref=0x7fff8401ab00) at /home/psergey/dev-git2/10.6-fix/sql/item.h:1147 #2 0x0000555555d8c1c3 in Item::fix_fields_if_needed_for_scalar (this=0x7fff8401a8f0, thd=0x7fff84000d78, ref=0x7fff8401ab00) at /home/psergey/dev-git2/10.6-fix/sql/item.h:1156 #3 0x0000555555e154b3 in Item::fix_fields_if_needed_for_bool (this=0x7fff8401a8f0, thd=0x7fff84000d78, ref=0x7fff8401ab00) at /home/psergey/dev-git2/10.6-fix/sql/item.h:1160 #4 0x00005555562a4712 in Item_cond::fix_fields (this=0x7fff8401a9e8, thd=0x7fff84000d78, ref=0x7fff8401b8d8) at /home/psergey/dev-git2/10.6-fix/sql/item_cmpfunc.cc:4971 #5 0x0000555555d8c196 in Item::fix_fields_if_needed (this=0x7fff8401a9e8, thd=0x7fff84000d78, ref=0x7fff8401b8d8) at /home/psergey/dev-git2/10.6-fix/sql/item.h:1147 #6 0x0000555555d8c1c3 in Item::fix_fields_if_needed_for_scalar (this=0x7fff8401a9e8, thd=0x7fff84000d78, ref=0x7fff8401b8d8) at /home/psergey/dev-git2/10.6-fix/sql/item.h:1156 #7 0x0000555555e154b3 in Item::fix_fields_if_needed_for_bool (this=0x7fff8401a9e8, thd=0x7fff84000d78, ref=0x7fff8401b8d8) at /home/psergey/dev-git2/10.6-fix/sql/item.h:1160 #8 0x0000555555e11bd7 in setup_conds (thd=0x7fff84000d78, tables=0x7fff84017110, leaves=..., conds=0x7fff8401b8d8) at /home/psergey/dev-git2/10.6-fix/sql/sql_base.cc:8542 #9 0x0000555555efa758 in setup_without_group (thd=0x7fff84000d78, ref_pointer_array=..., tables=0x7fff84017110, leaves=..., fields=..., all_fields=..., conds=0x7fff8401b8d8, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff8401b7af, reserved=0x7fff84016eec) at /home/psergey/dev-git2/10.6-fix/sql/sql_select.cc:857 we have: (gdb) p this $171 = (Item_cond_or * const) 0x7fff8401a8f0 (gdb) print item $172 = (Item_singlerow_subselect *) 0x7fff8401a728 this line is executed: item= *li.ref(); // item can be substituted in fix_fields and (gdb) print item $173 = (Item_cond_or *) 0x7fff84019d80 Fix suggestion: if we are an Item_cond_or and we've got an Item_cond_or as a child (=$new_child), concatenate $new_child->list to this->list. Also, do the same if both are Item_cond_and-s.

Oleg Smirnov added a comment - 2023-01-19 05:46 Please review the patch suggested in bb-10.4- MDEV-29294 .

Sergei Petrunia added a comment - 2023-01-19 14:50 Review input provided on Slack...

Oleg Smirnov added a comment - 2023-01-20 07:32 The fix has been pushed into 10.4.