Description

DLakomy

opened on Mar 29, 2025

Issue body actions

Describe the bug
When I compare two cursors using .join_by the duplicate rows are not detected (I'm attaching an example code).

Provide version info
Oracle Database 23ai Free Release 23.0.0.0.0
utPLSQL version: v3.1.14.4197
NLS: Polish
OS: x86_64/Linux 2.4.xx

Information about client software
Client: SQLDeveloper

To Reproduce
Steps to reproduce the behavior:

1. Prepare two identical cursors with a column you can join on.
2. Ensure one of the cursors has an additional row, identical to the ones already present.
3. Run ut.expect to compare them using .join_by(...).
4. See a message (via dbms_output) indicating a SUCCESS, yet clearly stating different rowcounts.

Expected behavior
The expectation should result in a FAILURE, not a SUCCESS. The rowcounts are clearly different, so the cursors don't yield the same rows, hence are not equal.

Example code

set serveroutput on
declare
  l_actual   sys_refcursor;
  l_expected sys_refcursor;
begin
  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;
  ut.expect( l_actual ).to_equal( l_expected ).join_by('USERNAME');
end;
/
--------
/* The result is:
SUCCESS
  Actual: refcursor [ count = 3 ] was expected to equal: refcursor [ count = 2 ]
*/

Activity

1. lwasylow commented on Mar 29, 2025

   

   lwasylow

   on Mar 29, 2025

   Member

   More actions

   Hmm that's interesting one.
   According to
   You can join two compound data types by defining join column(s) that will be used to uniquely identify and compare data rows.
   But you created scenario where rows cannot be uniquely identified.

   @jgebal do you remember what was a intended design?
   Sounds like there should be row number added possibly to comparison.
   In practice two cursor are different in theory bot sets are same :)
2. lwasylow

   self-assigned this

   on Mar 29, 2025
3. jgebal commented on Mar 29, 2025

   

   jgebal

   on Mar 29, 2025

   Member

   More actions

   It should fail.
   The result is clearly wrong.
   Even the count is incorrect.
   The row count itself should be sufficient for the test to fail

   Reacted by Lukasz Wasylow
4. jgebal commented on Mar 29, 2025

   

   jgebal

   on Mar 29, 2025

   Member

   More actions

   Cursor comparison was designed to be strict and treat data as collections.

   Reacted by Lukasz Wasylow
5. lwasylow commented on Mar 29, 2025

   

   lwasylow

   on Mar 29, 2025

   Member

   More actions

   I will look at it.
6. jgebal

   closed this as completedin #1295on Apr 3, 2025

Issue actions