Opened 7 years ago

Closed 4 years ago

#4494 closed enhancement (fixed)

OGR SQL: select distinct and case sensitivity

Reported by: peifer Owned by: warmerdam
Priority: normal Milestone:
Component: OGR_SF Version: svn-trunk
Severity: normal Keywords: ogr sql
Cc:

Description

I am not sure if the below result is a feature or a bug but I would have thought that select DISTINCT is case insensitive

ogrinfo My_Shape.shp -sql "select distinct MS from My_Shape"

  MS (String) = UK

  MS (String) = Uk

  MS (String) = uk

Change History (2)

comment:1 Changed 7 years ago by Even Rouault

Component: defaultOGR_SF
Keywords: ogr sql added
Type: defectenhancement

I've tested with sqlite, postgresql and mysql. The default behaviour is that sqlite and postgresql are case sensitive like OGR SQL, and that MySQL is case insensitive. For all, this can be changed in various ways, mainly through collation or using upper

ogrinfo pg:dbname=autotest -sql "select distinct upper(foo) from test" --> PG sensitive
ogrinfo test.sqlite -sql "select distinct upper(foo) from test" --> SQLite sensitive
ogrinfo test.sqlite -sql "select distinct foo collate nocase from test" --> SQLite sensitive
ogrinfo mysql:autotest -sql "select distinct foo collate latin1_bin from test" -> MySQL insensitive

So I guess OGR current behaviour is acceptable. But it might be nice to have an option to do case insensitive queries.

comment:2 Changed 4 years ago by Jukka Rahkonen

Resolution: fixed
Status: newclosed

By the documentation of OGR SQL dialect http://www.gdal.org/ogr_sql.html DISTINCT is now always case insensitive: "Currently the distinctness test against a string value is case insensitive in OGR SQL"

Closing as fixed, anyone who would prefer to make a case sensitive SELECT DISTINCT can create a new ticket.

Note: See TracTickets for help on using tickets.