crash in do_copy_not_null() in maria-5.3 with semijoin

[fimbulvetr]

Attaching test data for reproduction. Sometimes it can take a few times to crash. Already upgraded to beta from a trunk, bug still exists.

Best reproduced with:

set optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on';

Not sure which flag or combo it is, I'm sorry, I didn't have the time to test every combination.

Stacktrace:

110729 22:29:13 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=0
read_buffer_size=262144
max_used_connections=1
max_threads=302
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 235825 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f5281b8fd00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f526214ce68 thread_stack 0x48000
./bin/mysqld(my_print_stacktrace+0x2e) [0xa0ef8e]
./bin/mysqld(handle_segfault+0x3b3) [0x632b33]
/lib/libpthread.so.0(+0xf8f0) [0x7f55aff098f0]
./bin/mysqld() [0x761870]
./bin/mysqld(store_key_field::copy_inner()+0x24) [0x6c3cd4]
./bin/mysqld(cp_buffer_from_ref(THD*, st_table*, st_table_ref*)+0x7f) [0x6a1caf]
./bin/mysqld(join_read_key2(THD*, st_join_table*, st_table*, st_table_ref*)+0x8f) [0x6aa0ef]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x25c) [0x6af88c]
./bin/mysqld() [0x6af4a2]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x27d) [0x6af8ad]
./bin/mysqld() [0x6af4a2]
./bin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0xcf) [0x6af6ff]
./bin/mysqld() [0x6b2133]
./bin/mysqld(JOIN::exec()+0x83c) [0x6c0a1c]
./bin/mysqld(mysql_select(THD*, Item***, 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*)+0x195) [0x6c2ad5]
./bin/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x16f) [0x6c347f]
./bin/mysqld() [0x63a619]
./bin/mysqld(mysql_execute_command(THD*)+0x2e0e) [0x63f05e]
./bin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x299) [0x642999]
./bin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xc43) [0x643a23]
./bin/mysqld(do_command(THD*)+0x101) [0x644381]
./bin/mysqld(handle_one_connection+0xe7) [0x635f27]
/lib/libpthread.so.0(+0x69ca) [0x7f55aff009ca]
/lib/libc.so.6(clone+0x6d) [0x7f55af4be70d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x3ddcbe8): SELECT * FROM t1 WHERE t1.c1 IN ( SELECT t1.c1 FROM t1 LEFT JOIN t2 ON t2.c1 = t1.c1 WHERE t2.c5 >= '2011-02-01 07:00:00' AND t2.c5 <= '2011-03-01 06:59:59' )
Connection ID (thread ID): 3
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file
Segmentation fault

[Philip Stoev (pstoev-askmonty)]

Requires semijoin=on

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.c1 1 Using index
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.c1 1 End temporary

simplified test case:

CREATE TABLE t1 ( c1 varchar(32) NOT NULL , c2 int, PRIMARY KEY (c1)) ;
INSERT IGNORE INTO t1 VALUES (24276075,79436),(112396,350387);

CREATE TABLE t2 ( c1 varchar(32) NOT NULL , c5 int NOT NULL );

backtrace:

