IsNumeric завершается с ошибкой "Произошла серьезная ошибка в текущей команде". SQL Server 2014 CTE

Question

Я запускаю серию сценариев, которые генерируют базу данных. Они работают до конца на SQL Server 2012 (11.0.5058.0). На SQL Server 2014 (12.0.4213.0) ошибки скрипта с:

Сообщение 0, Уровень 11, Состояние 0, Строка 0
Произошла серьезная ошибка в текущей команде. Результаты, если таковые имеются, должны быть отброшены.

Сообщение 0, Уровень 20, Состояние 0, Строка 0
Произошла серьезная ошибка в текущей команде. Результаты, если таковые имеются, должны быть отброшены.

Похоже, что с использованием результатов IsNumeric Оператор внутри запроса CTE нарушает построение запроса, потому что для возникновения ошибки не требуется никаких строк. Вариант дела, с которым я столкнулся, выглядит следующим образом:

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

Простейший случай, который я могу найти:

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

Я проверил уровни ошибок от Microsoft, и похоже, что 11 - исправляемая ошибка пользователя, а 20 - фатальная ошибка, поэтому я чувствую, что получаю смешанное сообщение.

Есть ли правильный способ сделать это или это регрессия в 2014 году?

Answer 1

Это конечно ошибка.

Для CTE также нет необходимости производить это поведение. Нижеприведенное использование выражения имеет тот же эффект.

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

Я мог бы воспроизвести на 12.0.2269.0 и 12.0.4213.0, но не 12.0.4449.0, так что похоже, что теперь это исправлено.

Соответствующая статья базы знаний с подробностями: ( FIX: нарушение прав доступа, когда запрос использует функции ISDATE или ISNUMERIC в условиях соединения в SQL Server 2014 с пакетом обновления 1 (SP1)).

Трассировка стека при возникновении исключения приведена ниже (для поиска)

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

Я думаю, что это ошибка. Тем не менее, я нашел способ, который может помочь вам.

;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 возвращается NULL если приведение не удается, так что если это NOT NULL тогда вы знаете, что это действительный Int.

Между этими двумя функциями есть некоторые тонкие различия, но я предпочитаю TRY_PARSE во всяком случае, потому что в соответствии с MSDN

ISNUMERIC возвращает 1 для некоторых символов, которые не являются числами, такими как плюс (+), минус (-), и допустимыми символами валюты, такими как знак доллара ($)

ОБНОВИТЬ:

Я, наверное, должен уточнить одно из тонких различий. ISNUMERIC возвращает 1, если параметр может быть проанализирован для любого числового типа, и они:

• ИНТ
• числовой
• BIGINT
• Деньги
• SMALLINT
• smallmoney
• TINYINT
• поплавок
• десятичный
• реальный

Это не то же самое, что TRY_PARSE, который пытается проанализировать входные данные для одного из указанных выше конкретных типов данных. (В моем примере это INT). Так что если вы действительно хотите подражать ISNUMERICвам нужно будет использовать вложенный (или сплющенный) CASE для каждого типа. Даже тогда поведение все еще может быть несколько неожиданным, но это совсем другая история.