Bug #1373 DISTINCT not working with subselects
Submitted: 22 Sep 2003 8:00 Modified: 25 Oct 2003 12:46
Reporter: [ name withheld ] Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[22 Sep 2003 8:00] [ name withheld ] 
Description:

SELECT DISTINCT 
   e.eventid, 
   (SELECT megavalue FROM megavalue WHERE megavalue.megafieldid='17' AND megavalue.eventid=e.eventid) as xaxis, 
   (SELECT megaoption.optiontext FROM megavalue, megaoption WHERE megavalue.megafieldid='9' AND megavalue.eventid=e.eventid AND megaoption.megaoptionid=megavalue.megavalue) as yaxis 
FROM 
   event e, 
   megavalue m 
WHERE 
   e.eventid=m.eventid 
   AND e.logid='1' 
   AND (m.megafieldid='17' OR m.megafieldid='9');

This returns data like the following.  Note that there are no duplicate rows.  However, and this is the bug: the subselects are not evaluated correctly.
==============
463   6    7777
464   6    7777
471   6    7777
476   6    7777
479   6    7777

The same SQL with the DISTINCT removed will produce data like the following.  Note that there are duplicate rows.  Also note that for the first column of 471 you get 3 and 66 returned.  This is different than what's returned for the same 471 with DISTINCT.
==============
463   6    234
464   5    45
479   6    7777
471   3    66
476   6    3295
479   6    7777
464   5    45

I've had similar problems with simpler subselect/distinct and subselect/order by situations.

I'm open to the possibility that I'm doing something wrong.  In particular I wonder if joining the subselect to the main query aliases is causing the problem but I've been able to do the same on simpler demo queries.

How to repeat:

It has been difficult to duplicate.  I know that you don't want to hear that.  I think that my knowledge of SQL is the limiting factor.  With simple situations (i.e. a single subselect and a single join) I'm able to get distinct and/or order by working against a named subselect column.  With the example above and others like it I am able to get the main query working but as soon as I add the DISTINCT keyword I see duplicate returns for the subselect columns.
[25 Sep 2003 12:46] Indrek Siitan 
Could you provide us with SQL dumps of the tables involved, so we can try
to reproduce it using the exact same data/queries? If the dump (zipped)
is under 200kb, you can upload it via the "Files" tab here, otherwise upload
it to ftp://support.mysql.com/pub/mysql/secret and let us know of the
filename.
[14 Feb 2005 22:54] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".