[MDEV-29294] OR from subquery: (SELECT x OR y) OR z: Assertion `functype() == ((Item_cond *) new_item)->functype()' failed Created: 2022-08-11  Updated: 2023-01-24  Resolved: 2023-01-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.11.2, 11.0.1, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Critical
Reporter: Ramesh Sivaraman Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: ASAN, UBSAN, not-10.3, regression

Issue Links:
Relates

 Description    

CREATE TABLE t (c INT);
 
SELECT * FROM t WHERE c = 1 AND ( 3 = 0 OR  (SELECT c = 1 OR (SELECT 3 WHERE c = c ) = 3));

Leads to:

10.9.2 a5dc76a051ff908a5f506ed12e8d05311752e5fb (Debug)

 
mysqld: /test/10.9_dbg/sql/sql_select.cc:17967: virtual COND* Item_cond::remove_eq_conds(THD*, Item::cond_result*, bool): Assertion `functype() == ((Item_cond *) new_item)->functype()' failed.

10.9.2 a5dc76a051ff908a5f506ed12e8d05311752e5fb (Debug)

 
Core was generated by `/test/MD190722-mariadb-10.9.2-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x14c6a80a3700 (LWP 519539))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014c6bf03b859 in __GI_abort () at abort.c:79
 
#2  0x000014c6bf03b729 in __assert_fail_base (fmt=0x14c6bf1d1588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x558c7d40aeb8 "functype() == ((Item_cond *) new_item)->functype()", file=0x558c7d409948 "/test/10.9_dbg/sql/sql_select.cc", line=17967, function=<optimized out>) at assert.c:92
 
#3  0x000014c6bf04cfd6 in __GI___assert_fail (assertion=assertion@entry=0x558c7d40aeb8 "functype() == ((Item_cond *) new_item)->functype()", file=file@entry=0x558c7d409948 "/test/10.9_dbg/sql/sql_select.cc", line=line@entry=17967, function=function@entry=0x558c7d40aef0 "virtual COND* Item_cond::remove_eq_conds(THD*, Item::cond_result*, bool)") at assert.c:101
 
#4  0x0000558c7c86b210 in Item_cond::remove_eq_conds (this=0x14c67001ff90, thd=0x14c670000db8, cond_value=0x14c670020d00, top_level_arg=<optimized out>) at /test/10.9_dbg/sql/sql_select.cc:17967
 
#5  0x0000558c7c8524f3 in optimize_cond (join=join@entry=0x14c6700209a8, conds=0x14c67001ff90, join_list=0x14c670013ef0, ignore_on_conds=ignore_on_conds@entry=false, cond_value=cond_value@entry=0x14c670020d00, cond_equal=cond_equal@entry=0x14c670020e28, flags=1) at /test/10.9_dbg/sql/sql_select.cc:17676
 
#6  0x0000558c7c897883 in JOIN::optimize_inner (this=this@entry=0x14c6700209a8) at /test/10.9_dbg/sql/sql_select.cc:2231
 
#7  0x0000558c7c8986ee in JOIN::optimize (this=this@entry=0x14c6700209a8) at /test/10.9_dbg/sql/sql_select.cc:1846
 
#8  0x0000558c7c8987e1 in mysql_select (thd=thd@entry=0x14c670000db8, tables=0x14c670014310, fields=@0x14c670013fd8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14c6700142c8, last = 0x14c6700142c8, elements = 1}, <No data fields>}, conds=0x14c67001ff90, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x14c670020980, unit=0x14c670004fd8, select_lex=0x14c670013d38) at /test/10.9_dbg/sql/sql_select.cc:5031
 
#9  0x0000558c7c89902a in handle_select (thd=thd@entry=0x14c670000db8, lex=lex@entry=0x14c670004f00, result=result@entry=0x14c670020980, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.9_dbg/sql/sql_select.cc:579
 
#10 0x0000558c7c803fde in execute_sqlcom_select (thd=thd@entry=0x14c670000db8, all_tables=0x14c670014310) at /test/10.9_dbg/sql/sql_parse.cc:6260
 
#11 0x0000558c7c8102fc in mysql_execute_command (thd=thd@entry=0x14c670000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.9_dbg/sql/sql_parse.cc:3944
 
#12 0x0000558c7c7fe2ba in mysql_parse (thd=thd@entry=0x14c670000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14c6a80a2470) at /test/10.9_dbg/sql/sql_parse.cc:8036
 
#13 0x0000558c7c80b8b0 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14c670000db8, packet=packet@entry=0x14c67000b6c9 "SELECT *  FROM t WHERE c = 1 AND ( 3 = 0 OR  (SELECT c = 1 OR (SELECT 3 WHERE c = c ) = 3))", packet_length=packet_length@entry=91, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_class.h:1364
 
#14 0x0000558c7c80dfb8 in do_command (thd=0x14c670000db8, blocking=blocking@entry=true) at /test/10.9_dbg/sql/sql_parse.cc:1407
 
#15 0x0000558c7c96f6c2 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x558c80133b08, put_in_cache=put_in_cache@entry=true) at /test/10.9_dbg/sql/sql_connect.cc:1418
 
#16 0x0000558c7c96fbcb in handle_one_connection (arg=0x558c80133b08) at /test/10.9_dbg/sql/sql_connect.cc:1312
 
#17 0x000014c6bf54c609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#18 0x000014c6bf138133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.4.26 (dbg), 10.5.17 (dbg), 10.6.9 (dbg), 10.7.5 (dbg), 10.8.4 (dbg), 10.9.2 (dbg), 10.10.0 (dbg)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.3.36 (dbg), 10.3.36 (opt), 10.4.26 (opt), 10.5.17 (opt), 10.6.9 (opt), 10.7.5 (opt), 10.8.4 (opt), 10.9.2 (opt), 10.10.0 (opt)

 Comments   
Comment by Roel Van de Paar [ 2022-08-12 ]

psergei Hi! Can you please check if this issue is in optimizer code or in name resolution? Thank you

Comment by Roel Van de Paar [ 2022-12-17 ]

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

Comment by Roel Van de Paar [ 2022-12-17 ]

The original testcase also produces the same UBSAN + ASAN issues.

Comment by Oleg Smirnov [ 2023-01-10 ]

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

Comment by Sergei Petrunia [ 2023-01-13 ]

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
 
  )
 
)

Comment by Sergei Petrunia [ 2023-01-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...

Comment by Sergei Petrunia [ 2023-01-13 ]

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.

Comment by Oleg Smirnov [ 2023-01-19 ]

Please review the patch suggested in bb-10.4-MDEV-29294.

Comment by Sergei Petrunia [ 2023-01-19 ]

Review input provided on Slack...

Comment by Oleg Smirnov [ 2023-01-20 ]

The fix has been pushed into 10.4.

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