#3 <signal handler called>
#4 0x0000000000852d08 in do_copy_not_null (copy=0xc6eb2b0) at field_conv.cc:240
#5 0x0000000000758a18 in store_key_field::copy_inner (this=0xc6eb288) at sql_select.h:1328
#6 0x0000000000639410 in store_key::copy (this=0xc6eb288) at sql_select.h:1269
#7 0x000000000071ec4d in cp_buffer_from_ref (thd=0xc5d7108, table=0xc68d0b8, ref=0xc6eb0c0) at sql_select.cc:18490
#8 0x000000000072a623 in cmp_buffer_with_ref (thd=0xc5d7108, table=0xc68d0b8, tab_ref=0xc6eb0c0) at sql_select.cc:18472
#9 0x000000000072a99d in join_read_key2 (thd=0xc5d7108, tab=0xc6eaea8, table=0xc68d0b8, table_ref=0xc6eb0c0) at sql_select.cc:15585
#10 0x000000000072ab7b in join_read_key (tab=0xc6eaea8) at sql_select.cc:15568
#11 0x0000000000735098 in sub_select (join=0xc697910, join_tab=0xc6eaea8, end_of_records=false) at sql_select.cc:15005
#12 0x000000000072bb93 in evaluate_join_record (join=0xc697910, join_tab=0xc6eab90, error=0) at sql_select.cc:15205
#13 0x00000000007350e8 in sub_select (join=0xc697910, join_tab=0xc6eab90, end_of_records=false) at sql_select.cc:15008
#14 0x000000000072bb93 in evaluate_join_record (join=0xc697910, join_tab=0xc6ea878, error=0) at sql_select.cc:15205
#15 0x00000000007350e8 in sub_select (join=0xc697910, join_tab=0xc6ea878, end_of_records=false) at sql_select.cc:15008
#16 0x00000000007362fb in do_select (join=0xc697910, fields=0xc5d9ba0, table=0x0, procedure=0x0) at sql_select.cc:14673
#17 0x0000000000756044 in JOIN::exec (this=0xc697910) at sql_select.cc:2670
#18 0x000000000074ff5c in mysql_select (thd=0xc5d7108, rref_pointer_array=0xc5d9ce8, tables=0xc65cf98, wild_num=1, fields=..., conds=0xc6926d0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xc692850, unit=0xc5d95b0, select_lex=0xc5d9a98)
    at sql_select.cc:2891
#19 0x0000000000756376 in handle_select (thd=0xc5d7108, lex=0xc5d9510, result=0xc692850, setup_tables_done_option=0) at sql_select.cc:283
#20 0x00000000006a3046 in execute_sqlcom_select (thd=0xc5d7108, all_tables=0xc65cf98) at sql_parse.cc:5087
#21 0x00000000006a4d24 in mysql_execute_command (thd=0xc5d7108) at sql_parse.cc:2231
#22 0x00000000006adabd in mysql_parse (thd=0xc5d7108,
    rawbuf=0xc65cd00 "SELECT * FROM t1 WHERE c1 IN ( SELECT t1 .c1 FROM t1 LEFT JOIN t2 ON t2 .c1 = t1 .c1 WHERE c5 != 0 )", length=110,
    found_semicolon=0x4153bf08) at sql_parse.cc:6088
#23 0x00000000006ae955 in dispatch_command (command=COM_QUERY, thd=0xc5d7108, packet=0xc653899 "", packet_length=110) at sql_parse.cc:1...

[Philip Stoev (pstoev-askmonty)]

Simplified test case:

CREATE TABLE t1 ( c1 int NOT NULL , c2 int NOT NULL, PRIMARY KEY (c1)) ;
INSERT IGNORE INTO t1 VALUES (2,7),(1,3),(5,6);

CREATE TABLE t3 ( c1 int NOT NULL , c2 int NOT NULL, PRIMARY KEY (c1)) ;
INSERT IGNORE INTO t3 VALUES (2,7),(1,3),(5,6);

CREATE TABLE t2 ( c1 int NOT NULL , c5 int NOT NULL );
INSERT IGNORE INTO t2 VALUES (2,2),(2,2),(5,6);

SET SESSION optimizer_switch='semijoin=on';
SELECT * FROM t1 WHERE c1 IN ( SELECT t3.c1 FROM t3 LEFT JOIN t2 ON t2 .c1 = t3 .c1 WHERE t2.c5 != 0 );

[Sergey Petrunia (sergefp)]

EXPLAIN for the crashing query is:

MariaDB [j2]> explain extended SELECT * FROM t1 WHERE c1 IN (SELECT t3.c1 FROM t3 LEFT JOIN t2 ON t2.c1 = t3.c1 WHERE t2.c5 != 0);
+----+-------------+-------+--------+---------------+---------+---------+----------+------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+----------+------------------------------+
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Start temporary |
| 1 | PRIMARY | t3 | eq_ref | PRIMARY | PRIMARY | 4 | j2.t2.c1 | 1 | 100.00 | Using index |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | j2.t2.c1 | 1 | 100.00 | End temporary |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+----------+------------------------------+

[Sergey Petrunia (sergefp)]

The crash occurs because of incorrect ref access structures for table t1. It
happens as follows: First, we end up at this location:

