Inconsistent column names in cursor.description under SQLite break RawQuerySet

[alex@…]

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.

[Claude Paroz]

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

[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.

[Claude Paroz]

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

[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.

[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.

[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.

[Aymeric Augustin]

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.

[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

[Tim Graham]

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

[Alex Hill]

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

[Tim Graham <timograham@…>]

In 938da36cb12c07c93a44bb1b77984427e809d213:

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

[Tim Graham]

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?

[Shai Berger <shai@…>]

In 2a9f44dfbe293a1217097acc34993f48c0139e99:

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