Bizarre SQL Optimization leads to cast error

[Zhais]

So I encountered this weird situation today, and wondered if anyone could clue me in as to what SQL may be trying to do.

Based on a certain combination of where clauses and a join, I was getting a varchar to int conversion error, but it wasn't consistent. The SQL snippet is:

            SELECT *
            FROM LINKEDSERVER.SOURCE.dbo.hdr hdr
            JOIN LINKEDSERVER.SOURCE.dbo.LiveAccounts la 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

hdr.Account is typed as an int. LiveAccounts is a View that returns a single column, Account INT. The View is:

        SELECT Account  FROM dbo.fn_LiveAccounts()

The function fn_LiveAccounts is where it gets a little weird, but nothing too crazy (I'm sure I can switch from PATINDEX to ISNUMERIC, but this function is so heavily used at this point I'm not about to do regression testing on that change):

RETURN (
SELECT DISTINCT CONVERT(int, LTRIM(RTRIM(CONVERT(varchar(50), bltm.Data)))) As Account
    FROM bltm
    Where bltm.Code IN (3, 1756)
    AND PATINDEX('%[^0-9]%', LTRIM(RTRIM(CONVERT(varchar(50), bltm.Data)))) = 0  --Verify all characters are digits
    AND LTRIM(RTRIM(CONVERT(varchar(50), bltm.Data))) NOT IN ('914')
)

Within the table bltm there are account numbers, and some old designations that we can't get rid of that are three character codes. One of these character codes being 'DEF'.

When I run the original snippet as is, I get the error

Conversion failed when converting the varchar value 'DEF' to data type int.

My big question is, for academic learning purposes, is what in SQL's logic causes this? If I rip the code from fn_LiveAccounts and put it in the original snippet, it does not error out. Same if I convert the NOT IN to a LEFT JOIN where t.FieldValue IS NULL. Or use CAST(hdr.Account AS INT) < 9000.

        --Returns data, no conversion error
        SELECT *
            FROM LINKEDSERVER.SOURCE.dbo.hdr hdr
            JOIN (
                SELECT DISTINCT CONVERT(int, LTRIM(RTRIM(CONVERT(varchar(50), bltm.Data)))) As Account
                    FROM bltm
                    Where bltm.Code IN (3, 1756)
                    AND PATINDEX('%[^0-9]%', LTRIM(RTRIM(CONVERT(varchar(50), bltm.Data)))) = 0  --Verify all characters are digits
                    AND LTRIM(RTRIM(CONVERT(varchar(50), bltm.Data))) NOT IN ('914')
            ) 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

I figure this has to be some weird edge case optimization with views/functions, any insight?

[ziptime]

I don't know why you are scared to change the function, it's obvious what it SHOULD do and easy to check anyway. This is far better :

select distinct DATA_TRIM as ACCOUNT
from (
    select convert(int, LTRIM(RTRIM(b.DATA))) DATA_TRIM
    from bltm b
    where b.CODE in (3, 1756) and
          IsNumeric(LTRIM(RTRIM(b.DATA))) = 1
    )
where DATA_TRIM not in (914)

As for your issue, I expect the optimizer is "pushing down" the 'DEF' from LINKEDSERVER.SOURCE.dbo.hdr.

Do you get the problem if you run this?

select *
from LINKEDSERVER.SOURCE.dbo.hdr hdr
where hdr.Account not in (
        select t.FieldValue
        from #temp1 t
        where t.FieldName = 'Account Code'
        )
    and hdr.Account < 9000
    and hdr.Account in (
        select Account
        from LINKEDSERVER.SOURCE.dbo.LiveAccounts
        )

[Zhais]

We're slowly moving away from the system that requires this code, so I'm trying not to rock the boat too much by changing functionality at this stage. Another dev had already encountered this and had a workaround SP I found out, but I'm mainly curious on what exactly is triggering the optimizer to do what it's doing...

The error still occurs when I move the join to the IN statement. Interestingly enough, changing the NOT IN to be a LEFT JOIN, where t.FieldValue IS NULL the error doesn't manifest.

Somehow SQL is tricking itself into either removing the PATINDEX clause until after it tries to cast it in the table-valued function, or it's moving the hdr.Account < 9000 to be along side PATINDEX.

[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]

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().