Testing COALESCE with tSQLt

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    Comments posted to this topic are about the item Testing COALESCE with tSQLt

  • larry.blake

    SSC Enthusiast

    Points: 128

    Hi, Steve.  Love your articles, please keep it up.

    The FakeTable @SchemaName parameter is deprecated.  If you view the source, you'll see

    @SchemaName NVARCHAR(MAX) = NULL, --parameter preserved for backward compatibility. Do not use. Will be removed soon.

  • vl1969-734655

    Mr or Mrs. 500

    Points: 545

    Frankly, while this is a nice write up, I am a bit confused on what exactly is tested here or what is the point of this story at all?
    is this a write up of how you build a test in/for SQL?
    is this a write up on how you test for things? 
    or how you test specific rules?

    what is the point here?   what is a real life value of this? 
    really want to  know...

    your first SELECT

    select SELECT  Salutation + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName + ' '  + SuffixFROM dbo.Contacts;

    is not what an experienced  SQL dev would ever use. not even what an knowledgeable any kind of developer would use to do anything  but simply do a quick record count and/or to test if there are any  "NULL"s  in the data. 
    that is all it is useful for.
    esentially do a quick and dirty count and NULL test on the table. also test and confirm  the output formating rule.   nothing more or less.

    the second select with actual COALESCE function used is more to the point

    SELECT   COALESCE(Salutation + ' ', '')   + COALESCE(firstname + ' ', '')   + COALESCE(SUBSTRING(middlename, 1, 1) + '. ', '') + lastname   + COALESCE(', ' + suffix, '')  FROM dbo.Contacts;

    yet still not 100% correct.  it will work but it is not properly as it does not check , for example, if middlename contains a "."  already  before adding it. 

    Yes I have read the blog and you do mentioned the issue, but you do not explore the solution.
    and  I ,personally,  do not see a value of this test if it can not be used on a real data. 
    I can write a procedure to implement the logic I want and test it on the small set of data very easily. without going through all this shenanigans. and faster too. IF I need to do it in SQL. 
    and I had done it in real life.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    larry.blake - Thursday, August 17, 2017 10:06 AM

    Hi, Steve.  Love your articles, please keep it up.

    The FakeTable @SchemaName parameter is deprecated.  If you view the source, you'll see

    @SchemaName NVARCHAR(MAX) = NULL, --parameter preserved for backward compatibility. Do not use. Will be removed soon.

    Thanks, didn't see that as I haven't been on that doc page lately. Not sure how long it's been there, but the parameter certainly hasn't been removed yet.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    vl1969-734655 - Thursday, August 17, 2017 10:37 AM

    Frankly, while this is a nice write up, I am a bit confused on what exactly is tested here or what is the point of this story at all?
    is this a write up of how you build a test in/for SQL?
    is this a write up on how you test for things? 
    or how you test specific rules?

    what is the point here?   what is a real life value of this? 
    really want to  know...

    Yes I have read the blog and you do mentioned the issue, but you do not explore the solution.
    and  I ,personally,  do not see a value of this test if it can not be used on a real data. 
    I can write a procedure to implement the logic I want and test it on the small set of data very easily. without going through all this shenanigans. and faster too. IF I need to do it in SQL. 
    and I had done it in real life.

    First, developers certainly do write (select a + ' ' + b + ' ' ... ) all the time. It's not a great idea, and there are problems, but that code exists. Testing, and maintaining tests over time, help to show developers where their code has issues, and prevent regressions. The point here is to have repeatable testing.
    The case chosen is for names, which are complex. There are many more rules that could be applied, but to keep this relatively simple, I just chose a few. I didn't cover every case, because this is a simplified model. You certainly could expand this to check for existing periods or other issues. In fact, rather than just complaining about this piece not meeting your needs, perhaps you'd extend this to include another case, or explain how you would deal with the existing period? Remove it and always put it back? Check and make a decision on adding it? There are multiple implementations that could work, and one is likely more efficient than others, but how do you know that the code you've written works?

    You wrote "I can write a procedure and test it on a small set of data easily". Sure. You can. It's definitely going to be faster for you if you haven't built testing skills. However, I can assemble this test quickly. Most of the test would include the same sample data I want. However, as you noted, I didn't include a case with a middle name that has a period already. Would that be in your data set? Maybe.

    One of the issues with much code is that developers are busy and they focus on the problem from their view, which is often a limited view. Very few of us have time to explore every possibility. If you deployed this code (not saying you would), and QA reported this shows that "Steve A. Jones" is returned as "Steve A.. Jones", you'd know there's a bug. You could modify your code and assemble another data set, or run queries, but as the list of inputs grows, are you positive you check every output row correctly? I would argue that you, and most developers, don't perform that diligence as well as they could because we make mistakes as humans.

    With this test, I can easily add another row of data to the rest (both the input, and the expected output) and then just exec tsqlt.run my test and it will check not only the new case, but every previous one as well. If I add more complex logic for Van Hewitt or MacDonald as a last name or multiple middle names, I can prevent regressions by having a formal test.

    Not only can I prevent regressions, but I also prevent future individuals from changing the code without having a test run at some point (manual, CI, etc.) that validates previous cases we know about.

    It seems heavyweight, which is one reason I didn't try to cover every case or make this a 100% solution. However, once you start to write these tests, they are fairly easy to put together quickly. I often need the input test data anyway, and the formal test makes me spend a minute ensuring I explicitly document the result set I want, not relying on my focus to ensure I read a result set correctly every time.

  • vl1969-734655

    Mr or Mrs. 500

    Points: 545

    Steve, I never said that   code like " (select a + ' ' + b + ' ' ... )"  is not used.
    I said that an experienced developer, be that DBA, SQL dev or any other, would not use it with some exceptions. 
    infact, at my current job I still have apps with just such code in place.
    Worst yet, we still have somone who write this kind of code to this day. 
    he is not a professional dev. he is a self taught SQL/dBase 2and 3 / C# coder. 
    codding is not primary job. just something he does because there is  a need for it..
    when I have to work with his code I fix it as much as I can, but some things will be there for a long time. 

    Now to answer your other points.
    please understand that I was not trying to knock you down or belittle the overall  value of your post as a teaching instrument. 
    it has it's uses and intrinsic value to it.  however I have this personality fault that always looking on a long term usefulness of things not what it can do here and now but what are the future benefits might be. 
    I have been in the position where my company have come upon the test driven development (TDD) principals and tried to implement it company wide. it was a living nightmare. adding TDD API and software to the existing projects and make it work.
    especially to project that where written using old school technique. not strictly OO standards and more.
    at the end we had many projects where all tests that we build where all green and nice, BUT the app still had bugs up to wazoo.  
    what good of the tests did to the devs? Nothing. no good at all. and all devs agreed on that ,except that management had drink the kool-aid and pushed hard to get there. 

    that said, you do have a point, and if properly implemented , from the beginning , and if the overall development practices is properly managed , TDD has it moments.  but the overhead might just kill you  🙂

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    Apology accepted, and I'll apologize as well. Your comment came off as short and argumentative, and I was tired. I'm sorry for that.

    In terms of  TDD and implementing this. I'm not a huge TDD fan, often because people write tests for a thing (x or y), but they may end up with code that's slightly orthogonal to the item. I prefer to actually write tests that look for the issues I get. TDD and tests don't prevent bugs in software. They ensure that software passes the tests. If the tests aren't relevant, or don't test the functionality, then you can still have bugs.

    However,  bugs in  software are separate from testing. Testing can improve the quality of software, but you need to write the tests that look for the bugs. For example, I wouldn't necessarily test this:
    select  sum (notnullcolumn_x) from mytable
    Why? It's a simple function, and I believe SUM() works. If I need to handle NULLs somehow, I might, but overall, I can't worry about this not working. However, COALESCE is more complex, and because I could get it wrong, or forget cases, I would write the test. If I need to ensure a non-trivial, multi-row result set, a test is good. The example I chose doesn't prevent bugs (as you pointed out with the period case), but it does ensure that I pass the cases I've added. If I add additional cases, I can improve the test. If I change the code, my test at least ensures I don't create regressions.

    Adding testing to a project may or may not help. Your developers need to learn how to write tests. If  bugs still slip through, they aren't testing the things they should.

  • nadair

    SSC Rookie

    Points: 32

    Hey there, just reading this article for the first time, and am a little confused by some syntax. Shouldn't this part:

    EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
    INSERT dbo.Fullname

    be:

    EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
    INSERT dbo.Contacts
    ?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    nadair - Monday, October 9, 2017 7:13 AM

    Hey there, just reading this article for the first time, and am a little confused by some syntax. Shouldn't this part:

    EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
    INSERT dbo.Fullname

    be:

    EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
    INSERT dbo.Contacts
    ?

    Yes, sorry. Must have created a typo during editing.

  • nadair

    SSC Rookie

    Points: 32

    Steve Jones - SSC Editor - Monday, October 9, 2017 9:13 AM

    nadair - Monday, October 9, 2017 7:13 AM

    Hey there, just reading this article for the first time, and am a little confused by some syntax. Shouldn't this part:

    EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
    INSERT dbo.Fullname

    be:

    EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
    INSERT dbo.Contacts
    ?

    Yes, sorry. Must have created a typo during editing.

    No worries! I thought I must just be missing something.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

    nadair - Monday, October 9, 2017 9:19 AM

    No worries! I thought I must just be missing something.

    Well, I was definitely missing something.

Viewing 11 posts - 1 through 11 (of 11 total)

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