Opened 9 years ago

Closed 9 years ago

#7087 closed (fixed)

QuerySet.dates bug in Oracle

Reported by: Ian Kelly Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: queryset-refactor
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

The DateQuery.results_iter method doesn't account for the possibility of extra selects and just returns the first column from the results. When the query is subscripted in Oracle, that ends up being the row number.

Attachments (3)

qsrf_dates_oracle.diff (735 bytes) - added by Ian Kelly 9 years ago.
orcl_revert_resolve_columns.diff (878 bytes) - added by jbronn 9 years ago.
orcl_convert_values.diff (1.1 KB) - added by jbronn 9 years ago.
Missing import fix for convert_values.

Download all attachments as: .zip

Change History (16)

Changed 9 years ago by Ian Kelly

Attachment: qsrf_dates_oracle.diff added

comment:1 Changed 9 years ago by jbronn

Actually extra select SQL is broken everywhere -- I think your revision to my previous patch (by overridding set_limits and clear_limits) made the hacks to resolve_columns unnecessary.

Changed 9 years ago by jbronn

comment:2 Changed 9 years ago by jbronn

Cc: Malcolm Tredinnick added

Using my patch I was able to use the dates method to pull dates from a test model w/a DateTimeField just fine -- but I did not run the full test suite. My GIS tests now all pass as it fixes a bug due to my use of extra SQL to retrieve GML representations of geometries.

comment:3 Changed 9 years ago by jbronn

Cc: Malcolm Tredinnick removed
Owner: changed from nobody to Malcolm Tredinnick

comment:4 in reply to:  1 ; Changed 9 years ago by Ian Kelly

Replying to jbronn:

Actually extra select SQL is broken everywhere -- I think your revision to my previous patch (by overridding set_limits and clear_limits) made the hacks to resolve_columns unnecessary.

Hrm, I don't think that it did. resolve_columns will still receive the extra selects at the beginning of the row and will still need to account for that, since the extra selects don't have any corresponding entries at the beginning of the fields list.

Can you clarify what extra selects are broken? After applying my patch here and the one in #7057, the only test cases that are still failing for me are the values_list queries in the lookups test.

comment:5 in reply to:  2 ; Changed 9 years ago by Ian Kelly

Replying to jbronn:

Using my patch I was able to use the dates method to pull dates from a test model w/a DateTimeField just fine -- but I did not run the full test suite. My GIS tests now all pass as it fixes a bug due to my use of extra SQL to retrieve GML representations of geometries.

Try the queries test, if you would. The specific failure that motivated this ticket was this one:

File "/home/ikelly/projects/django.qs-rf/tests/regressiontests/queries/models.py", line ?, in regressiontests.queries.models.__test__.API_TESTS
Failed example:
    Item.objects.dates('created', 'day')[0]
Expected:
    datetime.datetime(2007, 12, 19, 0, 0)
Got:
    1

comment:6 in reply to:  5 Changed 9 years ago by jbronn

Replying to ikelly:

Try the queries test, if you would. The specific failure that motivated this ticket was this one:

My bad, I ran my dates test accidentally using trunk -- I get the same erroneous response w/qs-rf.

comment:7 in reply to:  4 ; Changed 9 years ago by jbronn

Can you clarify what extra selects are broken? After applying my patch here and the one in #7057, the only test cases that are still failing for me are the values_list queries in the lookups test.

Yes GeoQuerySet has a gml method that attaches a GML representation (XML) of the geometry, which is returned as a CLOB. Essentially, here's what I do:

   qs.extra(select={'gml' : 'SDO_UTIL.TO_GMLGEOMETRY("AU_AUSTRALIACITY"."POINT")'})

Without my patch I get the following:

In [2]: qs = AustraliaCity.objects.all().gml()

In [3]: qs[0].gml
Out[3]: <cx_Oracle.LOB object at 0x011FFAE8>

In [4]: qs[0].gml.read()
Out[4]: '<gml:Point srsName="SDO:4326" xmlns:gml="http://www.opengis.net/gml"><gml:coordinates decimal="." cs="," ts=" ">150.90199999999998681232
6824292540550232,-34.42450000000000187583282240666449069977 </gml:coordinates></gml:Point>'

This is because in resolve_columns, the values list is initialized like so:

    values = list(row[:index_start])    

as such, any extra selection fields don't benefit from the extra processing -- I think this would include any custom selects on DateField and TextField.

New ticket, perhaps?

comment:8 in reply to:  7 Changed 9 years ago by Ian Kelly

Replying to jbronn:

as such, any extra selection fields don't benefit from the extra processing -- I think this would include any custom selects on DateField and TextField.

New ticket, perhaps?

Ah, yes. We can't do all of the resolve_columns processing without a field object, but we can and should convert dates and clobs -- especially clobs, since the handle won't necessarily be available to read from later on. The change would also be consistent with what we already do in trunk.

comment:9 Changed 9 years ago by Malcolm Tredinnick

I'm in the process of writing a (hopefully) comprehensive patch for #7088 and I'll include some extra processing of the extra_select for dates and clobs in that. Will close this one in a moment, since the date bug Ian found is actually valid for all backends and I've just written a test to make sure it stays fixed.

comment:10 Changed 9 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

(In [7468]) queryset-refactor: Repaired the dates() method with extra(select=...).xi
It was broken by [7340]. Patch from Ian Kelly. Fixed #7087.

comment:11 Changed 9 years ago by Malcolm Tredinnick

(In [7470]) queryset-refactor: Pass any extra(select=...) columns through the value
conversion function in the Oracle backend after reading the row from the
database.

Refs #7087 (see comment 7 on that ticket).

Changed 9 years ago by jbronn

Attachment: orcl_convert_values.diff added

Missing import fix for convert_values.

comment:12 Changed 9 years ago by jbronn

Resolution: fixed
Status: closedreopened

Imports are needed for convert_values to work, see attached patch.

comment:13 Changed 9 years ago by anonymous

Resolution: fixed
Status: reopenedclosed

Fixed in [7475]. Thanks, Justin.

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