[fdo-trac] #644: Sqlite filtering on date returns wrong results

[FDO]

#644: Sqlite filtering on date returns wrong results
-------------------------------+--------------------------------------------
   Reporter:  bscott           |       Owner:  traianstanev
       Type:  defect           |      Status:  new         
   Priority:  major            |   Milestone:  3.5.0       
  Component:  SQLite Provider  |     Version:  3.5.0       
   Severity:  3                |    Keywords:              
External_id:                   |  
-------------------------------+--------------------------------------------
 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

-- 
Ticket URL: <http://trac.osgeo.org/fdo/ticket/644>
FDO <http://fdo.osgeo.org/>
Feature Data Objects