`annotate()` + `select_related()` + `only()` = `ValueError: invalid literal for int() with base 10`

[Tai Lee]

With some combinations of models and arguments to annotate(), select_related() and only(), I get ValueError: invalid literal for int() with base 10.

I think that SQLCompiler.results_iter() is not taking the deferred fields into account when calculating the aggregate_start offset.

To create a test, I started with a large number of models each with a large number of fields, taken from an app that we have in production, and pared it down by trial and error to as simple a form as I could and still reproduce the error.

As I did this (choosing models and fields to remove at random) the behaviour changed from raising a ValueError, to yielding no items when consuming the queryset (but with .count() still returning a positive number), to apparently working as expected.

This is probably related to (or the same issue) that was reported in the comments of #11890.

I had a crack at calculating the correct aggregate_start by looking at the deferred_loading attribute and the q.get_loaded_field_names() method on the Query object, but didn't have any luck.

I've put the test in it's own module, defer_annotate_select_related_only, just because it seems like such an obscure edge-case and I didn't want the other models and tests in defer_regress to have any influence.

Hopefully if someone can figure out how and why this is happening, we can integrate a more concise test into defer_regress.

[Tai Lee]

I've updated the patch with a couple more tests and a fix. The fix calculates the aggregate_start offset from loaded fields, if any are deferred, instead of self.query.select which includes all fields on the model.

[Tai Lee]

Failing test case and fix.

[Tai Lee]

Replaced tabs with spaces.

[Tai Lee]

I also noticed that a TypeError raised in resolve_aggregate() is silenced (not sure where), which causes the queryset to yield no results, even though .count() returns a positive number. But other exceptions, such as ValueError are not silenced.

This is why the symptoms of this bug vary between raising a ValueError or yielding no results. If the aggregate_start was calculated in such a way that a datetime value was passed to resolve_aggregate() for an ordinal aggregate such as Count, a TypeError exception is silenced. If a non-numerical string value is passed to an ordinal aggregate, a ValueError exception is raised.

This patch fixes both cases by correctly calculating aggregate_start, but it might also be a good idea to track down where TypeError is being swallowed and fix that, too.

[Jacob]

Milestone 1.4 deleted

[Tai Lee]

I've updated this patch on a branch at GitHub to apply cleanly.

​https://github.com/thirstydigital/django/tree/tickets/16436-annotate-select_related-only

I think this is RFC. Does anyone have feedback or issues that would prevent this from being committed?

[Tai Lee]

Rebased onto master and opened a pull request.

​https://github.com/django/django/pull/895

[FrankBie]

For me it seems to be ready to checkin.

[Anssi Kääriäinen <akaariai@…>]

In 69f7db153d8108dcef033207d49f4c80febf3d70:

Fixed #16436 -- defer + annotate + select_related crash

Correctly calculate the aggregate_start offset from loaded fields,
if any are deferred, instead of self.query.select which includes all
fields on the model.

Also made some PEP 8 fixes.

[Aymeric Augustin]

Unfortunately the test doesn't pass under Oracle:
​http://ci.djangoproject.com/job/Django%20Oracle/112/database=oracle,python=python2.7/testReport/junit/defer_regress.tests/DeferAnnotateSelectRelatedTest/test_defer_annotate_select_related/

Since it's a direct consequence of the patch, I'm re-opening the ticket and marking as a blocker.

[Anssi Kääriäinen <akaariai@…>]

In 728d3fe1bac6b5f23dbd088e11860cfba51cf7b5:

Fixed #16436 -- Oracle defer_regress test failure

Oracle doesn't like grouping by TextField, so use CharFields instead in
models.

[Anssi Kääriäinen <akaariai@…>]

In b495c24375aa1fe0373727511cf81298337a1227:

[1.5.x] Fixed #16436 -- defer + annotate + select_related crash

Correctly calculate the aggregate_start offset from loaded fields,
if any are deferred, instead of self.query.select which includes all
fields on the model.

Backpatch of 69f7db153d8108dcef033207d49f4c80febf3d70 from master.

[Tim Graham <timograham@…>]

In ed67184b3051c392a6cb722e889c062233730c2e:

Oracle defer test failure; refs #16436.

Oracle doesn't like grouping by TextField, so use CharFields instead in
models.

Backport of 728d3fe1bac6b5f23dbd088e11860cfba51cf7b5 from master

[Shai Berger]

For anyone else who might be confused at first: Anssi backported the fix to the problem, Tim backported the fix to the test on Oracle.

[Sławek Ehlert]

Replying to Tim Graham <timograham@…>:

Oracle doesn't like grouping by TextField, so use CharFields instead in
models.

Actually this is true for all types of LOB columns.

I have some serious doubts about whether or not this should be marked as fixed. If I get this right, we are in a situation where on Oracle one can't do any annotations on a model that has a TextField or BinaryField.

We should probably document this here ​https://docs.djangoproject.com/en/dev/ref/databases/#textfield-limitations (and add a note also about BinaryField) or try to fix this, by maybe introducing some parameter on a Query class. I noticed that there is some serious work regarding refactoring aggregate support (#14030). Is this issue also applicable for that kind of refactoring?

[Anssi Kääriäinen]

My understanding is that this can't be fixed in Django, this is a limitation in Oracle itself.

If documentation is needed for this issue then separate ticket seems like the way to go.