Sqlite filtering on date returns wrong results

[bscott]

Running our internal unit test i found some errors on date filtering. Our Unit test are runned against many FDO providers

SHP SDF Autodesk.Oracle King.Oracle PostGIS MySQL SqlServer

Our UnitTest returns the same result on date filtering except for Sqllite Tested on FeatureClass and Class

Table POINT structure

KEY_1 INTEGER DATE_1 TEXT

with fdo_columns.fdo_data_type = 2

Table POINT data KEY_1 DATE_1 1 2007-01-01T00:00:00.000 2 2007-01-02T00:00:00.000 99 2007-04-09T00:00:00.000 100 2007-04-10T00:00:00.000

Unit test Date and timestamp filter

Select KEY_1 from POINT where DATE_1 = DATE '2007-01-01'

failed returns nothing should return 1

Select KEY_1 from POINT where DATE_1 is null

ok

Select KEY_1 from POINT where DATE_1 is not null

ok

Select KEY_1 from POINT where DATE_1 > DATE '2007-04-09'

failed returns 99,100 should only return 100

Select KEY_1 from POINT where DATE_1 >= DATE '2007-04-09'

ok

Select KEY_1 from POINT where DATE_1 < DATE '2007-01-02'

ok

Select KEY_1 from POINT where DATE_1 <= DATE '2007-01-02'

failed return 1,2 should only return 1

Select KEY_1 from POINT where DATE_1 = TIMESTAMP '2007-01-01 00:00:00' ok Select KEY_1 from POINT where DATE_1 > TIMESTAMP '2007-04-09 00:00:00' ok Select KEY_1 from POINT where DATE_1 >= TIMESTAMP '2007-04-09 00:00:00' ok Select KEY_1 from POINT where DATE_1 < TIMESTAMP '2007-01-02 00:00:00' ok Select KEY_1 from POINT where DATE_1 <= TIMESTAMP '2007-01-02 00:00:00' ok

I have attached our unittest sqlite database on the ticket

[bscott]

Table POINT data ( well formated :) )

KEY_1 DATE_1

1 2007-01-01T00:00:00.000

2 2007-01-02T00:00:00.000

99 2007-04-09T00:00:00.000

100 2007-04-10T00:00:00.000

[bscott]

Still got problem formating text in trac :(

All test using TIMESTAMP works fine : Select KEY_1 from POINT where DATE_1 = TIMESTAMP '2007-01-01 00:00:00'

ok

Select KEY_1 from POINT where DATE_1 > TIMESTAMP '2007-04-09 00:00:00'

ok

Select KEY_1 from POINT where DATE_1 >= TIMESTAMP '2007-04-09 00:00:00'

ok

Select KEY_1 from POINT where DATE_1 < TIMESTAMP '2007-01-02 00:00:00'

ok

Select KEY_1 from POINT where DATE_1 <= TIMESTAMP '2007-01-02 00:00:00'

ok

[bscott]

I have a little patch that fixes this defect (attached file sqliteFilterOnDate.patch) I have run all my unittest on it and it really seems to fix. Could you check it please

Thanks

[traianstanev]

OK I'll take a look, but since I have some code which probably depends on this not having the 00:00:00, so I'd have to update more than just the provider, it will take me a few days.