Bug #48483 crash in get_best_combination()
Submitted: 2 Nov 2009 18:03 Modified: 20 Jun 2010 17:49
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.88,5.1.43, 5.4.3 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[2 Nov 2009 18:03] Shane Bester
Description:
stack trace of 5.1.40

mysqld.exe!memcpy()[memcpy.asm:192]
mysqld.exe!get_best_combination()[sql_select.cc:5496]
mysqld.exe!make_join_statistics()[sql_select.cc:2904]
mysqld.exe!JOIN::optimize()[sql_select.cc:955]
mysqld.exe!mysql_select()[sql_select.cc:2380]
mysqld.exe!handle_select()[sql_select.cc:268]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5044]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2238]
mysqld.exe!mysql_parse()[sql_parse.cc:5967]
mysqld.exe!dispatch_command()[sql_parse.cc:1226]
mysqld.exe!do_command()[sql_parse.cc:865]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]

How to repeat:
delimiter ;

drop table if exists `t1`;
create table `t1`(`a` int);
insert into `t1` values (1),(2);

drop function if exists `f1`;
delimiter $

create function `f1`() returns int reads sql data 
begin return 1; end $

delimiter ;

create or replace view `v1` as
select 1 from `t1` left join `t1` as `a` on 1=1;

select 1 from `v1` as `a` right join `v1` on `f1`();
[2 Nov 2009 21:49] Miguel Solorzano
Thank you for the bug report. Verified as described:

Version: '5.1.41-Win X64-debug-log'  socket: ''  port: 3510  Source distribution
091102 19:43:35 - mysqld got exception 0xc0000005 ;
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=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338112 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x41d20c8
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...
00000001402B4484    mysqld.exe!get_best_combination()[sql_select.cc:5495]
00000001402AD0D9    mysqld.exe!make_join_statistics()[sql_select.cc:2904]
00000001402A4837    mysqld.exe!JOIN::optimize()[sql_select.cc:955]
00000001402AB154    mysqld.exe!mysql_select()[sql_select.cc:2380]
00000001402A2038    mysqld.exe!handle_select()[sql_select.cc:268]
000000014020C5AA    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5043]
00000001402026F5    mysqld.exe!mysql_execute_command()[sql_parse.cc:2238]
000000014020F34D    mysqld.exe!mysql_parse()[sql_parse.cc:5967]
00000001401FFCCC    mysqld.exe!dispatch_command()[sql_parse.cc:1226]
00000001401FEFDA    mysqld.exe!do_command()[sql_parse.cc:865]
00000001400C48B5    mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
00000001405FCEB5    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001405D30F5    mysqld.exe!_callthreadstart()[thread.c:295]
00000001405D30C7    mysqld.exe!_threadstart()[thread.c:277]
0000000076CEC3BD    kernel32.dll!BaseThreadInitThunk()
0000000077024581    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000004239F08=select 1 from `v1` as `a` right join `v1` on `f1`()
thd->thread_id=1
thd->killed=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.
[30 Dec 2009 16:51] Shane Bester
a simpler testcase:

drop table if exists `t1`;
create table `t1`(`a` int)engine=innodb;
select 1 from `t1` join `t1` `t2`
on rand() right join `t1` `t3` on 1;
[11 Mar 2010 12:35] 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/102997

3386 Sergey Glukhov	2010-03-11
      Bug#48483 crash in get_best_combination()
      The crash happens because greedy_serach
      can not determine best plan due to
      wrong inner table dependences. These
      dependences affects join table sorting
      which performs before greedy_search starting.
      In our case table which has real 'no dependences'
      should be put on top of the list but it does not
      happen as inner tables have no dependences as well.
      The fix is to exclude RAND_TABLE_BIT mask from
      condition which checks if table dependences
      should be updated.
     @ mysql-test/r/innodb_mysql.result
        test result
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        RAND_TABLE_BIT mask should not be counted as it
        prevents update of inner table dependences.
        For example it might happen if RAND() function
        is used in JOIN ON clause.
[19 Mar 2010 11:12] 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/103813

