Bug #11390 ORDER BY in UNION ALL query Don't sort

• Submitted: 16 Jun 2005 15:59
• Modified: 16 Jun 2005 19:13
• Reporter: sergio Ros
• Status: Won't fix
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.9
• OS: Linux (Linux)
• CPU Architecture: Any

[16 Jun 2005 15:59] sergio Ros

Description:
When use UNION ALL in SELECT sentence and use ORDER BY in both SELECT, don't sort corectly this.

Example :

(SELECT * FROM inmuebles LEFT JOIN municipios ON inmuebles.Poblacion_INM = municipios.CP_MUN LEFT JOIN agencias ON inmuebles.IDAGE_INM = agencias.ID_AGE WHERE foto1 != '' AND Activo_INM <> 'N' AND Precio_INM BETWEEN '0' AND '99999999' AND (Pack_AGE = 'P-100' OR Pack_AGE = 'P-50') ORDER BY Precio_INM ASC) 
<<This ORDER BY Don't work correctly>>

UNION ALL 

(SELECT * FROM inmuebles LEFT JOIN municipios ON inmuebles.Poblacion_INM = municipios.CP_MUN LEFT JOIN agencias ON inmuebles.IDAGE_INM = agencias.ID_AGE WHERE foto1 != '' AND Activo_INM <> 'N' AND Precio_INM BETWEEN '0' AND '99999999' AND Pack_AGE = 'P-20' 
ORDER BY Precio_INM ASC) 
<<This ORDER BY Don't work correctly>>

LIMIT 0, 10 

If I do each Query alone work correctly ORDER BY sentence but UNION ALL disorder the query or order by another field.

How to repeat:
I don't know

[16 Jun 2005 16:07] sergio Ros

This example work correctly in Windows S.O.

[16 Jun 2005 16:32] sergio Ros

This change : LIMIT 0,10 after ORDER BY make that ORDER BY sort correctly by field Precio_INM.
But isn't correct because the select return XX records of the first SELECT and XX of the second SELECT.

The reason that LIMIT X,XX on the end of query (ex. 1). is for that results are XX records of both SELECT's

(SELECT * FROM inmuebles LEFT JOIN municipios ON inmuebles.Poblacion_INM = municipios.CP_MUN LEFT JOIN agencias ON inmuebles.IDAGE_INM = agencias.ID_AGE WHERE foto1 != '' AND Activo_INM <> 'N' AND Precio_INM BETWEEN '0' AND '99999999' AND (Pack_AGE = 'P-100' OR Pack_AGE = 'P-50') ORDER BY Precio_INM ASC LIMIT 0,10) 
<< with LIMIT X,XX UNION ALL work correctly >>

UNION ALL 

(SELECT * FROM inmuebles LEFT JOIN municipios ON inmuebles.Poblacion_INM = municipios.CP_MUN LEFT JOIN agencias ON inmuebles.IDAGE_INM = agencias.ID_AGE WHERE foto1 != '' AND Activo_INM <> 'N' AND Precio_INM BETWEEN '0' AND '99999999' AND Pack_AGE = 'P-20' 
ORDER BY Precio_INM ASC LIMIT 0,10)
<< with LIMIT X,XX UNION ALL work correctly >>

[16 Jun 2005 17:13] MySQL Verification Team

Unfortunately, sorting individual SELECT nodes within braces in UNION is no longer supported.

This support was discontinued in order to make better support of braces for some other features, like nested queries, nested join's etc ..

[16 Jun 2005 19:13] Sergei Golubchik

UNION and UNION ALL operate on "tables", and a "table" according to the SQL standard, is an "unordered set of rows". It means you cannot expect any particular order as a result of UNION.

[17 Jun 2005 10:02] LeChuck the Pirate

Well I find annoying removing features ... this can make applications already done fail when changed from one server to another (I think that's the case)

Shame on it...