Bug #18279 While using complex queries with bracketed joins mysqld-nt service terminate
Submitted: 16 Mar 2006 12:53 Modified: 6 Apr 2006 0:23
Reporter: Andrey Kazachkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.19-win/5.0.20BK/5.1.8BK OS:Microsoft Windows (Windows/Linux Suse 10)
Assigned to: Igor Babaev CPU Architecture:Any

[16 Mar 2006 12:53] Andrey Kazachkov
Description:
The problem also exists in previous version 5.0.18-win. 
While using complex queries with bracketed joins mysqld-nt service either  terminates abnormally or dies with the EXCEPTIONACCESS_VIOLATION. 
I can send memory dumps for both cases. 

Such stacks are possible

1. Stack with abnornmal termination

ffffffff 000000ff 00000000 ntdll!NtTerminateProcess+0xb
000000ff 0062868d 000000ff KERNEL32!ExitProcess+0x72
000000ff 033575b8 0335cad8 mysqld_nt!__crtExitProcess+0x2f
000000ff 00000001 00000000 mysqld_nt!doexit+0xa9
000000ff 00000000 00628d32 mysqld_nt!_exit+0xd
00000019 0043c067 0335c1f0 mysqld_nt!_amsg_exit+0x22
0335c1f0 0101ae58 0335c1f0 mysqld_nt!_purecall+0x12
0101ae58 0335c210 03348ec8 mysqld_nt!Item_cond::fix_fields+0x77
0335bd50 03348ec8 0335a738 mysqld_nt!setup_conds+0x11a
0101ae58 0335c910 03348ec8 mysqld_nt!setup_without_group+0x46
03342e74 03348ec8 00000000 mysqld_nt!JOIN::prepare+0x1c1
0101ae58 03342e74 03348ec8 mysqld_nt!mysql_select+0x16e
0101ae58 03342ac8 03348e38 mysqld_nt!handle_select+0xaf
0101ae58 0101ae58 004a2701 mysqld_nt!mysql_execute_command+0x514
0101ae58 0333f178 033492a0 mysqld_nt!sp_instr_stmt::exec_core+0xd
0101ae58 0333f178 00000000 mysqld_nt!sp_lex_keeper::reset_lex_and_exec_core+0xd1
00000017 0333f178 03348538 mysqld_nt!sp_instr_stmt::execute+0x71
0101ae58 0101b39c 0101ae58 mysqld_nt!sp_head::execute+0x2ba
0101ae58 0101b39c 0101ae58 mysqld_nt!sp_head::execute_procedure+0x27d
0101ae58 0101bf80 010379f7 mysqld_nt!mysql_execute_command+0x474a
0101ae58 010379e0 00000017 mysqld_nt!mysql_parse+0x102
00000003 0101ae58 0103c911 mysqld_nt!dispatch_command+0x522
0101ae58 004e4f20 0102a0f8 mysqld_nt!do_command+0xad
0101ae58 0102a0f8 0323fb38 mysqld_nt!handle_one_connection+0x26e
0102a0f8 0323fb38 77fcae15 mysqld_nt!pthread_start+0x3b
0101c3b8 0323fb38 77fcae15 mysqld_nt!_threadstart+0x6c
0062b8b4 0101c3b8 00000000 KERNEL32!BaseThreadStart+0x52

