Code

Opened 4 months ago

Last modified 4 weeks ago

#21603 new Cleanup/optimization

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: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
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.

Attachments (0)

Change History (8)

comment:1 Changed 4 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 4 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 4 months ago by claudep

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

comment:4 Changed 4 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 4 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 4 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 4 weeks 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 4 weeks 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


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

 
Note: See TracTickets for help on using tickets.