Ivan Bowman suggests CAST ( ... ON EXCEPTION ERROR / WARNING / NULL / value )

[Breck_Carter]

Here is a Question-and-Ivan's-Answer posted on the NNTP newsgroup; scroll to the bottom to see his suggestion.

=====

We had a problem where a piece of SQL failed in production but worked in test. Taking a backup of the production db and running it in test worked fine. We finally tracked the problem to a view but if you converted that to a subselect the symptoms were the same.

The subselect/view has several self-joins to return a set from an hierarchical table. Part of this includes the phrase "FROM code ct JOIN code cm ON convert(integer,left(cm.code_export,3)) = ct.code_id". This has always worked in test and (up until now) in production. The issue is that cm.code_export can contain alphnumerics. In test this isn't an issue as the execution plan has already identified the rows to go into the join and 'convert'. In production however, the plan performs a table scan for this join and so it fails.

I found this morning that restarting the server (it's run continuously for two years) fixed the problem as production now generates an execution plan just like test (no more table scan).

As this is a medical database the wrong results could be very bad so I'd really really like to know why stopping and restarting the server changes the way plans are generated. The code table has 930 rows and is updated twice a year so table statistics virtually never change. Does this merit a case opened with Sybase?

SQL Anywhere 10.0.1.3710 (32bit)

Windows 2003 Build 3790 Service Pack 2

===== On 21 Oct 2010 09:25:33 -0700, "Ivan T. Bowman [Sybase iAnywhere]" wrote:

If an expression can generate an error for some input data, then the statement may give an error on some executions and not others. As you point out, differences in execution plan mean that the statement may give an error during one execution but not another, even when the same data is used during the execution. Restarting the server or otherwise perturbing the system state can alter the plan selected by the query optimizer since the optimizer considers how much of each table is stored in the server's buffer pool (along with a number of other system parameters). It is an interesting question why the server was selecting a table scan before the restart an no longer afterwards, but the answer will be hard to achieve without detailed statistics from before the server was restarted.

Since plan selection can cause variable behaviour, I suggest that expressions that might generate errors should be wrapped in an IF expression along the following lines:

select  *
from    sa_split_list('12345,67a341,9756')
where   if 1=isnumeric( left( row_value, 3 ) ) then convert( integer, 
left( row_value, 3 ) ) endif > 0

The IF expression returns NULL if the first three characters of the row_value are not numeric. This is appropriate for your purposes because the NULL will not join with the other table.

Another alternative that I would not recommend is to set the Conversion_error to off:

    set temporary option Conversion_error='off'

This approach affects all conversions, causing them to return NULL instead of generating an error. It can be appropriate but it can also lead to missing important errors.

I have thought over the years that it would be nice for CAST and CONVERT to specify the behaviour for conversion errors (and possibly a separate specification for overflow). The options could be something like the following:

1. ERROR -- current behaviour and the default
2. WARNING -- generate a warning condition but not an error
3. NULL -- return NULL
4. value -- return a value specified in the CAST or CONVERT expression.

Lacking this feature you can still approximate it using extry code for an IF or CASE.

Ivan T. Bowman

SQL Anywhere Research and Development

[Sybase iAnywhere]