Code

Opened 2 years ago

Closed 2 years ago

Last modified 2 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') )

Attachments (0)

Change History (6)

comment:1 Changed 2 years ago by claudep

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 2 years ago by 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|


comment:3 Changed 2 years ago by akaariai

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 2 years ago by akaariai (previous) (diff)

comment:4 Changed 2 years ago by anonymous

  • Version changed from 1.3-rc1 to 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 Changed 2 years ago by akaariai

  • Resolution set to invalid
  • Status changed from new to 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 Changed 2 years ago by aaugustin

Also:

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.