Opened 5 years ago

Closed 5 years ago

#5507 closed defect (invalid)

"less than"/"greater than" on date for Shapefiles cause error

Reported by: anitagraser Owned by: warmerdam
Priority: normal Milestone:
Component: OGR_SF Version: 1.11.0
Severity: normal Keywords: ogr sql date
Cc:

Description

To reproduce this issue, you can use the following testdata: http://ge.tt/6DBclJh/v/0

With OGR 1.10.1 this works: ogrinfo test2.shp -sql 'SELECT COUNT(*) FROM test2 WHERE DATE < "1970-01-01"'

With OGR 1.11.0, I get the error "OGR[3] error 1: Type mismatch or improper type of arguments to < operator."

(Related to QGIS issue http://hub.qgis.org/issues/10165)

Change History (4)

comment:1 Changed 5 years ago by anitagraser

As noticed on gis.se (http://gis.stackexchange.com/questions/99991/how-to-filter-date-values-using-ogr-1-11-0), the query works if -dialect SQLite is specified. Too bad for applications which depend on the old behavior.

comment:2 Changed 5 years ago by Even Rouault

The issue is that binary comparisons of date/datetime field have never (yet) been implemented in OGR SQL dialect. In 1.10 and before, in fact, it turned to be an implicit conversion to string, which can cause really strange behaviour.

For example, you could have well used "select * from test2 where date < 'bla'" and it would not have complained. Or -sql "select * from test2 where date = '1969/02/11'" would return 3 results as expected, but -sql "select * from test2 where date = '1969-02-11'" none...

1.11 just rejects implicit comparison between date and string literals. You can still get the old behaviour by expliciting casting the date field to character, with :

-sql "select * from test2 where cast(date as character) < '1970/01/01'".

That will work with 1.10 too.

The fact that it works with sqlite dialect is just that sqlite has no strong typing, so implicit conversions to string must also be done.

comment:3 Changed 5 years ago by anitagraser

Thanks for the explanation and the working query syntax. Please go ahead and deal with this ticket as you see fit.

comment:4 Changed 5 years ago by Even Rouault

Resolution: invalid
Status: newclosed
Note: See TracTickets for help on using tickets.