MySQL Bugs: #11877: UNION and ORDER BY

Bug #11877	UNION and ORDER BY
Submitted:	12 Jul 2005 8:23	Modified:	9 Feb 2006 15:39
Reporter:	Tom Schindl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	4.1.12a	OS:	Windows (WinXP/Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
the following Statement worked on MySQL 4.0 but after upgrading to
4.1.12 on win32 the ORDER BY is not working any more. Is this a known
problem or is our SQL simply not useable on 4.1 or is there a bug in
MySQL 4.1.

----------------------------8<----------------------------
(
select
       p.*
 from
       persons p,
       client_contracts cc
 where
       cc.cc_fk_person = p.p_id and
       p.delmark = 0 and
       cc.delmark = 0 and
       p.p_id = 0 -- only mohi
)
UNION
(
select
       p.*
from
       persons p,
       client_contracts cc
where
       cc.cc_fk_person = p.p_id and
       p.delmark = 0 and
       cc.delmark = 0 and
       p.p_id != 0 -- no mohi
order by
       p.p_surname,
       p.p_givenname
)
----------------------------8<----------------------------

Should return:
---------------------------------
| p_id | p_givenname | p_surname|
| 0      | MOHI                | MOHI            | => First part of union
| 1      | Vorname          | ANachname|
| 100  | Vorname          | BNachname|
| 3      | Vorname          | CNachname|

But it returns:
---------------------------------
| p_id | p_givenname | p_surname|
| 0       | MOHI               | MOHI            | => First part of union
| 1       | Vorname         | ANachname|
| 3       | Vorname         | CNachname|
| 100  | Vorname         | BNachname|

How to repeat:
Simply by executing the SQL-Script from above

could you attach tables definition and some example data, that we can try to reproduce.  And what is output of

show variables like '%char%';

?

Running the SQL against this database shows the error

Attachment: wrong-db.dump (application/octet-stream, text), 119.29 KiB.

We can now also reproduce the problem on Linux

Forgot the output of %CHAR%:
Variable_name	Value
character_set_client	latin1
character_set_connection	latin1
character_set_database	utf8
character_set_results	latin1
character_set_server	utf8
character_set_system	utf8
character_sets_dir	C:\\mysql41\\share\\charsets/

Sorry. But We need your table definitions too. You can do it commands:

show create table persons;
show create table client_contracts;

isn't that also part of the dump file I added?

create the table person

Attachment: persons.create (application/octet-stream, text), 1.94 KiB.

contracts creation

Attachment: contracts.create (application/octet-stream, text), 1.21 KiB.

Yes the dump attached is enough. Thanks.

Unfortunately ORDER BY within parentheses of  a select node in UNION's is no longer supported as it is not ANSI SQL.

This is documented in a manual.

Then its documented in the wrong place and at least not in http://dev.mysql.com/doc/mysql/en/union.html

Where one can read this:
----------------8<----------------
 To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

ORDER BY for individual SELECT statements within parentheses only has an effect when combined with LIMIT. Otherwise, the ORDER BY is optimized away. 
----------------8<----------------
Nor is it mentionned in :
http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html.
http://dev.mysql.com/doc/mysql/en/news-4-1-x.html

How can now one achieve this in MySQL 4.1 then if it is dropped? If this syntax is not allowed any more I should at least get an SQL-Error when using such a statement telling my that it is not correct "ANSI-"SQL.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated UNION section to indicated that ORDER BY for invididual
SELECT in UNION is not supported.

Opening this again. The change to the manual was not correct.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I have updated this section with additional information about
UNION and ORDER BY:

http://dev.mysql.com/doc/refman/5.0/en/union.html

The information also includes a workaround that may be useful
for obtaining the desired behavior described in the initial bug
report.