CASE statement varchar to bigint conversion error on trigger with multiple target rows

Question

I have an interesting issue.  I have sql 2008  (publisher and subscriber) transactional replication from DB1.TABLEA to DB2.TABLEA.  DB2.TABLEA has an insert/delete trigger and an update trigger to DB2.TABLEB.  The datatype of two of the fields (colA and colB) are varchar(5) in DB2.TABLEA  and the results of a case statement go into an int field in DB2.TABLEB.col2 and I have narrowed down the issue to the update trigger statement case below which is trying to see if the varchar datatypes are not ints and to default the resulting value to 1 in the DB2.TABLEB table.  Replication fails if there are multiple rows in DB2.TABLEB and the varchar fields in DB2.TABLEA are not numeric with a "Error converting data type varchar to bigint" error.  

fn_isinteger is a function that accepts a varchar(100) parameter and returns a bit value as it parses the string and determines if the ASCII value is between 48 and 57.  Any ideas as to why this is happening would be appreciated.  The only thing I can think of is a precedence issue on the CASE statement related to the varchar value coming in, and the fact that bigint has a higher precedence but this issue is only happening when the are multiple records in DB2.TABLEB and not when we have a varchar value coming in to DB2.TABLEA and there is only one record in DB2.TABLEB so I am a bit stumped. 

update TABLE DB2.TABLEB

SET col2=I.result

FROM(select

CASE WHEN fn_isinteger(I.colA)<>1 OR fn_isinteger(I.colB)<>1 OR cast(I.colA as bigint)>32767 or cast(I.colB as bigint)>32767 THEN 1

END AS result

FROM INSERTED) I

inner join DB2.TABLEA

WHERE DB2.TABLEB.ID=I.ID

--DB2.TABLEB has multiple records for particular IDs

Answer 1

It could certainly be that SQL is sometimes deciding to do the CAST before it does the function calls.  The best way to force the order is to use multiple WHEN's, so for example

update TABLE DB2.TABLEB
SET col2=I.result
FROM(select
CASE WHEN fn_isinteger(I.colA)<>1 THEN 1
   WHEN fn_isinteger(I.colB)<>1 THEN 1
   WHEN cast(I.colA as bigint)>32767 THEN 1
   WHEN cast(I.colB as bigint)>32767 THEN 1
END AS result
FROM INSERTED) I
inner join DB2.TABLEA
WHERE DB2.TABLEB.ID=I.ID 

If that doesn't help, could you post the code to the fn_isinteger function?

Tom

Answer 2

That makes sense.  But what I'm also wondering is why would the cast only run when the target table has multiple records for the ID but not when there is only one record in the target table for the ID (i.e., the case statement fails only when the target table has multiple rows).  I'm running SQL 2008 SP2.  Not sure if there are any known bugs related or if it is similar to this situation:  http://www.fmsinc.com/free/newtips/sql/sqltip10.asp

Answer 3

What makes the query optimizer pick one plan or the other is often hard do know.  When you have something like

<condition 1> or <condition 2> or <condition 3> or <condition 4>

SQL is allowed to do those tests in any manner it deems to be the most efficient way to generate the correct result given all that the query optimizer knows (including the amount and distribution of the data).  So for different data SQL may produce different execution plans.

Generally speaking, if you want to force the conditions to be checked in a particular order, and if a particular condition succeeds you want to avoid processing any of the remaining conditions, you want to use a CASE statement with multiple WHEN's.  A year ago, instead of "generally speaking", I would have said "always", but it turns out there are some exceptions.  See http://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order and https://connect.microsoft.com/SQLServer/feedback/details/691535/aggregates-dont-follow-the-semantics-of-case.  But those should not apply in your problem.  So the CASE statement with multiple WHEN's in the order you want them processed should work.

Your problem is not caused by the situation discussed in http://www.fmsinc.com/free/newtips/sql/sqltip10.asp

In that situation the problem was caused because the different branches of the WHEN clauses returned different datatypes (in one case decimal, in the other varchar).  So SQL used the standard datatype precedence rules to decide what datatype the result should be.  Since decimal is higher than varchar, the result will always be decimal.  So if the IsNumeric() returns 1, the value is converted to decimal.  But if the IsNumeric() returns 0, the WHEN clause is not done, instead it does the ELSE, but the ELSE returns a varchar, but the CASE statement as a whole must return a decimal, so SQL attempts to convert this value to a decimal and it fails.  Note that I disagree with the author of that page when he writes "The real problem is that if you choose to Convert the values anywhere within the Case statement, the datatype you are converting the values to is the expected type of ALL the values regardless of if they are of that type or not."  SQL will not try to convert everything to the type specified in the Convert function.  Instead, it converts everything to the type with the highest precedence.

But in any case, you don't have the problem discussed in that document because that problem was caused by having multiple WHEN clauses that returned different datatypes.  But you are always returning the same datatype  (either a value of 1 - which will have a type of integer or NULL if none of the conditions are true, so since NULL can become any datatype, the value returned will have a type of integer.

Tom

Answer 4

Tom has already answered why your query blows up: because SQL Server is free to blow it the way you have written it.

Left to answer is the question why it depends on the number of rows inserted. Since SQL Server is free do whatever it likes, we could just answer "implementation dependent". However, one factor that may play in is that a trigger typically has two execution plans in the cache: one for single-row operations, and one for multi-row operations.

---

Erland Sommarskog, SQL Server MVP, [e-mail]