3

When running this query:

create table #test (field varchar(100))

insert into #test (field) values ('this is not a number')
insert into #test (field) values ('1234567')

select  *
from    (   select  field
            from    #test
            where   ISNUMERIC(field) = 1
        ) as subquery
where   CAST(field as int) > 100

drop table #test

I am expecting 1234567 to be returned, but get this instead:

Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'this is not a number' to data type int.

It's like the inner query doesn't execute first and the query optimiser makes (incorrect) assumptions about what I am trying to do. I have had to work around this with a temp table:

create table #test (field varchar(100))

insert into #test (field) values ('this is not a number')
insert into #test (field) values ('1234567')

select  field
into    #subquery
from    #test
where   ISNUMERIC(field) = 1

select  *
from    #subquery
where   CAST(field as int) > 100

drop table #subquery
drop table #test

Is this a bug in the SQL query optimiser? Can anyone explain this to me?

EDIT: In response to this question being a duplicate ... I am not using an 'IN' statement. The logic as I have written it implies that I require a filtered subquery returned BEFORE I apply the CAST condition. Any sane person would expect my query to behave in that manner. The optimiser's version of the query clearly is not logically identical.

Jack
  • 3,444
  • 5
  • 34
  • 50
  • Possible duplicate of [In which sequence are queries and sub-queries executed by the SQL engine?](https://stackoverflow.com/questions/2263186/in-which-sequence-are-queries-and-sub-queries-executed-by-the-sql-engine) – devlin carnate Feb 08 '18 at 23:32
  • Aside: [`IsNumeric()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql) is notoriously [problematic](http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/). – HABO Feb 09 '18 at 03:38

1 Answers1

3

SQL Server optimiser take the commands (t-sql) as input, looks at the available resources (indexes, statistics etc.) and comes up with the best possible plan, it think for the query. This includes what query/parts of query gets executed first, which predicates gets pushed deeper into the query etc etc.

Thus if you look at the execution plan for this query, the predicates were pushed into the sub-query hence the cast to int failed.

There is only single operator in the execution plan, i.e. table scan and both the predicates were passed to the inner query.

If you think about it, it makes sense, why return more rows from the sub-query than needed, only to be filtered out by the where clause from the outer query. I would not consider it a bug but a clever way of optimising execution plans :)

enter image description here

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Sorry, no, it does NOT make sense. The two are NOT logically identical. It makes perfect sense to expect the inner query to be completed BEFORE any outer conditions are applied. Is there an optimiser hint of keyword I can apply to make this happen? – Jack Feb 08 '18 at 23:42
  • 1
    Well if you don't agree with the way SQL Server works, you can open a connect item. But this is how it works and it works good in most of the cases :) – M.Ali Feb 08 '18 at 23:45
  • @Jack This is maybe not the example that shows it's good, but this IS actually good for most of use cases. And your use case that does not make sense actually does not need the sub-query at all. SQL Server has correctly concluded the same thing and merged your query into single statement - something you could have done. `SELECT field FROM #test WHERE ISNUMERIC(field) = 1 AND CAST(field as int) > 100` would work fine... – Nenad Zivkovic Feb 08 '18 at 23:49
  • @nenad ... run it and it will fail because it will try to cast the string 'this is not a number' to an int. It applies the cast to ALL rows ... it is not smart enough to realise that if ISNUMERIC condition fails then not to try the CAST. – Jack Feb 08 '18 at 23:53
  • 1
    @jack I tried it and it works fine... http://sqlfiddle.com/#!18/d9805/2 ... but again, like said, SQL Server will come up with the best possible plan when you run it.. so it maybe your instance has some more information, that we do not have – Nenad Zivkovic Feb 08 '18 at 23:56
  • i have tried `select field from #test where ISNUMERIC(field) = 1 and CAST(field as int) > 100 ` on SQL 2008R2 & 2016, both executes just fine. Anybody can explain ? The predicate is `isnumeric([tempdb].[dbo].[#test].[field])=(1) AND CONVERT(int,[tempdb].[dbo].[#test].[field],0) > (100)` It seems like the sequence of the `WHERE` condition does matters – Squirrel Feb 09 '18 at 00:01
  • As long as the ISNUMERIC is called first in WHERE it will run first and does work, i have tried this on SQL 2008, 2012,2016. – Owain Esau Feb 09 '18 at 00:49
  • I tried the same query with WITH clause and still it fails trying to convert invalid numeric data – Anil Soman Feb 09 '18 at 05:48
  • @OwainEsau Can you cite documentation that states that expressions in a `where` clause are evaluated left-to-right with short circuiting? "I tried it and it worked" isn't quite the basis for a robust solution. – HABO Feb 09 '18 at 19:09
  • @M.Ali It looks like [Connect](https://learn.microsoft.com/en-us/collaborate/connect-redirect) moved on effective 1 January 2018. Sigh. – HABO Feb 09 '18 at 19:14