Opened 14 years ago

Closed 7 years ago

Last modified 4 years ago

#13312 closed New feature (fixed)

Add a way to customize the order_by() of null fields

Reported by: Sergey Dobrov Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: ordering, order_by, null
Cc: contact@…, serhiy.int@…, markus.magnuson@…, josh.smeaton@…, github@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Applying order_by on null=True fields gives different results on mysql and pgsql: pgsql thinks that NULL values are bigger than any and mysql that smaller. I think that this must be unified on all engines for prevent problems with portability. pgsql has additional parameter for this:

SELECT select_list
    FROM table_expression
    ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
             [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

But I don't know how it on other engines.

Change History (27)

comment:1 by James Bennett, 14 years ago

Resolution: wontfix
Status: newclosed

I think this is one of those cases where we really just have to tell people to know the behavior of the DB they choose to use; NULL-handling in all its many forms is one of those things that's just not worth the time and complexity of abstraction.

comment:2 by Sergey Dobrov, 14 years ago

that's ok, but there is no way to control this behavior. i mean that i can't tell pgsql that i want nulls to be less than others in any good way. :(

comment:3 by chexum, 13 years ago

Keywords: null added

Just to document it, there is a very easy way to control this behavior. The most portable seems to be an additional order by for "field IS NULL". It puts NULLs last in at least sqlite3, mysql and PostgreSQL. The reverse is either "field IS NULL DESC" or "field IS NOT NULL".

It's also easy to use in a Django way:

    q = q.extra(select={'null1':'field1 is null','null2':'field2 is null'})
    q = q.extra(order_by=['null1','-field1','-null2','field2'])

Because it's so easy to do in a standard way, it might be worth at some point to add a specific queryset for it (not necessarily modifying order_by()), as extra() is not always desirable. Having a new queryset would allow backend specific optimization, like nulls last/first if supported.

comment:4 by Benny Daon, 13 years ago

thanks chexum

comment:5 by Daniel Hahler, 8 years ago

Easy pickings: unset
Severity: Normal
Type: Uncategorized
UI/UX: unset

See http://stackoverflow.com/a/35494930/15690, which uses a specilaized QuerySet/Query.

The extra() approach is deprecated, and won't work with foreign keys / joined tables.

Last edited 8 years ago by Daniel Hahler (previous) (diff)

comment:6 by Josh Smeaton, 8 years ago

Resolution: wontfix
Status: closednew

Now that we have expressions, I think it's fine to reopen this ticket. We can introduce a OrderNulls.last()/.first() expression to handle this in a standard way. We could also just build it right into the OrderBy expression:

qs.order_by(F('my_field').asc().nullslast())

If we do build this into the existing order_by, you'll need to be careful about preserving a 'feature' that stops django from inserting duplicate ordering clauses (it builds each component then does a string compare to ensure the new clause isn't a duplicate).

This won't normalise order by nulls across backends, but it'll allow users to control null handling semantics if they have a use for it. NULL handling was originally going to be added to the OrderBy expression, but I ran out of time before that could happen.

comment:7 by Tim Graham, 8 years ago

Summary: order_by on null-field gives different results on different db enginesAdd a way to customize the order_by() of null fields
Triage Stage: UnreviewedAccepted
Type: UncategorizedNew feature
Version: 1.1master

comment:8 by Ionel Cristian Mărieș, 8 years ago

Cc: contact@… added

comment:9 by Serhiy, 8 years ago

Cc: serhiy.int@… added

comment:10 by Serhiy, 8 years ago

AFAIU, it's possible in all main supported engines:

ORDER BY IF(ISNULL(my_field),1,0),my_field in MySQL
[NULLS { FIRST | LAST }] in both PostgreSQL and Oracle
ORDER BY col1 IS NULL in SqLite3

comment:11 by Markus Amalthea Magnuson, 8 years ago

Cc: markus.magnuson@… added

comment:12 by Josh Smeaton, 8 years ago

Cc: josh.smeaton@… added

comment:13 by Richard Eames, 8 years ago

Cc: github@… added

comment:14 by Simon Charette, 8 years ago

Has patch: set

comment:15 by Tim Graham, 8 years ago

Patch needs improvement: set

Comments for improvement are on the PR. Please uncheck "Patch needs improvement" after you update it an add documentation (see our PatchReviewChecklist).

comment:16 by Simon Charette, 8 years ago

Patch needs improvement: unset

comment:17 by Tim Graham, 8 years ago

Needs documentation: set

comment:18 by Tim Graham, 8 years ago

Needs documentation: unset
Patch needs improvement: set

comment:19 by Tim Graham, 7 years ago

Patch needs improvement: unset

comment:20 by Mariusz Felisiak, 7 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:21 by Simon Charette, 7 years ago

Triage Stage: AcceptedReady for checkin

Looking good pending some cosmetic changes.

comment:22 by Tim Graham <timograham@…>, 7 years ago

Resolution: fixed
Status: assignedclosed

In 47ef8f3:

Fixed #13312 -- Allowed specifying the order of null fields in queries.

Thanks Mariusz Felisiak for finishing the patch.

comment:23 by CHI Cheng, 7 years ago

What about one parameter with three possible values?

nulls_first:

  • True: nulls at first
  • False: nulls at last
  • None (default): database default ordering
Version 0, edited 7 years ago by CHI Cheng (next)

comment:24 by Josh Smeaton, 7 years ago

I don't like the idea of three-value logic here, because it imposes a slight burden when reading. It just seems unnecessary when there's nothing wrong with two kwarg options.

comment:25 by Simon Charette, 7 years ago

@Cloudream see the discussion on the original PR about why nulls_first and nulls_last were chosen.

comment:26 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In a699595f:

Refs #13312 -- Removed unnecessary IF wrapping in nulls_last handling on MySQL.

ISNULL function already returns 0 and 1 on MySQL.

comment:27 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 7286eaf:

Refs #13312 -- Simplified handling of nulls ordering on MySQL.

MySQL & MariaDB support the standard IS NULL and IS NOT NULL so
the same workaround used for NULLS FIRST and NULLS LAST that is
used for SQLite < 3.30.0 can be used.

Thanks Simon Charette for the discussion.

Note: See TracTickets for help on using tickets.
Back to Top