CODEWITHSUNDEEP
sql-servert-sql
mma
mma

Reputation: 401

T-SQL puzzle: query fails when using it as an inline view

Preparation

create table t (x varchar(18))

insert into t (x) values ('8003372602728'), ('a')

Query #1:

select cast(x as bigint) y 
from t 
where isnumeric(x) = 1 and cast(x as bigint) > 100

Result for query #1:

y
--------------------
8003372602728

(1 row affected)

Query #2:

select * 
from
    (select cast(x as bigint) y 
     from t 
     where isnumeric(x) = 1 and cast(x as bigint) > 100) a
where y is not null

Result for query #2:

y
--------------------
8003372602728

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

What is the reason of the error in the result for query #2?

Upvotes: 1

Views: 61

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

First, note the difference between these queries:

select cast(x as bigint) y 
from t 
where isnumeric(x) = 1
  and cast(x as bigint) > 100;

select cast(x as bigint) y 
from t 
where cast(x as bigint) > 100
  and isnumeric(x) = 1;

The first one works and the second fails with the conversion error. The first one works because isnumeric is evaluated first and only numeric values are evaluated. With the second the query is evaluating: CAST(x as bigint) first. In theory, neither query should work because filters are not guaranteed to be evaluated in any specific order.

Next, this works:

select * 
from
    (select cast(x as bigint) y 
     from t 
     where isnumeric(x) = 1 and cast(x as bigint) > 100) a
----WHERE a.y > 100

... But, once you uncomment the WHERE clause it fails. This is because the WHERE clause on the Outer query is evaluating x before determining if ISNUMERIC is true.

This works:

select * 
from
    (select cast(x as bigint) y 
     from t 
     where isnumeric(x) = 1 and cast(x as bigint) > 100) a
    WHERE isnumeric(a.y) = 1

But, again, when I change the outer query to include a filter for y > 100...

select * 
from
    ( select cast(x as bigint) y 
      from t 
      where isnumeric(x) = 1 and cast(x as bigint) > 100) a
    WHERE isnumeric(a.y) = 1 and CAST(a.y AS bigint) > 100

It fails. Why? Because order of evaluation is not guaranteed.

TRY_CAST solves two problems:

First, isnumeric is a proprietary legacy function which does not do what most people think. isnumeric thinks all this is numeric:

SELECT ISNUMERIC('.'), ISNUMERIC('$'), ISNUMERIC('-'), ISNUMERIC('+'), ISNUMERIC('$+.');

Second, it solves the order of evaluation issue. This query works:

select * 
from
    (select TRY_CAST(x as bigint) y 
     from t 
     where TRY_CAST(x AS bigint) IS NOT NULL ) a
WHERE y > 100; -- Implies that it's NOT NULL

Upvotes: 2

Related Questions