3412 Sergey Glukhov	2010-03-19
      Bug#48483 crash in get_best_combination()
      The crash happens because greedy_serach
      can not determine best plan due to
      wrong inner table dependences. These
      dependences affects join table sorting
      which performs before greedy_search starting.
      In our case table which has real 'no dependences'
      should be put on top of the list but it does not
      happen as inner tables have no dependences as well.
      The fix is to exclude RAND_TABLE_BIT mask from
      condition which checks if table dependences
      should be updated.
     @ mysql-test/r/join.result
        test case
     @ mysql-test/t/join.test
        test case
     @ sql/sql_select.cc
        RAND_TABLE_BIT mask should not be counted as it
        prevents update of inner table dependences.
        For example it might happen if RAND() function
        is used in JOIN ON clause.
[24 Mar 2010 7:17] Shane Bester
Hi Sergey!

Here's another testcase:
---
drop table if exists `t1`,`t2`;
create table `t1`(`a` int)engine=myisam;
create table `t2`(`b` int)engine=myisam;
insert into `t2` values (1),(2);
drop function if exists `f1`;
create function `f1`() returns timestamp return 1;
select 1 from `t1` join `t1` `t3` on `f1`()
natural right outer join `t2`;
---
[24 Mar 2010 10:13] 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/104163

3413 Sergey Glukhov	2010-03-24
      Bug#48483 crash in get_best_combination()
      The crash happens because greedy_serach
      can not determine best plan due to
      wrong inner table dependences. These
      dependences affects join table sorting
      which performs before greedy_search starting.
      In our case table which has real 'no dependences'
      should be put on top of the list but it does not
      happen as inner tables have no dependences as well.
      The fix is to exclude RAND_TABLE_BIT mask from
      condition which checks if table dependences
      should be updated.
     @ mysql-test/r/join.result
        test case
     @ mysql-test/t/join.test
        test case
     @ sql/sql_select.cc
        RAND_TABLE_BIT mask should not be counted as it
        prevents update of inner table dependences.
        For example it might happen if RAND() function
        is used in JOIN ON clause.
[24 Mar 2010 10:38] 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/104171

2853 Sergey Glukhov	2010-03-24
      Bug#48483 crash in get_best_combination()
      The crash happens because greedy_serach
      can not determine best plan due to
      wrong inner table dependences. These
      dependences affects join table sorting
      which performs before greedy_search starting.
      In our case table which has real 'no dependences'
      should be put on top of the list but it does not
      happen as inner tables have no dependences as well.
      The fix is to exclude RAND_TABLE_BIT mask from
      condition which checks if table dependences
      should be updated.
     @ mysql-test/r/join.result
        test result
     @ mysql-test/t/join.test
        test case
     @ sql/sql_select.cc
        RAND_TABLE_BIT mask should not be counted as it
        prevents update of inner table dependences.
        For example it might happen if RAND() function
        is used in JOIN ON clause.
[26 Mar 2010 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[6 Apr 2010 7:53] Bugs System
Pushed into 5.0.91 (revid:joro@sun.com-20100406075152-flz4btqirl9hly31) (version source revid:sergey.glukhov@sun.com-20100324103728-lkiflp9a3nq3x5ez) (merge vers: 5.0.91) (pib:16)
[6 Apr 2010 7:59] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:sergey.glukhov@sun.com-20100324104509-6eawbtwzecd41f5r) (merge vers: 5.1.46) (pib:16)
[15 Apr 2010 15:29] Paul Dubois
Noted in 5.0.91, 5.1.46, 6.0.14 changelogs.

The server crashed when it could not determine the best execution
plan for queries involving outer joins and functions such as RAND() 
for which the sign of the result is random.

Setting report to Need Merge pending push into Celosia.
[19 Apr 2010 13:31] Paul Dubois
Revised changelog entry:

The server crashed when it could not determine the best execution
plan for queries involving outer joins with nondeterministic ON
clauses such as the ones containing the RAND() function, a
user-defined function, or a NOT DETERMINISTIC stored function.

Setting report to Need Merge pending push into Celosia.
[28 May 2010 6:02] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)
[28 May 2010 6:58] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100402142218-6qwdp8qkdd1pwvis) (merge vers: 5.5.4-m3) (pib:16)
[30 May 2010 0:25] Paul Dubois
Noted in 5.5.5 changelog.
[17 Jun 2010 12:06] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:51] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:33] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)