Philosophical testing question

33 views
Skip to first unread message

Bryant McClellan

unread,
Jul 13, 2021, 10:29:04 AM7/13/21
to tSQLt
Today I was reviewing code from one of our teams testing a MERGE procedure. Ignoring the potential issues in using T-SQL MERGE how do you approach MERGE testing? 3 separate tests (1 for each leg of the MERGE verb)? One test that exercises all 3 legs? Both? Something else entirely? What is your rationale?

FWIW our developers are coached that tests are supposed to function as black boxes. Given X input the test should expect Y output. And the test cannot be cognizant of the logic in the object under test. Those seem like obvious considerations but TDDD is not as commonly taught/learned/used as TDD in other arenas.

Dmitrij Kultasev

unread,
Jul 13, 2021, 10:30:59 AM7/13/21
to ts...@googlegroups.com
I would guess that you don't need to test merge statement. You need to test behavior and that doesn't matter how it was implemented.

вт, 13 июл. 2021 г., 17:29 Bryant McClellan <bryant.m...@doitbest.com>:
Today I was reviewing code from one of our teams testing a MERGE procedure. Ignoring the potential issues in using T-SQL MERGE how do you approach MERGE testing? 3 separate tests (1 for each leg of the MERGE verb)? One test that exercises all 3 legs? Both? Something else entirely? What is your rationale?

FWIW our developers are coached that tests are supposed to function as black boxes. Given X input the test should expect Y output. And the test cannot be cognizant of the logic in the object under test. Those seem like obvious considerations but TDDD is not as commonly taught/learned/used as TDD in other arenas.

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/8d204bee-96e5-4d27-86af-72a991d25d63n%40googlegroups.com.

Blake, Larry

unread,
Jul 13, 2021, 10:33:03 AM7/13/21
to ts...@googlegroups.com

I’m interested in seeing how others answer.  Personally, I would do a single AssertEqualsTable with records that exercise each of the legs and the data clearly explaining what each one expects.  For example “Should be INSERTED” in one of the columns.

 

Larry Blake


--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/8d204bee-96e5-4d27-86af-72a991d25d63n%40googlegroups.com.

[External Email: This message has originated from an external source. Please use proper judgment and caution when opening attachments, clicking links, or responding to this email.]

​

Ed Elliott

unread,
Jul 13, 2021, 10:37:16 AM7/13/21
to ts...@googlegroups.com
I’d probably setup the data and run the merge in the setup proc then have one test to validate each type of output, if it was important. 

If the merge was making sure the staff canteen rota was up to date I would maybe have a single test that does everything. If the merge controlled how many mg’s of medicine someone was to be given I’d write more tests :)

Ed

On 13 Jul 2021, at 15:29, Bryant McClellan <bryant.m...@doitbest.com> wrote:

Today I was reviewing code from one of our teams testing a MERGE procedure. Ignoring the potential issues in using T-SQL MERGE how do you approach MERGE testing? 3 separate tests (1 for each leg of the MERGE verb)? One test that exercises all 3 legs? Both? Something else entirely? What is your rationale?

FWIW our developers are coached that tests are supposed to function as black boxes. Given X input the test should expect Y output. And the test cannot be cognizant of the logic in the object under test. Those seem like obvious considerations but TDDD is not as commonly taught/learned/used as TDD in other arenas.

Bryant McClellan

unread,
Jul 13, 2021, 10:45:59 AM7/13/21
to ts...@googlegroups.com
Sorry, I didn't mean to imply we are testing whether MERGE works. More that we are testing how competent our developers are at exercising it in meaningful ways to get predictable results.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803



To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAJ1xV3ga01%3DgONiUXY-aXd5mDdzfEw_UPpm-CSqxpgMitY0cug%40mail.gmail.com.

Blake, Larry

unread,
Jul 13, 2021, 10:48:36 AM7/13/21
to ts...@googlegroups.com

Adding to Dmitrij’s comment: yes, you test the behavior.  So later if you refactor and stop using MERGE, the test still applies.

I still would use the AssertEqualsTable as mentioned earlier, and identify the different paths of logic in the data.

 

Larry Blake

 

From: ts...@googlegroups.com <ts...@googlegroups.com> On Behalf Of Bryant McClellan

Sent: Tuesday, July 13, 2021 10:45 AM
To: ts...@googlegroups.com
Subject: Re: #tSQLt: Philosophical testing question

 

Sorry, I didn't mean to imply we are testing whether MERGE works. More that we are testing how competent our developers are at exercising it in meaningful ways to get predictable results.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803

 

 

On Tue, Jul 13, 2021 at 10:31 AM Dmitrij Kultasev <dkul...@gmail.com> wrote:

I would guess that you don't need to test merge statement. You need to test behavior and that doesn't matter how it was implemented.

 

вт, 13 июл. 2021 г., 17:29 Bryant McClellan <bryant.m...@doitbest.com>:

Today I was reviewing code from one of our teams testing a MERGE procedure. Ignoring the potential issues in using T-SQL MERGE how do you approach MERGE testing? 3 separate tests (1 for each leg of the MERGE verb)? One test that exercises all 3 legs? Both? Something else entirely? What is your rationale?

 

FWIW our developers are coached that tests are supposed to function as black boxes. Given X input the test should expect Y output. And the test cannot be cognizant of the logic in the object under test. Those seem like obvious considerations but TDDD is not as commonly taught/learned/used as TDD in other arenas.


Bryant McClellan

