This week we had a very confusing problem with a SQL subselect in a JDBC PreparedStatement for MS SQL Server. I looks like the following:

UPDATE table_a

SET intCol_A = (SELECT intCol_B

                FROM table_b

                WHERE strCol_B = ?)

WHERE strCol_A = ?

Very simple and easy to understand. It’s works fine until the first question mark was replaced by the null value. Boom!

strCol_B has no null value and should not have one. So logically the subselect should not select anything and return the null value. But it does not. It selected always the same value from strCol_B! (Not intCol_B!) A very weird behaviour. The string was a guid made of capitals and digits, so it could not be casted as an integer, which leads to an error.

How we fixed this? Instead of null we now use a string strCol_B can not contain: $NIL%. The subselect now does not select anything and returns the null value. All fine now.

Weird, isn’t it?