Assertion `table_ref->has_record' failed with small value for join_buffer_size

[Philip Stoev]

If a table contains a field that is comparable in size to the value for join_cache_level, certain joins cause the following assertion:

mysqld: sql_select.cc:14041: int join_read_key2(THD*, JOIN_TAB*, TABLE*, TABLE_REF*): Assertion `table_ref->has_record' failed.

This happens in maria-5.3 where the fixes for all other "table_ref->has_record" bugs appear to be present.

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x0832d094 in join_read_key2 (thd=0xb256b50, tab=0xae59caa0, table=0xae569088, table_ref=0xae59cbe4) at sql_select.cc:14041
#10 0x0832ceb0 in join_read_key (tab=0xae59caa0) at sql_select.cc:13992
#11 0x0832bc1f in sub_select (join=0xae596f10, join_tab=0xae59caa0, end_of_records=false) at sql_select.cc:13460
#12 0x0832c2ee in evaluate_join_record (join=0xae596f10, join_tab=0xae59c8c8, error=0) at sql_select.cc:13658
#13 0x0832bc72 in sub_select (join=0xae596f10, join_tab=0xae59c8c8, end_of_records=false) at sql_select.cc:13463
#14 0x082e3352 in JOIN_CACHE::generate_full_extensions (this=0xae59edd8, rec_ptr=0xae579442 "\371\022") at sql_join_cache.cc:2278
#15 0x082e3114 in JOIN_CACHE::join_matching_records (this=0xae59edd8, skip_last=false) at sql_join_cache.cc:2172
#16 0x082e2c9e in JOIN_CACHE::join_records (this=0xae59edd8, skip_last=false) at sql_join_cache.cc:1980
#17 0x0832b898 in sub_select_cache (join=0xae596f10, join_tab=0xae59c6f0, end_of_records=true) at sql_select.cc:13251
#18 0x0832bab1 in sub_select (join=0xae596f10, join_tab=0xae59c518, end_of_records=true) at sql_select.cc:13413
#19 0x0832b0b8 in do_select (join=0xae596f10, fields=0xb25857c, table=0x0, procedure=0x0) at sql_select.cc:13009
#20 0x083109a1 in JOIN::exec (this=0xae596f10) at sql_select.cc:2404
#21 0x0831117e in mysql_select (thd=0xb256b50, rref_pointer_array=0xb2585ec, tables=0xae531b88, wild_num=1, fields=..., conds=0xae533890, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764738, result=0xae577160, unit=0xb25824c, select_lex=0xb2584e8)
    at sql_select.cc:2621
#22 0x08309685 in handle_select (thd=0xb256b50, lex=0xb2581f0, result=0xae577160, setup_tables_done_option=0) at sql_select.cc:286
#23 0x082a47e0 in execute_sqlcom_select (thd=0xb256b50, all_tables=0xae531b88) at sql_parse.cc:5070
#24 0x0829b1fb in mysql_execute_command (thd=0xb256b50) at sql_parse.cc:2234
#25 0x082a6d7e in mysql_parse (thd=0xb256b50,
    rawbuf=0xae531948 "SELECT STRAIGHT_JOIN *\nFROM t6 JOIN t5 ON t6.f3\nLEFT JOIN t1 JOIN t4 ON t4.f1 ON t5.f1 = t4.f1\nLEFT JOIN t3 JOIN t2 ON t3.f3 ON t6.f2\nWHERE t2.f1", length=145, found_semicolon=0xae883228) at sql_parse.cc:6077
#26 0x08298ce4 in dispatch_command (command=COM_QUERY, thd=0xb256b50, packet=0xb26ec81 "", packet_length=146) at sql_parse.cc:1210
#27 0x0829814e in do_command (thd=0xb256b50) at sql_parse.cc:903
#28 0x0829522a in handle_one_connection (arg=0xb256b50) at sql_connect.cc:1154
#29 0x00821919 in start_thread () from /lib/libpthread.so.0
#30 0x0076acce in clone () from /lib/libc.so.6

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t6 ALL NULL NULL NULL NULL 3 Using where
1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where
1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t5.f1 1 Using where
1 SIMPLE t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (flat, BNL join)

bzr version-info:

revision-id: <email address hidden>
date: 2011-01-12 15:00:10 +0300
build-date: 2011-01-13 13:33:06 +0200
revno: 2876
branch-nick: maria-5.3

test case:

SET SESSION join_buffer_size = 1024;
CREATE TABLE t2 ( f1 int(11)) ;

CREATE TABLE t3 ( f3 int(11)) ;
INSERT IGNORE INTO t3 VALUES (20),(5);

CREATE TABLE t6 ( f3 int(11), f2 int(11)) ;
INSERT IGNORE INTO t6 VALUES (3,-19),(7,20),(18,-18);

CREATE TABLE t5 ( f1 int(11)) ;
INSERT IGNORE INTO t5 VALUES (9);

CREATE TABLE t1 ( f2 int(11)) ;
INSERT IGNORE INTO t1 VALUES (17);

CREATE TABLE t4 ( f4 varchar(1024), f1 int(11), PRIMARY KEY (f1)) ;

SELECT STRAIGHT_JOIN *
FROM t6 JOIN t5 ON t6.f3
LEFT JOIN t1 JOIN t4 ON t4.f1 ON t5.f1 = t4.f1
LEFT JOIN t3 JOIN t2 ON t3.f3 ON t6.f2
WHERE t2.f1 ;

[Philip Stoev (philip-stoev)]

also reproducible with maria-5.3-mwl128

 bzr version-info
revision-id: <email address hidden>
date: 2011-01-06 11:40:01 -0800
build-date: 2011-01-13 13:54:08 +0200
revno: 2877
branch-nick: maria-5.3-mwl128

[Igor Babaev (igorb-seattle)]

The following test case reproduces this problem for the current mariadb-5.1 tree:

CREATE TABLE t1 (f1 int) ;
INSERT INTO t1 VALUES (9);

CREATE TABLE t2 (f1 int);
INSERT INTO t2 VALUES (3),(7),(18);
INSERT INTO t2 VALUES (3),(7),(18);
INSERT INTO t2 VALUES (3),(7),(18);
INSERT INTO t2 VALUES (3),(7),(18);

CREATE TABLE t3 (f1 int);
INSERT INTO t3 VALUES (17);

CREATE TABLE t4 (f1 int PRIMARY KEY, f2 varchar(1024)) ;

CREATE TABLE t5 (f1 int) ;
INSERT INTO t5 VALUES (20),(5);

CREATE TABLE t6(f1 int);
INSERT INTO t6 VALUES (9),(7);

SET SESSION join_buffer_size = 9000;

EXPLAIN
SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;

[Philip Stoev (philip-stoev)]

Not reproducible in either mysql 5.5.8 nor in mysql 5.1.53

[Igor Babaev (igorb-seattle)]

The bug is reproducible with mysql-5.1 as well (see my bug report http://bugs.mysql.com/bug.php?id=59493)

[Igor Babaev (igorb-seattle)]

Fix committed into 5.1. Ported to 5.3.