Tests are run even when annotations are removed

[tredston]

Describe the bug Tests are not evicted from the caches when annotations are removed from packages.

Provide version info oracle 19c utplsql v3.1.10.3349

Information about client software SQLDeveloper

To Reproduce With utplsql2 and utplsql3 installed, run:

CREATE OR REPLACE PACKAGE UT_TESTS
AS
    --%suite

    --%test()
    PROCEDURE ut_test;
END;
/
CREATE OR REPLACE PACKAGE BODY UT_TESTS AS
 PROCEDURE ut_test IS
 BEGIN
   ut.expect(TRUE).to_be_true();
 END;
END;
/

set serverout on;
begin
ut.run('ut_tests');
end;
/

CREATE OR REPLACE PACKAGE UT_TESTS
AS
    PROCEDURE ut_test;
END;
/
CREATE OR REPLACE PACKAGE BODY UT_TESTS AS

 PROCEDURE ut_test IS
 BEGIN
   utAssert.eq('error', TRUE, TRUE);
 END;

END;
/
begin
ut.run('ut_tests');
end;
/

It produces the following error:

ut_tests
  ut_test [.004 sec] (FAILED - 1)

Failures:

  1) ut_test
      ORA-02291: integrity constraint (SYSTEM.UTR_ERROR_OUTCOME_FK) violated - parent key not found
      ORA-06512: at "SYSTEM.UTRERROR", line 149
      ORA-06512: at "SYSTEM.UTRERROR", line 103
      ORA-06512: at "SYSTEM.UTRERROR", line 324
      ORA-06512: at "SYSTEM.UTROUTCOME", line 146
      ORA-01400: cannot insert NULL into ("SYSTEM"."UTR_OUTCOME"."RUN_ID")
      ORA-06512: at "SYSTEM.UTROUTCOME", line 125
      ORA-06512: at "SYSTEM.UTRESULT2", line 75
      ORA-06512: at "SYSTEM.UTASSERT2", line 140
      ORA-06512: at "SYSTEM.UTASSERT2", line 604
      ORA-06512: at "SYSTEM.UTASSERT", line 141
      ORA-06512: at "DRIAD.UT_TESTS", line 5
      ORA-06512: at "SYSTEM.UTRERROR", line 149
      ORA-06512: at "SYSTEM.UTRERROR", line 103
      ORA-06512: at "SYSTEM.UTRERROR", line 324
      ORA-06512: at "SYSTEM.UTROUTCOME", line 146
      ORA-06512: at "SYSTEM.UTROUTCOME", line 125
      ORA-06512: at "SYSTEM.UTRESULT2", line 75
      ORA-06512: at "SYSTEM.UTASSERT2", line 140
      ORA-06512: at "SYSTEM.UTASSERT2", line 604
      ORA-06512: at "SYSTEM.UTASSERT", line 141
      ORA-06512: at "DRIAD.UT_TESTS", line 5
      ORA-06512: at line 6
Finished in .005519 seconds
1 tests, 0 failed, 1 errored, 0 disabled, 0 warning(s)

Expected behavior Packages without annotations should not be run as utplsql3 tests.

Additional context We've run in to this while migrating from utplsqlv2 to v3 and it's causing failures in our CI system.

We have been performing the migration in a feature branch. However, tests we've updated to use v3 annotations in the feature branch are now failing in develop as they're picked up by ut.run() (due to being added to the annotation/suite caches by the feature branch) but the test body is still utplsql2.

Seems like ut_annotation_manager.get_sources_to_annotate should evict the objects to refresh if no sources are found.

[jgebal]

Are you're using our migration/bridge solution for v2-v3 migration?

I also noticed that your utplsql v2 is installed in SYSTEM schema. This is really really dangerous. You should never put any code into SYSTEM schema.

Having that said, I'll try to reproduce it on my local machine and see what the cause is.

One key question is: Did you install utplsql v3 with DDL trigger or without?

[tredston]

We haven't been using the migration tool but will evaluate it going forward, thanks.

Yeah, we are planning to remove v2 after the migration and it seems like wasted effort to move it to a different schema now. v3 is installed in its own user as recommended ;)

We have the DDL trigger installed

[tredston]

I have a simplified test case: With utplsqlv3 + DDL trigger installed, run:

CREATE OR REPLACE PACKAGE UT_TESTS
AS
    --%suite

    --%test()
    PROCEDURE ut_test;
END;
/
CREATE OR REPLACE PACKAGE BODY UT_TESTS AS
 PROCEDURE ut_test IS
 BEGIN
   ut.expect(TRUE).to_be_true();
 END;
END;
/

set serverout on;
begin
ut.run('ut_tests');
end;
/

CREATE OR REPLACE PACKAGE UT_TESTS
AS

    PROCEDURE ut_test;
END;
/
CREATE OR REPLACE PACKAGE BODY UT_TESTS AS

 PROCEDURE ut_test IS
 BEGIN
   null;
 END;

END;
/
begin
ut.run('ut_tests');
end;
/

Which produces:

Package UT_TESTS compiled

Package Body UT_TESTS compiled

ut_tests
  ut_test [.003 sec]

Finished in .004088 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

PL/SQL procedure successfully completed.

Package UT_TESTS compiled

Package Body UT_TESTS compiled

ut_tests
  ut_test [.003 sec]

Finished in .00412 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

PL/SQL procedure successfully completed.

I would expect the second run to have executed no tests as the package is not annotated as such.

[lwasylow]

@jgebal I had a quick look and seems like procedure ut_annotation_cache_manager.update_cache will remove data only when the l_cache_id is populated, however this not getting populated when event trigger is not null. The condition for rebuild based on trigger is removing only when action is DROP which is not necessary correct as we can see above. I think we need to also put a condition that there are no annotation and object is in cache should be removed.