There is a temporary table of the following form:

temporary table

CREATE TABLE #1 ( num1 FLOAT ,num2 FLOAT ) INSERT INTO #1 SELECT 0 ,0 INSERT INTO #1 SELECT 0 ,1 INSERT INTO #1 SELECT 1 ,0 INSERT INTO #1 SELECT 1 ,1

I execute the following request and it falls with an error:

 SELECT * FROM #1 a WHERE ( a.num1 > 0 AND a.num2 = 0 ) OR ( a.num1 > 0 AND a.num2 > 0 AND a.num1 / a.num2 >= 2 ) OR ( a.num1 >= 0 AND a.num2 < 0 )

Divide by zero error encountered.

I smoked a certificate, it says that the division operation is performed before all logical operations. Ok, now it became clear why falls with an error. But then I remove the "=" sign from the last condition and everything works:

 SELECT * FROM #1 a WHERE ( a.num1 > 0 AND a.num2 = 0 ) OR ( a.num1 > 0 AND a.num2 > 0 AND a.num1 / a.num2 >= 2 ) OR ( a.num1 > 0 AND a.num2 < 0 )

Looked at the query execution plan: execution plan
Judging by the query execution plan, the division by zero error should have reappeared. But that did not happen. Checked on Microsoft SQL Server version 2005 and 2012. The problem is this everywhere present. In Teradata, the first request works without problems.


My question is not how to rewrite the request to make it work, but WHY it works. Why, when in the second query I remove the "=" sign, the query works without problems?

    2 answers 2

    Apparently this is what it is.

    See the execution plan (XML). When you remove = , the predicate is optimized in (edited for brevity)

     <Predicate> <ScalarOperator ScalarString="..."> <Logical Operation="AND"> <ScalarOperator> num1 > 0 </ScalarOperator> <ScalarOperator> <Logical Operation="OR"> <ScalarOperator> num2 = 0 </ScalarOperator> <ScalarOperator> num1 / num2 >= 2 AND num2 > 0 </ScalarOperator> <ScalarOperator> num2 < 0 </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </Predicate>

    those.

     a.num1 > 0 AND ( a.num2 = 0 OR a.num1 / a.num2 >= 2 AND a.num2 > 0 OR a.num2 < 0 )

    The data lines (0, 0) and (0, 1) a.num1 > 0 by the condition a.num1 > 0 . The rows (1, 0) and (1, 1) satisfy it, but (1, 0) passes by the condition a.num2 = 0 (it does not reach division). Line (1, 1) does not pass on any OR condition.

    For comparison, in an unchanged query, the predicate (also edited for brevity), taken from the evaluation plan of the query, looks like this:

     <Predicate> <ScalarOperator ScalarString="..."> <Logical Operation="OR"> <ScalarOperator> num1 > 0 AND num2 = 0 </ScalarOperator> <ScalarOperator> num1/num2 >= 2 AND num1 > 0 AND num2 > 0 </ScalarOperator> <ScalarOperator> num1 >= 0 AND num2 < 0 </ScalarOperator> </Logical> </ScalarOperator> </Predicate>
    • Thanks for such a detailed answer! - Mexoff

    From the plan it is clear that the values ​​are treated as real. A real division by zero leads to the result NaN (not a number).

    While a clear division by zero results in a Divide by zero error.

    Probably, comparison > treated as an inaccurate comparison with zero, hence real numbers. A comparison >= interpreted as an exact comparison, hence the numbers.