DateField __year queries don't return all records.

Patches the unit test to expose the problem.

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

Duplicate of #3689.

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.

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...

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.