#6020 closed defect (fixed)

Select distinct does not return NULL values when they are in the first records

Reported by: ndawson Owned by: warmerdam
Priority: normal Milestone: 2.0.1
Component: OGR_SF Version: 1.11.2
Severity: normal Keywords: ogr sql null distinct
Cc:

Description

Running a "SELECT DISTINCT..." query from a layer containing NULL values does not include a NULL value in the returned results.

Here's the results of running this query on the shapefile available at https://github.com/qgis/QGIS/tree/master/tests/testdata/provider

ogrinfo -sql "SELECT DISTINCT name FROM shapefile" shapefile.shp

INFO: Open of `shapefile.shp'

using driver `ESRI Shapefile' successful.

Layer name: shapefile Geometry: Point Feature Count: 4 Layer SRS WKT: (unknown) name: String (80.0) OGRFeature(shapefile):0

name (String) = Pear

OGRFeature(shapefile):1

name (String) = Orange

OGRFeature(shapefile):2

name (String) = Apple

OGRFeature(shapefile):3

name (String) = Honey

Removing the "DISTINCT" clause shows that null values are present:

ogrinfo -sql "SELECT name FROM shapefile" shapefile.shp

INFO: Open of `shapefile.shp'

using driver `ESRI Shapefile' successful.

Layer name: shapefile Geometry: Point Feature Count: 5 Extent: (-71.123000, 66.330000) - (-65.320000, 78.300000) Layer SRS WKT: GEOGCS["GCS_WGS_1984",

DATUM["WGS_1984",

SPHEROID["WGS_84",6378137,298.257223563]],

PRIMEM["Greenwich",0], UNIT["Degree",0.017453292519943295]]

name: String (80.0) OGRFeature(shapefile):0

name (String) = (null) POINT (-71.123 78.23)

OGRFeature(shapefile):1

name (String) = Pear

OGRFeature(shapefile):2

name (String) = Orange POINT (-70.332 66.33)

OGRFeature(shapefile):3

name (String) = Apple POINT (-68.2 70.8)

OGRFeature(shapefile):4

name (String) = Honey POINT (-65.32 78.3)

Change History (1)

comment:1 Changed 21 months ago by Even Rouault

Component: defaultOGR_SF
Keywords: ogr sql null distinct added
Milestone: 2.0.1
Resolution: fixed
Status: newclosed
Summary: Select distinct does not return NULL valuesSelect distinct does not return NULL values when they are in the first records

The issue was specific only if the records with NULL values were before records with non-NULL values.

trunk r29481, branches/2.0 r29482 "OGR SQL: do not silently skip NULL values in the first records when evaluating a SELECT DISTINCT (#6020)"

Note: See TracTickets for help on using tickets.