Geek asks and answers

MSSQL cast ([varcharColumn] to int) in SELECT is run before the WHERE clause filters bad values

Assume the following schema and query:

Please look at glaring design issues with the values ​​in the varchar column that we expect as ints.

create table dbo.Parent (
    Id bigint NOT NULL,
    TypeId int NOT NULL
)

create table dbo.Child (
    Id bigint NOT NULL,
    ParentId bigint NOT NULL,
    TypeId int NOT NULL,
    varcharColumn varchar(300) NULL
)

select cast(c.varcharColumn as int)
from dbo.Parent p (nolock)
    inner join dbo.Child c (nolock)
        on p.Id = c.ParentId
            and c.TypeId = 2
where p.TypeId = 13

Gap:

We get a throw throw due to a value that cannot be converted to int. In this case: "123-1". The strange thing is that the transferred value is filtered from the final set of results.

For example, this returns null results.

select c.varcharColumn
from dbo.Parent p (nolock)
    inner join dbo.Child c (nolock)
        on p.Id = c.ParentId
            and c.TypeId = 2
where p.TypeId = 13
    and c.varcharColumn = '123-1'

The query plan ends with a look at the Child table and actually uses the cast function before the where clause.

We were able to fix this by creating a new index in the child table (it was doing a PK scan)

create index [NCIDX_dbo_Child__TypeId] on dbo.Child (
    TypeId
)
include (
    ParentId,
    varcharColumn
)

Now it filters the parent table, where the sentence first.

? , , , . , , .

, , .

- :

, . - 15 , .

, . case, , ( Aaron), .

, !

+6
4

-, " ". SQL - , , , prcoessing. , , . , , SQL.

SQL Server (http://msdn.microsoft.com/en-us/library/ms181765.aspx), evauation CASE . , :

select (case when isnumeric(c.varcharColumn) = 1 then cast(c.varcharColumn as int) end)

, "int":

select (case when isnumeric(c.varcharColumn) = 1 and c.varcharColumn not like '%.%' and c.varcharColumn not like '%e%'
             then cast(c.varcharColumn as int)
        end)

, CAST. , ( ).

+6

, SQL Server . , , , - . , , , ( ..). :

;WITH c AS 
(
  SELECT varcharColumn, ParentID, TypeId
   FROM dbo.Child AS c
   WHERE c.TypeId = 2
   AND ISNUMERIC(varcharColumn) = 1 --*
)
SELECT CONVERT(INT, c.varcharColumn)
FROM dbo.Parent AS p
INNER JOIN c
ON c.ParentId = p.Id
WHERE p.TypeId = 13;

, CTE , . , , :

SELECT varcharColumn, ParentID, TypeId
INTO #c
   FROM dbo.Child AS c
   WHERE c.TypeId = 2
   AND ISNUMERIC(varcharColumn) = 1; --*

SELECT CONVERT(INT, c.varcharColumn)
  FROM dbo.Parent AS p
  INNER JOIN #c AS c
  ON c.ParentId = p.Id
  WHERE p.TypeId = 13;

( CASE .)

SQL Server 2012, - , , ISNUMERIC() ISNUMERIC(). *

SELECT TRY_CONVERT(INT, c.varcharColumn)
  FROM dbo.Parent AS p
  INNER JOIN dbo.Child AS c
  ON c.ParentId = p.Id
  WHERE c.TypeId = 2
  AND p.TypeId = 13;

* , IsNumeric . , .

+6

,

 create table dbo.Parent (
    Id bigint NOT NULL,
    TypeId int NOT NULL
)
    create table dbo.Child (
        Id bigint NOT NULL,
        ParentId bigint NOT NULL,
        TypeId int NOT NULL,
        varcharColumn varchar(300) NULL
    )

    select cast(c.varcharColumn as int)
    from dbo.Parent p (nolock)
        inner join dbo.Child c (nolock)
            on p.Id = c.ParentId
                and c.TypeId = 2
    where p.TypeId = 13

inner join, , , SQL- " " . , .

, ,

SELECT    MAX( Cast(dealer_number AS INT) + 1)
                    FROM   dealer_number dn
                    INNER JOIN dealer d
                    ON dn.dealer_seq = d.dealer_seq 
                    INNER JOIN dealer_type(nolock) dt
                    ON dt.dealer_number_seq = dn.dealer_number_seq
                    INNER JOIN program_dealer_type(nolock) pdt
                     ON pdt.program_dealer_type_seq = dt.program_dealer_type_seq
                    WHERE 
                    Isnumeric(dealer_number) = 1 AND
                    pdt.dealer_type = 'Dealer'
                    AND d.program_seq = 57

varchar '10054239051' int.

Isnumeric 100% , - .

Isnumeric ,

Isnumeric,

SELECT    MAX( Cast(dealer_number AS INT) + 1)
                    FROM   dealer_number dn
                    INNER JOIN dealer d
                    ON dn.dealer_seq = d.dealer_seq 
                    INNER JOIN dealer_type(nolock) dt
                    ON dt.dealer_number_seq = dn.dealer_number_seq
                    INNER JOIN program_dealer_type(nolock) pdt
                    ON pdt.program_dealer_type_seq = dt.program_dealer_type_seq
                    WHERE 
                    pdt.dealer_type = 'Dealer'
                    AND d.program_seq = 57

now I just removed this preprocessing filter and it just solves the problem, but 100% proof that I didn’t receive any alphanumeric numbers, I had to use case in select

SELECT    MAX(case WHEN 
                    ISNUMERIC(DEALER_NUMBER )=1 
                        THEN  Cast(dealer_number AS INT) 
                        ELSE 0 END 
                    + 1) 
                    FROM   dealer_number dn
                    INNER JOIN dealer d
                    ON dn.dealer_seq = d.dealer_seq 
                    INNER JOIN dealer_type(nolock) dt
                    ON dt.dealer_number_seq = dn.dealer_number_seq
                    INNER JOIN program_dealer_type(nolock) pdt
                    ON pdt.program_dealer_type_seq = dt.program_dealer_type_seq
                    WHERE 
                    pdt.dealer_type = 'Dealer'
                    AND d.program_seq = 57

Above will solve both of these problems. Anyone had a point of view on this most long-awaited. :)

0
source

You can move the filter to a subquery that first filters out the bad shafts and then throws everything. This does not answer the question, but it really gives you what you want in that, in my opinion, the easiest way.

select cast(varcharColumn as int)
FROM(
select c.varcharColumn

from dbo.Parent p (nolock)
    inner join dbo.Child c (nolock)
        on p.Id = c.ParentId
            and c.TypeId = 2
where p.TypeId = 13
) table1
-1
source

More articles:


All Articles