IsNumeric failing with "A severe error occurred on the current command." SQL Server 2014 CTE

Question

I'm running a series of scripts which generate a database. They run to completion on SQL Server 2012 (11.0.5058.0). On SQL Server 2014 (12.0.4213.0) a script errors with:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

It appears that using the results of an IsNumeric statement inside a CTE query breaks query building, because no rows are required to cause the error. A boiled down version of the case I ran into is:

CREATE TABLE #Temp1 ( CTECol VARCHAR );
CREATE TABLE #Temp2 ( NumCol Int null);
;
WITH cte AS 
(
    SELECT 
        CASE WHEN ISNUMERIC(t.CTECol) = 1 
                THEN 1 
                ELSE null
        END as IsNCol1
    FROM
        #Temp1 t
)
SELECT * 
FROM #Temp2 
JOIN cte ON #Temp2.NumCol = cte.IsNCol1

The simplest case I can find is:

CREATE TABLE #Temp3 ( CTECol Int );
CREATE TABLE #Temp4 ( NumCol Int );
;
WITH cte AS 
(
    SELECT ISNUMERIC(t.CTECol) as IsNCol1
    FROM #Temp3 t
)
SELECT * 
FROM #Temp4 
JOIN cte ON #Temp4.NumCol = cte.IsNCol1

I checked the error levels from Microsoft and it looks like 11 is correctable user error and 20 is fatal error so I feel like I'm getting a mixed message.

Is there a correct way to do this or is it a regression in 2014?

Answer 1

This is certainly a bug.

There is no need for the CTE to produce this behaviour either. The below using the expression directly has the same effect.

SELECT *
FROM   #Temp4
       JOIN #Temp3
         ON #Temp4.NumCol = ISNUMERIC(#Temp3.CTECol) 

I could repro on 12.0.2269.0 and 12.0.4213.0 but not 12.0.4449.0 so it looks like it is now fixed.

The relevant KB article with details is (FIX: Access violation when a query uses ISDATE or ISNUMERIC functions in Join conditions in SQL Server 2014 SP1).

The stack trace when the exception was thrown is below (for searchability)

KernelBase.dll!RaiseException()
msvcr100.dll!_CxxThrowException(void * pExceptionObject, const _s__ThrowInfo * pThrowInfo) Line 157
sqldk.dll!ExceptionBackout::GetCurrentException(void)
sqldk.dll!ex_raise2(int,int,int,int,void *,char *)
sqldk.dll!ex_raise_va_list(int,int,int,int,char *)
sqllang.dll!alg_ex_raise(int,int,int,int,int,...)
sqllang.dll!CAlgTableMetadata::RaiseBadTableException(int,int)
sqllang.dll!CAlgTableMetadata::Bind(class CRelOp_Query *,class COptExpr *)
sqllang.dll!CRelOp_Get::BindTree(class COptExpr *,class CBindEnv *,int)
sqllang.dll!COptExpr::BindTree(class CBindEnv *,int)
sqllang.dll!CRelOp_FromList::BindTree(class COptExpr *,class CBindEnv *,int)
sqllang.dll!COptExpr::BindTree(class CBindEnv *,int)
sqllang.dll!CRelOp_QuerySpec::BindTree(class COptExpr *,class CBindEnv *,int)
sqllang.dll!COptExpr::BindTree(class CBindEnv *,int)
sqllang.dll!CRelOp_DerivedTable::BindTree(class COptExpr *,class CBindEnv *,int)
sqllang.dll!COptExpr::BindTree(class CBindEnv *,int)
sqllang.dll!CRelOp_Query::BindCTEList(class CBindEnv *,class COptExpr *)
sqllang.dll!CRelOp_SelectQuery::BindTree(class COptExpr *,class CBindEnv *,int)
sqllang.dll!COptExpr::BindTree(class CBindEnv *,int)
sqllang.dll!CRelOp_Query::FAlgebrizeQuery(class COptExpr *,class CCompExecCtxtStmt const &,enum EObjType,class CSequenceProjectContext *)
sqllang.dll!CProchdr::FNormQuery(class CCompExecCtxtStmt const &,class CAlgStmt *,enum EObjType)
sqllang.dll!CProchdr::FNormalizeStep(class CCompExecCtxtStmt const &,class CAlgStmt *,class CCompPlan *,bool,class CParamExchange *,unsigned long *)
sqllang.dll!CSQLSource::FCompile(class CCompExecCtxt const &,class CParamExchange *)
sqllang.dll!CSQLSource::FCompWrapper(class CCompExecCtxt const &,class CParamExchange *,enum CSQLSource::ESqlFunction)
sqllang.dll!CSQLSource::Transform(class CCompExecCtxt const &,class CParamExchange *,enum CSQLSource::ESqlState)
sqllang.dll!CSQLSource::Execute(class CCompExecCtxtBasic const &,class CParamExchange *,unsigned long)
sqllang.dll!process_request(class IBatch *,class SNI_Conn *,enum RequestType)
sqllang.dll!process_commands(void *)
sqldk.dll!SOS_Task::Param::Execute(class SOS_Task *,void * * const)
sqldk.dll!SOS_Scheduler::RunTask(class Worker *)
sqldk.dll!SOS_Scheduler::ProcessTasks(class SOS_Scheduler *,class Worker *)
sqldk.dll!SchedulerManager::WorkerEntryPoint(class Worker *)
sqldk.dll!SystemThread::RunWorker(class Worker *)
sqldk.dll!SystemThreadDispatcher::ProcessWorker(class SystemThread *)
sqldk.dll!SchedulerManager::ThreadEntryPoint(void *)
kernel32.dll!BaseThreadInitThunk()
ntdll.dll!RtlUserThreadStart()

Answer 2

I think it's a bug. However, I came up with a workaround that might work for you.

;WITH cte AS 
(
    --SELECT 
    --    CASE WHEN ISNUMERIC(t.CTECol) = 1 
    --        THEN 1 
    --        ELSE null
    --END as IsNCol1

    SELECT CASE WHEN TRY_PARSE(t.CTECol AS INT) IS NOT NULL
               THEN 1
               ELSE NULL
           END AS IsNCol1
    FROM #Temp1 t
)
SELECT  * 
FROM    #Temp2
JOIN    cte 
        ON #Temp2.NumCol = cte.IsNCol1

TRY_PARSE returns NULL if the cast fails, so if it is NOT NULL then you know it's a valid int.

There are some subtle differences between the two functions, but I prefer TRY_PARSE anyway, because according to MSDN

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($)

UPDATE:

I probably should clarify one of the subtle differences. ISNUMERIC returns 1 if the parameter can be parsed to any numeric type, and they are:

• int
• numeric
• bigint
• money
• smallint
• smallmoney
• tinyint
• float
• decimal
• real

It is not the same as TRY_PARSE, which attempts to parse the input to one of the above specific data types. (In my example, it INT). So if you really want to mimic ISNUMERICyou would need to use a nested (or flattened) CASE for each type. Even then behavior can still be somewhat unexpected, but that's a whole other story.