Icon View Incident Report

Serious Serious
Reported By: Chris B
Reported On: 12/12/2010
For: Version 2.04 Build 4
# 3362 JOINing a Derived Table with a RANGE Clause Can Cause Incorrect Results

I'm having problems with the below query. The result should contain a maximum of 10 rows. However, Elevate sometimes returns incorrect results - excution plan sometimes changes/re-orders the driver table to 'Business' so the join is missing a condition. Instead of 10 rows I get a rowcount which is the distinct number of ProductSaleHistory.ProductCode records.

SELECT
ordering_field,
Product.ProductCode AS ProductCode
FROM Product  
INNER JOIN (SELECT
     SUM(SaleQuantity) AS ordering_field,
     Product.ProductCode AS ProductCode
     FROM Product
     INNER JOIN ProductSaleHistory ON Product.ProductCode = ProductSaleHistory.ProductCode 
     GROUP BY ProductCode
     ORDER BY ordering_field DESC 
     RANGE 1 TO 10) AS TopProductSaleHistory
        ON Product.ProductCode = TopProductSaleHistory.ProductCode
INNER JOIN ProductSaleHistory ON Product.ProductCode = ProductSaleHistory.ProductCode
INNER JOIN Business ON ProductSaleHistory.BusinessCode = Business.BusinessCode
Product.ProductCode = ProductSaleHistory.ProductCode
GROUP BY ProductCode
ORDER BY ordering_field DESC



Resolution Resolution
Fixed Problem on 12/15/2010 in version 2.05 build 1


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

Image