SQLite, filter, datefield, datetime no quotes around date in sql query

[elmopl@…]

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') )

[Claude Paroz]

Please can you tell us more precisely why you think the query is invalid. Does the query produce an error? Is the result wrong?

[anonymous]

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|

[Anssi Kääriäinen]

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?

[anonymous]

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

[Anssi Kääriäinen]

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.

[Aymeric Augustin]

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