TheSQLGuru (11/23/2014)
TomThomson (9/25/2014)
sfrostx (9/25/2014)
Can anyone help me understand why the second and third selects in the following generate an error?I don't understand it at all. An interesting twist is that whether the failing select produces a row before producing its error message depends on the order in which the rows were inserted into @tbl. The conversion fails for the string beginning 7 and also for the string beginning C when the value it is to be produced is part of an IN list, but the same conversion works in that context for the string beginning 3. However, I can't see any way of making the conversion fail for either value except when operating inside an IN list. There's nothing special about the GUIDs, all three are version 4 GUIDs (assuming that MS still uses standard quartet order for the text representation of its GUIDs, which it certainly used to do). The two that don't work are MS GUID version 4 standard. The one that does work is MS GUID version 4 for NCS backward compatability, and I can't see MS treating that as a particularly privileged sort of GUID. So to me it looks like a bug, but maybe someone who knows more about how MS in general and SQL Server in particular treat GUIDs will come up with a non-bug explanation. If you don't get a better response from someone, maybe you should raise a connect item for it.
I haven't played with this on any SQL Server version other than 2014 - is that the version you hit the problem on?
edit: I see Paul has a different, probably more useful, answer. As I read his answer, this definitely is a bug. But MS would deny that because the optimiser is a sacred cow. It's certainly interesting that TRY_CONVERT and TRY_CAST work when neither CONVERT nor CAST does, so at least there's a workaround.
Aren't the operations you are arguing about or stating are a bug COMMUTATIVE in nature? If so, it is illogical (in every sense of the word) to state that they cannot be reordered as the optimizer sees fit.
One of teh two fuctions in question is CAST, so you seem to be suggesting that (for example) CAST(F(X)) is identical in all respects to F(CAST(X)) where you can see what F is by looking at the code which demonstartes the bizarre behavious. That's what it means to say that the functions CAST and F are commuttive. What's happening here is that the optimizer is presented with CAST(F(X)) for some expresion X and function F, and decides to call CAST(X) before it thinks about applying F. But F and CAsT are not communtative, so the optimizer should not do that. MS have admitted that in response to Erland's CONNECT item, but have done nothing about it (years after they said they intended to fix it).
Tom