utPLSQL JSON test cases fail under Oracle DB 21.3

[PhilippSalvisberg]

Describe the bug

The following two JSON related test cases fail when running under Oracle Database 21.3:

• utplsql.test_user.expectations.test_expectations_json.fail_on_diff_data
• UT3$USER#:utplsql.test_user.expectations.test_expectations_json.long_json_diff

All other tests complete successful as under 19c.

Provide version info

21.0.0.0.0
21.0.0


PL/SQL procedure successfully completed.


UT_VERSION                                                  
------------------------------------------------------------
v3.1.11.3475-develop


BANNER                                                                           BANNER_FULL                                                                                                                                                      BANNER_LEGACY                                                                        CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production           Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production                                                                                           Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production                    0
                                                                                 Version 21.3.0.0.0                                                                                                                                                                                                                                          



PARAMETER                      VALUE                                                           
------------------------------ ----------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN                                                        
NLS_TERRITORY                  AMERICA                                                         
NLS_CURRENCY                   $                                                               
NLS_ISO_CURRENCY               AMERICA                                                         
NLS_NUMERIC_CHARACTERS         .,                                                              
NLS_CALENDAR                   GREGORIAN                                                       
NLS_DATE_FORMAT                DD.MM.YYYY                                                      
NLS_DATE_LANGUAGE              AMERICAN                                                        
NLS_SORT                       BINARY                                                          
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                  
NLS_TIMESTAMP_FORMAT           DD.MM.YYYY HH24:MI:SSXFF                                        

PARAMETER                      VALUE                                                           
------------------------------ ----------------------------------------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                              
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.YYYY HH24:MI:SSXFF TZR                                    
NLS_DUAL_CURRENCY              $                                                               
NLS_COMP                       BINARY                                                          
NLS_LENGTH_SEMANTICS           BYTE                                                            
NLS_NCHAR_CONV_EXCP            FALSE                                                           

17 rows selected. 


PORT_STRING                                                 
------------------------------------------------------------
x86_64/Linux 2.4.xx

Information about client software

SQL Developer 21.2.1. But this does not really matter. It's reproducible with any client.

To Reproduce

Steps to reproduce the behavior:

1. Clone the current develop branch
2. Install the develop version, I've done the following:

