SQL not properly recognizing and honoring the WHERE statement

Question

I have two tables.

DECLARE @definitions TABLE
( fDefId varchar(8)   ,
  dType  varchar(MAX) ,
  fName  varchar(MAX)
)

INSERT @definitions values('8c7eab0e','string','custpartno')
INSERT @definitions values('8c7eab02','int'   ,'itemno'    )

DECLARE @fields TABLE
( rowId varchar(8)   ,
  fkFId varchar(8)   ,
  adj   varchar(MAX)
)

INSERT @fields values('83EDE211','8c7eab0e','89319971151801')
INSERT @fields values('83EDE211','8c7eab02','1'             )

I am trying to find the records in @fields where the value is not an int and the related record in the @definitions table has a dType value of 'int'.

When I try to get this list of results (if any), I tried these statements:

SELECT f.rowId ,
       f.fkFId ,
       f.adj   ,
       d.fName
  FROM @fields      f
  JOIN @definitions d ON  f.fkFId = d.fDefId
                      AND d.dType = 'int'
  WHERE ISNUMERIC( adj        ) <> 1
     OR CAST(      adj AS INT ) <> adj

SELECT *
FROM ( SELECT f.rowId ,
              f.fkFId ,
              f.adj   ,
              d.fName
       FROM @fields      f
       JOIN @definitions d ON  f.fkFId = d.fDefId
                           AND d.dType='int'
     ) a
WHERE ISNUMERIC( adj        ) <> 1
   OR CAST(      adj AS INT ) <> adj

and got this error with both:

The conversion of the varchar value '89319971151801' overflowed an int column.

However, when I first store the values in a table variable like this:

DECLARE @temp TABLE
( rowId    varchar(8),
  fDefId   varchar(8),
  adjusted varchar(MAX)
)

INSERT @temp
SELECT f.rowId ,
       f.fkFId ,
       f.adj
FROM @fields      f
JOIN @definitions d ON  f.fkFId = d.fDefId
                    AND d.dType = 'int'

SELECT * FROM @temp
WHERE ISNUMERIC( adjusted        ) <> 1
   OR CAST(      adjusted AS INT ) <> adjusted

I get the expected results (no records in this example).

If I remove the WHERE clause I get these results:

The row with the large field isn't there, so why would it cause an error after adding the WHERE?

I can also avoid the issue by casting as BIGINT instead of INT, but why would that matter since the JOIN and WHERE clauses remove the value that is not an INT from the beginning?

Answer 1

You do know that the SQL Standard has no requirements mandating a specific order of expression evaluation or short-circuiting of expression evaluation? The query optimizer is free to rearrange pretty much the entire query so long as the semantic meaning (not intent) is maintained.

Your second stab at things, pre-filtering on datetype = 'int' and loading the filtered results works because that forces an order of operation on the query processor.

Your single-query tries fail because the expression

CAST( adj AS INT ) <> adj

Which

• takes a varchar
• converts it to an int
• converts that int back into a varchar, and finally...
• compars that varchar (containing a normalized/canonical string representation of the int) back to the original varchar value.

Fails. It fails because the query processor must evaluate that expression for every candidate row, regardless of whether or not the column datatype contains int or not.

The second single-query attempt, with the derived table in the from clause fails because the optimizer is smart enough to see that the query can be refactored so as to not use a derived table.

The real problem, of course, is that you have a denormalized database design and you're overloading the meaning (and data type) of the column adj, trying to make a single table be multiple things.