Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
Submitted: 11 Jan 2010 14:27 Modified: 22 Nov 2010 1:08
Reporter: Tor Didriksen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Tor Didriksen CPU Architecture:Any

[11 Jan 2010 14:27] Tor Didriksen 
Description:
==1435== Invalid read of size 8
==1435==    at 0x7666A0: fix_semijoin_strategies_for_picked_join_order(JOIN*) (sql_select.cc:7961)
==1435==    by 0x7671A5: get_best_combination(JOIN*) (sql_select.cc:8152)
==1435==    by 0x75EF76: make_join_statistics(JOIN*, TABLE_LIST*, Item*, st_dynamic_array*) (sql_select.cc:4537)
==1435==    by 0x755C63: JOIN::optimize() (sql_select.cc:1621)
==1435==    by 0x75B2E2: 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*) (sql_select.cc:3123)
==1435==    by 0x78A3F9: mysql_explain_union(THD*, st_select_lex_unit*, select_result*) (sql_select.cc:22247)
==1435==    by 0x6E413D: execute_sqlcom_select(THD*, TABLE_LIST*) (sql_parse.cc:4933)
==1435==    by 0x6DBCD8: mysql_execute_command(THD*) (sql_parse.cc:2157)
==1435==    by 0x6E64FA: mysql_parse(THD*, char const*, unsigned int, char const**) (sql_parse.cc:5969)
==1435==    by 0x6D95AA: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1082)
==1435==    by 0x6D8892: do_command(THD*) (sql_parse.cc:766)
==1435==    by 0x6D6EAB: handle_one_connection (sql_connect.cc:1163)
==1435==    by 0x3F24406A39: start_thread (in /lib64/libpthread-2.11.so)
==1435==    by 0x3F238DDF3C: clone (in /lib64/libc-2.11.so)

How to repeat:
CREATE TABLE t1 (i INTEGER);
CREATE TABLE t2 (i INTEGER);
CREATE TABLE t3 (i INTEGER);

INSERT INTO t1 VALUES (1), (2);

EXPLAIN EXTENDED
SELECT * FROM t1 WHERE (t1.i) IN 
  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);

DROP TABLE t1, t2, t3;

Suggested fix:
The problem seems to be wrong cost calculation for semijoins.
We sometimes end up with NaN cost for IO, which may lead to
choice of wrong plan, or (as in this case) no plan at all.
[13 Jan 2010 10:05] 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/96711

3823 Tor Didriksen	2010-01-13
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.cc
        Dbug assert that cost != NaN
     @ sql/handler.h
        Dbug assert that cost != NaN
     @ sql/sql_select.cc
        Ensure that we don't divide by zero when calculating cost.
[14 Jan 2010 11:23] 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/96877

3826 Tor Didriksen	2010-01-14
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.h
        Don't divide by zero.
        Dbug assert that cost is finite and != NaN
     @ sql/sql_select.cc
        Ensure that we don't divide by zero when calculating cost.
[14 Jan 2010 14:59] Øystein Grøvlen 
Patch approved, but please consider these question/comments:

 - Is it safe to assume that if a float is != 0.0, that dividing by it
   does not create a NaN?  I am no expert, just asking.

 - I do not understand the reasoning for still avoiding 0 io_count.
   Why cannot 0 be a valid cost?

 - Why is it checked for both !isnan and isfinite?  I thought isnan => isfinite.
   (in other words, isfinite => !isnan)
[15 Jan 2010 7:57] 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/97001

3829 Tor Didriksen	2010-01-14
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.h
        Don't divide by zero.
        Dbug assert that cost is finite and != NaN
     @ sql/sql_select.cc
        Ensure that we don't divide by zero when calculating cost.
[15 Jan 2010 12: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/97057

3830 Tor Didriksen	2010-01-15
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.h
        Don't divide by zero.
        Dbug assert that cost is finite and != NaN
     @ sql/sql_select.cc
        Keep existing cost estimate unchanged if we have zero rows.
[15 Jan 2010 12:15] Tor Didriksen 
pushed to
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/
[15 Jan 2010 13:48] 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/97078

3831 Tor Didriksen	2010-01-15
      Postfix for Bug #50237, isfinite failed to compile on solaris/sunstudio/debug
     @ sql/handler.h
        isfinite/finite/HAVE_FINITE seems to be broken for solaris
[21 Jan 2010 8:38] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100121083501-but9pj2g3zmu10md) (version source revid:alik@sun.com-20100119194323-gcog2uiox2b7wsln) (merge vers: 6.0.14-alpha) (pib:16)
[23 Jan 2010 0:43] Paul DuBois 
Noted in 6.0.14 changelog.

Cost calculation for semijoins could be incorrect, leading to an
incorrect execution plan or a server crash.
[14 May 2010 8:57] 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/108296

3174 Tor Didriksen	2010-05-14
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      Backport of tor.didriksen@sun.com-20100115121255-26i42spl5b1bg1o9
      
      Conflicts:
        subselect_sj.test and result files.
        because fix for Bug#49845 has already been backported.
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.h
        Don't divide by zero.
        Dbug assert that cost is finite and != NaN
     @ sql/sql_select.cc
        Keep existing cost estimate unchanged if we have zero rows.
[14 May 2010 9:08] 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/108297

3175 Tor Didriksen	2010-05-14
      Postfix for Bug #50237, isfinite failed to compile on solaris/sunstudio/debug
      
      Backport of tor.didriksen@sun.com-20100115134756-dpm6exlim49w71u1
     @ sql/handler.h
        isfinite/finite/HAVE_FINITE seems to be broken for solaris
[14 May 2010 9:09] 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/108298

3175 Tor Didriksen	2010-05-14
      Bug #50237 Segfault in fix_semijoin_strategies_for_picked_join_order
      
      Backport of tor.didriksen@sun.com-20100115121255-26i42spl5b1bg1o9
      
      Conflicts:
        subselect_sj.test and result files.
        because fix for Bug#49845 has already been backported.
      
      The problem was wrong cost calculation for semijoins.
      We sometimes ended up with NaN cost for IO, which may lead to
      choice of wrong plan, or (as in this case) no plan at all.
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/handler.h
        Don't divide by zero.
        Dbug assert that cost is finite and != NaN
     @ sql/sql_select.cc
        Keep existing cost estimate unchanged if we have zero rows.
[14 May 2010 9:09] 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/108299

3176 Tor Didriksen	2010-05-14
      Postfix for Bug #50237, isfinite failed to compile on solaris/sunstudio/debug
      
      Backport of tor.didriksen@sun.com-20100115134756-dpm6exlim49w71u1
     @ sql/handler.h
        isfinite/finite/HAVE_FINITE seems to be broken for solaris
[16 Aug 2010 6:38] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:26] Bugs System 
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:08] Paul DuBois 
Noted in 5.6.1 changelog.
[23 Nov 2010 2:23] Paul DuBois 
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.