STUFF

  • Carlo Romagnano

    SSC-Insane

    Points: 22009

    Comments posted to this topic are about the item STUFF

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    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

  • palotaiarpad

    SSCertifiable

    Points: 5615

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

  • Carlo Romagnano

    SSC-Insane

    Points: 22009

    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!

  • John Mitchell-245523

    SSC Guru

    Points: 148771

    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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    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”

  • Ed Wagner

    SSC Guru

    Points: 286985

    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.

  • DBA_Rob

    Default port

    Points: 1493

    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.

  • Revenant

    SSC-Forever

    Points: 42467

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

  • Rune Bivrin

    SSCertifiable

    Points: 7857

    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.

  • DBA_Rob

    Default port

    Points: 1493

    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!

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

  • TomThomson

    SSC Guru

    Points: 104773

    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 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply