L' Eomot Inversé (6/11/2013)
Nice question that shows one of the less obvious pitfalls arising from implicit conversion. Pity the explanation doesn't explain why it works as it does. Of course if you run the code what is going on is obvious from the error messages (which will complain about failure to covert to date, not from), and anyway Hugo has explained it nice and clearly.
I wonder why the language designers decided that type coercion shouldn't be pushed downwards in a situation like this, so that conversion to an intermediate type with too much precedence would not take place. I guess it could be because they reckoned it would make implicit conversion more difficult for many people to understand than it already is.
Tom, if you look at my conversation in this thread with Louis Hillebrand, it appears that type coercion may be pushed downwards in SQL Server 2008R2 under some set of circumstances - the image of his execution plan shows the date value implicitly converted to varchar() before the merge join operator that accomplishes the UNION.
Yes, you are right. That plan shows clearly that the destination type overrides any precedence issues, i.e. the coercion has been pushed down onto the individual rows of the union before precedence between the rows has been considered. To me that seems the logical thing to do, as you probably guessed from my previous post.
I seem to remember it happening in SQLS 2000 too. But in SQLS 2012 it appears not to happen - I haven't done anything like enough checking to be 100% certain so I will stick to "appears to" - and I think this is by design rather than by accident. If it (no push down) sometimes happens in SQLS 2008 I suspect that will be by design rather than by accident, because I think it happened more there than in SQLS 2000 and imagine that was a change by design, not an accident. I haven't gotten around to installing SQL 2008R2 on my new machine yet, currently I can only check using SQLS 2012 unless I dig the old machine out, so I'm not sure what happens there. All in all I find it irritating that there isn't any nice clear easily found documentation that explains why SQLS 2008 and SQLS 2012 are different in this respect, but maybe that's because I don't read enough of the MS SQL team's blogs (and the blogs of ex-members of that team).
Edit: Just read Louis Hillebrand's last post. That confirms pretty clearly that this was something that changed between SQLS 2000 and subsequent releases. I wonder how much code it broke out there in the real world?