2. Stack with exception access violation
mysqld_nt!Field_bit_as_char::store+0x18
mysqld_nt!Item_cond::fix_fields+0x122
mysqld_nt!setup_without_group+0x46
mysqld_nt!JOIN::prepare+0x1c1
mysqld_nt!mysql_select+0x16e
mysqld_nt!handle_select+0xaf
mysqld_nt!mysql_execute_command+0x514
mysqld_nt!sp_instr_stmt::exec_core+0xd
mysqld_nt!sp_lex_keeper::reset_lex_and_exec_core+0xd1
mysqld_nt!sp_instr_stmt::execute+0x71
mysqld_nt!sp_head::execute+0x2ba
mysqld_nt!sp_head::execute_procedure+0x27d
mysqld_nt!mysql_execute_command+0x474a
mysqld_nt!sp_instr_stmt::exec_core+0xd
mysqld_nt!sp_lex_keeper::reset_lex_and_exec_core+0xd1
mysqld_nt!sp_instr_stmt::execute+0x71
mysqld_nt!sp_head::execute+0x2ba
mysqld_nt!sp_head::execute_procedure+0x27d
mysqld_nt!mysql_execute_command+0x474a
mysqld_nt!mysql_parse+0x102
mysqld_nt!dispatch_command+0x522
mysqld_nt!do_command+0xad
mysqld_nt!handle_one_connection+0x26e
mysqld_nt!pthread_start+0x3b
mysqld_nt!_threadstart+0x6c
WARNING: Stack unwind information not available. Following frames may be wrong.
KERNEL32!lstrcmpiW+0xb7

How to repeat:
Here is the simplest script that I managed to create
Second run of the provided stored procedure `ak_fault_proc` results to the problem. 

delimiter GO

DROP DATABASE IF EXISTS TEST1
GO

CREATE DATABASE TEST1
GO

USE TEST1
GO

DROP TABLE IF EXISTS ak_test1
GO

CREATE TABLE ak_test1(
	nId1 int PRIMARY KEY,
	nId2 int
)
GO

DROP TABLE IF EXISTS ak_test2
GO

CREATE TABLE ak_test2(
	nId1 int PRIMARY KEY,
	nId2 int
)
GO

DROP TABLE IF EXISTS ak_test3
GO

CREATE TABLE ak_test3(
	nId1 int PRIMARY KEY,
	nId2 int
)
GO

DROP TABLE IF EXISTS ak_test4
GO

CREATE TABLE ak_test4(
	nId1 int PRIMARY KEY,
	nId2 int
)
GO

DROP TABLE IF EXISTS ak_test5
GO

CREATE TABLE ak_test5(
	nId1 int PRIMARY KEY,
	nId2 int
)
GO

CREATE VIEW v_ak_t_1
AS 
SELECT ak_test1.nId1  AS `nId`  , `ak_test5`.`nId1`  AS `nGroupByNsa`  
FROM	ak_test1 
		INNER JOIN  ak_test2 ON ak_test2.nId2 = `ak_test1`.`nId1` 
		LEFT OUTER JOIN  
		(	ak_test3
				INNER JOIN  ak_test4 
					ON ak_test4.nId1 = ak_test3.nId2
				INNER JOIN  ak_test5
					ON ak_test4.nId2 = ak_test5.nId1
		)ON ak_test3.nId2 IS NOT NULL
GO

DROP PROCEDURE IF EXISTS `ak_fault_proc`
GO

CREATE PROCEDURE `ak_fault_proc` (
    IN __vtmp_nId INT)
BEGIN
    /* Declarations */
    DECLARE __st_FETCH_STATUS INT;
    DECLARE __vtmp_nGroupToMove INT;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET __st_FETCH_STATUS = 1;

	SELECT nGroupByNsa INTO `__vtmp_nGroupToMove` 
	FROM v_ak_t_1
	WHERE  v_ak_t_1.nId = `__vtmp_nId`;
END
GO

CALL `ak_fault_proc`(2)
GO

CALL `ak_fault_proc`(2)
GO
[16 Mar 2006 14:17] Miguel Solorzano
Thank you for the bug report. I was able to repeat on Linux too with
current source server on 5.0/5.1:

