Opened 5 years ago

Closed 5 years ago

Last modified 4 years ago

#13312 closed (wontfix)

order_by on null-field gives different results on different db engines

Reported by: binary Owned by: nobody
Component: Database layer (models, ORM) Version: 1.1
Severity: Keywords: ordering, order_by, null
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

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 (4)

comment:1 Changed 5 years ago by ubernostrum

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

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 Changed 5 years ago by binary

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 Changed 5 years ago by chexum

  • 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 Changed 4 years ago by daonb

thanks chexum

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