SQLite3 problem with date comparison

[anonymous]

I've hit a bug using Django 1.2.1.

I've a model with date types. Django generates a query like this that returns the correct results:

SELECT xxx FROM "table" WHERE ("table"."pub_date" >= 2010-01-24 );

If I switch to "lt" or "lte" it gives me nothing, because sqlite seems to want "'" around the date. You can see for yourself in the following test case run with SQLite 3.6.16 and 3.7.0.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

$ sqlite3 prova.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( d date null);
sqlite> insert into foo(d) values( '2009-09-09' );
sqlite> select * from foo where (d >= 2007-01-01);
2009-09-09
sqlite> select * from foo where (d <= 2010-01-01);
sqlite> select * from foo where (d <= '2010-01-01');
2009-09-09
sqlite>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

$ sqlite3 prova.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo( d date null);
sqlite> insert into foo(d) values( '2009-09-09' );
sqlite> select * from foo where (d >= 2007-01-01);
2009-09-09
sqlite> select * from foo where (d <= 2010-01-01);
sqlite> select * from foo where (d <= '2010-01-01');
2009-09-09
sqlite>

[tzulberti]

I don't have that problem. Feel free to change the change anything.

The patch is an example I run against django code.

Models:

class Reporter(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)

    def __unicode__(self):
        return u"%s %s" % (self.first_name, self.last_name)


class Article(models.Model):
    headline = models.CharField(max_length=100)
    pub_date = models.DateField()
    reporter = models.ForeignKey(Reporter)

    def __unicode__(self):
        return self.headline

The test:

    def test_regression_14016(self):
        reporter = models.Reporter.objects.create(first_name='Django',
                                                  last_name='Spring')
        article = models.Article.objects.create(
                            reporter=reporter,
                            pub_date=datetime.date.today())


        self.assertEquals(len(models.Article.objects.all()), 1)
        articles = models.Article.objects.filter(
                            pub_date__gte = datetime.date.today())
        self.assertEquals(len(articles), 1)
        articles = models.Article.objects.filter(
                            pub_date__lte = datetime.date.today())
        self.assertEquals(len(articles), 1)