utPLSQL · jgebal · Feb 23, 2020 · Nov 10, 2019 · Nov 10, 2019 · Nov 10, 2019
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -119,8 +119,6 @@ create or replace package body ut_compound_data_helper is
               || case when a_order_enforced then q'[
               --column position is not matching (both when excluded extra/missing columns as well as when they are included)
               or (a_pos_nn != e_pos_nn and exp_col_pos != act_col_pos)]'
-              else
-                null
               end ||q'[
            order by exp_col_pos, act_col_pos]'
       bulk collect into l_results using a_expected, a_actual;
@@ -417,73 +415,100 @@ create or replace package body ut_compound_data_helper is
     l_join_xpath     varchar2(32767) := ut_utils.to_xpath(a_join_by_list);
     l_results        tt_row_diffs;
     l_sql            varchar2(32767);
+    l_column_order   varchar2(100);
   begin
+    if a_enforce_column_order then
+      l_column_order := 'col_no';
+    else
+      l_column_order := 'col_name';
+    end if;
     l_sql := q'[
-    with exp as (
+    with
+    exp_cols as (
       select
-          exp_item_data, exp_data_id, item_no rn, rownum col_no, pk_value,
+          i.exp_item_data, i.exp_data_id, i.item_no rn, rownum col_no, i.diff_id,
           s.column_value col, s.column_value.getRootElement() col_name,
-          nvl(s.column_value.getclobval(),empty_clob()) col_val
+          nvl( s.column_value.getclobval(), empty_clob() ) col_val
         from (
           select
-              exp_data_id, extract( ucd.exp_item_data, :column_path ) exp_item_data, item_no,
-              replace( extract( ucd.exp_item_data, :join_by ).getclobval(), chr(10) ) pk_value
-            from ut_compound_data_diff_tmp  ucd
-           where diff_id = :diff_id
+              ucd.exp_data_id, extract( ucd.exp_item_data, :column_path ) exp_item_data,
+              ucd.item_no, ucd.diff_id
+            from ut_compound_data_diff_tmp ucd
+           where ucd.diff_id = :diff_id
              and ucd.exp_data_id = :self_guid
           ) i,
-          table( xmlsequence( extract(i.exp_item_data,:extract_path) ) ) s
+          table( xmlsequence( extract( i.exp_item_data, :extract_path ) ) ) s
     ),
-    act as (
+    act_cols as (
       select
-          act_item_data, act_data_id, item_no rn, rownum col_no, pk_value,
+          i.act_item_data, i.act_data_id, i.item_no rn, rownum col_no, i.diff_id,
           s.column_value col, s.column_value.getRootElement() col_name,
-          nvl(s.column_value.getclobval(),empty_clob()) col_val
+          nvl( s.column_value.getclobval(), empty_clob() ) col_val
         from (
           select
-              act_data_id, extract( ucd.act_item_data, :column_path ) act_item_data, item_no,
-              replace( extract( ucd.act_item_data, :join_by ).getclobval(), chr(10) ) pk_value
-            from ut_compound_data_diff_tmp  ucd
-           where diff_id = :diff_id
+              ucd.act_data_id, extract( ucd.act_item_data, :column_path ) act_item_data,
+              ucd.item_no, ucd.diff_id
+            from ut_compound_data_diff_tmp ucd
+           where ucd.diff_id = :diff_id
              and ucd.act_data_id = :other_guid
           ) i,
-          table( xmlsequence( extract(i.act_item_data,:extract_path) ) ) s
+          table( xmlsequence( extract( i.act_item_data, :extract_path ) ) ) s
+    ),
+    data_diff as (
+      select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, null pk_value, col_name, diff_id
+        from (
+          select nvl(exp.rn, act.rn) rn,
+                 exp.diff_id diff_id,
+                 xmlagg(exp.col order by exp.col_no) exp_item,
+                 xmlagg(act.col order by nvl(exp.col_no, act.col_no)) act_item,
+                 max(nvl(exp.col_name,act.col_name)) col_name
+            from exp_cols exp
+            join act_cols act
+              on exp.rn = act.rn and exp.col_name = act.col_name
+           where dbms_lob.compare(exp.col_val, act.col_val) != 0
+           group by nvl(exp.rn, act.rn), exp.diff_id
+        )
+      unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') )
+    ),
+    unordered_diff as (
+      select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, null pk_value, col_name, diff_id
+        from (
+          select nvl(exp.rn, act.rn) rn,
+                 exp.diff_id diff_id,
+                 xmlagg(exp.col order by exp.col_name) exp_item,
+                 xmlagg(act.col order by act.col_name) act_item,
+                 max(nvl(exp.col_name,act.col_name)) col_name
+            from exp_cols exp
+            join act_cols act
+              on exp.rn = act.rn and exp.col_name = act.col_name
+           where dbms_lob.compare(exp.col_val, act.col_val) != 0
+           group by nvl(exp.rn, act.rn), exp.diff_id
+        )
+      unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') )
     )
-    select rn, diff_type, diffed_row, pk_value pk_value
+    select rn, diff_type, diffed_row, pk_value
     from (
       select rn, diff_type, diffed_row, pk_value,
              case when diff_type = 'Actual:' then 1 else 2 end rnk,
              1 final_order,
              col_name
       from ( ]'
         || case when a_unordered then q'[
-        select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, pk_value, col_name
-          from (
-            select nvl(exp.rn, act.rn) rn,
-                   nvl(exp.pk_value, act.pk_value) pk_value,
-                   exp.col exp_item,
-                   act.col act_item,
-                   nvl(exp.col_name,act.col_name) col_name
-              from exp
-              join act
-                on exp.rn = act.rn and exp.col_name = act.col_name
-             where dbms_lob.compare(exp.col_val, act.col_val) != 0
-          )
-        unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') ) ]'
+        select /*+ no_unnest */
+               u.rn, u.diff_type, u.diffed_row,
+               replace(
+                 extract( case when i.exp_data_id is null then i.act_item_data else i.exp_item_data end, :join_by ).getclobval(),
+                  chr(10)
+               ) pk_value,
+               u.col_name
+          from data_diff u
+          join ut_compound_data_diff_tmp i
+              on i.diff_id = u.diff_id
+             and i.item_no = u.rn]'
         else q'[
-        select rn, diff_type, xmlserialize(content data_item no indent) diffed_row, null pk_value, col_name
-          from (
-            select nvl(exp.rn, act.rn) rn,
-                   xmlagg(exp.col order by exp.col_no) exp_item,
-                   xmlagg(act.col order by act.col_no) act_item,
-                   max(nvl(exp.col_name,act.col_name)) col_name
-              from exp exp
-              join act act
-                on exp.rn = act.rn and exp.col_name = act.col_name
-             where dbms_lob.compare(exp.col_val, act.col_val) != 0
-             group by (exp.rn, act.rn)
-          )
-        unpivot ( data_item for diff_type in (exp_item as 'Expected:', act_item as 'Actual:') ) ]'
+        select rn, diff_type, diffed_row, pk_value, col_name
+          from data_diff
+         where :join_by is null ]'
         end ||q'[
       )
       union all
