MySQL Bugs: #31269: IFNULL don't work on order by

Bug #31269	IFNULL don't work on order by
Submitted:	28 Sep 2007 5:06	Modified:	29 Mar 2011 19:00
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
ORDER BY cfop_numero DESC or ASC don't work, since '0' always become first than anyother data:

SELECT a.plano_conta_id_red,a.nome,a.aliquota,a.reducao,a.aliquota_origem,a.reducao_origem,a.obs_faturamento,a.cfop_id,a.cfop_id_red,
IFNULL((SELECT plano_conta_numero FROM cfop_itens WHERE plano_conta_id=a.cfop_id AND plano_conta_id_red=a.cfop_id_red),'0') AS cfop_numero 
FROM impostos_valores AS a 
WHERE 
a.pais_origem="BR" AND a.uf_origem="" AND a.cidade_origem="" AND a.pais_destino="BR" AND a.uf_destino="" AND 
a.cidade_destino="" AND a.plano_conta_id=1 AND a.nome="IPI" AND plano_conta_id_red IN (
SELECT plano_conta_id_red FROM estoque_itens WHERE plano_conta_id=1 AND plano_conta_numero REGEXP "^([^.]*)\.009\.([^.]*)\.([^.]*)\.([^.]*)$"
) 
ORDER BY a.cfop_id,cfop_numero DESC,a.plano_conta_id,a.plano_conta_id_red,a.nome LIMIT 30 OFFSET 0

How to repeat:
file attacheds

Suggested fix:
maybe an sql_mode may be wrong?! i don't know

SQL_MODE:
PIPES_AS_CONCAT,ALLOW_INVALID_DATES

Thank you for the report.

Please also provide dump of tables cfop_itens and estoque_itens

Files appended

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

In your query: 'ORDER BY a.cfop_id,cfop_numero DESC', so first data sorted by a.cfop_id in default ascending order and only then by cfop_numero. In your data for cfop_numero which is equal '0' always corresponds cfop_id equal '0' too. So order is correct.

ok closed