Is it possible to sometimes call SELECT expressions for rows that do not match the WHERE clause?

Question

I would like to know if it is possible to evaluate expressions that are part of a list of SELECT for rows that do not match the WHERE ?

From the execution order described here , it seems that SELECT is evaluated long after WHERE , however, I ran into a very strange problem with a real query similar to the query below .

To bring you into context, in the example SomeOtherTable has SomeOtherTable column, which always contains numeric values ​​for code 105, but may contain non-numeric values ​​for other codes.

The query operator works:

  SELECT an_id, an_integer FROM SomeTable UNION ALL SELECT an_id, CAST(a_varchar AS int) FROM SomeOtherTable WHERE code = 105 

The following request complains that it cannot pass a_varchar to int :

 SELECT 1 FROM ( SELECT an_id, an_integer FROM SomeTable UNION ALL SELECT an_id, CAST(a_varchar AS int) FROM SomeOtherTable WHERE code = 105 ) i INNER JOIN AnotherOne a ON a.an_id = i.an_id 

And finally, the following query works:

 SELECT 1 FROM ( SELECT an_id, an_integer FROM SomeTable UNION ALL SELECT an_id, CASE code WHEN 105 THEN CAST(a_varchar AS int) ELSE NULL END FROM SomeOtherTable WHERE code = 105 ) i INNER JOIN AnotherOne a ON a.an_id = i.an_id 

Therefore, the only explanation I could find was that with JOIN request will be optimized differently so that CAST(a_varchar AS int) will be executed even if code <> 105 .

Queries are launched in SQL SERVER 2008.

Answer 1

That's right.

The documentation you refer to has a section called Logical Processing Order for the SELECT statement . This is not a physical processing order. It explains how the query itself is interpreted. For example, the alias defined in the select clause cannot be a link in the where clause, since the where clause is processed first.

In fact, SQL Server has the ability to optimize queries by performing various data conversion operations when reading data. This is a good performance advantage because the data is stored in memory locally and operations can simply be done locally. However, a runtime error may fail:

 select cast(a_varchar as int) from table t where a_varchar not like '%[^0-9]%'; 

The filter is applied after an attempt to convert to a real process stream. I think this is a mistake; presumably, people at Microsoft do not think so because they did not bother to fix it.

Two workarounds are available. The first is try_convert() , which performs the conversion and returns NULL for failure, not at runtime. The second is the case :

 select (case when a_varchar not like '%[^0-9]%' then cast(a_varchar as int) end) from table t where a_varchar not like '%[^0-9]%';