(gdb) wher 4
  #0 Copy_field::set (this=0xa5985f4, to=0xa5986d8, from=0xa58add8, save=false) at field_conv.cc:649
  #1 0x0837b767 in store_key_field::change_source_field (this=0xa5985e0, fld_item=0xa55d3f0) at sql_select.h:1309
  #2 0x08370511 in JOIN::optimize (this=0xa58c1e0) at sql_select.cc:1233
  #3 0x08372ff8 in mysql_select (

where
  to=t1.c1
  from=t2.c1

from->maybe_null() == true
from->real_maybe_null() == false

to->maybe_null() == false
to->real_maybe_null() == false

based on this, Copy_field::set() sets do_copy= do_copy_not_null.

do_copy_not_null() copies from NULL-able field to non-NULLable. It crashes when
trying to check copy->from_null_ptr.

[Sergey Petrunia (sergefp)]

One can see two problems here:

Problem-A). Why we have from={t2.c1}, from->maybe_null() == true

This seems to be a known problem of field NULLability attributes not being updated after outer-to-inner join conversion.

Problem-B). Still, it is a valid situation when ref access refers to a table column that is on an inner side of outer join and so has

  from->maybe_null() == true
  from->real_maybe_null() == false

do_copy_not_null() function does not provide valid processing for this case.

[Sergey Petrunia (sergefp)]

When I try to construct a pure example of Problem-B, I observe interesting things:

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (a int, b int);
insert into t1 values (1,1), (2,2);

create table t2a (a not null, b not null);
insert into t2a values (1,1), (3,3);

create table t3a ( b int not null, filler char(100), key(b));
insert into t3a select A.a + 10 *B.a + 100*C.a, 'filler' from t0 A, t0 B, t0 C;

MariaDB [j33]> explain extended select * from (t1 left join t2a on t1.a=t2a.a) join t3a on t3a.b<=>t2a.b;
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
| 1 | SIMPLE | t2a | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | SIMPLE | t3a | ref | b | b | 4 | j33.t2a.b | 1 | 100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------+-------------+
3 rows in set, 1 warning (7.87 sec)
  Breakpoint 2, Copy_field::set (this=0xa0f3ce4, to=0xa0f3d38, from=0xa0e7aa8, save=false) at field_conv.cc:601

from = {t2a.b}
from->real_maybe_null() == false
from->maybe_null() = true

to = {t3a.b}
to->real_maybe_null() == true
to->maybe_null() = true

We don't get into crashing scenario, because {t3a.b}->maybe_null()==TRUE. This is odd, because in table definition t3a.b was defined as NOT NULL.

Further investigation shows that the Field_long object used for t3a.b was created here:

  #0 Field::new_key_field (this=0xa0ebc68, root=0x959040a4, new_table=0xa0e54e0, new_ptr=0xa0f3c98 "", new_null_ptr=0xa0f3ce0 "", new_null_bit=1) at field.cc:1795
  #1 0x0837b91e in store_key (this=0xa0f3cd0, thd=0x95902018, field_arg=0xa0ebc68, ptr=0xa0f3c98 "", null=0xa0f3ce0 "", length=4) at sql_select.h:1247
  #2 0x0837bb2f in store_key_field (this=0xa0f3cd0, thd=0x95902018, to_field_arg=0xa0ebc68, ptr=0xa0f3c98 "", null_ptr_arg=0x0, length=4, from_field=0xa0e7aa8, name_arg=0xa0f3cc0 "j33.t2a.b") at sql_select.h:1296
  #3 0x08353de0 in get_store_key (thd=0x95902018, keyuse=0xa0e9648, used_tables=0, key_part=0xa0ebd80, key_buff=0xa0f3c98 "", maybe_null=0) at sql_select.cc:7390
  #4 0x08362b98 in create_ref_for_key (join=0xa0ed858, j=0xa0f3a80, org_keyuse=0xa0e9648, used_tables=4611686018427387911) at sql_select.cc:7319
  #5 0x08365e3e in get_best_combination (join=0xa0ed858) at sql_select.cc:7085
  (More stack frames follow...)

In particular, Field::new_key_field() created a new field object that is NULLable while the field itself was not:

(gdb) p this->maybe_null()
  $13 = false
(gdb) p tmp->maybe_null()
  $14 = true