@@ -522,14 +547,12 @@ create or replace package body ut_compound_data_helper is
          case when final_order = 1 then to_char(rn) else col_name end,
          case when final_order = 1 then to_char(rnk) else col_name end
      ]'
-   else
-     null
    end;
    execute immediate l_sql
    bulk collect into l_results
-    using l_exp_extract_xpath, l_join_xpath, a_diff_id, a_expected_dataset_guid,a_extract_path,
-          l_act_extract_xpath, l_join_xpath, a_diff_id, a_actual_dataset_guid,a_extract_path,
-          l_join_xpath, l_join_xpath, a_diff_id;    
+    using l_exp_extract_xpath, a_diff_id, a_expected_dataset_guid, a_extract_path,
+          l_act_extract_xpath, a_diff_id, a_actual_dataset_guid, a_extract_path,
+          l_join_xpath, l_join_xpath, l_join_xpath, a_diff_id;
     return l_results;
   end;
 

diff --git a/test/ut3_user/expectations/test_expectation_anydata.pkb b/test/ut3_user/expectations/test_expectation_anydata.pkb
@@ -864,10 +864,8 @@ Rows: [ 60 differences, showing first 20 ]
     l_expected_message := q'[%Actual: ut3_tester_helper.test_dummy_object_list [ count = 2 ] was expected to equal: ut3_tester_helper.test_dummy_object_list [ count = 2 ]
 %Diff:
 %Rows: [ 3 differences ]
-%PK <ID>2</ID> - Actual:   <name>Something 2</name>
-%PK <ID>2</ID> - Actual:   <Value>2</Value>
-%PK <ID>2</ID> - Expected: <name>Something 1</name>
-%PK <ID>2</ID> - Expected: <Value>1</Value>
+%PK <ID>2</ID> - Actual:   <name>Something 2</name><Value>2</Value>
+%PK <ID>2</ID> - Expected: <name>Something 1</name><Value>1</Value>
 %PK <ID>1</ID> - Extra:    <TEST_DUMMY_OBJECT><ID>1</ID><name>Something 1</name><Value>1</Value></TEST_DUMMY_OBJECT>
 %PK <ID>4</ID> - Missing:  <TEST_DUMMY_OBJECT><ID>4</ID><name>Something 2</name><Value>2</Value></TEST_DUMMY_OBJECT>]';
     l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);

diff --git a/test/ut3_user/expectations/test_expectations_cursor.pkb b/test/ut3_user/expectations/test_expectations_cursor.pkb
@@ -371,7 +371,7 @@ create or replace package body test_expectations_cursor is
     l_expected_message := q'[Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
 %Diff:
 %Rows: [ 1 differences ]
