1

I am facing a strange behaviour while executing the below query

WITH X1 AS 
(
  SELECT 1 AS N1, 0 AS N2
  UNION ALL
  SELECT 1 AS N1, 1 AS N2
)
,X2 AS
(
 SELECT * FROM X1 WHERE N2>0
)
SELECT N1,N2,N1/N2 FROM X2

Output: enter image description here

However when I put below where condition in above select statement

SELECT N1,N2,N1/N2 FROM X2
WHERE N1/N2>0

I get "Divide by zero error encountered." Please can anyone explain why I am getting this error?

Hemendr
  • 673
  • 6
  • 12
  • Because `1/0` is a division by 0, and you have a row in the result set where `N1` has the value `1` and `N2` is 0: `N1/N2=1/0=ERROR`. – Thom A Mar 24 '21 at 17:07
  • Hi @Larnu, I am executing select from the result of X2 which is returning one row with N2>0. So N1/N2 is 1/1 and it should not be zero. – Hemendr Mar 24 '21 at 17:10
  • 2
    It's because the optimizer doesn't execute statements in the order written (nor is it obliged to). This is one of the misconceptions of `CTE`s, although written as a self-contained query, it's not. – Stu Mar 24 '21 at 17:13
  • 1
    you are assuming that SQL Server only evaluates it against rows returned from `X2`. This is not the case it can push calculations like this before the rows are filtered – Martin Smith Mar 24 '21 at 17:13
  • Oh!. So How can I fix this query using CTE? – Hemendr Mar 24 '21 at 17:16
  • 1
    Use `N1/NULLIF(N2,0)` instead of `N1/N2` so it doesn't matter if it is evaluated against `0` – Martin Smith Mar 24 '21 at 17:17
  • I wouldn't. If you "must" do something like this, I would wrap the divisor in a `NULLIF` as the query will already be non-SARGable. – Thom A Mar 24 '21 at 17:17
  • You can sometimes `force` the optimzer to your bidding using a `row goal` – Stu Mar 24 '21 at 17:17
  • Great!. Thanks, everyone for your help in explaining the reason and solution. – Hemendr Mar 24 '21 at 17:21

1 Answers1

1

Using a row goal will force the optimizer to evaluate the CTE before evaluating the where

 WITH X1 AS 
    (
      SELECT 1 AS N1, 0 AS N2
      UNION ALL
      SELECT 1 AS N1, 1 AS N2
    )
    ,X2 AS
    (
     SELECT top 10 * FROM X1 WHERE N2>0
    )
    SELECT N1,N2,N1/N2,* FROM X2
    WHERE N1/N2>2
Stu
  • 30,392
  • 6
  • 14
  • 33
  • It doesn't force any such thing. In this case you get lucky there is nothing forcing it though. This is not a robust solution – Martin Smith Mar 24 '21 at 17:20
  • Maybe force is the wrong choice of words. I've seen this discussed several times as a tuning trick to get the optimizer to evaluate intermediate results, can't recall if it was Arron Bertrand or Grant Fritchey maybe, but it's always worked as expected where I've used it. – Stu Mar 24 '21 at 17:30
  • Maybe it is the case that SQL Server will never reorder evaluation of a compute scalar to below a `TOP` in current versions of the product. I don't know, But unless this is a documented feature it will always be subject to change, using `NULLIF` or `CASE` will be more robust as it doesn't rely on (potentially variable over time) optimizer quirks. – Martin Smith Mar 24 '21 at 17:38
  • I agree in this use-case handling with nullif would be appropriate, see [here](https://www.sqlservercentral.com/articles/a-plunge-into-top) as example of using a row goal with derived tables – Stu Mar 24 '21 at 17:45
  • Personally I would be more confident about using it in cases where there is an inherent reason why reordering operations would break. In the case of a result of a join then clearly it needs to count the joined rows so it would be difficult for the optimizer to reorder things. In the case of the placement of a compute scalar it doesn't affect the row counts where the expression evaluating `N1/N2` is placed in the plan - that just affects the number of columns going into the `TOP` not the number of rows so it is entirely possible it could be reordered in the future – Martin Smith Mar 24 '21 at 17:55
  • One reason why it might be reordered in the future could be to assist in matching of expressions to computed columns or indexed view definitions if these are currently not matched due to this (though I guess it will be of limited utility here anyway as it won't be able to push any additional seek on a computed column past the top ) – Martin Smith Mar 24 '21 at 18:03