Version: '5.1.8-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1119902640 (LWP 5900)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1119902640 (LWP 5900)]
0x081b14bf in Item_cond::fix_fields (this=0x9267fc0, thd=0x9219f08, ref=0x9267710) at item_cmpfunc.cc:2549
2549        while (item->type() == Item::COND_ITEM &&
(gdb) 

Version: '5.0.20-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 5875)]
[Thread 1131862960 (zombie) exited]
[New Thread 1131862960 (LWP 5880)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 5880)]
0x0818dead in Item_cond::fix_fields (this=0x8eab5c0, thd=0x8e619e8, ref=0x8eaad18) at item_cmpfunc.cc:2549
2549        while (item->type() == Item::COND_ITEM &&
(gdb) bt full
#0  0x0818dead in Item_cond::fix_fields (this=0x8eab5c0, thd=0x8e619e8, ref=0x8eaad18) at item_cmpfunc.cc:2549
        tmp_table_map = 4
        li = {<base_list_iterator> = {list = 0x8eab62c, el = 0x8ea60f8, prev = 0x8eab658, current = 0x8ea60f8}, <No data fields>}
        item = (class Item *) 0xa5a5a5a5
        buff = "8¹vC"
        __PRETTY_FUNCTION__ = "virtual bool Item_cond::fix_fields(THD*, Item**)"
#1  0x0822e0c7 in setup_conds (thd=0x8e619e8, tables=0x8e97260, leaves=0x8ea91a8, conds=0x8ea5f4c) at sql_base.cc:4741
        embedded = (TABLE_LIST *) 0x8eaacf8
        embedding = (TABLE_LIST *) 0x8eaacf8
        select_lex = (SELECT_LEX *) 0x8e83dd0
        arena = (class Query_arena *) 0x0
        backup = {_vptr.Query_arena = 0x8613788, free_list = 0x822b74a, mem_root = 0x10, is_backup_arena = false, state = 1131854264}
        it_is_update = false
        _db_func_ = 0x8e83e58 "pqé\bpqé\b\001"
        _db_file_ = 0x4376b990 "X>è\b<9~\bpqé\b<9~\b"
        table = (TABLE_LIST *) 0x8eaa808
        _db_level_ = 135593396
        _db_framep_ = (char **) 0x4376b948
#2  0x0825fd52 in setup_without_group (thd=0x8e619e8, ref_pointer_array=0x8eab400, tables=0x8e97260, leaves=0x8ea91a8, fields=@0x8e83e58, 
    all_fields=@0x8ea5ed8, conds=0x8ea5f4c, order=0x0, group=0x0, hidden_group_fields=0x8ea5ebe) at sql_select.cc:282
        _db_func_ = 0x8e97170 "<9~\b¸·ê\b__vtmp_nGroupToMove"
        _db_file_ = 0x87e393c "<9~\b"
        res = 142489916
        save_allow_sum_func = 0
        _db_level_ = 149438040
        _db_framep_ = (char **) 0x85bbac5
<cut>
[28 Mar 2006 10:34] Konstantin Osipov
The bug is not stored procedures specific, here is a simple test case to repeat the bug with prepared statements:

drop table if exists t1, t2, t3, t4, t5;
drop view if exists v1;

create table t1 (id1 int primary key, id2 int);
create table t2 (id1 int primary key, id2 int);
create table t3 (id1 int primary key, id2 int);
create table t4 (id1 int primary key, id2 int);
create table t5 (id1 int primary key, id2 int);

create view v1 as
  select t1.id1 as id, t5.id1 as ngroupbynsa
  from
   t1 inner join t2 on t2.id2 = t1.id1 left outer join
   (t3 inner join t4 on t4.id1 = t3.id2 inner join t5 on t4.id2 = t5.id1)
   on t3.id2 is not null;

prepare stmt from "select * from v1 where id=2";
execute stmt;
execute stmt;

The server crashes on the second execution of the statement, which hints that there is an optimization at the first execution which damages the parsed statement tree.
[28 Mar 2006 10:35] Konstantin Osipov
0xffffe410 in __kernel_vsyscall ()
(gdb) bt
#0  0xffffe410 in __kernel_vsyscall ()
#1  0x40219541 in raise () from /lib/tls/libc.so.6
#2  0x4021adbb in abort () from /lib/tls/libc.so.6
#3  0x40212925 in __assert_fail () from /lib/tls/libc.so.6
#4  0x085b7062 in __cxa_pure_virtual () at my_new.cc:51
#5  0x0818c704 in Item_cond::fix_fields (this=0x8861c40, thd=0x884b6f0, 
    ref=0x886a658) at item_cmpfunc.cc:2549
#6  0x0822f3e2 in setup_conds (thd=0x884b6f0, tables=0x8865a30, 
    leaves=0x8868028, conds=0x8860524) at sql_base.cc:4741
