How do you ensure accuracy of your query

Question

I am have a table with about 100,000 rows.

Lets suppose the primary key is some sequence of numbers like a barcode for example.

I am doing checks like

SELECT count(*) FROM TABLE WHERE LEFT(Barcode, 1)  is 0

I have about a 100 more rules to implement. They should ALL return 0 rows. But how do I ensure that they are returning 0 rows because the data is in fact correct OR that it is returning 0 rows because of a poorly written query.

I know that all my queries should return 0 since there are constraints that prevent bad data from getting in the first place but this is still needed by the company.

Answer 1

You can do this pretty easily using SUM and CASE:

SELECT 
    COUNT(*) as 'Total Rowcount',
    SUM(CASE WHEN LEFT(BARCODE, 1) = 0 THEN 1 ELSE 0 END) as 'Leading Zero Barcode',
    SUM(CASE WHEN LEFT(Foo, 1) = 'X' THEN 1 ELSE 0 END) as 'Leading X in Foo'
....

Then you get a number for the WHOLE table/query, and a breakdown of each category, all on the same line. It'll be easy to see if you have a bad query since you'll get a low/high rowcount or something else.

This has the added advantage or processing more efficiently than a bunch of separate queries, since you just evaluate the whole table once.

Answer 2

Take care with nulls and never forget that SQL's three-valued logic is treats constraints differently i.e.

P(row)  | WHERE P(row) | CHECK(P(row))
--------+--------------+-------------------------
TRUE    | Row accepted | Row satisfies constraint
FALSE   | Row rejected | Row violates constraint   
UNKNOWN | Row rejected | Row satisfies constraint   <-- careful here!  

My personal approach is the write a SELECT * FROM query and add columns returning a ternary value for each constraint:

SELECT T.*, 
       CASE 
          WHEN     ( LEFT(Barcode, 1) <> 0 ) THEN 'TRUE'
          WHEN NOT ( LEFT(Barcode, 1) <> 0 ) THEN 'FALSE'
          ELSE 'UNKNOWN'
       END AS result__Barcode__first_char_cannot_be_0, 
       CASE 
          WHEN     ( LEFT(Foo, 1) <> 'X' ) THEN 'TRUE'
          WHEN NOT ( LEFT(Foo, 1) <> 'X' ) THEN 'FALSE'
          ELSE 'UNKNOWN'
       END AS result__Foo__first_char_cannot_be_X
 FROM YourTable AS T;

Alternatively:

SELECT T.*, 
       CASE 
          WHEN NOT ( LEFT(Barcode, 1) <> 0 ) THEN '{{violates constraint}}'
          ELSE '{{satisfies constraint}}'
       END AS result__Barcode__first_char_cannot_be_0, 
       CASE 
          WHEN NOT ( LEFT(Foo, 1) <> 'X' ) THEN '{{violates constraint}}'
          ELSE '{{satisfies constraint}}'
       END AS result__Foo__first_char_cannot_be_X
 FROM YourTable AS T;

These results can be used for further analysis e.g. tally failure modes, tally failures per failure mode, (tally of) rows that satisify all constraints, (tally of) rows that fail at least one constraint, etc.

Consider using a unit test framework such as tSQLt to add data that would otherwise violate constraints that are actually in force (FakeTable command in tSQLt): if your query doesn't highlight this data then you need to rewrite your query!

p.s. ensure you do not waste time testing the DBMS itself. If the constraint is declared and in force (not deferred, not in NO CHECK mode, etc) then you should trust the DBMS. By all means query the INFORMATION SCHEMA to ensure an expected key exists on the required columns but there is simply no point in testing that the values comprising the key are actually unique: if they weren't then the DBMS would have a serious bug and we'd all know about it!

Answer 3

Your question is a little vague, but would two queries per rule get you there? For your example, perhaps make sure the count returned without the WHERE criteria is greater than 0?

Also, the code you have provided is incorrect. Not sure if you meant = instead of is or quotes around the 0 or what...