SQL Server Why does UPDATE statement give divide by zero even if that particular row doesn't exist?

xzv2uavs  于 2023-03-07  发布在  其他
关注(0)|答案(4)|浏览(173)

Ok this may be idiot question but I want to know why does the update statement gives divided by 0 operation even thought there is no row in the table. Does it execute the set function first and then try to apply to the where clause.

CREATE TABLE #tmp
    (
    Id INT,
    Amount money
    )

    UPDATE t
    SET t.Amount=0/0
    FROM #tmp AS t
    WHERE t.Id=2

    DROP TABLE #tmp
6qqygrtg

6qqygrtg1#

If you try it like this you will not get an error :

CREATE TABLE #tmp
(
Id INT,
Amount money
)

UPDATE t
SET    t.Amount=0/0
FROM   #tmp AS t
WHERE  1 = 0

DROP TABLE #tmp

You get the error because your where clause forced a search in your table, and it will peform the set operation also at the same time

c9qzyr3d

c9qzyr3d2#

In SQL, you tell the system what you want, not how to do it.

Integer division is deterministic and you have an expression ( 0/0 ) that has no dependency on row values. It therefore seems perfectly reasonable to extract the expression 0/0 out, perform that calculation first, and then use the result of that expression as it actually scans through the table. So you get an error because it's not even started any work of processing the table.

In Guido's answer , the optimizer found a WHERE clause that meant that it could already tell that the query would affect zero rows (without needing to access any statistics about the table) and so it was able to optimize its work even further.

gudnpqoy

gudnpqoy3#

why does the update statement gives divided by 0 operation even thought there is no row in the table. Does it execute the set function first and then try to apply to the where clause.

Question is not about dived by 0,rather it is about when does any error occur.

BTW query easily pass through "Parse stage" and hand it is handed over to "Optimization" stage.

Example,
declare @table table(id int identity(1,1),PhoneNumber int)

select *,0/0 from @table

If you see " Estimated Execution plan (EEP) " then there is no error.that means it passes through Parse Stage and also EEP is created.

when we try to view Actual Execution Plan (AEP) then we get relevant error or divided by 0 error in this example.

select *,case when id=2 then 0/0 else 0 END
 from @table

Here again there is no problem in getting EEP. If we click on AEP then 1 rows is return and get error in 2nd row.

This prove that divided by 0/0 condition are executed when it meet certain condition.

select *
 from @table
 where id= case when id=2 then 0/0 else id END

Here again there is no problem in getting EEP. If we click on AEP then 1 rows is return and get error in 2nd row.

This prove that divided by 0/0 condition are executed when it meet certain condition.

3pmvbmvn

3pmvbmvn4#

Because the query will be parsed before SQL Server attempts to execute it. At that point it will throw an error because the command is unexecutable.

相关问题