Check for arithmetic overflows in computed columns?

Question

In our application we're going to be allowing users to type in arithmetic expressions (+ - * /) using other database columns for numbers, which would then be parsed by the application and written into the database as a computed column.

However there are problems that arise with allowing users to create expressions that could cause exceptions when you select * the table, such as divide-by-zero, arithmetic-overflow, and possible others I haven't come across yet (though I think that's all of them).

Having the database throw an exception on select * would be absolutely devastating. I would rather try to rewrite their expression into something that would fail gracefully if they have error-prone data.

For divide-by-zero the solution is pretty straightforward:

add [Col] as case {divisor} when 0 then N'DIVIDE-BY-ZERO' else {expression} end

My question is what can I do for arithmetic overflow? Showing bunk or obviously wrong data in the column would not be a problem, but throwing exceptions would.

Answer 1

How about catching the exceptions? It seems hard to try to detect all possible causes of arithmetic overflows, let alone exceptions in general.

On exceptions, you can return a nonsense result set that is compatible with the original user-defined one:

begin try
    select exp(999)
end try
begin catch
    select 1
end catch

Answer 2

Since you have parsing technology, you can re-write the expressions to trap all the potential issues with a bunch of CASE statements - divide by zero, overflows, upcasting to different types, etc.

But I wouldn't do that, because I don't think putting these into the database as part of the schema is a great idea, unless this tool is a code-generation tool nd users are responsible for reviewing and testing the expressions for edge conditions just as if they were included in the original database design.

If you are already parsing the expression, I would compile this at the client side, too, and handle the error on a row-by-row basis.

Catching the exception using TRY/CATCH is an all or nothing scenario, not on a row-by-row basis.

Answer 3

I hate to see you accept an answer which doesn't really get you closer to your goal.

As a separate answer which might help you out a little, you can make computed columns call a (deterministic) scalar UDF.

See, for instance, here

So if you are going to create a computed column, make it pass the columns to a generated UDF (or several UDFs) and do the work there. In a scalar UDF, you can have plenty of code to catch the problems but you still cannot use TRY/CATCH. What you can do in your scalar UDF is catch cases and return appropriate answers (bubbling up NULLs, probably).

But performance will be very poor on scalar UDFs (not sure about non-persisted inline computed columns vs. UDFs, we mainly use persisted), so you might seriously want to consider making the column persisted, which will then use space in the database and make inserts and updates a little slower. That's a big tradeoff.

Answer 4

If performance is important, use a trigger or indexed view to store the calculations when the data is inserted, updated or deleted.

If performance is not important, use a scalar valued function.