#7  0x08259e37 in setup_without_group (thd=0x884b6f0, 
    ref_pointer_array=0x88618f8, tables=0x8865a30, leaves=0x8868028, 
    fields=@0x8863104, all_fields=@0x8860490, conds=0x8860524, order=0x0, 
    group=0x0, hidden_group_fields=0x8860476) at sql_select.cc:282
#8  0x0823874a in JOIN::prepare (this=0x885f760, rref_pointer_array=0x88631c0, 
    tables_init=0x8865a30, wild_num=0, conds_init=0x88619d0, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, 
    select_lex_arg=0x8863078, unit_arg=0x8862e50) at sql_select.cc:339
#9  0x0823d77d in mysql_select (thd=0x884b6f0, rref_pointer_array=0x88631c0, 
    tables=0x8865a30, wild_num=0, fields=@0x8863104, conds=0x88619d0, 
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2424588800, result=0x885f3b8, unit=0x8862e50, 
    select_lex=0x8863078) at sql_select.cc:1879
#10 0x08238379 in handle_select (thd=0x884b6f0, lex=0x8862e40, 
    result=0x885f3b8, setup_tables_done_option=0) at sql_select.cc:238
#11 0x081fc9f5 in mysql_execute_command (thd=0x884b6f0) at sql_parse.cc:2483
#12 0x082682f1 in Prepared_statement::execute (this=0x8862e00, 
    expanded_query=0x40b74a20, open_cursor=false) at sql_prepare.cc:2909
#13 0x082669c4 in mysql_sql_stmt_execute (thd=0x884b6f0) at sql_prepare.cc:2303
#14 0x081fca46 in mysql_execute_command (thd=0x884b6f0) at sql_parse.cc:2497
#15 0x08205aa2 in mysql_parse (thd=0x884b6f0, inBuf=0x8865d00 "execute stmt", 
    length=12) at sql_parse.cc:5710
#16 0x081fa9fb in dispatch_command (command=COM_QUERY, thd=0x884b6f0, 
    packet=0x884ebe9 "execute stmt", packet_length=13) at sql_parse.cc:1720
#17 0x081fa21f in do_command (thd=0x884b6f0) at sql_parse.cc:1516
#18 0x081f92b4 in handle_one_connection (arg=0x884b6f0) at sql_parse.cc:1159
#19 0x40175297 in start_thread () from /lib/tls/libpthread.so.0
#20 0x402b037e in clone () from /lib/tls/libc.so.6
[28 Mar 2006 10:41] Konstantin Osipov
Brian, the bug is not stored procedures specific, as the test case above demonstrates.
It's likely that LEFT JOIN elimination algorithm uses wrong memory to allocate new items.
Please reassign to Igor's team, I can review the patch if necessary.
[28 Mar 2006 17:43] Konstantin Osipov
An update: a view is not necessary either.

prepare stmt from "select t1.id1 as id, t5.id1 as ngroupbynsa from
t1 inner join t2 on t2.id2 = t1.id1 left outer join (t3 inner join t4 on t4.id1 = t3.id2 inner join t5 on t4.id2 = t5.id1) on t3.id2 is not null where t1.id1=2";
execute stmt;
execute stmt;

Also crashes the server.
[30 Mar 2006 0:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4299
[31 Mar 2006 16:18] Igor Babaev
ChangeSet
  1.2113 06/03/29 16:45:29 igor@rurik.mysql.com +4 -0
  Fixed bug #18279: crash in the cases when on conditions are moved 
  out of a nested join to the on conditions for the nest.
  The bug happened due to:
  1. The function simplify_joins could change on expressions for nested joins.
     Yet modified on expressions were not saved in prep_on_expr.
  2. On expressions were not restored for nested joins in 
     reinit_stmt_before_use.

The fix will appear in 5.0.20. It was merged into 5.1.
[6 Apr 2006 0:23] Paul Dubois
Noted in 5.0.20, 5.1.9 changelogs.

Complex queries with nested joins could cause a server crash.
(Bug #18279)