Opened 12 years ago
Closed 12 years ago
#4354 closed defect (fixed)
[PATCH] OGR SQL 'COUNT([DISTINCT] xx)' not conformant with other SQL implementations w.r.t NULL values
Reported by: | Even Rouault | Owned by: | warmerdam |
---|---|---|---|
Priority: | normal | Milestone: | 1.9.0 |
Component: | OGR_SF | Version: | unspecified |
Severity: | normal | Keywords: | |
Cc: |
Description (last modified by )
Currently, OGR SQL considers COUNT(a_column) as a synonym of COUNT(*). However the standard mandates that COUNT(a_column) only count non-NULL values, whereas COUNT(*) count all rows (after the effect of WHERE clause of course). So COUNT(a_column) should not be considered as equivalent to COUNT(*)
The same applies for COUNT(DISTINCT a_column): it should only count unique non-NULL values.
References :
- http://www.w3schools.com/sql/sql_func_count.asp
- http://msdn.microsoft.com/en-us/library/ms175997.aspx
- http://www.sqlite.org/lang_aggfunc.html
What is a bit annoying here is that ogr_sql.html says that : "As a special case, the COUNT() operator can be given a "*" argument instead of a field name which is a short form for count all the records though it would get the same result as giving it any of the column names [...]"
If we fix the current behaviour to be conformant with the standard, it can break assumptions made by users. But currently, there's an inconsistency if you look at the result of -sql "select sum(foo), count(foo), avg(foo) from bar" when there are null values
Attachments (1)
Change History (4)
by , 12 years ago
Attachment: | ticket_4354.patch added |
---|
comment:1 by , 12 years ago
Description: | modified (diff) |
---|
comment:2 by , 12 years ago
Description: | modified (diff) |
---|
comment:3 by , 12 years ago
Milestone: | → 1.9.0 |
---|---|
Resolution: | → fixed |
Status: | new → closed |
r23436 /trunk/ (4 files in 3 dirs): [backward-compatibility] OGR SQL: exclude null-values from COUNT(column_name) or COUNT(DISTINCT colum_name) (#4354)