#17612 closed Bug (invalid)
SQLite, filter, datefield, datetime no quotes around date in sql query
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.3 |
Severity: | Normal | Keywords: | sqlite, filter, datefield, datetime |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Model:
class StoreBill( Cost ): date = models.DateField()
Query:
ds = datetime.strptime(date, '%Y-%m-%d') de = ds + timedelta( days=1 ) bills = StoreBill.objects.filter(date__gte = ds, date__lt = de)
resulting invalid where:
WHERE ("spendings_storebill"."date" >= 2012-01-19 AND "spendings_storebill"."date" < 2012-01-20 )
This should be:
WHERE ("spendings_storebill"."date" >= '2012-01-19' AND "spendings_storebill"."date" < '2012-01-20' )
or even better:
WHERE ("spendings_storebill"."date" >= date('2012-01-19') AND "spendings_storebill"."date" < date('2012-01-20') )
Change History (6)
comment:1 by , 13 years ago
comment:2 by , 13 years ago
sqlite> select count(*) from spendings_storebill where date > 2012-01-21; 119 sqlite> select count(*) from spendings_storebill where date > date('2012-01-21'); 1
Clearly not the same.
2012-01-21 is just an arithmetic expression, not a date.
If you don't believe see below steps 6-7 and 2-6
sqlite> explain select count(*) from spendings_storebill where date > date('2012-01-21'); 0|Trace|0|0|0||00| 1|Null|0|1|0||00| 2|Goto|0|16|0||00| 3|OpenRead|0|54|0|2|00| 4|Rewind|0|11|0||00| 5|Column|0|1|2||00| 6|String8|0|4|0|2012-01-21|00| \_ here 7|Function|1|4|3|date(-1)|01| / 8|Le|3|10|2|collseq(BINARY)|6b| 9|AggStep|0|0|1|count(0)|00| 10|Next|0|5|0||01| 11|Close|0|0|0||00| 12|AggFinal|1|0|0|count(0)|00| 13|Copy|1|5|0||00| 14|ResultRow|5|1|0||00| 15|Halt|0|0|0||00| 16|Transaction|0|0|0||00| 17|VerifyCookie|0|43|0||00| 18|TableLock|0|54|0|spendings_storebill|00| 19|Goto|0|3|0||00| sqlite> explain select count(*) from spendings_storebill where date > 2012-01-21; 0|Trace|0|0|0||00| 1|Null|0|1|0||00| 2|Integer|2012|4|0||00| \ 3|Integer|1|5|0||00| | 4|Subtract|5|4|3||00| |- and here 5|Integer|21|5|0||00| | 6|Subtract|5|3|2||00| / 7|Goto|0|19|0||00| 8|OpenRead|0|54|0|2|00| 9|Rewind|0|14|0||00| 10|Column|0|1|5||00| 11|Le|2|13|5|collseq(BINARY)|6b| 12|AggStep|0|0|1|count(0)|00| 13|Next|0|10|0||01| 14|Close|0|0|0||00| 15|AggFinal|1|0|0|count(0)|00| 16|Copy|1|6|0||00| 17|ResultRow|6|1|0||00| 18|Halt|0|0|0||00| 19|Transaction|0|0|0||00| 20|VerifyCookie|0|43|0||00| 21|TableLock|0|54|0|spendings_storebill|00| 22|Goto|0|8|0||00|
comment:3 by , 13 years ago
Can you get wrong results when you run a query against the table using Django ORM?
So, does StoreBill.objects.filter(date__gte=thedate).count()
produce different results if you write that query correctly by hand?
Also, version 1.3-rc1 is not the latest version, can you reproduce the above error on 1.3.1?
comment:4 by , 13 years ago
Version: | 1.3-rc1 → 1.3 |
---|
As for version I got confused as there seems to be no 1.3.1 to select from list. I do have 1.3.1.
If I run it with count() it seems to be working fine. Why then it shows wrong query?
>>> ds = datetime.strptime('2012-01-21', '%Y-%m-%d') >>> de = ds + timedelta(days=1) >>> print StoreBill.objects.filter(date__gte = ds, date__lt = de).count() 1 >>> print connection.queries[-1] {'time': '0.000', 'sql': u'SELECT COUNT(*) FROM "spendings_storebill" WHERE ("spendings_storebill"."date" >= 2012-01-21 AND "spendings_storebill"."date" < 2012-01-22 )'}
If I run it by hand from sqlite it returns:
sqlite> SELECT COUNT(*) FROM "spendings_storebill" WHERE ("spendings_storebill"."date" >= 2012-01-21 AND "spendings_storebill"."date" < 2012-01-22 ); 0
comment:5 by , 13 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
I think the reason is that connection.queries does not contain the queries exactly as sent to the backend. And the reason for that is that Django doesn't know the exact query. The escaping happens in libraries beyond Django's control. So, Django executes this:
cursor.execute('select * from storebill where storebill.date >= %s', (thedate,))
and the backend adds the quotation. Hence, Django doesn't know the exact query.
There doesn't seem to be a way to get the exact query from the Python's SQLite library, so there is little Django can do here. If you find out a way to get the exact query from the SQLite library, I think a ticket for using that would be accepted. connection.queries can be a little confusing currently.
comment:6 by , 13 years ago
Also:
- this limitation is documented here: https://docs.djangoproject.com/en/dev/ref/databases/#parameters-not-quoted-in-connection-queries
- a hack to get proper quoting under SQLite was rejected here: https://code.djangoproject.com/ticket/14091#comment:11
Please can you tell us more precisely why you think the query is invalid. Does the query produce an error? Is the result wrong?