Icon View Incident Report

Serious Serious
Reported By: Ole Willy Tuv
Reported On: 4/8/2007
For: Version 1.02 Build 1
# 2319 GROUP BY Expressions that Reference SELECT Column Expressions May Return Incorrect Results

The following query returns 27 groups, which is incorrect. The expected result is 81 groups.

select
 floor((PV.rowID-1)/3)+1 as rowCellID,
 floor((PV.columnID-1)/3)+1 as columnCellID,
 PV.cellValue,
 count(*) as occurrences
from PossibleValuesStage2 as PV
group by
 floor((PV.rowID-1)/3)+1,
 floor((PV.columnID-1)/3)+1,
 PV.cellValue
order by rowCellID, columnCellID, cellValue

Workaround:

select
 floor((PV.rowID-1)/3)+1 as rowCellID,
 floor((PV.columnID-1)/3)+1 as columnCellID,
 PV.cellValue,
 count(*) as occurrences
from PossibleValuesStage2 as PV
group by rowCellID, columnCellID, cellValue
order by rowCellID, columnCellID, cellValue



Comments Comments and Workarounds
The query optimizer was not matching up the FLOOR expressions properly with the same expressions in the SELECT column expressions.


Resolution Resolution
Fixed Problem on 4/9/2007 in version 1.03 build 1


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image