STUFF

  • Comments posted to this topic are about the item STUFF

  • 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

  • I thought it will follow the 'CONCAT_NULL_YIELDS_NULL is always ON' rule. But i was wrong.

  • John Mitchell-245523 - Tuesday, May 2, 2017 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

    Here's the same!

  • 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

  • John Mitchell-245523 - Tuesday, May 2, 2017 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

    Agreed
    Nice question, thanks Carlo

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • Stewart "Arturius" Campbell - Tuesday, May 2, 2017 5:19 AM

    John Mitchell-245523 - Tuesday, May 2, 2017 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

    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.

  • 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.

  • STUFF always makes for an interesting QotD. Thanks, Carlo!

  • DBA_Rob - Tuesday, May 2, 2017 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.

    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.


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin - Wednesday, May 3, 2017 12:38 AM

    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.


    Just because you're right doesn't mean everybody else is wrong.

    I love you signature line.  It happens to be very appropriate for this question & answer!

  • John Mitchell-245523 - Tuesday, May 2, 2017 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

    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

  • Ed Wagner - Tuesday, May 2, 2017 6:07 AM

    Stewart "Arturius" Campbell - Tuesday, May 2, 2017 5:19 AM

    John Mitchell-245523 - Tuesday, May 2, 2017 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

    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.

    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

  • SQLRNNR - Wednesday, August 16, 2017 10:51 PM

    Ed Wagner - Tuesday, May 2, 2017 6:07 AM

    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.

    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