Posted inSQL
Microsoft
5 years ago
Update produces impossible result
he following produces an illogical result:DECLARE @Target table(c1 integer PRIMARY KEY,c2 integer NOT NULL,c3 integer NOT NULL);DECLARE @Source table(c1 integer NULL,c2 integer NULL,c3 integer NULL,INDEX c CLUSTERED (c1));INSERT @Target(c1, c2, c3)VALUES(1, 0, 0);INSERT @Source(c1, c2, c3)VALUES(1, 2, NULL),(1, NULL, 3),(1, 4, 4);UPDATE TSET T.c2 = S.c2,T.c3 = S.c3FROM @Target AS TJOIN @Source AS SON S.c1 = T.c1;SELECT * FROM @Target AS T;Result:c1 c2 c31 2 3The update is non-deterministic, but it should not produce a result that comes from a mixture of matched rows. It should throw an error in this case because the target does not accept nulls in any row, and the matching source rows all have a null.
New
ProgrammabilityBugsNew
Vote
0 Comments