-%Row No. 1 - Actual:   <COL_4>40</COL_4><COL_3>30</COL_3>
+%Row No. 1 - Actual:   <COL_3>30</COL_3><COL_4>40</COL_4>
 %Row No. 1 - Expected: <COL_3>3</COL_3><COL_4>4</COL_4>]';
     l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
     --Assert
@@ -394,7 +394,7 @@ create or replace package body test_expectations_cursor is
     l_expected_message := q'[Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
 %Diff:
 %Rows: [ 1 differences ]
-%Row No. 1 - Actual:   <COL_4>40</COL_4><COL_3>30</COL_3>
+%Row No. 1 - Actual:   <COL_3>30</COL_3><COL_4>40</COL_4>
 %Row No. 1 - Expected: <COL_3>3</COL_3><COL_4>4</COL_4>]';
     l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
     --Assert
@@ -860,9 +860,9 @@ Columns:
 Rows: [ 4 differences ]
   Row No. 1 - Actual:   <SALARY>25000</SALARY>
   Row No. 1 - Expected: <SALARY>10000</SALARY>
-  Row No. 2 - Actual:   <FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><ID>3</ID><SALARY>100000</SALARY>
+  Row No. 2 - Actual:   <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
   Row No. 2 - Expected: <ID>2</ID><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><SALARY>1000</SALARY>
-  Row No. 3 - Actual:   <FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><ID>4</ID><SALARY>2345</SALARY>
+  Row No. 3 - Actual:   <ID>4</ID><FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><SALARY>2345</SALARY>
   Row No. 3 - Expected: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
   Row No. 4 - Extra:    <GENDER>M</GENDER><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><ID>2</ID><SALARY>1000</SALARY>]';
     l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
@@ -896,9 +896,9 @@ Columns:
 Rows: [ 4 differences ]
   Row No. 1 - Actual:   <SALARY>25000</SALARY>
   Row No. 1 - Expected: <SALARY>10000</SALARY>
-  Row No. 2 - Actual:   <FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><ID>3</ID><SALARY>100000</SALARY>
+  Row No. 2 - Actual:   <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
   Row No. 2 - Expected: <ID>2</ID><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><SALARY>1000</SALARY>
-  Row No. 3 - Actual:   <FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><ID>4</ID><SALARY>2345</SALARY>
+  Row No. 3 - Actual:   <ID>4</ID><FIRST_NAME>JESSICA</FIRST_NAME><LAST_NAME>JONES</LAST_NAME><SALARY>2345</SALARY>
   Row No. 3 - Expected: <ID>3</ID><FIRST_NAME>TONY</FIRST_NAME><LAST_NAME>STARK</LAST_NAME><SALARY>100000</SALARY>
   Row No. 4 - Extra:    <GENDER>M</GENDER><FIRST_NAME>LUKE</FIRST_NAME><LAST_NAME>SKYWALKER</LAST_NAME><ID>2</ID><SALARY>1000</SALARY>]';
     l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
@@ -1237,10 +1237,8 @@ Rows: [ 4 differences ]
     l_expected_message := q'[Actual: refcursor [ count = 1 ] was expected to equal: refcursor [ count = 1 ]
 %Diff:
 %Rows: [ 1 differences ]
-%PK <COL_1>1</COL_1> - Actual:   <COL_3>30</COL_3>
-%PK <COL_1>1</COL_1> - Expected: <COL_3>3</COL_3>
-%PK <COL_1>1</COL_1> - Actual:   <COL_4>40</COL_4>
-%PK <COL_1>1</COL_1> - Expected: <COL_4>4</COL_4>]';
+%PK <COL_1>1</COL_1> - Actual:   <COL_3>30</COL_3><COL_4>40</COL_4>
+%PK <COL_1>1</COL_1> - Expected: <COL_3>3</COL_3><COL_4>4</COL_4>]';
     l_actual_message := ut3_tester_helper.main_helper.get_failed_expectations(1);
     --Assert
     ut.expect(l_actual_message).to_be_like(l_expected_message);
@@ -2511,7 +2509,7 @@ Diff:%
   begin
     l_exp_message :='ORA-20218: SQL exception thrown when fetching data from cursor:
 ORA-01476: divisor is equal to zero
-at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line 2522 ut3.ut.expect(l_actual).to_equal(l_expected);%
+at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line % ut3.ut.expect(l_actual).to_equal(l_expected);%
 Check the query and data for errors.';
 
     open l_actual for
@@ -2536,7 +2534,7 @@ Check the query and data for errors.';
 
     l_exp_message :='ORA-20218: SQL exception thrown when fetching data from cursor:
 ORA-01476: divisor is equal to zero
-at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line 2547 ut3.ut.expect(l_actual).to_equal(l_expected);%
+at "UT3$USER#.TEST_EXPECTATIONS_CURSOR%", line % ut3.ut.expect(l_actual).to_equal(l_expected);%
 Check the query and data for errors.';
 
     open l_expected for