![]() Let's say we enter the expression, and we check the first WHEN clause, and the result is 3 we skip that clause and move on. In turn, what happens is that each WHEN clause evaluates and invokes RAND() independently – and in each case it could yield a different value. WHEN CONVERT(SMALLINT, 1+RAND()*3) = 3 THEN 'three'ĮLSE NULL - this is always implicitly there WHEN CONVERT(SMALLINT, 1+RAND()*3) = 2 THEN 'two' WHEN CONVERT(SMALLINT, 1+RAND()*3) = 1 THEN 'one' How does this happen? Well, the entire CASE expression is expanded to a searched expression, as follows: SELECT = CASE Now, put this into a simple CASE expression, and run it a dozen times – eventually you will get a result of NULL: SELECT = CASE CONVERT(SMALLINT, 1+RAND()*3) Consider that this expression yields a SMALLINT between 1 and 3 go ahead and run it many times, and you will always get one of those three values: SELECT CONVERT(SMALLINT, 1+RAND()*3) When this is a variable, or a constant, or a column reference, this is unlikely to be a real problem however, things can change quickly when it's a non-deterministic function. The reason it is important to understand that the expression being evaluated will be evaluated multiple times, is because it can actually be evaluated multiple times. It is important to understand that this will be executed as a searched CASE expression, like this: SELECT CASE I often see people writing a simple CASE expression, like this: SELECT CASE 1 THEN 'foo' ![]() So, when aggregates or non-native services like Full-Text Search are involved, please do not make any assumptions about short circuiting in a CASE expression. I don't have a repro handy, but I do believe him, and I don't think we've unearthed all of the edge cases where this may occur. On that item, Paul White commented that he also observed something similar using the new LAG() function introduced in SQL Server 2012. For example, Connect #780132 : FREETEXT() does not honor order of evaluation in CASE statements (no aggregates involved) shows that, well, CASE evaluation order is not guaranteed to be left-to-right when using certain full-text functions either. This behavior can yield itself in some other, less obvious scenarios, too. I reported the bug in Connect #690017 : CASE / COALESCE won't always evaluate in textual order it was swiftly closed as "By Design." Paul White ( blog | subsequently filed Connect #691535 : Aggregates Don't Follow the Semantics Of CASE, and it was closed as "Fixed." The fix, in this case, was clarification in the Books Online article namely, the snippet I copied above. I was first made aware of this specific scenario in a conversation on a private e-mail distribution list by Itzik Ben-Gan ( who in turn was initially notified by Jaime Lafargue. There are trivial workarounds (such as ELSE (SELECT MIN(1/0)) END), but this comes as a real surprise to many who haven't memorized the above sentences from Books Online. The official documentation once implied that the entire expression will short-circuit, meaning it will evaluate the expression from left-to-right, and stop evaluating when it hits a match: To use CASE within the same query logic, you would have to use a CASE expression for each output column: SELECTįROM dbo.some_table CASE will not always short circuit This type of control-of-flow logic may be possible with CASE statements in other languages (like VBScript), but not in Transact-SQL's CASE expression. When people think of CASE as a statement, it leads to experiments in code shortening like this: SELECT CASE I find this mildly annoying (like row/record and column/field) and, while it's mostly semantics, but there is an important distinction between an expression and a statement: an expression returns a result. Likely not important to most people, and perhaps this is just my pedantic side, but a lot of people call it a CASE statement – including Microsoft, whose documentation uses statement and expression interchangeably at times. The return expression is always a single value, and the output data type is determined by data type precedence.Īs I said, the CASE expression is often misunderstood here are some examples: CASE is an expression, not a statement
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |