I know, I know
I swore I’d never talk about variables again. Then I ran into some funny business. Maybe it was quite as funny as the Temp Table Ghosts, but hey.
What’s as funny as temp tables?
It’s in your heaaaaaaaad
If I run this, what do you think will come back?
|
1 2 3 4 5 6 7 |
IF (2 + 2 = 5) BEGIN DECLARE @i TINYINT; END; SELECT @i = 1; SELECT @i AS [Wil I Nil I?]; GO |
Will the variable assignment throw an error? Will the select throw an error?
Will it return 1?
How about this?
|
1 2 3 4 5 6 |
IF (2 + 2 = 5) BEGIN DECLARE @i TINYINT = 9223372036854775807; END; SELECT @i AS [Wil I Nil I?]; GO |
Will the variable assignment fail, because there’s no way the BIGINT max can possibly fit into a TINYINT?
Will the select fail because of an arithmetic overflow?
Does 2+2 really equal 5?
And what about this?
|
1 2 3 4 5 6 |
IF (2 + 2 = 5) BEGIN DECLARE @i TINYINT = 1 / 0; END; SELECT @i AS [Wil I Nil I?]; GO |
Will this divide by zero? Will it throw an error?
Chatterbox
When I asked this stuff in the company chat, Brent chimed in with some queries that he thinks are funny, too.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
IF EXISTS ( SELECT 1 / 0 FROM sys.databases ) SELECT 'I can totally divide by zero'; GO IF EXISTS ( SELECT SUM(1 / 0) FROM sys.databases ) SELECT 'You''re not my real dad'; GO IF EXISTS ( SELECT COUNT(1 / 0) FROM sys.databases ) SELECT 'I know you are but what am I'; GO IF EXISTS ( SELECT AVG(1 / 0) FROM sys.databases ) SELECT 'I know you are but what am I'; GO |
It’s all very freaky. What’s going on back there?
And furthermore
What’s going on here?
Thanks for reading!
13 Comments. Leave new
What about adding a SELECT at the end?
IF (2 + 2 = 5)
BEGIN
DECLARE @i TINYINT;
END;
SELECT @i = 1;
SELECT @i AS [Wil I Nil I?];
SELECT TOP (1) @i = O.object_id
FROM sys.objects AS O;
GO
Check the actual execution plan. Does it get executed? All of it?
Taking this to the logical (at least to my weird mind) next step, you get an error if you try to DECLARE the same variable again elsewhere in the same batch, which makes sense with what you’ve demonstrated.
I spot-checked a few other T-SQL commands and none of them demonstrated the same behavior as DECLARE does here- not even DECLARE CURSOR. Strange! I’m guessing they made DECLARE work this way to keep people from writing scripts where one variable with the same name could be initialized as a different type depending on flow control, which would probably lead to some incredibly ugly T-SQL (even uglier than some of the stuff out there now).
Honestly, sometimes T-SQL seems like something that was designed and coded by a team that wasn’t on speaking terms. I can hear the project meeting, now… “Bob, you parse T-SQL variable declarations. Kim, you parse conditional logic. Sam, you handle error trapping. There are no additional known requirements. No time to talk. Anybody who takes time to talk about what they are doing is automatically fired. I want your code Thursday.”
Since the IF block never executes then the value of @i is not allocated and no value is set until the SELECT @i statement. However, since @i is part of the same batch the declaration is recognized. SQL is a declarative language (c#, c++, java are object oriented languages and have different rules of compilation), and as MSDN states:
“The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.” This is why it is normally a good practice to do all declaration of variables prior to execution of IF Blocks or Loops within a single batch or stored procedure.
So pretty much the variables are hoisted to the top of the function scope, but any inline sets are run inline (similar to JavaScript)?
Does it ever matter what you ‘SELECT’ in an EXISTS sub-query, or is it always ignored? I’ve sometimes used the literal 1, *, or a single column.
It is always ignored. It just tests to see if anything exists. It never evaluates the SELECT clause.
Yeah, the divide-by-zero trick is a classic response to an age-old debate regarding EXISTS. Early in anybody’s SQL journey, a padawan shall get the “SELECT * FROM QUALIFIED.TABLE is evil” mantra down pat. That’s expected.
Occasionally you’ll spot somebody waging a holy war against SELECT *. I’ve actually heard a proposal for a pre-commit hook to reject any instances of such via regex. And for various reasons of basic human fault the One True Answer for SQL, “it depends” is sometimes overlooked or forgotten in the fervour. And so the seed is planted that one should not SELECT * in an EXISTS subquery lest a cursed index be chosen, row lookups, table scans, rivers running backwards, code readability and logical inference. SELECT ColumnName shall surely be the righteous path. Or perhaps SELECT 1? Nay! SELECT NULL!
In SQL Server, there should be no difference whatsoever; EXISTS is an operator that returns a Boolean value indicating whether rows would be returned by the subquery or not. It’s backed up by Microsoft and is in line with ANSI-92, but it has led to some cracking Stack questions with some interesting variations on 1/0 as the proving expression. There was even a dramatic subplot for a bit when Conor Cunningham from the Query Processor team at MS personally and gracefully eschewed SELECT * because of an infinitesimal cost incurred during plan compilation to retrieve column metadata, but it turned out that those retrievals happen regardless of the expression.
It’s more than a little similar to the old COUNT(*) vs COUNT(1) debates, obviously. You may enjoy reading these two questions and their answers (and the various clicky clickies if you really don’t have anything better to do); I really like reading through conversations between like minds who simply will not leave a thing alone until they understand HOW it works and WHY it works and IF they can break it and WHETHER there’s an esoteric premature optimisation that they can slip into their estate somewhere that might shave off a picosecond or two in some unforeseen future time of need. 🙂
https://stackoverflow.com/questions/6137433/where-does-the-practice-exists-select-1-from-come-from
https://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists/6140367
For reference, I actually make sure to always use SELECT * in my code as a sort of well-intentioned trap so that I can open discussions with people.
And I should definitely qualify that by saying “in EXISTS subqueries in my code”. I definitely don’t want to talk to people *that* much.
Could it be something to do with how the execution plan is generated, at least in relation to the divide by zero queries?
Here’s the PasteThePlan link: https://www.brentozar.com/pastetheplan/?id=BJSjDZ4TZ
When you look at the execution plans, SQL estimates that there will be one row returned, and looking at the seek/scan operations, they all return 1 actual row. My theory is that even if the object simply exists and contains one or more rows, a flag is set during parsing that the EXISTS clause is valid and true because Actual Number of Rows COULD BE > 0, and that’s why it appears as though 1/0 is valid.
I tested this by creating an temp table and using that in the EXISTS portion of the first statement, and “I can totally divide by zero” wasn’t returned.
I tested this by creating an EMPTY temp table…
Here is something interesting as well. If we remove the IF statement and add a TRY CATCH we can see an error is raised, but the variable is still created.
BEGIN TRY
DECLARE @i TINYINT = ‘ABC’;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNum
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
SELECT @i AS [Wil I Nil I?]
I think Curt may be on to something in his comment!
So I looked at this as well the declaration always happens , the system is clearly scanning for declaration of variables regardless of the where these are placed as long as the declaration takes place before the use of the variable. Parsing from the top of the script down if it finds a @variable declaration after the use of that variable then result is an error.
However regardless of conditional code that seems to say only declare this variable is the logic states it should happen under a given condition then it will create the variable regardless. The assignment of the data to that variable is only happening under the code logic.
IF (2 + 2 = 5)
BEGIN
/* the dclare will happen regardless but the placement of the value will only happen if the condition is true.*/
DECLARE @i TINYINT = 1 / 0;
END;
SELECT @i AS [Wil I Nil I?];
GO
with count() it seems it is possibile to write almost anything
SELECT COUNT(1/0+’A’+ SQRT(29)/’B’+ 1/0) FROM sys.databases
works
😛