Code

Opened 7 years ago

Closed 6 years ago

#5012 closed (fixed)

Allow QuerySets to handle offsets without limits and negative slicing

Reported by: SmileyChris Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: qs-rf-fixed
Cc: cgrady@…, michael@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

This patch allows QuerySets to:

  1. use negative slicing (with the caveat that they require explicit ordering) and
  1. use offsets without limits (eg Entries.objects.all()[5:])

So really it's two separate features, but negative slicing was the feature I was trying to implement and to make it work in a logical way it required fixing offsets without limits.

Feature 2 requires a change to the db backends. All except Oracle (because the comment in django.db.backends.oracle.base::get_limit_offset_sql says it's handled in ./query.py but that file doesn't exist) and MS-SQL (because it never worked there to begin with).

Attachments (3)

negative_sliced_querysets.patch (16.6 KB) - added by SmileyChris 7 years ago.
negative_sliced_querysets_oracle.patch (2.9 KB) - added by ian.g.kelly@… 7 years ago.
Negative indexing patch of django/db/backends/oracle/base.py
negative_sliced_querysets_oracle.2.patch (2.6 KB) - added by ian.g.kelly@… 7 years ago.

Download all attachments as: .zip

Change History (14)

Changed 7 years ago by SmileyChris

comment:1 Changed 7 years ago by SmileyChris

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Design decision needed

comment:2 Changed 7 years ago by SmileyChris

Finishing that last sentence of the topic: "... are included in the patch"

comment:3 Changed 7 years ago by ian.g.kelly@…

That Oracle comment needs to be changed: the QuerySet subclass that was in query.py at one point wound up back in base.py. I'll take a look at supplying an Oracle patch for this.

Changed 7 years ago by ian.g.kelly@…

Negative indexing patch of django/db/backends/oracle/base.py

comment:4 Changed 7 years ago by ian.g.kelly@…

One of the test cases for this is failing:

In [63]: Article.objects.all()[-5:-2][-1]
---------------------------------------------------------------------------
<type 'exceptions.IndexError'>            Traceback (most recent call last)

/home/ikelly/projects/testproject/<ipython console> in <module>()

/home/ikelly/projects/django.trunk/django/db/models/query.py in __getitem__(self, k)
    216                 try:
    217                     return list(self._clone(_offset=k, _limit=1,
--> 218                                             **extra_clone_args))[0]
    219                 except self.model.DoesNotExist, e:
    220                     raise IndexError, e.args

<type 'exceptions.IndexError'>: list index out of range

The sql that the Oracle backend is generating for this case is:

SELECT * FROM
  (SELECT "TEST_ARTICLE"."ID","TEST_ARTICLE"."HEADLINE","TEST_ARTICLE"."PUB_DATE",
          ROW_NUMBER() OVER (ORDER BY "TEST_ARTICLE"."PUB_DATE" DESC, "TEST_ARTICLE"."HEADLINE" DESC ) AS rn
   FROM "TEST_ARTICLE" ORDER BY "TEST_ARTICLE"."PUB_DATE" DESC, "TEST_ARTICLE"."HEADLINE" DESC)
WHERE rn > -2 AND rn <= -1

This query will return no results due to the negative indices. Their presence in the SQL suggests that there's a bug in the way the negative indices are mapped in this case. The same test case also fails using the sqlite backend, but there it simply returns the wrong article rather than generating an error.

Changed 7 years ago by ian.g.kelly@…

comment:5 Changed 7 years ago by Collin Grady <cgrady@…>

  • Cc cgrady@… added

comment:6 Changed 7 years ago by piranha

  • Keywords qs-rf added

comment:7 Changed 7 years ago by miracle2k

  • Cc michael@… added

comment:8 Changed 6 years ago by mtredinnick

(In [7147]) queryset-refactor: Implemented slicing to end of querysets.

Refs #2150, #5012.

comment:9 Changed 6 years ago by mtredinnick

(In [7148]) queryset-refactor: Implemented the reverse() method on querysets.

Refs #5012.

comment:10 Changed 6 years ago by mtredinnick

  • Keywords qs-rf-fixed added; qs-rf removed
  • Triage Stage changed from Design decision needed to Accepted

Negative slicing isn't possible to implement in a way that is analogous with Python's behaviour without being fairly inefficient. The problem is you have to reverse the SQL query's order, limit the result set and then pull the results back into Python and reverse them again. That last step is the problem. We've just moved away from pulling results into memory until we need them (very important for large result sets), so adding it back just for this isn't worth it.

Instead, I've added a reverse() method to querysets so people can reverse and then slice off the front. It's close enough to negative slicing to provide all the same functionality, without being confusing by looking like Python slicing and not behaving precisely the same.

comment:11 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.