unread,
Jul 13, 2021, 10:55:36 AM7/13/21
to ts...@googlegroups.com
Larry, 
Right. And that goes along with the black box treatment. Even if the procedure implemented INSERT/UPDATE/DELETE using those verbs instead of MERGE, the intent of testing is the same. And, as you note, changing the implementation should have zero effect on the test.

I pose the question because there are those that separate the DML functions logically and insist they must be tested separately. There is the other school that sticks with input X should result in output Y and provide all the necessary faked data.

I know what I think and as the person who drives tSQLt here, I have some influence. What I was looking for is how other shops approach it so I'm not pushing standards in the dark.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803


To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/MN2PR07MB65275B2D81B70D1AC9633D75E5149%40MN2PR07MB6527.namprd07.prod.outlook.com.

Pesse

unread,
Jul 13, 2021, 11:19:07 AM7/13/21
to ts...@googlegroups.com
I would do it with one combined AssertEqualsTable since the reporting indicates very clearly where a potential problem is (so we don't have a reduce in Signal vs. Noise). Writing the combined test seems easier to me, so we have the benefit of easier to write test, probably lest (test-)code while still having no downsides.
If the reporting however would be harder to read or the results harder to understand (e.g. test data without clear indication what we're testing with each row), that's something to consider and balance against.

Samuel

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAN3pJNU5CJDJjqTgp1OV4Jko3GOTGUwpC9u0Cfff0c1_ZE4Upw%40mail.gmail.com.

Bryant McClellan

unread,
Jul 13, 2021, 11:24:10 AM7/13/21
to ts...@googlegroups.com
Samuel,
Very good points. A single test is certainly less code. That doesn't guarantee it is simpler but is a step in a positive direction.

OTOH test assertions do not have the luxury of reporting context. Separating tests by function (I/U/D) would make it clear what operation the failure is related to.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803


To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAAwA796opMK%2BoWC8t_LMnKcgO_jidxK4pKUiQ5Vy49b6pz0UAg%40mail.gmail.com.

Pesse

unread,
Jul 13, 2021, 11:28:29 AM7/13/21
to ts...@googlegroups.com
Sorry, didn't remember exactly how AssertEqualsTable reports (I'm mainly using a different tool on a different RDBMS with similar functionality - and challenges :)).
If the report doesn't indicate what exactly went wrong, that's a serious downside to consider, because it adds effort to understand what went wrong. So in that case it's a decision I would make depending on context (like, is that an area that changes often/breaks often, so good, clear signal is more important, how mature/experienced is the team, how frequent are the tests run etc)

Samuel

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAN3pJNUO%2Bmi7sF2dkU1OFt_guVT%2BSJWu-B5T_zy32NmD-BwVHQ%40mail.gmail.com.

Blake, Larry

unread,
Jul 13, 2021, 11:32:45 AM7/13/21
to ts...@googlegroups.com

AssertEqualsTable does 2 things:

 

  1. Passes or fails the test.
  2. (Assuming you have access to the test output): shows expected and actual table results.  So properly labeled data should indicate what is different / missing.  You can certainly overload the test to the point that a failure is difficult to diagnose, but keeping it intelligible is achievable.

 

Larry Blake

Bryant McClellan

unread,
Jul 13, 2021, 11:37:37 AM7/13/21
to ts...@googlegroups.com
Larry,
Excellent point. We could easily add a column to the expected and actual tables for this purpose. Then we get shorter code with more informative results. I like it.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803


To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/MN2PR07MB65279576E6FE68A6E7AB731EE5149%40MN2PR07MB6527.namprd07.prod.outlook.com.

Sebastian Meine

unread,
Jul 13, 2021, 5:10:59 PM7/13/21
to ts...@googlegroups.com
Bryant,

I generally write all code test-driven, following the advice of taking small steps. Test some functionality, like “a customer record that does not exist is created”, and then write just enough code to make that test pass. And then the next small step. 

Even when writing tests for pre-existing code, I try to follow that pattern closely. 

So, if this is a straight-forward one-table IUD-merge, I’d likely write these tests:

- if the source table is empty, no new customer is created
- a customer record that does not exist is created (this test would be looking at all relevant columns.)
- several customers that do not exist are created correctly (this test would only look at columns needed to identify each customer, like name)
- a single customer that exists is updated (all columns)
- multiple customers are updated correctly (identifying columns + one updated column)
- a single customer that is not in the source table is deleted 
- several customers not in the source table are deleted

The insert tests would start with an empty customer table, the other tests would only contain the customers affected.

While technically not needed, I probably would add a single test that has one record each to be inserted, updated, or deleted:

- several customers are correctly merged (note: while this test mentions the term “merge” it still would not force implementation detail)

If there are where-clauses involved, multiple tables, or more complex merge rules, the number of tests would naturally go up. 

The goal during test selection should always be to write as few tests as needed to cover your functionality. But in addition, you also want to keep the number of tests that fail “for the same reason” minimal. That’s why I don’t put “collateral” records into my tests. While it might feel simple to also add records into the above tests that are treated differently then the functionality the test is about (like a record that will get updated in the delete test), it tends to lead to higher maintenance costs in the long run, without providing any benefit. 

Hope this helps. 

Sebastian


--

Thanks

Sebastian
Dedicated to freeing your SQL
 

Sebastian Meine
+1-832-377-5489
seba...@sqlity.net

sqlity.net llc
Quality for SQL

Reply all
Reply to author
Forward
0 new messages