# Assertion `((Item_cond *) cond)->functype() == ((Item_cond *) new_item)->functype()' fails on a query with IN and equal conditions, AND/OR, materialization+semijoin

XMLWordPrintable

#### Details

• Bug
• Status: Closed
• Critical
• Resolution: Fixed
• None
• None
• None

#### Description

 `SET optimizer_switch = 'materialization=on,semijoin=on';` ` ` `CREATE TABLE t1 (pk INT, a INT, b INT, PRIMARY KEY(pk)) ENGINE=MyISAM;` `INSERT INTO t1 VALUES (1,3,5),(2,4,6);` `SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b );`

Starting with the following revision, the query causes an assertion failure:

bzr version-info

 `revision-id: igor@askmonty.org-20130815235920-io2h7tlypwlbunsp` `revno: 3676` `branch-nick: 5.3`

 `Version: '5.3.13-MariaDB-debug-log' ` `mysqld: sql_select.cc:13342: COND* remove_eq_conds(THD*, COND*, Item::cond_result*): Assertion `((Item_cond *) cond)->functype() == ((Item_cond *) new_item)->functype()' failed.` `130817 13:20:37 [ERROR] mysqld got signal 6 ;`

 `#7 0x00007f6aef09d192 in __GI___assert_fail (assertion=0xd2f858 "((Item_cond *) cond)->functype() == ((Item_cond *) new_item)->functype()", file=0xd2e97f "sql_select.cc", line=13342, function=0xd31240 "COND* remove_eq_conds(THD*, COND*, Item::cond_result*)") at assert.c:103` `#8 0x00000000007275d2 in remove_eq_conds (thd=0x23a18c8, cond=0x7f6ad4036058, cond_value=0x7f6ad40322f8) at sql_select.cc:13341` `#9 0x0000000000726fbd in optimize_cond (join=0x7f6ad4032008, conds=0x7f6ad4036058, join_list=0x23a44d0, ignore_on_conds=false, cond_value=0x7f6ad40322f8, cond_equal=0x7f6ad4032418) at sql_select.cc:13128` `#10 0x0000000000707ac4 in JOIN::optimize (this=0x7f6ad4032008) at sql_select.cc:1019` `#11 0x000000000070ed8f in mysql_select (thd=0x23a18c8, rref_pointer_array=0x23a45b8, tables=0x7f6ad4019198, wild_num=1, fields=..., conds=0x7f6ad4031e10, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7f6ad4031fe8, unit=0x23a3e58, select_lex=0x23a4360) at sql_select.cc:2976` `#12 0x0000000000705899 in handle_select (thd=0x23a18c8, lex=0x23a3db8, result=0x7f6ad4031fe8, setup_tables_done_option=0) at sql_select.cc:288` `#13 0x0000000000691b72 in execute_sqlcom_select (thd=0x23a18c8, all_tables=0x7f6ad4019198) at sql_parse.cc:5172` `#14 0x000000000068893a in mysql_execute_command (thd=0x23a18c8) at sql_parse.cc:2305` `#15 0x00000000006945fc in mysql_parse (thd=0x23a18c8, rawbuf=0x7f6ad4018f40 "SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b )", length=79, found_semicolon=0x7f6aedf207e0) at sql_parse.cc:6173` `#16 0x0000000000686057 in dispatch_command (command=COM_QUERY, thd=0x23a18c8, packet=0x241bb29 "SELECT * FROM t1 WHERE 8 IN ( SELECT MIN(pk) FROM t1 ) AND ( pk = a OR pk = b )", packet_length=79) at sql_parse.cc:1243` `#17 0x00000000006852c8 in do_command (thd=0x23a18c8) at sql_parse.cc:923` `#18 0x0000000000682162 in handle_one_connection (arg=0x23a18c8) at sql_connect.cc:1231` `#19 0x00007f6aefc38e9a in start_thread (arg=0x7f6aedf21700) at pthread_create.c:308` `#20 0x00007f6aef161cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112`

Before the revision 3676, the query didn't behave quite right, either. It produced the following error and valgrind warnings, although I don't know if they are related to the new assertion failure.

 `130817 13:31:33 [Warning] Plugin 'MEMORY' will be forced to shutdown` `130817 13:31:33 [ERROR] Plugin 'MEMORY' has ref_count=1 after shutdown.` `...` `==6575== 8 bytes in 1 blocks are indirectly lost in loss record 1 of 7` `==6575== at 0x4C2B6CD: malloc (in /usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)` `==6575== by 0xBB13F9: my_malloc (my_malloc.c:42)` `==6575== by 0x8EF0B2: intern_plugin_lock(st_lex*, st_plugin_int**) (sql_plugin.cc:861)` `==6575== by 0x8EF23E: plugin_lock(THD*, st_plugin_int**) (sql_plugin.cc:904)` `==6575== by 0x80FB7E: ha_lock_engine(THD*, handlerton const*) (handler.cc:172)` `==6575== by 0x730342: create_tmp_table(THD*, TMP_TABLE_PARAM*, List&, st_order*, bool, bool, unsigned long long, unsigned long long, char*, bool) (sql_select.cc:14324)` `==6575== by 0x804994: create_dummy_tmp_table(THD*) (opt_subselect.cc:4998)` `==6575== by 0x805155: setup_jtbm_semi_joins(JOIN*, List*, Item**) (opt_subselect.cc:5171)` `==6575== by 0x70D391: JOIN::optimize() (sql_select.cc:1015)` `==6575== by 0x7147A6: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (sql_select.cc:2976)` `==6575== by 0x70B1E6: handle_select(THD*, st_lex*, select_result*, unsigned long) (sql_select.cc:288)` `==6575== by 0x696682: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:5172)` `==6575== by 0x68D441: mysql_execute_command(THD*) (sql_parse.cc:2305)` `==6575== by 0x6990FC: mysql_parse(THD*, char*, unsigned int, char const**) (sql_parse.cc:6173)` `==6575== by 0x68ABE5: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1243)` `==6575== by 0x689E83: do_command(THD*) (sql_parse.cc:923)`

#### People Oleksandr Byelkin Elena Stepanova
{"ft_lang_label":"__label__en","ft_lang_prob":0.5486162,"math_prob":0.8550892,"size":5112,"snap":"2023-14-2023-23","text_gpt3_token_len":1925,"char_repetition_ratio":0.15935788,"word_repetition_ratio":0.10554562,"special_character_ratio":0.4397496,"punctuation_ratio":0.24539171,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9704038,"pos_list":[0,1,2,3,4,5,6,7,8,9,10],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-06-04T16:52:01Z\",\"WARC-Record-ID\":\"<urn:uuid:6042428b-16b4-4501-b79c-4efacfc34ba4>\",\"Content-Length\":\"121420\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:0f5f84ae-bf73-4da1-95fc-fdf39f02e06c>\",\"WARC-Concurrent-To\":\"<urn:uuid:287168fe-45a5-463f-9d17-32c25e94842a>\",\"WARC-IP-Address\":\"149.202.78.157\",\"WARC-Target-URI\":\"https://jira.mariadb.org/browse/MDEV-4908\",\"WARC-Payload-Digest\":\"sha1:5IXUN4WFU3FWLIW4M6VXGXHEQ35UITZI\",\"WARC-Block-Digest\":\"sha1:ONJK7SRAJHSGDRB5K2UCJBX2I4XVRGOJ\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-23/CC-MAIN-2023-23_segments_1685224650201.19_warc_CC-MAIN-20230604161111-20230604191111-00738.warc.gz\"}"}