#Why django orm limit '21' instead of '2' when we use `get` to fetch one result

9 messages · Page 1 of 1 (latest)

stiff trellis
#

I saw that django is executing sql queries with limit set to 21 whenever i try to use get method to fetch one result.
ex.

User.objects.get(email='[email protected]')
"""
SQL:
SELECT "account_user"."username",
  "account_user"."email"
  FROM "account_user"
 WHERE "account_user"."email" = '[email protected]'
 LIMIT 21
"""

This was case for all of the models.
I know django checks if there are multiple results and throws error, but for that can't it just set limit to 2?
And how would this impact speed of our sql query.

toxic sinew
#

See here: https://github.com/django/django/pull/11215#issuecomment-483629741
and here: https://stackoverflow.com/questions/60797028/django-annotate-unexpected-limit-21/62195057#62195057
and the original issue when limit was added, here: https://code.djangoproject.com/ticket/6785

TL;DR the rationale is higher limit gives you better error details (better count) when the query returns more than one, provided it's less than 21 obviously. On the performance part, the optimization is not worth it since this is an "error" that you should not be optimizing for.

stiff trellis
dusky pilot
#

Wait what, Oracle did not support LIMIT before ~6 years ago? 😮

kindred oriole
#

hard to believe; maybe they used a different keyword with similar semantics

dusky pilot
#

SELECT * FROM (SELECT ROWNUM AS "_RN", "_SUB".* FROM (SELECT "BAR_MYMODEL"."ID", "BAR_MYMODEL"."FIELD1" FROM "BAR_MYMODEL" WHERE "BAR_MYMODEL"."ID" = :arg0) "_SUB" WHERE ROWNUM <= 21) WHERE "_RN" > 0

#

Looks a lot like it was implemented using ROWNUM 😮

#

Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

#

So yes, they did not have LIMIT.