SQL Server 2008 R2 devide by zero bug

Question

Please tell me if I am missing something, and if I am loosing my mind

The below query produces a devide by zero error

DECLARE @Test TABLE(
        ID INT,
        Val FLOAT
)

INSERT INTO @Test SELECT 1, 0

;WITH   Grouped AS (
        SELECT  ID,
                Val,
                SUM(Val) OVER (PARTITION BY ID) TotalVal
        FROM    @Test
)
, Filtered AS (
        SELECT  *
        FROM    Grouped
        WHERE   TotalVal != 0
)
SELECT  *
FROM    Filtered
WHERE   Val / TotalVal > 0.05

SQL Fiddle DEMO

Where as the below does not produce the same error

DECLARE @Test TABLE(
        ID INT,
        Val FLOAT
)

INSERT INTO @Test SELECT 1, 0

;WITH   Grouped AS (
        SELECT  ID,
                Val,
                SUM(Val) OVER (PARTITION BY ID) TotalVal
        FROM    @Test
)
, Filtered AS (
        SELECT  *
        FROM    Grouped
        WHERE   TotalVal != 0
)
SELECT  *,
        Val / TotalVal Test
FROM    Filtered

SQL Fiddle DEMO

From looking at the estimated query plan, the first query tries to optimize the where clause as

[Val]/[Expr1004]>(5.000000000000000e-002) AND [Expr1004]<>(0.000000000000000e+000)

where I can see that the TotalVal != 0 is after the actual devide by TotalVal.

Is this a known issue? Is there a way (other than using another temp table) to bypass this. I know I can do it using a

CASE WHEN TotalVal = 0 THEN 0 ELSE Val / TotalVal END

but that would defeat the purpose of the second CTE statement.

the optimizer can evaluate the cte's in any order that is valid. (just like the conditions in a where clause) — Mitch Wheat, Oct 22 '12 at 04:36

score 1 · Answer 1 · edited May 23 '17 at 10:34

Go to this connect item and vote it up.

Personally, I'm happy with the way SQL Server works, because it allows for some level of optimisation to pre-calculate (streamline) an expression rather than carry the components forward to calculate later (even it that makes sense logically).

As for your query, yes, the Filtered CTE is quite a non-event and should be rolled into the final query. Writing it that way suggests that you believe CTEs are somehow processed and cached in memory. That is not true - see this answer to another SO question. For an extended read of SQL Server's CTE implementation, visit this article.

SQL Server 2008 R2 devide by zero bug

SQL Fiddle DEMO

SQL Fiddle DEMO

1 Answers1