Opened 12 years ago

Closed 12 years ago

Last modified 12 years ago

#17612 closed Bug (invalid)

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

Reported by: elmopl@… 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 Claude Paroz, 12 years ago

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

comment:2 by anonymous, 12 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 Anssi Kääriäinen, 12 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?

Last edited 12 years ago by Anssi Kääriäinen (previous) (diff)

comment:4 by anonymous, 12 years ago

Version: 1.3-rc11.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 Anssi Kääriäinen, 12 years ago

Resolution: invalid
Status: newclosed

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 Aymeric Augustin, 12 years ago

Also:

Note: See TracTickets for help on using tickets.
Back to Top