Outer query is running before inner query

Question

Why does this not work?

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where cast(membership_number as int) > 2

See SQL Fiddle Demo.

The subquery should filter out data that is non numeric, and the outer query is casting this to an integer so that I can look for anything > 2.

It seems like it is running the where clause of the outer query first. How do I get around this?

Have you run the inner query by itself to ensure it is returning what you expect? — OldProgrammer, Apr 10 '13 at 00:52
@OldProgrammer- Yes I have, and the inner query works fine. I've tried a CTE and it does the same thing. The only way I can get it to work is if I do a `select membership number into test_table` and then replace the subquery with that table. — Lock, Apr 10 '13 at 00:56
If you look at the execution plan with a slightly modified `WHERE` it is rolling the sub-query where clause in with the main `WHERE`: [Execution Plan](http://screencast.com/t/v7ZyNISqC) I'm still thinking about how to prevent that behavior. — Adam Wenger, Apr 10 '13 at 00:57
Please check this - T-SQL functions do no imply a certain order of execution http://rusanu.com/2011/08/10/t-sql-functions-do-no-imply-a-certain-order-of-execution/ — EricZ, Apr 10 '13 at 01:04
For your question, you can use WHERE ISNUMERIC(membership_number) = 1 AND cast(membership_number as int) > 2 — EricZ, Apr 10 '13 at 01:05
@EricZ I do not believe `SQL-Server` guarantees the short-circuit behavior you're suggesting in the `WHERE` clause — Adam Wenger, Apr 10 '13 at 01:08
@EricZ: See updated SQL Fiddle Demo here: http://sqlfiddle.com/#!6/adc12/2 — Lock, Apr 10 '13 at 01:09
@Lock, your second demo had issue is becuase "1234." is vaild number, but not INT, if you cast to NUMERIC, then it's running fine. — EricZ, Apr 10 '13 at 01:16

score 2 · Answer 1 · answered Apr 10 '13 at 01:09

2

Maybe that:

select *
from
(
    select
        membership_number
    from
        members
    where
        membership_number not like '%[^0-9]%'
) mem
where Try_Convert(int, membership_number) > 2

answered Apr 10 '13 at 01:09

mkjasinski

3,115
2
22
21

@EthanLi yes, but in question did not specify a version. – mkjasinski Apr 10 '13 at 01:13

score 1 · Answer 2 · answered Apr 10 '13 at 01:10

I had the issue before. What I did was:

1, you can have a view which does:

select membership_number
    from members
    where membership_number not like '%[^0-9]%'

2, or use temp table for it

3, or use case clause:

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where (CASE WHEN ISNUMERIC(membership_number) THEN cast(membership_number as int) ELSE 0 END) > 2

did not have a elegant solution, but hope this helps

score 1 · Accepted Answer · answered Apr 10 '13 at 01:32

Very interesting, I tried to reproduce this on SQL Server and found next. I changed your query to simple just to make sure that query will not fail and I can see the execution plan:

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where membership_number > '2'

Execution plan is has Table Scan with predicate:

[master].[dbo].[members].[membership_number]>'2' 
    AND NOT [master].[dbo].[members].[membership_number] like '%[^0-9]%'

So this is because SQL Optimization engine works in this way (as somebody said - nobody can guarantee you the order of where clauses). One of the ways to fix it probably is to use ISNUMERIC before

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where ISNUMERIC(mem.membership_number) = 1 and cast(mem.membership_number as int) > 2

nathan_jr · Answer 4 · 2013-04-10T03:52:04.583

0

The comments explain how the execution plan can (at times) choose to evaluate the cast prior to the like. A case statement can aid the order of evaluation but as Adams mentions even this method is not 100%.

select  *
from    members
where   case
            when membership_number like '%[^0-9]%' then 0
            when cast(membership_number as int) > 2 then 1
            else 0
        end = 1

edited Apr 10 '13 at 03:52

answered Apr 10 '13 at 01:14

nathan_jr

9,092
3
40
55

1

`CASE` statements do not always short-circuit: [Connect item](http://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order) – Adam Wenger Apr 10 '13 at 01:32
@AdamWenger noted in post. +1 – nathan_jr Apr 10 '13 at 03:52
1

@AdamWenger: in the article, it mentions a specific case with aggregate functions, which are specified to be evaluated first always. In the OP's question, there's no aggregate function, so this shouldn't be a problem. And, as always, test to verify. – siride Apr 10 '13 at 04:00

score 0 · Answer 5 · edited May 23 '17 at 12:24

0

You can try this, in following query first condition is executed and if it fails then it won't executes 2nd condition

select
    membership_number
  from
    members
where 
isnumeric(membership_number) = 1 and 
cast(membership_number as int) > 2

And to answer why your query is not working check this explanation here

edited May 23 '17 at 12:24

Community

1
1

answered Apr 10 '13 at 04:06

rs.

26,707
12
68
90

score 0 · Answer 6 · answered Apr 04 '22 at 08:28

Old query, but still relevant.

Found this trick to force subquery to be run first. Try this:

select *
from
(
    select TOP (9223372036854775807) membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where cast(membership_number as int) > 2

Value 9223372036854775807 is the BIGINT max value, probably big enough for most cases.

Outer query is running before inner query

6 Answers6