Opened 3 years ago

Closed 2 years ago

#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 Berger, Tim Graham 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 3 years ago by Claude Paroz

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

comment:2 Changed 3 years ago by Alex Hill

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 3 years ago by Claude Paroz

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

comment:4 Changed 3 years ago by Vajrasky Kok

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 3 years ago by Claude Paroz

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 3 years ago by Alex Hill

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 3 years ago by Aymeric Augustin

Patch needs improvement: set
Triage Stage: UnreviewedAccepted
Type: BugCleanup/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 3 years ago by Alex Hill

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 2 years ago by Tim Graham

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

comment:10 Changed 2 years ago by Alex Hill

Patch needs improvement: unset

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

comment:11 Changed 2 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In 938da36cb12c07c93a44bb1b77984427e809d213:

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

comment:12 Changed 2 years ago by Tim Graham

Cc: Shai Berger Tim Graham added
Has patch: unset
Resolution: fixed
Status: closednew

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 2 years ago by Shai Berger <shai@…>

Resolution: fixed
Status: newclosed

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