CODEWITHSUNDEEP
sql-server
Dan
Dan

Reputation: 573

SQL Server inline view bug?

I'm using SQL Server 2012 and having what I think is a very strange issue with inline views.

To begin with, I have this query:

select 
    parcelnumber, 
    sectionname,
    value.Value CalculatedValue,
    overriddenvalue.Value OverriddenValue,
    homk.Value CalculatedHomeownersValue,
    cv.CharacteristicValue,
    pdt.PropertyDataTypeID
from landsections ls
join lands l 
  on ls.landid = ls.landid
join parcels p
  on l.ParcelID = p.ParcelID
join CharacteristicValueGroups cvg
  on cvg.ParcelObjectID = ls.ParcelObjectID
join CharacteristicValues cv 
  on cvg.CharacteristicValueGroupID = cv.CharacteristicValueGroupID
join Characteristics c
  on cv.CharacteristicID = c.CharacteristicID
join CharacteristicDetails cd
  on c.CharacteristicDetailsID = cd.CharacteristicDetailsID
 and cd.name = '09 Composite Adjustment'
join PropertyDataTypes pdt
  on cv.PropertyDataTypeID = pdt.PropertyDataTypeID
 and pdt.Label = 'Type'
join parcelobjectvalues value
  on value.ParcelObjectID = ls.ParcelObjectID
 and value.valuetype = 'CalculatedAdjustedValue'
left join parcelobjectvalues overriddenvalue
  on overriddenvalue.ParcelObjectID = ls.ParcelObjectID
 and overriddenvalue.valuetype = 'OverriddenValue'
join parcelobjectvalues homk
  on homk.ParcelObjectID = ls.ParcelObjectID
 and homk.valuetype = 'CalculatedHomeownersValue'
where year(appraisaldate) = 2016
  and CharacteristicValue like '%F%'

Note the last item in the where clause. This query return 0 results, which should prove that there are no rows that have a CharacteristicValue column containing an F.

Now lets run this query:

select *
from
(
    select 
        *,
        case 
            when CharacteristicValue like '%A' then convert(int, substring(characteristicvalue, 1, len(characteristicvalue) - 1))
            when CharacteristicValue like '%K' then convert(int, substring(characteristicvalue, 1, len(characteristicvalue) - 1))
            when isnumeric(CharacteristicValue) = 1 then convert(int, characteristicvalue)
            else 0
        end Quantity
    from
    (
        select 
            parcelnumber, 
            sectionname,
            value.Value CalculatedValue,
            overriddenvalue.Value OverriddenValue,
            homk.Value CalculatedHomeownersValue,
            cv.CharacteristicValue,
            pdt.PropertyDataTypeID
        from
            landsections ls
            join lands l on ls.landid = ls.landid
            join parcels p on l.ParcelID = p.ParcelID
            join CharacteristicValueGroups cvg on cvg.ParcelObjectID = ls.ParcelObjectID
            join CharacteristicValues cv on cvg.CharacteristicValueGroupID = cv.CharacteristicValueGroupID
            join Characteristics c on cv.CharacteristicID = c.CharacteristicID
            join CharacteristicDetails cd on c.CharacteristicDetailsID = cd.CharacteristicDetailsID and cd.name = '09 Composite Adjustment'
            join PropertyDataTypes pdt on cv.PropertyDataTypeID = pdt.PropertyDataTypeID and pdt.Label = 'Type'
            join parcelobjectvalues value on value.ParcelObjectID = ls.ParcelObjectID and value.valuetype = 'CalculatedAdjustedValue'
            left join parcelobjectvalues overriddenvalue on overriddenvalue.ParcelObjectID = ls.ParcelObjectID and overriddenvalue.valuetype = 'OverriddenValue'
            join parcelobjectvalues homk on homk.ParcelObjectID = ls.ParcelObjectID and homk.valuetype = 'CalculatedHomeownersValue'
        where
            year(appraisaldate) = 2016
    ) Tbl
) Tbl2
where
    CharacteristicValue like '%A' and Quantity > 10

Notice that I've removed the %F% check. Since the first query proved there were no rows with an F in the CharacteristicValue column, I would assume the outer query could rely on this.

However, I get this:

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

I think the only way this could happen is ignoring the pdt.Label = 'Type', but for the life of me I can't understand why.

Upvotes: 3

Views: 96

Answers (2)

usr
usr

Reputation: 171178

Your view filters on:

where year(appraisaldate) = 2016 and CharacteristicValue like '%F%'

Which means that there can be rows that satisfy just CharacteristicValue like '%F%'.

Execution order is not specified in T-SQL. This is a problem when trying to guard calculations that might throw with some check.

Usually, case ... when is a reliable guard. I see no reason why it wouldn't in your case. So you could do when CharacteristicValue like '%A' and CharacteristicValue not like '%F%'.

Or, you can use TRY_CONVERT which is always safe.

where is not a reliable guard. In the other answer you posted that this works but that is just coincidence. With a different query plan this might fail. Plans change for many reasons.

Upvotes: 2

Dan
Dan

Reputation: 573

The answer appears to be that the inner query can't be executed prior to the outer query due to correlation. Adding filters in the outer query solved the issue.

Upvotes: -1

Related Questions