May 2, 2017 at 3:06 am
If expression is NULL, zero length data is inserted
Do you know whether that's documented anywhere? It's not on the page you linked to. To me, this behaviour is unexpected. If CONCAT_NULL_YIELDS_NULL is ON, the function should return NULL. Thanks for the question, though!
John
May 2, 2017 at 3:08 am
I thought it will follow the 'CONCAT_NULL_YIELDS_NULL is always ON' rule. But i was wrong.
May 2, 2017 at 3:40 am
John Mitchell-245523 - Tuesday, May 2, 2017 3:06 AMIf expression is NULL, zero length data is inserted
Do you know whether that's documented anywhere? It's not on the page you linked to. To me, this behaviour is unexpected. If CONCAT_NULL_YIELDS_NULL is ON, the function should return NULL. Thanks for the question, though!
John
May 2, 2017 at 3:48 am
Thanks Carlo. Good to know I'm not the only one who feels that way! But the behaviour appears to be in the "observed" rather than "documented" category. Perhaps Microsoft should have responded to the Connect item by noting that since it is (or seems to be) undocumented behaviour, they're free to change it!
John
May 2, 2017 at 5:19 am
John Mitchell-245523 - Tuesday, May 2, 2017 3:48 AMThanks Carlo. Good to know I'm not the only one who feels that way! But the behaviour appears to be in the "observed" rather than "documented" category. Perhaps Microsoft should have responded to the Connect item by noting that since it is (or seems to be) undocumented behaviour, they're free to change it!John
Agreed
Nice question, thanks Carlo
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
May 2, 2017 at 6:07 am
Stewart "Arturius" Campbell - Tuesday, May 2, 2017 5:19 AMJohn Mitchell-245523 - Tuesday, May 2, 2017 3:48 AMThanks Carlo. Good to know I'm not the only one who feels that way! But the behaviour appears to be in the "observed" rather than "documented" category. Perhaps Microsoft should have responded to the Connect item by noting that since it is (or seems to be) undocumented behaviour, they're free to change it!John
Agreed
Nice question, thanks Carlo
Agreed on both points. Nice question to illustrate a subtle point. Microsoft isn't always the best about responding to Connect items or updating their documentation. I know they have a lot of it, but they also have a lot of people.
May 2, 2017 at 6:49 am
There are some fairly smart people on this forum, but I'm surprised only 5% of us expected a string STUFFed with a NULL would lead to a NULL. I guess many more people have experience STUFFing NULLs, or they just ran the SQL before answering 😉 Anyway a good question to demonstrate that SQL doesn't always do what is expected.
May 2, 2017 at 11:27 am
STUFF always makes for an interesting QotD. Thanks, Carlo!
May 3, 2017 at 12:38 am
DBA_Rob - Tuesday, May 2, 2017 6:49 AMThere are some fairly smart people on this forum, but I'm surprised only 5% of us expected a string STUFFed with a NULL would lead to a NULL. I guess many more people have experience STUFFing NULLs, or they just ran the SQL before answering 😉 Anyway a good question to demonstrate that SQL doesn't always do what is expected.
I did try out the null case, since there was nothing in the docs about it. I do agree it should result in a null being returned, but when that's not explicitly stated, anything is possible.
May 3, 2017 at 6:47 am
Rune Bivrin - Wednesday, May 3, 2017 12:38 AMI did try out the null case, since there was nothing in the docs about it. I do agree it should result in a null being returned, but when that's not explicitly stated, anything is possible.
I love you signature line. It happens to be very appropriate for this question & answer!
May 5, 2017 at 4:27 am
John Mitchell-245523 - Tuesday, May 2, 2017 3:48 AMThanks Carlo. Good to know I'm not the only one who feels that way! But the behaviour appears to be in the "observed" rather than "documented" category. Perhaps Microsoft should have responded to the Connect item by noting that since it is (or seems to be) undocumented behaviour, they're free to change it!John
Well, they closed it as No Fix very quickly, a bit under two and a half days after it was raised. I thought they would at least change the BoL documentation to describe this behaviour (since one of the points in the DETAIL part of the Connect item was that the documentation didn't describe this undesirable behaviour) but no, they've done nothing about the documentation either. I've made a few sharp comments on Microsoft's handling of bugs and their misuse of the "backward compatability" excuse over the years, this is just another example. Why they think that having undocumented behaviours like this is OK is beyond me.
P.S. Thanks for the easy question !:hehe:😀!
Tom
August 16, 2017 at 10:51 pm
Ed Wagner - Tuesday, May 2, 2017 6:07 AMStewart "Arturius" Campbell - Tuesday, May 2, 2017 5:19 AMJohn Mitchell-245523 - Tuesday, May 2, 2017 3:48 AMThanks Carlo. Good to know I'm not the only one who feels that way! But the behaviour appears to be in the "observed" rather than "documented" category. Perhaps Microsoft should have responded to the Connect item by noting that since it is (or seems to be) undocumented behaviour, they're free to change it!John
Agreed
Nice question, thanks CarloAgreed on both points. Nice question to illustrate a subtle point. Microsoft isn't always the best about responding to Connect items or updating their documentation. I know they have a lot of it, but they also have a lot of people.
That is a common frustration. Sometimes it seems like it is getting better.
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
August 20, 2017 at 3:13 pm
SQLRNNR - Wednesday, August 16, 2017 10:51 PMEd Wagner - Tuesday, May 2, 2017 6:07 AMAgreed on both points. Nice question to illustrate a subtle point. Microsoft isn't always the best about responding to Connect items or updating their documentation. I know they have a lot of it, but they also have a lot of people.That is a common frustration. Sometimes it seems like it is getting better.
And at other times it seems like it's getting worse.
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply