Bug #46733 NULL value not returned for aggregate on empty result set w/ semijoin on
Submitted: 14 Aug 2009 17:02 Modified: 23 Nov 2010 2:56
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4 / 6.0 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: aggregate, semijoin

[14 Aug 2009 17:02] Patrick Crews
Description:
Azalea / 6.0 is not returning a NULL value when an aggregate is applied to an empty result set.

This only occurs when semijoin is on.

SELECT  MAX( table1 .`pk`  )  
FROM CC table1  JOIN B table2  ON table2 .`varchar_nokey`  
ORDER  BY table2 .`varchar_key`   ;

Returns NULL in 5.1, nothing in azalea when semijoin=on

How to repeat:
MTR test case is attached:
Coment / uncomment the appropriate Server0 / Server1 line depending on which version of the server is being tested.

Toggle the semijoin value on and off and observe the change in result sets.

Suggested fix:
Ensure correct query processing regardless of optimizer settings.
[14 Aug 2009 17:02] Patrick Crews
MTR test file for repeating this bug

Attachment: bug46733_test.txt (text/plain), 12.33 KiB.

[24 Sep 2009 13:53] Jørgen Løland
The problem is repeatable in mysql-6.0-codebase-bugfixing if semijoin is turned off as well.
[25 Sep 2009 13:24] 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/84653

2841 Jorgen Loland	2009-09-25
      Bug#46733 - NULL value not returned for aggregate on empty result 
                  set w/ semijoin on
      
      Issue was fixed by BUG#46328. This patch adds a regression test
      for the query that was reported in BUG#46733.
     @ mysql-test/r/join_cache.result
        Add regression test for BUG#46733
     @ mysql-test/t/join_cache.test
        Add regression test for BUG#46733
[25 Sep 2009 13:31] Jørgen Løland
The bug was fixed by the patch for BUG#46328. The patch above contains a regression test only.
[28 Sep 2009 9:59] 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/84814

3624 Jorgen Loland	2009-09-28
      Bug#46733 - NULL value not returned for aggregate on empty result 
                  set w/ semijoin on
      
      Issue was fixed by BUG#46328. This patch adds a regression test
      for the query that was reported in BUG#46733.
     @ mysql-test/r/join_cache.result
        Add regression test for BUG#46733
     @ mysql-test/t/join_cache.test
        Add regression test for BUG#46733
[28 Sep 2009 12:43] Øystein Grøvlen
Good to push.
[28 Sep 2009 13:31] Jørgen Løland
Regression test pushed to mysql-6.0-codebase-bugfixing
[9 Oct 2009 8:47] Bugs System
Pushed into 6.0.14-alpha (revid:alik@ibmvm-20091009083208-0o0f0i9w1sq3c1kn) (version source revid:jon.hauglid@sun.com-20090929073341-4ev88lvoxsil8t41) (merge vers: 6.0.14-alpha) (pib:12)
[16 Oct 2009 0:06] Paul DuBois
Noted in 6.0.14 changelog.

The optimizer failed to return NULL for an aggregate applied to an
empty result when semijoin optimization was used.
[6 May 2010 19:43] 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/107688

3139 oystein.grovlen@sun.com	2010-05-06
      Bug#46733 - NULL value not returned for aggregate on empty result 
                  set w/ semijoin on
      
      Issue was fixed by BUG#46328. This patch adds a regression test
      for the query that was reported in BUG#46733.
     @ mysql-test/r/join_cache.result
        Add regression test for BUG#46733
     @ mysql-test/t/join_cache.test
        Add regression test for BUG#46733
[16 Aug 2010 6:32] 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:22] 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)
[23 Nov 2010 2:56] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.