Bug #2608 Error using subqueries + order by
Submitted: 2 Feb 2004 5:35 Modified: 5 Mar 2004 11:23
Reporter: Jaroslaw Szczepankiewicz Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.1a-alpha OS:Windows (WinXP)
Assigned to: Assigned Account CPU Architecture:Any

[2 Feb 2004 5:35] Jaroslaw Szczepankiewicz
Description:
I have used something like:

SELECT (SELECT COUNT(*) FROM message WHERE message.id_project=project.id_project AND message.issent=1) AS cnt_sent, id_project, (SELECT COUNT(*) FROM message WHERE message.id_project=project.id_project) AS cnt_messages, (SELECT COUNT(DISTINCT message.id_message) FROM message, messagelifetimenegative WHERE message.id_project=project.id_project AND        messagelifetimenegative.id_message=message.id_message) AS cnt_notsent,        projectname, base.name AS name, (project.creationstamp + 0) AS creationstamp, issent, isactive, useminimalsentstamp,        minimalsentstamp, contenttype, embedobjects, senderidentity.identityname AS identityname, proxysetting.smtpusername AS       smtpusername, proxysetting.smtpservername AS smtpservername, executorthreadcount   FROM project,base,senderidentity,proxysetting WHERE project.isexecuteproject=0 AND project.isprobeproject=0 AND project.id_base=base.id_base AND project.id_identity=senderidentity.id_identity   AND project.id_proxysetting=proxysetting.id_proxysetting 
ORDER BY projectname ASC

and some results(columns) that are from subqueries are not correct (all results from subqueries are mixed), when i delete ORDER BY projectname ASC all results are correct

How to repeat:
use subqueries (3) + order by some column not from subqueries

Suggested fix:
not known
[5 Feb 2004 11:23] Dean Ellis
We need a complete test case, including the SQL to create and populate the tables, in order to test your report.  Please upload a small test which demonstrates the behavior.

Thank you.
[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".