April 10, 2010 at 1:23 am
Tom.Thomson (4/9/2010)
I wish you would pay some attention..blah...blah..blah..
No. You failed to answer correctly because you made an incorrect assumption, based on incomplete knowledge. Attempting to wriggle and back-track to somehow blame the explanation is very poor form.
Your rambling and border-line incoherent response above completely fails to address the text you quoted: The explanation perfectly adequately explains why IsUnknown becomes IsUnk. Your whole difficulty arises from an incorrect inference to the meaning of the word 'length' in that explanation. Your view relies on length being applied to the value of numdesc inside the check_expression, as opposed to the maximum length of the type returned by LEFT. No doubt you would prefer the following:
"the IsNull() function will truncate the length of replacement_value to the typed length of check_expression."
And that is precisely the distinction that I regard as pedantic, overly-academic, picky, and ultimately tedious. This is a Question of the Day, not a paper - and your lack of flexibility does you no credit.
I haven't changed what I'm saying. The explanation is incomplete.
You said it was wrong. Now you say it is incomplete. Back-tracking and wriggling.
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)?
No - whatever gives you that idea? That was your assertion in your first post: "The length of left(null,5) is not 5, it is null." - though it's not clear whether you mean the length of NULL is NULL (a tautology) or that the enclosing type would have a NULL length (which is wrong).
...that does seem to be a bit of a retraction from wrong though, doesn't it
Yes. Makes it hard to keep track of what your current position is.
...I have good grounds to believe it is not documented...more thorough than you've undertaken, I suspect
It doesn't exist because you can't find it? :unsure:
And you are claiming to be psychic now as well? I have not said that it definitely is documented - just that most behaviour is. Read back, and try to respond to what I actually said, rather than what you would like me to have said 😛
Let's be clear about this: I do not care why you got this question wrong. What I do object to is your over-use of strong words like 'nonsense', 'wrong', 'crazy', and 'arbitrary'. It seems as if you think (or hope) others will not question your remarks if you present them as fact using sufficiently over-blown language.
If you think it makes you look smart to be an arch-pedant over the wording of the explanation to a QotD...think again.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 10, 2010 at 11:27 am
Paul White NZ (4/10/2010)
Tom.Thomson (4/9/2010)
I wish you would pay some attention..blah...blah..blah..No. You failed to answer correctly because you made an incorrect assumption, based on incomplete knowledge. Attempting to wriggle and back-track to somehow blame the explanation is very poor form.
Your rambling and border-line incoherent response above completely fails to address the text you quoted: The explanation perfectly adequately explains why IsUnknown becomes IsUnk. Your whole difficulty arises from an incorrect inference to the meaning of the word 'length' in that explanation. Your view relies on length being applied to the value of numdesc inside the check_expression, as opposed to the maximum length of the type returned by LEFT. No doubt you would prefer the following:
"the IsNull() function will truncate the length of replacement_value to the typed length of check_expression."
yes, that would be an improvement.
And that is precisely the distinction that I regard as pedantic, overly-academic, picky, and ultimately tedious. This is a Question of the Day, not a paper - and your lack of flexibility does you no credit.
I haven't changed what I'm saying. The explanation is incomplete.
You said it was wrong. Now you say it is incomplete. Back-tracking and wriggling.
More clever selective quoting. The line you quote a fragment of is actually
The explanation is incomplete. What there is of it is also incorrect
I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said as dishonest and disgraceful. I have said that it is incomplete and incorrect consistently throughout.
...that does seem to be a bit of a retraction from wrong though, doesn't it
Yes. Makes it hard to keep track of what your current position is.
What? You are trying to say that when you change from saying "wrong" to saying "pedantic" that's me changing my position? That's pure drivel. Your position changed, not mind - I agree with neither your original "wrong" nor your later "pedantic".
...I have good grounds to believe it is not documented...more thorough than you've undertaken, I suspect
It doesn't exist because you can't find it? :unsure:
And you are claiming to be psychic now as well? I have not said that it definitely is documented - just that most behaviour is. Read back, and try to respond to what I actually said, rather than what you would like me to have said 😛
[/quote] No I'm not psychic? Where did you pick up that nonsense from? The point you would do well to understand is that if you insist on saying "most behaviour is documented" as if it were an answer to the question whether this particular behaviour is documented you are being downright unhelpful.
Let's be clear about this: I do not care why you got this question wrong. What I do object to is your over-use of strong words like 'nonsense', 'wrong', 'crazy', and 'arbitrary'.
And after this exchange, I don't care what you object to.
It seems as if you think (or hope) others will not question your remarks if you present them as fact using sufficiently over-blown language.
If you think it makes you look smart to be an arch-pedant over the wording of the explanation to a QotD...think again.
If you think it makes you look smart to use the tricks of selective quotation and attributing to people things they have no said that are normally associated with the depths of gutter journalism, then you need to think again.
Tom
April 10, 2010 at 9:09 pm
Tom.Thomson (4/10/2010)
yes, that would be an improvement.
Then that is all that needed saying. You chose to make the bald statement "This explanation is wrong." - and then ramble on for pages, dodging and weaving, back-tracking, and wriggling to turn it into "incomplete". Laughable.
Sknox put it better in the post previous to your first one, "This explanation feels a little light. For completeness, it should include the behavior of the LEFT() function as well."
I don't know what you think your input added to that.
I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said
I have tried to be fair and extract the salient points from your verbose replies. Not my fault if you can't express yourself clearly.
For the record, I haven't changed my position one iota.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 6:01 am
Paul White NZ (4/10/2010)
Tom.Thomson (4/10/2010)
yes, that would be an improvement.Then that is all that needed saying. You chose to make the bald statement "This explanation is wrong." - and then ramble on for pages, dodging and weaving, back-tracking, and wriggling to turn it into "incomplete". Laughable.
You continue to distort - clearly quite deliberately. Can you tell me how "it's incomplete. What there is of it is incorrect" means "not incorrect", how it means "incomplete" alone and doesn't suggest "incorrect"? Or even how it is unclear?
Sknox put it better in the post previous to your first one, "This explanation feels a little light. For completeness, it should include the behavior of the LEFT() function as well."
I don't know what you think your input added to that.
The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting,
I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said
I have tried to be fair and extract the salient points from your verbose replies. Not my fault if you can't express yourself clearly.
So you continue to distort. I think the statement you mangled was clear, and you deliberately chose to truncate it to give the impression that I had said something which I clearly diod not say.
For the record, I haven't changed my position one iota.
Then why did you say that your change from "wrong" to "pedantic" was a change in position?
Tom
April 11, 2010 at 8:40 am
Tom.Thomson (4/11/2010)
You continue to distort - clearly quite deliberately.
Psychic Tom strikes again! Not only did you "know" how much documentation I had checked earlier, you now "know" that I am 'quite clearly' distorting your words! How do you know that? Why would I do that? What would be my motivation? If you really want to know, I find your replies hard work: they are too long, and you do not communicate at all clearly. I don't have all day to try to decode what you write, you know.
Can you tell me how "it's incomplete. What there is of it is incorrect" means "not incorrect", how it means "incomplete" alone and doesn't suggest "incorrect"? Or even how it is unclear?
Yes Tom, I can. It is an unclear and bizarre construction. I'm not sure even you know what you mean here. Is it merely incomplete? Or is it incorrect? I don't know why you insist on writing two awkward sentences when a couple of words will do, really I don't. Do you do it intentionally?
The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting
Well, that's rich! I would invite you to read your own submissions before casting aspersions.
I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said
Hmmm...civilised might be a bit strong, given your performance here. Look, I've already said that I have tried to be fair when quoting. Your responses are just far too long to quote in their entirety. If you feel you have been misquoted, I would invite you to consider whether that was really done deliberately and maliciously - or whether perhaps you might have been less than clear?
So you continue to distort.
All I said was that I tried to be fair, and you don't express yourself well. That is my genuine perception, and even 'psychic Tom' can't argue with that.
I think the statement you mangled was clear, and you deliberately chose to truncate it to give the impression that I had said something which I clearly diod not say.
No, Tom. You presume far too much. Your statement was not clear at all. I did not truncate it to misrepresent what I read.
Then why did you say that your change from "wrong" to "pedantic" was a change in position?
I didn't - I thought you were referring to yourself in the following (complete) passage:
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.
:Wow:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 2:15 pm
Paul White NZ (4/11/2010)
Psychic Tom strikes again! Not only did you "know" how much documentation I had checked earlier, you now "know" that I am 'quite clearly' distorting your words!
Of course I can know that without being psychic. I just have to look at what I wrote and at how you quote it, and observe that it is distorted.
How do you know that? Why would I do that? What would be my motivation?
As noted, I know it by observing it. However, it was unfair of me to say the distortion is deliberate and I withdraw that. I actually haven't a clue why you did it, I guess it has to be an error, so "deliberately"was unfair - as bad as some of your comments.
If you really want to know, I find your replies hard work: they are too long, and you do not communicate at all clearly. I don't have all day to try to decode what you write, you know.
Maybe they are too long - I think that's a side effect of the degree of irritation that you sometimes manage to induce in me. I think they are quite clear though, but obviously I'm not meeting the standard of clarity that you expect; you might take a moment to consider though that my reaction to some of your message may mean that I'm mistaking your meaning, perhaps because you are not meeting the standard of clarity that I expect.
Can you tell me how "it's incomplete. What there is of it is incorrect" means "not incorrect", how it means "incomplete" alone and doesn't suggest "incorrect"? Or even how it is unclear?
Yes Tom, I can. It is an unclear and bizarre construction. I'm not sure even you know what you mean here. Is it merely incomplete? Or is it incorrect? I don't know why you insist on writing two awkward sentences when a couple of words will do, really I don't. Do you do it intentionally?
Seriously I just don't know what to make of that. There are two statements. (1) It's incomplete. (2) What there is of it is incorrect. The phrase "What there is of it" is extremely common British and American English and means the available part of something incomplete; maybe this phrase isn't used n NZ? To me it's neither unclear nor bizarre, it's the sort of thing I have been used to seeing every day in books and journals and office memos throughout ny working life (and in emails, usenet posts, web pages, and blogs since those have existed).
The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting
Well, that's rich! I would invite you to read your own submissions before casting aspersions.
I guess it's fair repayment for your remarks about wriggling etcetera? In any case, I never cast nasturtiums, only their seeds.:-D
I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said
Hmmm...civilised might be a bit strong, given your performance here. Look, I've already said that I have tried to be fair when quoting. Your responses are just far too long to quote in their entirety. If you feel you have been misquoted, I would invite you to consider whether that was really done deliberately and maliciously - or whether perhaps you might have been less than clear?
I don't expect you to quote everything - of course not. I do regard it as unacceptable to quote selectively in such a way as to totally distort or even reverse the meaning, and then base argument on the resulting straw man.
So you continue to distort.
All I said was that I tried to be fair, and you don't express yourself well. That is my genuine perception, and even 'psychic Tom' can't argue with that.
I suppose I have to accept that you tried to be fair if you say so; but I think you failed abysmally.
I think the statement you mangled was clear, and you deliberately chose to truncate it to give the impression that I had said something which I clearly diod not say.
No, Tom. You presume far too much. Your statement was not clear at all. I did not truncate it to misrepresent what I read.
As noted above, I find that pretty incomprehensible.
Then why did you say that your change from "wrong" to "pedantic" was a change in position?
I didn't - I thought you were referring to yourself in the following (complete) passage:
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.
:Wow:
:laugh: Oh dear, I suggest you read it again: who does it say insisted that I was wrong or pedantic? Quite clearly it says you did, I think? So the person who changed from "wrong" to "pedantic" was you. Funny that you recognised it as a change in position when you mistakenly thought it was me, isn't it.
Maybe if you comment again I should reply in Gaelic :hehe:- then you won't be able to accuse me of being unclear or verbose, because I am the very model of clarity and brevity in that tongue. It is a language of such purity that only politicians can successfully be obscure and long-winded in it (and, of course, :-D17th century poets - but very many languages suffered from that problem). Or in Spanish, of which I don't know enough to be verbose (but would probably be very unclear:rolleyes:).
Anyway, all this is crazy - we both have more important things to do than to throw insults at each other and continue to quarrel over trivia.
Tom
April 12, 2010 at 10:28 am
I'm late to the party, as it were, so I did not read through each of the posts in the argument between Tom and Paul. So this is not to say that I'm siding with Tom over Paul, or vice versa, but here is my take.
The original explanation for the correct answer that I saw was:
"In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression. "
1. I didn't find the word "truncate" anywhere on the referenced page (URL given below, from the answer):
http://msdn.microsoft.com/en-us/library/ms184325%28SQL.90%29.aspx
So, given that the word "truncate" is not in the explanation at the referenced page, I think it is fair to expect at least a little explanation (if it is known) why this behavior is. After all, I agree with Tom that it is counterintuitive to provide a function that ostensibly allows you to provide replacement text of your choice, when in reality that text may be truncated to the exact type AND length of the type specificed. Even if one thinks that this is a logical leap to make based on common sense, it is not a connection that can be made from the explanation in the reference, since there's no explicit reference to truncation. Even the examples given don't use varchar. Of course, it is fair to expect a diligent DBA to do further research and find other examples, but that doesn't resolve the explanation gap in referring to "truncate" when that term is not present in the referenced URL.
2. Going back to the ostensible logic of the ISNULL behavior, I can certainly understand why Tom would be, if not flabbergasted, then baffled (as Paul put it) by this behavior, prior to learning more about it. Tom did say in his original comment that he had learned from it and that "anything that makes me learn is good from my point of view." But I agree with Tom that this is not intuitive behavior. Here is why I think it's not intuitive. The second parameter for the ISNULL function is, supposedly, the exact text you want to output in case the first parameter to the function is NULL. But the second parameter is not guaranteed to be the exact output. It's really the exact output, constrained to the data type AND length of the check expression. Since length isn't mentioned explicitly, to me that's an explanation gap that would benefit from clarification for pedagogical purposes - for those of us who don't always make such leaps intuitively.
I'm not sure if this confirms that ISNULL's behavior is not intuitive, but I Googled "ISNULL() and varchar" and found a page with this sample code, showing that the COALESCE function behaves differently and returns the exact replacement text (at least in this example).
DECLARE @t VARCHAR(2)
SELECT ISNULL(@t, 'Open'), COALESCE(@t, 'Open')
---- ----
Op Open
(1 row(s) affected)
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 12, 2010 at 1:24 pm
Oh dear :unsure:. This makes me think maybe Paul was right when he said my post was unclear.
webrunner (4/12/2010)
IAfter all, I agree with Tom that it is counterintuitive to provide a function that ostensibly allows you to provide replacement text of your choice, when in reality that text may be truncated to the exact type AND length of the type specificed.
Actually I don't think it's counterintuitive - the type of the result is the same as the type of the check expression, and in the case of varchar, varbinary, and nvarchar the type includes specification of the maximum permitted length. It's the behaviour of LEFT that surprised me, because I expected it to deliver a result with the same type as its first argument, just as IsNull does.
2. Going back to the ostensible logic of the ISNULL behavior, I can certainly understand why Tom would be, if not flabbergasted, then baffled (as Paul put it) by this behavior, prior to learning more about it.
The IsNull behaviour is nice and clean and tidy, Paul and I have no disagreement on that. In fact what we mostly disagreed about was words (words badly chosen by each of us, I think) not anything in T-SQL.
On a different topic: my first QoTD is due to appear in 8 to 10 days time; I've tried to choose something that I can't get wrong, so that I don't suffer the bashing that many QoTD setters get, but I'm not at all sure I've succeded.
Tom
April 12, 2010 at 2:12 pm
Tom.Thomson (4/12/2010)
Oh dear :unsure:. This makes me think maybe Paul was right when he said my post was unclear.
No, you're right. As I said, I didn't read the whole thread carefully, and may have projected my own reading of what is counterintuitive onto your comments about the LEFT function. I re-read your initial comment and it is indeed about the LEFT function behavior.
I still think it would help if the ISNULL documentation had a varchar example, though.
webrunner (4/12/2010)
IAfter all, I agree with Tom that it is counterintuitive to provide a function that ostensibly allows you to provide replacement text of your choice, when in reality that text may be truncated to the exact type AND length of the type specificed.Actually I don't think it's counterintuitive - the type of the result is the same as the type of the check expression, and in the case of varchar, varbinary, and nvarchar the type includes specification of the maximum permitted length. It's the behaviour of LEFT that surprised me, because I expected it to deliver a result with the same type as its first argument, just as IsNull does.
Interesting - by "type" do you mean type and length, such as varchar(10)? In that case, I never thought about it, but it does make sense. A LEFT function performs a kind of substring operation, so I can see that it would return a value matching the length chosen in the second parameter. If by "type" you mean type only (varchar vs. varbinary, etc.), though, I just thought the ISNULL function would override that and ensure that the result of ISNULL would be long enough to accommodate the entire replacement value chosen. That was ignorance on my part - in reality it seems one just has to be careful to make sure the lengths work out; I guess it may also be ignorance on my part not to be surprised by the behavior of LEFT(). 🙂
2. Going back to the ostensible logic of the ISNULL behavior, I can certainly understand why Tom would be, if not flabbergasted, then baffled (as Paul put it) by this behavior, prior to learning more about it.
The IsNull behaviour is nice and clean and tidy, Paul and I have no disagreement on that. In fact what we mostly disagreed about was words (words badly chosen by each of us, I think) not anything in T-SQL.
Yes, as I said above, if "type" means "type and length," then the ISNULL documentation make sense to me now. As far as LEFT is concerned, I can see the logic of returning the length that was chosen for the second parameter, but I suppose I can see the logic of keeping the length that was passed in. I guess the existing behavior didn't surprise me as much as it did you.
I don't totally agree with you guys regarding ISNULL with varchars - especially since it seems that the existence of the COALESCE function** means that someone thought returning the whole string would be useful in certain cases - but I think as long as the behavior of ISNULL with a varchar is explained properly I can live with it.
** For example:
DECLARE @t VARCHAR(4)
SELECT ISNULL(LEFT(@t,2), 'Open'), COALESCE(LEFT(@t,2), 'Open')
On a different topic: my first QoTD is due to appear in 8 to 10 days time; I've tried to choose something that I can't get wrong, so that I don't suffer the bashing that many QoTD setters get, but I'm not at all sure I've succeded.
Good for you. I've never tried a question and, frankly, still consider myself unqualified to post one that wouldn't get torched for inaccuracy.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply