sqlinner-joinwhere-clauseoptimizer-hints

Is a type conversion in a inner join safe if reliant on data/row filtering?

If a join condition does data conversion that's only valid on some of the rows, but your where condition filters the rows to the "valid" rows is that a "safe" join condition?

I'm of the opinion it's not but I'm by no means and expert and am just trying to understand. AFAIK the optimizer can pretty much do whatever it wants as long as at the end of the day all the joins happened and all the conditions eventually get run.

I've tried googling this but haven't seen anything concrete

SELECT *
  FROM (SELECT 1 AS ID) a
       JOIN (SELECT '1' AS ID,
                    'Y' AS FILTER
             UNION
             SELECT 'NOT_AN_INT' AS ID,
                    'N'          AS FILTER) b
         ON a.ID = CONVERT(INT, b.ID)
 WHERE b.FILTER = 'Y' 


SELECT *
  FROM (SELECT 1 AS ID) a
       JOIN (SELECT '1' AS ID,
                    'Y' AS FILTER
             UNION
             SELECT 'NOT_AN_INT' AS ID,
                    'N'          AS FILTER) b
         ON a.ID = CONVERT(INT, b.ID)
            AND b.FILTER = 'Y'

As I understand it the join conditions should be commutative on an inner join and both of those queries should be equivalent to each other. However the second one blows up due to type coercion problems. Does that prove that this is bad practice and you should do the safe conversion of a's ID to a varchar instead of b's ID to an int? Or is there some ansi spec guaranteeing this is ok?

Solution

  • No. SQL does not guarantee that the WHERE clause is executed "before" other clauses. Filtering may occur before other operations. Filtering may not.

    This applies to CTEs and subqueries as well. The SQL optimizer can re-arrange operations. SQL is a descriptive language, not a procedural language. A query describes the result set, not how it is produced.

    Your code seems to be SQL Server code. If so, just use TRY_CONVERT() instead of CONVERT(). If the conversion fails, the result is NULL, which fails most WHERE-clause comparisons.

    Personally, I consider the behavior to be a bug -- type conversion errors on rows that are filtered out. I do not know if the SQL standard has specifications on this subject.