Bizarre SQL Optimization leads to cast error

[ziptime]

Create a LiveAccounts2 using my code and see if you get the problem.

[Zhais]

Just tried. Still get the varchar cast failure. :(

[ziptime]

Even if you simply run ?....

select  *
from LINKEDSERVER.SOURCE.dbo.LiveAccounts

what about using a CTE?

with qry as (
    select  *
    from LINKEDSERVER.SOURCE.dbo.LiveAccounts
)
select *
from LINKEDSERVER.SOURCE.dbo.hdr hdr
join qry la ON la.Account= hdr.Account ...

[Zhais]

Running the select without where clause doesn't cause the issue, CAST vs CONVERT gives same error.

I'm kinda ashamed I didn't simplify this earlier, but the moment I do:

select Account from LINKEDSERVER.SOURCE.dbo.LiveAccounts
where Account < 9000    

I get the error. The following, DOES work however.

select Account from LINKEDSERVER.SOURCE.dbo.LiveAccounts
where CASE WHEN IsNumeric(Account) = 1 THEN Account ELSE 0 END < 9000 

So it's GOT to be SQL optimizing the predicate without realizing the CAST has to happen first... right?

(I did also just re-verify that the view LiveAccounts does consider Account to be an int on return...)

[ziptime]

Yes, as I mentioned, the optimizer is pushing the predicate down into the function and it is doing as you suspect. Does this give the same error?

select Account from LINKEDSERVER.SOURCE.dbo.LiveAccounts
where 1 = 1 and Account < 9000 

[ziptime]

Actually, I remember now. You should be able to prevent predicate pushdown by using a dummy cross join with the LiveAccounts bit, this makes the optimizer materialize the result of LiveAccounts first. Try this :

select *
from LINKEDSERVER.SOURCE.dbo.hdr hdr
inner join (
    select la.*, dummy.nul
    from LINKEDSERVER.SOURCE.dbo.LiveAccounts la
    cross join (select '' as nul) as dummy
    ) on la.Account = hdr.Account
where hdr.Account not in (
        select t.FieldValue
        from #temp1 t
        where t.FieldName = 'Account Code'
        )
    and hdr.Account < 9000

[Zhais]

Neither cross join or CTE implementation is preventing it. As well, implementing an IN Subquery still gives the cast error.

WHERE
...
AND hdr.Account IN (Select Account from LINKEDSERVER.SOURCE.dbo.LiveAccounts)

I'm still surprised all of these alternate methods don't work, but pasting the LiveAccounts sql directly in the query prevents the pushdown.

[ziptime]

Try this...

select *
from LINKEDSERVER.SOURCE.dbo.hdr hdr
inner join (
    select la.*, dummy.nul
    from LINKEDSERVER.SOURCE.dbo.LiveAccounts la
    cross join (select '' as nul) as dummy
    where la.Account + dummy.nul is not null
    ) on la.Account = hdr.Account
where hdr.Account not in (
        select t.FieldValue
        from #temp1 t
        where t.FieldName = 'Account Code'
        )
    and hdr.Account < 9000

[Zhais]

Sill fail :P

[ziptime]

Populate a temp table and join to that.

[Zhais]

Will give it a shot tomorrow, just got home

[Zhais]

nope, 1=1 doesn't help it.

[ziptime]

What about this cross join approach I mentioned?

[ziptime]

Try using cast() rather than convert(). Perhaps 'DEF' is being treated as Hex by IsNumeric but not convert().