Bug and SQLSERVERbug
Today, the customer reported that there was a bug in ms SQL, and the problem was quite big. So we made a mark:
Open the SQL query window and paste the following code:
Declare @ I intset @ I = 1 while @ I <5 -- execute in declare @ aa varchar (1) cyclically four times -- Define the variable print convert (varchar (1), @ I) + '--' + isnull (@ aa, '') -- print the variable select @ aa = '0' where 1> 2 -- determine if 1 is greater than 2, assign the value to the variable, the variable is not true. Therefore, the variable is not assigned a value if @ aa is null. if the variable is not obtained, assign the variable 1 set @ aa = '1' set @ I = @ I + 1 -- cyclic end
When the operation is completed, what is the situation discovered:
1 --
2-1
3--1
The variable defined in the 4-1 loop is indeed normal when it is printed for the first time. It is null because it is not assigned a value, but it turns into 1 after several cycles. What is the situation? The system actually took the value of the previous loop!
SQLSERVER defines variables in the loop. If the variable is assigned a value at the end of the loop, the value will be assigned to the defined variable at the next loop.There is no way at last. assign values to variables first and then perform the following operations:
Declare @ I intset @ I = 1 while @ I <5 begin declare @ aa varchar (1) set @ aa = ''print convert (varchar (1), @ I) + '--' + isnull (@ aa ,'') select @ aa = '0' where 1> 2 if @ aa is null set @ aa = '1' set @ I = @ I + 1end
The running result is as follows:
1 --
2 --
3 --
4 --
Alas, this is a problem for Microsoft.