Code

Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#6141 closed (duplicate)

DateField __year queries don't return all records.

Reported by: dougvanhorn@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: DateField __year query
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

When querying a DateField using the __year lookup type, the first day of the year, January 1, is not returned.

I can't speak to why the code is the way it is right now, but my patch changes the __year lookups to be the same as the __month and __day lookups. As quick background, __year lookups are handled using 'between' in the where clause, versus an SQL function for __month and __day.

  • test.diff modifies the basic test case to highlight the error.
  • fix.diff fixes the bug.

All tests appear to run correctly against sqlite and postgresql.

Attachments (2)

test.diff (1.1 KB) - added by Doug Van Horn 6 years ago.
Patches the unit test to expose the problem.
fix.diff (1.9 KB) - added by Doug Van Horn 6 years ago.
Modifies query.py and the fields module (init.py) to fix the bug.

Download all attachments as: .zip

Change History (6)

Changed 6 years ago by Doug Van Horn

Patches the unit test to expose the problem.

Changed 6 years ago by Doug Van Horn

Modifies query.py and the fields module (init.py) to fix the bug.

comment:1 Changed 6 years ago by ubernostrum

  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #3689.

comment:2 follow-up: Changed 6 years ago by Karen Tracey <kmtracey@…>

Looks like year used to be handled the way suggested here, but was changed in r4505 to use BETWEEN (the changeset comment mentions this should be more efficient?).

There's also a 3rd ticket (#5223) that notes a problem with __year and sqlite. Sounds like the problem might be dependent on sqlite version? The suggested patches for that ticket change the format of the BETWEEN values to not include the time portion.

You mention running the test on sqlite and postgresql -- did you encounter the problem on postgresql or just on sqlite? I don't see a problem with picking up Jan. 1 events with the current code mysql.

comment:3 in reply to: ↑ 2 Changed 6 years ago by anonymous

Replying to Karen Tracey <kmtracey@gmail.com>:

Looks like year used to be handled the way suggested here, but was changed in r4505 to use BETWEEN (the changeset comment mentions this should be more efficient?).

There's also a 3rd ticket (#5223) that notes a problem with __year and sqlite. Sounds like the problem might be dependent on sqlite version? The suggested patches for that ticket change the format of the BETWEEN values to not include the time portion.

You mention running the test on sqlite and postgresql -- did you encounter the problem on postgresql or just on sqlite? I don't see a problem with picking up Jan. 1 events with the current code mysql.

I ran the tests against both databases /after/ I made the change. I only ran against SQLite before I made the change. Looks like things work as expected with Postgres using between.

It's definitely a problem with SQLite only. I posted a question an SQLite forum:

http://www.nabble.com/Querying-DATE-column-with-date-time-string.-tf4956413.html

Here's the SQL that highlights the problem:

$ sqlite3 date_test
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table foo (d date null);
sqlite> insert into foo (d) values ('2008-01-01');
sqlite> select d from foo where d between '2008-01-01' and '2008-01-31';
2008-01-01
sqlite> select d from foo where d between '2008-01-01 00:00:00' and '2008-01-31 23:59:59.999999';
sqlite> .quit

I'm going to ignore the problem for now as I'll be moving off of SQLite as soon as my data model firms up. But I'll keep an eye on that post and report back here as to why it doesn't work, if I find out...

comment:4 Changed 6 years ago by Doug Van Horn

From the gentlemen on the SQLite forum:

sqlite> create table foo( d date null );
sqlite> insert into foo(d) values( '2008-01-01' );
sqlite> select d, typeof(d) from foo;
2008-01-01|text

See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions.

So dates are stored as strings in SQLite, and 2008-01-01 is 'less than' 2008-01-01 00:00:00, and so falls outside the bounds of the query. I'm going to head over to #5223 and post this information there as that ticket is still open.

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.