#21603 closed Cleanup/optimization (fixed)

Inconsistent column names in cursor.description under SQLite break RawQuerySet

Reported by: alex@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: shai, timgraham Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

See pull request at https://github.com/django/django/pull/2069

SQLite will sometimes return column names in cursor.description in the form table_alias."column_name" or simply "column_name" instead of just column_name. This can happen when returning columns from subqueries, views or unions.

This means that RawQuerySet can't match up the columns when trying to create model instances, and raises InvalidQuery: Raw query must include the primary key.

This change introduces a column_name_converter() to the backend introspection code to mirror table_name_converter() and implements the fix on the SQLite backend.

Change History (13)

comment:1 Changed 21 months ago by claudep

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

test_subquery_in_raw_sql doesn't fail on my system. Is this behavior specific to some version of SQLite?

comment:2 Changed 21 months ago by alexh

This bug report suggests it might be platform/version specific: http://bugs.python.org/issue19167

I'm on OSX 10.9, Python 2.7.5, and if I run sqlite3 from a command line it reports version 3.7.13.

comment:3 Changed 21 months ago by claudep

I'm on Linux, that may explain why I cannot reproduce the issue.

comment:4 Changed 21 months ago by vajrasky

Ladies and gentlemen, this has nothing to do with OS. Mine, sqlite 3.7 on Fedora Core 18 is afflicted.

This is a bug from Sqlite. Sqlite 3.7 is afflicted. Solution: upgrade to sqlite 3.8.

http://sqlite.1065341.n5.nabble.com/sqlite3-column-name-contains-quotes-for-views-td65226.html
http://www.sqlite.org/src/info/5526e0aa3c

I guess it is not our place to fix this bug. So, I am inclined to make this ticket as invalid. But I let OP or core developers to do that.

comment:5 Changed 21 months ago by claudep

Thanks for the research! And yes, I have the 3.7.17 version of the debian mozilla team which is posterior to the fixing patch. However plain Debian Wheezy (currently stable) package seems to be affected. I'm still undecided about the value of working around this bug in Django.

comment:6 Changed 21 months ago by alexh

Yes, thanks for unearthing that!

I personally would like to see it fixed in Django, because a feature I'm hoping to get merged (#21604) relies on getting this data from cursor.description. As well as Debian, 3.7 is the system installed version on the current versions of Ubuntu and OS X - I think it's widespread enough to warrant including the workaround.

comment:7 Changed 18 months ago by aaugustin

  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Bug to Cleanup/optimization

Given that the patch isn't too invasive, yes, we could include it... It needs a comment stating which versions of SQLite are affected, so we can eventually remove the workaround when we consider them sufficiently outdated.

comment:8 Changed 17 months ago by alexh

OK, I've added the version affected and a link to the bugfix commit in the comments. Also added a TODO to remove the change at some point in the future. Let me know if it needs anything else.

Alex

comment:9 Changed 13 months ago by timo

I left comments for improvement on the PR. Please uncheck "Patch needs improvement" when you update it, thanks.

comment:10 Changed 13 months ago by alexh

  • Patch needs improvement unset

Have updated the PR to incorporate your feedback, should be good to go.

comment:11 Changed 13 months ago by Tim Graham <timograham@…>

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

In 938da36cb12c07c93a44bb1b77984427e809d213:

Fixed #21603 -- Fixed complex RawQuerySets queries on some versions of SQLite.

comment:12 Changed 13 months ago by timgraham

  • Cc shai timgraham added
  • Has patch unset
  • Resolution fixed deleted
  • Status changed from closed to new

The query added in this test doesn't work on Oracle (DatabaseError: ORA-00920: invalid relational operator). Should we try to make it work there or just skip it?

comment:13 Changed 13 months ago by Shai Berger <shai@…>

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

In 2a9f44dfbe293a1217097acc34993f48c0139e99:

Fixed #21603 (again) -- Made a raw query in a test Oracle-compliant

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