April 5, 2010 at 9:30 pm
Comments posted to this topic are about the item Column length
April 6, 2010 at 12:19 am
Dear ken,
Can you explain this behavior specifically w.r.t Operator Precedence? and is this behavior is default for all versions of SQL?
Thank you!
April 6, 2010 at 12:32 am
Nice question and useful information.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2010 at 1:36 am
Nice Question.
Thanks
April 6, 2010 at 4:56 am
Interesting variation on the normal ISNULL-related QotD.
I do wish that the table definition had included an explicit NULL on the column definition though.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 7:05 am
Nice rework of a question a week or two ago. It is great to reinforce the information contained in these questions.
I use these questions not so much as a test of my existing knowledge, but more as a reason to learn and dig into the questions and answers so I come away with more knowledge.
Thanks,
April 6, 2010 at 8:15 am
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
This explanation feels a little light. For completeness, it should include the behavior of the LEFT() function as well.
Specifically, the truncation by ISNULL() is based on the datatype length not the data length (it would have to be, as NULL data would have 0 length.) This means that the LEFT() function is returning a shorter datatype than its source column (in this case a varchar(5)).
I see no reason why this shouldn't happen this way, I just never thought about the datalength returned by substring functions before.
April 8, 2010 at 7:34 pm
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
This explanation is wrong. The IsNull function converts to the type of the check_expression, which may involve truncation. It's nonsense to talk about truncating to the length of an expression whose value is null because that length is null. The length of left(null,5) is not 5, it is null.
What is interesting here is that left(X,5) delivers an expression of type varchar(5), not an expression of the same type as X. I haven't seen that documented anywhere, and I'm flabbergasted by it. Needless to say I got the wrong answer, and I've learnt this crazy behaviour of Left from it. I guess that makes it a good question - anything that makes me learn is good from my point of view.
edit: spelling errors
Tom
April 8, 2010 at 11:13 pm
Tom.Thomson (4/8/2010)
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
This explanation is wrong. The IsNull function converts to the type of the check_expression, which may involve truncation. It's nonsense to talk about truncating to the length of an expression whose value is null because that length is null. The length of left(null,5) is not 5, it is null.
What is interesting here is that left(X,5) delivers an expression of type varchar(5), not an expression of the same type as X. I haven't seen that documented anywhere, and I'm flabbergasted by it. Needless to say I got the wrong answer, and I've learnt this crazy behaviour of Left from it. I guess that makes it a good question - anything that makes me learn is good from my point of view.
edit: spelling errors
Yup, nice and more convincing.
April 8, 2010 at 11:39 pm
Tom.Thomson (4/8/2010)
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
This explanation is wrong. The IsNull function converts to the type of the check_expression, which may involve truncation. It's nonsense to talk about truncating to the length of an expression whose value is null because that length is null. The length of left(null,5) is not 5, it is null.
I see nothing wrong with the original explanation. It correctly conveys the reason that the result is 'IsUnk' and not 'IsUnknown'.
It has nothing to do with truncating NULLs. The check_expression here is the result of applying LEFT(..., 5) to a VARCHAR(50) NULLable column. The type of check_expression is clearly VARCHAR(5) NULLable. The replacement_value is implicitly converted to that type, resulting in truncation - exactly what the explanation conveys.
From the BOL reference included with the explanation: "The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different."
The quick textual explanation is fine, especially so since it includes a BOL reference for further details. You are being overly picky here; this is a QotD, not an academic paper for peer review 😛
What is interesting here is that left(X,5) delivers an expression of type varchar(5), not an expression of the same type as X. I haven't seen that documented anywhere, and I'm flabbergasted by it. Needless to say I got the wrong answer, and I've learnt this crazy behaviour of Left from it. I guess that makes it a good question - anything that makes me learn is good from my point of view.
LEFT is documented as returning (n)varchar...what is it exactly that baffles you about an expression with a defined maximum length of 5 being returned as (n)varchar(5)?
From Expressions (Transact-SQL): When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 9, 2010 at 3:49 am
Paul White NZ (4/8/2010)
I see nothing wrong with the original explanation. It correctly conveys the reason that the result is 'IsUnk' and not 'IsUnknown'.
I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.
The check_expression here is the result of applying LEFT(..., 5) to a VARCHAR(50) NULLable column. The type of check_expression is clearly VARCHAR(5) NULLable. The replacement_value is implicitly converted to that type, resulting in truncation - exactly what the explanation conveys.
Yes that's what is happening (except that I would take issue with the word "clearly", see below) . The explanation completely fails to convey that, it says something completely different is happening. It does give a pointer to part of the correct explanation.
[
What is interesting here is that left(X,5) delivers an expression of type varchar(5), not an expression of the same type as X. I haven't seen that documented anywhere, and I'm flabbergasted by it. Needless to say I got the wrong answer, and I've learnt this crazy behaviour of Left from it. I guess that makes it a good question - anything that makes me learn is good from my point of view.
LEFT is documented as returning (n)varchar...what is it exactly that baffles you about an expression with a defined maximum length of 5 being returned as (n)varchar(5)?
From Expressions (Transact-SQL): When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.[/quote]
On that logic I could say what's wrong with * (or + or -) returning bigint when it needs to, instead of an error. But in fact
declare @x int = 214735276, @y int = 1073741827
select @x*@y
doesn't return 230570247573589252 which on your logic it ought to return: what would baffle you about a multiplication which returns a number between 2**62 and 2**64 returning a bigint, as it's obvious from the parameters that it won't fit a smaller type? But in that case the design decision was to return an error, and I guess you agree with it despite your interpretation of that "the operator determines the type" statement. In the case of left the decision was to make an arbitrary type change depending on the parameters - there's no consistency there, and cetrainly no clarity.
It would be nice if this case where an arbitrary type change is made were documented, but as far as I can tell it isn't.
None of that of course detracts from it being a good question with a correct answer. But I still think the explanation is wrong - it would have been improved by a change to say simply "see <BOL reference>" without the incorrect statement that actually precedes that.
Tom
April 9, 2010 at 4:42 am
Tom.Thomson (4/9/2010)
I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.
The type of the check expression is VARCHAR(5) NULLable. It has a maximum length of 5. What is nonsensical or even slightly hard to understand about that?
The explanation completely fails to convey that, it says something completely different is happening. It does give a pointer to part of the correct explanation.
No. It conveys the crucial point without being verbose.
On that logic I could say what's wrong with * (or + or -) returning bigint when it needs to, instead of an error.
If you look up the multiply operator in Books Online, you will see that it "Returns the data type of the argument with the higher precedence". Decided by the operator, as I said before.
In the case of left the decision was to make an arbitrary type change depending on the parameters - there's no consistency there, and cetrainly no clarity.
It's clear and consistent - it is decided by the operator. Try it with other functions, computed columns, SELECT...INTO, parameterization, and so on and so on. All work the same, consistent and clear.
It would be nice if this case where an arbitrary type change is made were documented, but as far as I can tell it isn't.
I can only suggest you keep looking - most behaviour is documented in Books Online in detail.
...I still think the explanation is wrong - it would have been improved by a change to say simply "see <BOL reference>" without the incorrect statement that actually precedes that.
The vast majority of people are looking for a 'bite-sized' explanation - not just a lazy link to BOL. The lack other people in this thread whining about any perceived inadequacies in the 'bite-sized' answer seems to indicate that your view is in the minority.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 9, 2010 at 10:26 am
Paul White NZ (4/9/2010)
Tom.Thomson (4/9/2010)
I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.The type of the check expression is VARCHAR(5) NULLable. It has a maximum length of 5. What is nonsensical or even slightly hard to understand about that?
There is nothing nonsensical or hard to understand about what you are saying there. What is nonsense is your previous statement that the explanation says this. It doesn't. The explanation says that the result is truncated to the length of the check expression (implying that the length of NULL is 5 here). That is NOT remotely the same thing. Why do you want to claim it is?
I'll skip most of the rest of your comment, because anything I said would be much the same as before, so pointless repetition: a waste of space; but on a couple of things it may be worthwile to say something.
It would be nice if this case where an arbitrary type change is made were documented, but as far as I can tell it isn't.
I can only suggest you keep looking - most behaviour is documented in Books Online in detail.
The best you've offered is a pointer to the general statements that functions determine the results of their results. That doesn't actually tell me a single thing about what left determines as the type of its result. I'm pretty sure that this is NOT documented in BoL - sure enough that I'll waste no more time looking for it. It's quite clear from its behaviour on a few test cases that it makes no use any type information available for its first argument other than to place an upper bound on the length component of the type of a NULL and to determine whether the character component is char or nchar, so what do I need documentation for? Oh, it might change in the next release if it's not documented - well, hard luck me).
...I still think the explanation is wrong - it would have been improved by a change to say simply "see <BOL reference>" without the incorrect statement that actually precedes that.
The vast majority of people are looking for a 'bite-sized' explanation - not just a lazy link to BOL. The lack other people in this thread whining about any perceived inadequacies in the 'bite-sized' answer seems to indicate that your view is in the minority.
[/quote]
Clever piece of selective quoting there, sir, a nice "..." conceals the words that make nonsense of your remark about whining about the answer by stating clearly and unambiguously that the answer is correct. You know perfectly well that this discussion has nothing to do with the answer, but concerns the explanation. Why pretend it has?
As to number of people, we first have abrar asking for more explanation (which is part of why I commented - and his reply to my comment suggests he found it useful) and sknox saying he thinks the explanation is inadequate and making much the same points as me in a looser style. So that's 3 people on one side. On the other side we have one liners from Jason and Ramchandra, a two-liner from yoursel (quibbling that the absence of a NOT NULL constraint was not explicitly declared - now that really is whining about nothing, isn't it) and a comment from dbowlin. That's 4 people. We don't have the usual collection of whiners claiming it's not fair they didn't get their point, because there is nothing anyone could hang such a claim on - as I said before, the question is clear and unambiguous and the answer is correct. So it's 3:4 - not a startling majority either way, is it? Or is that a grey area that you want to claim is white?
My comments are fourfold:
(i) I chose the wrong answer (not because there's anything wrong with the question but because I didn't know that the type of the result of left(cast(null as varchar(50)),5) is varchar(5) - I should have known, but I didn't).
(ii) It's a good clear question with a good correct answer.
(iii) The explanation is incorrect (only the type of the check argument affects truncation of the result, not its length) and incomplete (no mention of the type of the result of the left function).
(iv) I find the behaviour of the left function surprising and consider it not consistent with the behaviours of arithmetic functions, which have the advantage of being well documented.
Do you actually disagree with any of these, or are you just having fun?
Tom
April 9, 2010 at 9:42 pm
Tom.Thomson (4/9/2010)
What is nonsense is your previous statement that the explanation says this. It doesn't. The explanation says that the result is truncated to the length of the check expression (implying that the length of NULL is 5 here). That is NOT remotely the same thing.
Good grief, man! :doze:
The explanation says "the IsNull() function will truncate the length of replacement_value to that of check_expression." The check_expression has a type of VARCHAR(5). The replacement_value has an implied type of VARCHAR(9). Many people taking the question would expect the result to be 'IsUnknown', and would be surprised that it was 'IsUnk'. This represents by far the majority of wrong answers - and, I would wager, was the main point of the question.
The explanation succinctly expresses the reason for the unexpected answer - the VARCHAR(9) is truncated to VARCHAR(5) by the ISNULL function. It really is as simple as that.
The fact that you, and the other 19% that selected the wrong answer, did not know this, simply means you have learnt something, which is the point of the QotD. Your point about 'the length of NULL' is irrelevant and shows your lack of understanding - not that of the questioner.
The best you've offered is a pointer to the general statements that functions determine the results of their results. That doesn't actually tell me a single thing about what left determines as the type of its result. I'm pretty sure that this is NOT documented in BoL - sure enough that I'll waste no more time looking for it.
I am not here to locate stuff in Books Online for you - if you want to expand your understanding, you will have to put some effort in. I would encourage you to look into how SQL Server determines the type of an expression in general - for computed columns, SELECT...INTO statements, parameterization, and so on. It is logical, consistent, and documented.
The explanation is incorrect (only the type of the check argument affects truncation of the result, not its length) and incomplete (no mention of the type of the result of the left function).
Are you saying the explanation is incorrect (as in all your previous statements) or that it is just incomplete now? You seem to be moving from absolute statements like 'nonsense', 'wrong' and 'incorrect' to something closer to 'well, technically, it might be more accurate to say...' :laugh:
To avoid any danger of coming off as a tedious, overly-academic, pedant - and there is a risk - I would encourage you to accept the explanation for what it is: a simple statement. A QotD explanation is not required to explore ever possible nuance. In any case, the type of check_expression defines its maximum length.
I find the behaviour of the left function surprising and consider it not consistent with the behaviours of arithmetic functions, which have the advantage of being well documented.
I believe I have answered this already: The operator determines the type of the result, in all cases.
Your refusal to check the documentation disqualifies you from making absolute statements about what is, and is not, documented. You may find it surprising, you may consider it inconsistent, you may think it's wrong, nonsense or whatever. That doesn't make it so.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 9, 2010 at 11:01 pm
Paul White NZ (4/9/2010)
Tom.Thomson (4/9/2010)
What is nonsense is your previous statement that the explanation says this. It doesn't. The explanation says that the result is truncated to the length of the check expression (implying that the length of NULL is 5 here). That is NOT remotely the same thing.Good grief, man! :doze:
That exactly matches my reaction to your comments! :doze:
The explanation says "the IsNull() function will truncate the length of replacement_value to that of check_expression." The check_expression has a type of VARCHAR(5). The replacement_value has an implied type of VARCHAR(9). Many people taking the question would expect the result to be 'IsUnknown', and would be surprised that it was 'IsUnk'. This represents by far the majority of wrong answers - and, I would wager, was the main point of the question.
The explanation succinctly expresses the reason for the unexpected answer - the VARCHAR(9) is truncated to VARCHAR(5) by the ISNULL function. It really is as simple as that.
The fact that you, and the other 19% that selected the wrong answer, did not know this, simply means you have learnt something, which is the point of the QotD. Your point about 'the length of NULL' is irrelevant and shows your lack of understanding - not that of the questioner.
I wish you would pay some attention to what I say instead of to what you want to pretend I said. I think my comments make it absolutely clear that I know perfectly well that IsNull truncates to the length embedded in the type of the check argument when the check argument is one of the character or binary types. Your claim that I don't understand that is just plain outrageous. The reason (as you well know, since it was there plainly and clearly in my original post, so I can only assume you are being deliberately inaccurate on this) that I got the wrong answer is that I thought the left function returned a value with the same type as its first argument.
The best you've offered is a pointer to the general statements that functions determine the results of their results. That doesn't actually tell me a single thing about what left determines as the type of its result. I'm pretty sure that this is NOT documented in BoL - sure enough that I'll waste no more time looking for it.
I am not here to locate stuff in Books Online for you - if you want to expand your understanding, you will have to put some effort in. I would encourage you to look into how SQL Server determines the type of an expression in general - for computed columns, SELECT...INTO statements, parameterization, and so on. It is logical, consistent, and documented.
I don't want you to locate that stuff for me - you'd have a job anyway, as it isn't there (at least google can't find it, nor bing, nor MSDNs own search).
The explanation is incorrect (only the type of the check argument affects truncation of the result, not its length) and incomplete (no mention of the type of the result of the left function).
Are you saying the explanation is incorrect (as in all your previous statements) or that it is just incomplete now? You seem to be moving from absolute statements like 'nonsense', 'wrong' and 'incorrect' to something closer to 'well, technically, it might be more accurate to say...' :laugh:
I haven't changed what I'm saying. The explanation is incomplete. What there is of it is also incorrect, because it talks of LENGTH (meaningless since it has to apply to NULL, whose length is also NULL) instead of TYPE. How is that different from "incorrect and incomplete"? Are you suggesting that the length of the result is NULL (the LENGTH of the first argument) rather than 5 (the length embedded in the TYPEof the first argument)?
To avoid any danger of coming off as a tedious, overly-academic, pedant - and there is a risk - I would encourage you to accept the explanation for what it is: a simple statement. A QotD explanation is not required to explore ever possible nuance. In any case, the type of check_expression defines its maximum length.
Exactly. It is the TYPE that is at issue. It can hardly be the LENGTH of an expression whose value is NULL, can it? It really irritates me that you keep on insisting that "LENGTH" is correct and that I'm wrong or pedantic (that does seem to be a bit of a retraction from wrong though, doesn't it) to say that it's the TYPE that matters, since you clearly do understand full well that it's the TYPE (and the length that is embedded in that TYPE) not the LENGTH of the argument that counts.
I find the behaviour of the left function surprising and consider it not consistent with the behaviours of arithmetic functions, which have the advantage of being well documented.
I believe I have answered this already: The operator determines the type of the result, in all cases.
As I have pointed out before, that is not an answer. It is a tautology that a function determines the type of its result. That doesn't tell anyone, for any particular function, what that type is for given arguments - that needs to be specified.
Your refusal to check the documentation disqualifies you from making absolute statements about what is, and is not, documented. You may find it surprising, you may consider it inconsistent, you may think it's wrong, nonsense or whatever. That doesn't make it so.
And you may believe it is documented: that does not make it so either. I've done a pretty thorough search of the documentation, using tools that I would expext to find the definition of how left determines the result TYPE ( as opposed to the result LENGTH for non-null cases) if such a definition were present - and it has turned up nothing, so I have good grounds to believe it is not documented. That's not a refusal to check the documentation - it's the result of a very thorough check - more thorough than you've undertaken, I suspect, since you are unable to provide any reference that supports your claim that the type of the LEFT functions result is documented. You continue to assert that the definition is there, but refuse to give a reference. Well, why should I believe you when you present no evidence?
Tom
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply