Merge pull request #1295 from utPLSQL/feature/issue_1293

jgebal · web-flow · commit e872b304608f · 2025-04-03T21:20:11.000+02:00
Enable duplicate detection on cursor comparison using join by
diff --git a/source/expectations/data_values/ut_compound_data_helper.pkb b/source/expectations/data_values/ut_compound_data_helper.pkb
@@ -300,8 +300,8 @@ create or replace package body ut_compound_data_helper is
       ut_utils.append_to_clob(a_partition_stmt,' row_number() over (partition by '||l_partition_tmp||' order by '||l_partition_tmp||' ) ');
       
       if a_pk_table.count > 0 then   
-        -- If key defined do the join or these and where on diffrences
-        a_join_by_stmt := ut_utils.table_to_clob(l_join_by_list, ' and ');
+        -- If key defined do the join or these and where on diffrences as well as on duplicate number when rows are same.
+        a_join_by_stmt := ' e."UT3$_Dup#No" = a."UT3$_Dup#No" and '||ut_utils.table_to_clob(l_join_by_list, ' and ');
       elsif a_unordered then
         -- If no key defined do the join on all columns
         a_join_by_stmt := ' e."UT3$_Dup#No" = a."UT3$_Dup#No" and '||ut_utils.table_to_clob(l_equal_list, ' and ');
diff --git a/test/ut3_user/expectations/test_expectations_cursor.pkb b/test/ut3_user/expectations/test_expectations_cursor.pkb
@@ -2950,5 +2950,47 @@ Rows: [ 2 differences ]
     );
   end;
 
+  procedure cr_joinby_compare_issue_1293 is
+    l_actual   sys_refcursor;
+    l_expected sys_refcursor;
+  begin
+    --Arrange
+    open l_expected for 
+      select 'FOO'  username, 12 from dual union all
+      select 'TEST' username, -600 user_id from dual
+      order by 1 desc;
+    open l_actual for 
+      select 'FOO'  username, 12 from dual union all
+      select 'TEST' username, -600 user_id from dual union all
+      -- DUPLICATE!!!
+      select 'TEST' username, -600 user_id from dual
+      order by 1 asc; 
+    --Act
+    ut3_develop.ut.expect(l_actual).to_equal(l_expected).join_by('USERNAME');
+    --Assert
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
+  end;
+
+  procedure cr_not_joinby_comp_issue_1293 is
+    l_actual   sys_refcursor;
+    l_expected sys_refcursor;
+  begin
+    --Arrange
+    open l_expected for 
+      select 'FOO'  username, 12 from dual union all
+      select 'TEST' username, -600 user_id from dual
+      order by 1 desc;
+    open l_actual for 
+      select 'FOO'  username, 12 from dual union all
+      select 'TEST' username, -600 user_id from dual union all
+      -- DUPLICATE!!!
+      select 'TEST' username, -600 user_id from dual
+      order by 1 asc; 
+    --Act
+    ut3_develop.ut.expect(l_actual).to_equal(l_expected);
+    --Assert
+    ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
+  end;
+
 end;
 /
diff --git a/test/ut3_user/expectations/test_expectations_cursor.pks b/test/ut3_user/expectations/test_expectations_cursor.pks
@@ -485,5 +485,11 @@ create or replace package test_expectations_cursor is
   --%test( Multiple failures reported correctly - Issue #998 )
   procedure multiple_cursor_expectations;
 
+  --%test( Compares cursors with duplicate rows using join by - Issue #1293 )
+  procedure cr_joinby_compare_issue_1293;
+
+  --%test( Compares cursors with duplicate rows - Issue #1293 )
+  procedure cr_not_joinby_comp_issue_1293;
+
 end;
 /
