4

With OGR 1.10.1 this works:

ogrinfo test2.shp -sql 'SELECT COUNT(*) FROM test2 WHERE DATE < "1970-01-01"'

Note that DATE is a column of type date (not string).

(Here's test data if you want to try: http://ge.tt/6DBclJh/v/0)

The same query in 1.11.0 causes

OGR[3] error 1: Type mismatch or improper type of arguments to < operator.

Has the syntax changed or is this really a bug?

  • I filed a ticket, so in case this is a bug, I'm all set: trac.osgeo.org/gdal/ticket/5507 – underdark Jun 4 '14 at 19:24
  • 1
    The same happens for me with the default OGR SQL dialect. If I change the dialect by adding -dialect SQLite to the query it works OK. Can you verify this? – user30184 Jun 4 '14 at 21:27
  • You are right! -dialect does the trick ... Wonder if that helps me with my issues in QGIS – underdark Jun 4 '14 at 21:42
  • 1
    -where works also for me C:\temp\test>ogrinfo time.shp time -where "time<'1970-01-01'" and "time<'1970/01/01'" is accepted too. – user30184 Jun 4 '14 at 21:43
  • Seems that Even Rouault gave a prompt answer as a comment to the ticket trac.osgeo.org/gdal/ticket/5507 – user30184 Jun 5 '14 at 5:49
5

This is Even Rouault's answer to the OGR ticket:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.