Início / dba / Perguntas / 167189
Accepted
Brad
Asked: 2017-03-15 13:45:46 +0800 CST

Erro ao converter NVARCHAR para BIGINT quando em SUBQUERY com cláusula WHERE após registros inválidos filtrados [duplicado]

  • 772

Tenho uma tabela que possui uma coluna NVARCHAR que contém dados que não podem ser convertidos para BIGINT. Estou bem ciente disso e filtrei usando ISNUMERIC(BB.NVARCHARCOL) = 1. Apesar disso, ainda recebo um erro ao tentar consultar os dados informando queError converting data type nvarchar to bigint.

O seguinte funciona bem (sem erros relatados pelo SQL):

SELECT *
FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
WHERE ISNUMERIC(BB.NVARCHARCOL) = 1

O seguinte lança o erro:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234

Essa variação também lança o erro:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN
        (SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1) BB
      ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234

Tenha em mente que isso não dá erro e retorna todos os registros com sucesso...

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1

Consegui encontrar uma solução, que era converter meu BIGINT para NVARCHAR na subconsulta:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN myBadTable BB ON BB.NVARCHARCOL = CAST(AA.MYBIGINTCOL AS NVARCHAR)
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234

Se eu inserir os registros em uma tabela temporária:

SELECT CAST(NVARCHARCOL AS BIGINT) NVARCHARCOL INTO #TEMP FROM myBadTable WHERE ISNUMERIC(NVARCHARCOL) = 1

Eu posso usar essa tabela temporária em uma subconsulta com sucesso:

SELECT *
FROM (
    SELECT *
    FROM myNormalTable AA INNER JOIN #TEMP BB ON BB.NVARCHARCOL = AA.MYBIGINTCOL
    WHERE ISNUMERIC(BB.NVARCHARCOL) = 1
    ) ZZ 
WHERE ZZ.MYBIGINTCOL = 1234

O que anda acontecendo no mundo? Parece que o SQL se recusa a usar a subconsulta para obter um conjunto de resultados menor antes de executar a consulta externa, ele quer usar a tabela inteira, independentemente do que eu fizer. SQL Server 2012 Developer Edition

sql-server subquery

2 respostas

  1. Best Answer

    Use try_cast()em vez disso.

    select *
from (
  select *
  from myNormalTable AA
  inner join #TEMP BB on try_cast(BB.NVARCHARCOL as bigint) = AA.MYBIGINTCOL
  --where try_cast(BB.NVARCHARCOL as bigint) is not null /* not neccessary for inner join */
 ) ZZ
where ZZ.MYBIGINTCOL = 1234

    No Sql Server 2012 e superior: cada um deles retornará nullquando a conversão falhar em vez de um erro.

    • 5

  2. O SqlZim já te deu um bom método para evitar o erro na resposta dele . No entanto, na pergunta e nos comentários, você parece curioso para saber por que uma consulta gera um erro e a outra não. Consegui reproduzir seu problema:

    CREATE TABLE dbo.X_BIGINT_TABLE (ID BIGINT NOT NULL);

INSERT INTO dbo.X_BIGINT_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.X_NVARCHAR_TABLE (ID_NV NVARCHAR(10) NOT NULL);

INSERT INTO dbo.X_NVARCHAR_TABLE WITH (TABLOCK)
SELECT TOP (999) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10))
FROM master..spt_values

UNION ALL

SELECT 'ZOLTAN';

    Esta consulta funciona bem:

    SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE ISNUMERIC(NV.ID_NV) = 1;

    Esta consulta gera um erro:

    SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

    Msg 8114, Nível 16, Estado 5, Linha 25

    Erro ao converter o tipo de dados nvarchar para bigint.

    O otimizador de consulta do SQL Server pode reordenar os elementos de uma consulta como achar melhor para tentar encontrar um plano de consulta com um custo estimado suficientemente bom, desde que as alterações não afetem os resultados finais da consulta. Para ilustrar o conceito, vamos percorrer uma possível maneira pela qual a segunda consulta pode ser refatorada. Para ser claro, este não é o processo passo a passo real pelo qual o otimizador de consulta passa para este exemplo. Comece com a consulta:

    SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

    Empurre o predicado:

    SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1
) ZZ;

    A tabela derivada não é mais necessária, então livre-se dela:

    SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1

    Sabemos disso BI.ID = NV.ID_NVpara que possamos aplicar o filtro também Z.IDa NV.ID_NV:

    SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500

    A junção não precisa mais ser implementada INNER JOINporque estamos filtrando para um único valor para ambas as colunas de junção. Podemos reescrever como CROSS JOIN:

    SELECT * 
FROM 
(
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    WHERE BI.ID = 500
) 
CROSS JOIN 
(
    SELECT *
    FROM dbo.X_NVARCHAR_TABLE NV
    WHERE ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500
);

    Se observarmos o plano de consulta para a segunda consulta, podemos dizer que o resultado final é muito semelhante à consulta transformada final:

    consulta transformada

    Aqui está o texto do predicado de filtro para referência:

    CONVERT_IMPLICIT(bigint,[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0)=(500) 
AND isnumeric(CONVERT_IMPLICIT(varchar(20),[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0))=(1)

    Se o SQL Server avaliar a CONVERT_IMPLICITparte do predicado antes da isnumericparte, obteremos um erro.

    Como regra geral, evite confiar na ordem implícita das operações ao escrever consultas SQL. Você pode ter uma consulta que funciona bem hoje, mas começa a gerar erros se os dados forem adicionados à tabela ou se um plano de consulta diferente for escolhido. Existem, é claro, exceções (mais ou menos). Na prática, você normalmente verá as diferentes partes de uma CASEdeclaração para avaliar na ordem em que as escreveu, mas mesmo assim é possível obter erros que você não esperava . Você também pode adicionar um supérfluo TOPa partes de sua consulta para incentivar uma determinada ordem de operações. Considere a seguinte consulta:

    SELECT *
FROM (
    SELECT TOP (9223372036854775807) *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
    ) ZZ 
WHERE ZZ.ID = 500;

    Você e eu sabemos que TOPnão alterará os resultados da consulta, no entanto, não há garantia para o otimizador de que a tabela derivada não retornará mais de 9223372036854775807 linhas, portanto, ele deve avaliar o arquivo TOP. Tecnicamente, nessa consulta, solicitamos as primeiras 9223372036854775807 linhas e, em seguida, queremos filtrar as linhas com um valor IDdiferente de 500. Empurrar o ID = 500predicado para a tabela derivada pode alterar os resultados, de modo que o SQL Server não fará isso. Neste exemplo, a consulta é executada sem erro e a filtragem é feita no final:

    plano superior

    • 4

relate perguntas