Crash in hp_movelink with Aria engine and subqueries

[Philip Stoev]

Repeatable with maria-5.3, maria-5.3-mwl89. Not repeatable with maria-5.2.

backtrace:

#5 0x085293f5 in hp_movelink (pos=0xae68cbf8, next_link=0x0, newlink=0xae68cc30) at hp_hash.c:232
#6 0x08528a8b in hp_write_key (info=0xae653250, keyinfo=0xae66b33c, record=0xae652ec0 "\377\021",
    recpos=0xae67a7e4 "\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245"...) at hp_write.c:364
#7 0x08528165 in heap_write (info=0xae653250, record=0xae652ec0 "\377\021") at hp_write.c:51
#8 0x08525a82 in ha_heap::write_row (this=0xae66ad80, buf=0xae652ec0 "\377\021") at ha_heap.cc:237
#9 0x0833a25a in handler::ha_write_tmp_row (this=0xae66ad80, buf=0xae652ec0 "\377\021") at sql_class.h:3664
#10 0x083ca2c5 in do_sj_dups_weedout (thd=0xab74da8, sjtbl=0xae66a160) at opt_subselect.cc:2916
#11 0x08329ee6 in evaluate_join_record (join=0xae65dd20, join_tab=0xae66974c, error=0) at sql_select.cc:14132
#12 0x08329aa1 in sub_select (join=0xae65dd20, join_tab=0xae66974c, end_of_records=false) at sql_select.cc:14006
#13 0x0832a2bc in evaluate_null_complemented_join_record (join=0xae65dd20, join_tab=0xae669568) at sql_select.cc:14256
#14 0x08329b02 in sub_select (join=0xae65dd20, join_tab=0xae669384, end_of_records=false) at sql_select.cc:14011
#15 0x08329fc6 in evaluate_join_record (join=0xae65dd20, join_tab=0xae6691a0, error=0) at sql_select.cc:14161
#16 0x08329aa1 in sub_select (join=0xae65dd20, join_tab=0xae6691a0, end_of_records=false) at sql_select.cc:14006
#17 0x08328d2b in do_select (join=0xae65dd20, fields=0xab767d8, table=0x0, procedure=0x0) at sql_select.cc:13501
#18 0x0830f596 in JOIN::exec (this=0xae65dd20) at sql_select.cc:2482
#19 0x0830fdb2 in mysql_select (thd=0xab74da8, rref_pointer_array=0xab76848, tables=0xae6130e0, wild_num=1, fields=..., conds=0xae6148f0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae614a88, unit=0xab764a8, select_lex=0xab76744)
    at sql_select.cc:2701
#20 0x0830860f in handle_select (thd=0xab74da8, lex=0xab7644c, result=0xae614a88, setup_tables_done_option=0) at sql_select.cc:282
#21 0x082a5954 in execute_sqlcom_select (thd=0xab74da8, all_tables=0xae6130e0) at sql_parse.cc:5094
#22 0x0829c79e in mysql_execute_command (thd=0xab74da8) at sql_parse.cc:2239
#23 0x082a7ef7 in mysql_parse (thd=0xab74da8,
    rawbuf=0xae612ee8 "SELECT *\nFROM t4\nWHERE f10 IN\n(\nSELECT t1.f11\nFROM t1\nLEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 )", length=110,
    found_semicolon=0xae7ff228) at sql_parse.cc:6094
#24 0x0829a427 in dispatch_command (command=COM_QUERY, thd=0xab74da8, packet=0xabcf901 "", packet_length=110) at sql_parse.cc:1215
#25 0x08299885 in do_command (thd=0xab74da8) at sql_parse.cc:904
#26 0x08296938 in handle_one_connection (arg=0xab74da8) at sql_connect.cc:1154
#27 0x00821919 in start_thread () from /lib/libpthread.so.0
#28 0x0076acce in clone () from /lib/libc.so.6

test case (note that the IN predicate compares varchar to int:
CREATE TABLE t4 (f10 varchar(32) , KEY (f10)) ENGINE=Aria;
INSERT INTO t4 VALUES ('x'),('m'),('c');

CREATE TABLE t1 (f11 int) ENGINE=Aria;
INSERT INTO t1 VALUES (0),(0),(0);

CREATE TABLE t2 ( f10 int) ENGINE=Aria;
INSERT INTO t2 VALUES (0),(0),(0);

CREATE TABLE t3 ( f10 int, f11 int) ENGINE=Aria;

SELECT *
FROM t4
WHERE f10 IN
(
SELECT t1.f11
FROM t1
LEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 );

bzr version-info:

revision-id: <email address hidden>
date: 2011-05-04 18:08:44 -0700
build-date: 2011-05-06 12:47:37 +0300
revno: 2980
branch-nick: maria-5.3

[Timour Katchaounov (timour)]

Confirmed with the latest 5.3 as of 14-06.2011, tip:
------------------------------------------------------------
revno: 3041
committer: Sergey Petrunya <email address hidden>
branch nick: 5.3-push3
timestamp: Mon 2011-06-13 12:41:19 +0400
message:
  Remove redundant code that is a result of a wrong merge.
  (Changeset <email address hidden> moved this loop from one place
  to another, then the merge of <email address hidden> have
  kept both copies).
------------------------------------------------------------

[Timour Katchaounov (timour)]

The bug is clearly semijoin related, not repeatable with other strategies.
Explain:
MariaDB [test]> explain SELECT * FROM t4 WHERE f10 IN ( SELECT t1.f11 FROM t1 LEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 );
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Start temporary |
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 0 | Using where |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 1 | PRIMARY | t4 | ALL | f10 | NULL | NULL | NULL | 3 | Range checked for each record (index map: 0x1); End temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------------------------------------+