MySQL Bugs: #59651: Assertion `table_ref->has_record' failed with join_cache

Bug #59651	Assertion `table_ref->has_record' failed with join_cache_level=3
Submitted:	21 Jan 2011 10:31	Modified:	26 Apr 2011 15:43
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.2-debug	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	join_cache_level, optimizer_switch, regression

Description:
Version: '5.6.2-m5-valgrind-max-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 0x7fffe3f98710 (LWP 9808)]
mysqld: ./sql/sql_select.cc:18067: int join_read_key2(JOIN_TAB*, TABLE*, TABLE_REF*): Assertion `table_ref->has_record' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7fffe3f98710 (LWP 9808)]
0x00000030e14329a5 in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
64        return INLINE_SYSCALL (tgkill, 3, pid, selftid, sig);
(gdb) bt
#0  in raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1  in abort () at abort.c:92
#2  in __assert_fail at assert.c:81
#3  in join_read_key2 at ./sql/sql_select.cc:18067
#4  in join_read_key at ./sql/sql_select.cc:18014
#5  in sub_select at ./sql/sql_select.cc:17371
#6  in JOIN_CACHE::generate_full_extensions at ./sql/sql_join_cache.cc:1956
#7  in JOIN_CACHE::join_null_complements at ./sql/sql_join_cache.cc:2108
#8  in JOIN_CACHE::join_records at ./sql/sql_join_cache.cc:1688
#9  in sub_select_cache at ./sql/sql_select.cc:17174
#10 in sub_select at ./sql/sql_select.cc:17344
#11 in sub_select_cache at ./sql/sql_select.cc:17176
#12 in sub_select at ./sql/sql_select.cc:17344
#13 in sub_select_cache at ./sql/sql_select.cc:17176
#14 in sub_select at ./sql/sql_select.cc:17344
#15 in sub_select_cache at ./sql/sql_select.cc:17176
#16 in sub_select at ./sql/sql_select.cc:17344
#17 in do_select at ./sql/sql_select.cc:16922
#18 in JOIN::exec at ./sql/sql_select.cc:3331
#19 in mysql_select at ./sql/sql_select.cc:3554
#20 in handle_select at ./sql/sql_select.cc:323
#21 in execute_sqlcom_select at ./sql/sql_parse.cc:4513
#22 in mysql_execute_command at ./sql/sql_parse.cc:2096
#23 in mysql_parse at ./sql/sql_parse.cc:5550
#24 in dispatch_command at ./sql/sql_parse.cc:1078
#25 in do_command at ./sql/sql_parse.cc:815
#26 in do_handle_one_connection at ./sql/sql_connect.cc:748
#27 in handle_one_connection at ./sql/sql_connect.cc:684
#28 in start_thread at pthread_create.c:301
#29 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
(gdb) 

testcase for bug #59493 didn't crash mysql-trunk build that this crashes.
this testcase doesn't crash 5.5.10 either. hence, filing separate bug report.

How to repeat:
import attached file.

testcase. import into debug build

Attachment: bug59651.sql (, text), 9.64 KiB.

With current mysql-trunk (5.6.2-m5-debug, built with BUILD/compile-pentium-debug-max, not with valgrind) since revno 3511 on Mac OS X I get:

macbook-pro:trunk openxs$ bin/mysql -uroot test < ~/Downloads/bug59651.sql ERROR 1062 (23000) at line 30: Duplicate entry '00' for key 'PRIMARY'

and no assertion failure.

Verified finally:

Version: '5.6.2-m5-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (table_ref->has_record), function join_read_key2, file /Users/openxs/bzr2/mysql-trunk-work/sql/sql_select.cc, line 18067.
110121 18:41:10 - mysqld got signal 6 ;
...

One has to source test case in mysql to get this assertion failure.

Error only happens with join_cache_level==3. 
(Succeeds for join_cache_level=={0,1,2,4})

Noted in 5.6.3 changelog.

Setting join_cache_level=3 caused an assertion to be raised for some
queries. 

CHANGESET - http://lists.mysql.com/commits/136078