Bug #45762 configure NULL position in ORDER BY

• Submitted: 25 Jun 2009 17:24
• Reporter: Scott Noyes
• Status: Verified
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: any
• OS: Any
• CPU Architecture: Any

[25 Jun 2009 17:24] Scott Noyes

Description:
Requesting a configuration to cause ORDER BY to put NULL last instead of first when using ASC.

How to repeat:
CREATE TABLE t (d int NULL);
INSERT INTO t VALUES (NULL), (1);
SELECT * FROM t ORDER BY d;
-- returns (NULL), (1)

SET NULL_ORDERS_LAST=1;
SELECT * FROM t ORDER BY d;
-- should now return (1), NULL;

Suggested fix:
Add suggested configuration

[2 Mar 2016 7:29] Guilhem Bichot

In SQL:2015 draft, if optional feature T611 is supported then there is NULLS FIRST / NULLS LAST at end of ORDER BY.
To have efficient index usage, it should also be allowed to specify this when creating the index.
Current workaround:
http://dev.mysql.com/doc/refman/5.7/en/working-with-null.html
" When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC."
So, if the column is numeric,
ORDER BY col ASC
can be changed to
ORDER BY -col DESC
to get NULLs last. However this will prevent the use of any index on "col" to resolve the ordering.
Another similar workaround:
ORDER BY isnull(col) ASC, col ASC