cd /.../github/utPLSQL
git fetch --all
git pull
chmod +x development/*.sh
chmod +x test/*.sh
chmod +x .travis/*.sh
echo "export CONNECTION_STR=127.0.0.1:1522/opdb1.docker" >> development/env.sh
. development/env.sh
development/cleanup.sh
development/refresh_ut3.sh
development/refresh_sources.sh
development/install.sh
test/install_and_run_tests.sh

3. You then see two failed tests as a result of running install_and_run_tests.sh.

You can also run the following block as user ut3_tester_helper in a SQL client:

set serveroutput on size unlimited
begin
   ut.run(
      ut_varchar2_list(
         'UT3$USER#:utplsql.test_user.expectations.test_expectations_json.fail_on_diff_data',
         'UT3$USER#:utplsql.test_user.expectations.test_expectations_json.long_json_diff'
      )
   );
end;
/

The result is:

utplsql
  test_user
    expectations
      json expectations
        Long complex json differences [.288 sec] (FAILED - 1)
 
utplsql
  test_user
    expectations
      json expectations
        Gives failure for different data [.051 sec] (FAILED - 2)
 
Failures:
 
  1) long_json_diff
      Actual: (varchar2)
          'Actual: json was expected to equal: json
          Diff: 133 differences found, showing first 20
          1 missing properties, 132 unequal values
            Extra   property: object on path: $[5]
            Actual value: "5ce6ec46cb9977b050f15d97" was expected to be: "5ce6ec6660565269b16cf836" on path: $[0]."_id"
            Actual value: "5ce6ec469ba57bef5c421021" was expected to be: "5ce6ec66383ddbf3c400e3ed" on path: $[1]."_id"
            Actual value: "5ce6ec4632328a654d592cb6" was expected to be: "5ce6ec6600fb7aaee2d1243e" on path: $[3]."_id"
            Actual value: "5ce6ec464e6f8751e75ed29f" was expected to be: "5ce6ec660a8b5f95ed543305" on path: $[2]."_id"
            Actual value: "5ce6ec46d9dbfbf9b184cee7" was expected to be: "5ce6ec660585cbb589b34fc8" on path: $[4]."_id"
            Actual value: "59be5b73-fffe-4a4f-acea-65c5abbdb53c" was expected to be: "2e778803-50d3-411f-b34d-47d0f19d03f7" on path: $[1]."guid"
            Actual value: "9dece65b-6b48-4960-880b-7795ff63c81c" was expected to be: "18547241-6fd0-466d-9f79-21aeb0485294" on path: $[4]."guid"
            Actual value: "42e07b71-b769-4078-b226-f79048b75bd2" was expected to be: "bb0eaa88-f7fd-4b72-8538-8c0b4595bcec" on path: $[2]."guid"
            Actual value: "6b9124a9-fbde-4c60-8dac-e296f5daa3c4" was expected to be: "4a4363b5-9d65-4b22-9b58-a5c8c1c5bd5d" on path: $[3]."guid"
            Actual value: "1acb2b6b-15b5-4747-a62f-db477e18df61" was expected to be: "c222eda5-d925-4163-89e3-4b0e50d5e297" on path: $[0]."guid"
            Actual value: FALSE was expected to be: TRUE on path: $[2]."isActive"
            Actual value: TRUE was expected to be: FALSE on path: $[3]."isActive"
            Actual value: TRUE was expected to be: FALSE on path: $[1]."isActive"
            Actual value: "$3,895.35" was expected to be: "$2,299.28" on path: $[1]."balance"
            Actual value: "$1,443.80" was expected to be: "$3,626.25" on path: $[0]."balance"
            Actual value: "$3,366.81" was expected to be: "$3,085.28" on path: $[2]."balance"
            Actual value: "$2,927.54" was expected to be: "$3,853.86" on path: $[4]."balance"
            Actual value: "$2,374.96" was expected to be: "$3,152.70" on path: $[3]."balance"
            Actual value: 23 was expected to be: 36 on path: $[2]."age"'
       was expected to be like: 
          '%Diff: 133 differences found, showing first 20
          %132 unequal values, 1 missing properties
          %Extra   property: object on path: $[5]
          %Actual value: "5ce6ec46cb9977b050f15d97" was expected to be: "5ce6ec6660565269b16cf836" on path: $[0]."_id"
          %Actual value: "5ce6ec469ba57bef5c421021" was expected to be: "5ce6ec66383ddbf3c400e3ed" on path: $[1]."_id"
          %Actual value: "5ce6ec4632328a654d592cb6" was expected to be: "5ce6ec6600fb7aaee2d1243e" on path: $[3]."_id"
          %Actual value: "5ce6ec464e6f8751e75ed29f" was expected to be: "5ce6ec660a8b5f95ed543305" on path: $[2]."_id"
          %Actual value: "5ce6ec46d9dbfbf9b184cee7" was expected to be: "5ce6ec660585cbb589b34fc8" on path: $[4]."_id"
          %Actual value: "59be5b73-fffe-4a4f-acea-65c5abbdb53c" was expected to be: "2e778803-50d3-411f-b34d-47d0f19d03f7" on path: $[1]."guid"
          %Actual value: "9dece65b-6b48-4960-880b-7795ff63c81c" was expected to be: "18547241-6fd0-466d-9f79-21aeb0485294" on path: $[4]."guid"
          %Actual value: "42e07b71-b769-4078-b226-f79048b75bd2" was expected to be: "bb0eaa88-f7fd-4b72-8538-8c0b4595bcec" on path: $[2]."guid"
          %Actual value: "6b9124a9-fbde-4c60-8dac-e296f5daa3c4" was expected to be: "4a4363b5-9d65-4b22-9b58-a5c8c1c5bd5d" on path: $[3]."guid"
          %Actual value: "1acb2b6b-15b5-4747-a62f-db477e18df61" was expected to be: "c222eda5-d925-4163-89e3-4b0e50d5e297" on path: $[0]."guid"
          %Actual value: FALSE was expected to be: TRUE on path: $[2]."isActive"
          %Actual value: TRUE was expected to be: FALSE on path: $[3]."isActive"
          %Actual value: TRUE was expected to be: FALSE on path: $[1]."isActive"
          %Actual value: "$3,895.35" was expected to be: "$2,299.28" on path: $[1]."balance"
          %Actual value: "$1,443.80" was expected to be: "$3,626.25" on path: $[0]."balance"
          %Actual value: "$3,366.81" was expected to be: "$3,085.28" on path: $[2]."balance"
          %Actual value: "$2,927.54" was expected to be: "$3,853.86" on path: $[4]."balance"
          %Actual value: "$2,374.96" was expected to be: "$3,152.70" on path: $[3]."balance"
          %Actual value: 23 was expected to be: 36 on path: $[2]."age"%'
      at "UT3$USER#.TEST_EXPECTATIONS_JSON.LONG_JSON_DIFF", line 1584 ut.expect(l_actual_message).to_be_like(l_expected_message);
       
  2) fail_on_diff_data
      Actual: (varchar2)
          'Actual: json was expected to equal: json
          Diff: 20 differences found
          13 missing properties, 3 incorrect types, 4 unequal values
            Missing property: "Alexander Skarsg?rd" on path: $
            Extra   property: "Alexander Skarsgard" on path: $
            Missing property: "Alice Farmer" on path: $
            Extra   property: "Clarke Peters" on path: $
            Extra   property: "one" on path: $."Amy Ryan"
            Missing property: "The Sopranos" on path: $."Annie Fitzgerald"[2]
            Extra   property: "two" on path: $."Amy Ryan"
            Missing property: "Oz" on path: $."Annie Fitzgerald"[3]
            Missing property: "otherint" on path: $."Aidan Gillen"
            Extra   property: "object1" on path: $."Aidan Gillen"."object"
            Extra   property: "object2" on path: $."Aidan Gillen"."object"
            Extra   property: "object3" on path: $."Aidan Gillen"."object"
            Extra   property: "object4" on path: $."Aidan Gillen"."object"
            Actual  type: 'array' was expected to be: 'object' on path: $."Amy Ryan"
            Actual  type: 'string' was expected to be: 'number' on path: $."Aidan Gillen"."int"
            Actual  type: 'string' was expected to be: 'boolean' on path: $."Aidan Gillen"."aboolean"
            Actual value: "True Blood" was expected to be: "Big Love" on path: $."Annie Fitzgerald"[0]
            Actual value: "Big Love" was expected to be: "True Blood" on path: $."Annie Fitzgerald"[1]
            Actual value: FALSE was expected to be: TRUE on path: $."Aidan Gillen"."boolean"
            Actual value: "Game of Thrones" was expected to be: "Game of Thron\"es" on path: $."Aidan Gillen"."array"[0]'
       was expected to be like: 
          '%Diff: 20 differences found
          %3 incorrect types, 4 unequal values, 13 missing properties
          %Missing property: "Alexander Skarsg?rd" on path: $
          %Extra   property: "Alexander Skarsgard" on path: $
          %Missing property: "Alice Farmer" on path: $
          %Extra   property: "Clarke Peters" on path: $
          %Extra   property: "one" on path: $."Amy Ryan"
          %Missing property: "The Sopranos" on path: $."Annie Fitzgerald"[2]
          %Extra   property: "two" on path: $."Amy Ryan"
          %Missing property: "Oz" on path: $."Annie Fitzgerald"[3]
          %Missing property: "otherint" on path: $."Aidan Gillen"
          %Extra   property: "object1" on path: $."Aidan Gillen"."object"
          %Extra   property: "object2" on path: $."Aidan Gillen"."object"
          %Extra   property: "object3" on path: $."Aidan Gillen"."object"
          %Extra   property: "object4" on path: $."Aidan Gillen"."object"
          %Actual  type: 'array' was expected to be: 'object' on path: $."Amy Ryan"
          %Actual  type: 'string' was expected to be: 'number' on path: $."Aidan Gillen"."int"
          %Actual  type: 'string' was expected to be: 'boolean' on path: $."Aidan Gillen"."aboolean"
          %Actual value: "True Blood" was expected to be: "Big Love" on path: $."Annie Fitzgerald"[0]
          %Actual value: "Big Love" was expected to be: "True Blood" on path: $."Annie Fitzgerald"[1]
          %Actual value: FALSE was expected to be: TRUE on path: $."Aidan Gillen"."boolean"
          %Actual value: "Game of Thrones" was expected to be: "Game of Thron\"es" on path: $."Aidan Gillen"."array"[0]%'
      at "UT3$USER#.TEST_EXPECTATIONS_JSON.FAIL_ON_DIFF_DATA", line 95 ut.expect(l_actual_message).to_be_like(l_expected_message);
       
Finished in .376903 seconds
2 tests, 2 failed, 0 errored, 0 disabled, 0 warning(s)
 


PL/SQL procedure successfully completed.

Expected behavior

All enable tests complete successful under Oracle Database 21.3.

[jgebal]

Resolved by: #1158