#33 Test procedures execute in wrong order

[Andrew Cohen]

As far as I know, if I have procedures test_a, test_b, test_c, they should execute in alphabetical order.

Since our upgrade from 9i to 10g, this is not happening anymore. It is happening in a non-alphanumeric order, so any dependencies between test-cases cause some test procedures to fail.

I think that ordering should be introduced in.

[Andrew Cohen]

I have found the problem in ut_plsql.pkb. The SELECT statements inside of populate_test_array have a SELECT DISTINCT but do not include an ORDER BY. I think in previous versions of Oracle, SELECT DISTINCT always included an implied ORDER BY, but not in 10G. This should be a fairly simple update.

[Paul Walker]

The standard in xUnit frameworks is for each test to be independent, and therefore SETUP and TEARDOWN should be called before and after each test ([bugs:#4] relates to this).

Having said that, I can see no reason for not performing them in order (it makes the report look tidier if nothing else), so I would support this request.

[David Pyke Le Brun]

a sample patch

Index: ut_plsql.pkb
===================================================================
--- ut_plsql.pkb        (revision 134)
+++ ut_plsql.pkb        (working copy)
@@ -723,7 +723,8 @@
       samepackage_in   IN   BOOLEAN := FALSE,
       prefix_in        IN   VARCHAR2 := NULL,
       owner_in         IN   VARCHAR2 := NULL,
-      subprogram_in    IN   VARCHAR2 := '%'
+      subprogram_in    IN   VARCHAR2 := '%',
+      alphabetic_order_in   IN   VARCHAR2 := 'Y'
    )
    IS
       v_pkg   VARCHAR2 (1000)
@@ -772,7 +773,7 @@
                                         prefix_in
                                      || c_teardown
                                   )
-                                 ))
+                                 ) ORDER BY DECODE(alphabetic_order_in,'Y',procedure_name,object_id))
              &end_lt_9
              &start_ge_9
              (SELECT procedure_name
@@ -792,7 +793,7 @@
                                                   prefix_in
                                                || c_teardown
                                             )
-                                           ))
+                                 ) ORDER BY DECODE(alphabetic_order_in,'Y',procedure_name,object_id))
              &end_ge_9
          LOOP
             addtest (

[Paul Walker]

Thanks David, I'll try to review and test this shortly

[Paul Walker]

I haven't gotten round to testing this yet, but I see the ordering is controlled by a parameter. I'm wondering if it needs to be? Is there any reason why anyone would set it to "No"?

[David Pyke Le Brun]

yes i beleive so..
i had a reason.. but i think it should probably be controlled by a config option.

i saw the case where you wanted to run in a specific order but didnt/couldnt name your procedures correctly and so you rely on physical ordering.

i dont mind personally.. i prefer alphabetic.. but i dont mind if you want to just fix it as non changeable.

[Paul Walker]

Applied David's patch (above) to the branch in [r155]

[Paul Walker]

Just a point to note that without an explicit ORDER BY (as was the case before this patch), no assumption can be made about the order the rows will be returned. Therefore there is no "default" order:

• The Tom Kyte Blog: Order in the court!
• Ask Tom "Does a Group By gaurantee that output da..."

[Paul Walker]

I've removed the parameter to control this - as noted above, there is no guaranteed "default order" in Oracle anyway. Committed to the branch in [r156].

Edit: I accidentally left an extra bracket in [r156] - corrected in [r157]

[Paul Walker]

[r158] tidies up a bit of spacing that got changed in [r155]

[Paul Walker]

I've tested this and it appears to work as expected. Merged into trunk, ready for release. [r159]