Skip to content

Cursor comparison not detecting duplicate rows #1293

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
DLakomy opened this issue Mar 29, 2025 · 4 comments · Fixed by #1295
Closed

Cursor comparison not detecting duplicate rows #1293

DLakomy opened this issue Mar 29, 2025 · 4 comments · Fixed by #1295
Assignees

Comments

@DLakomy
Copy link

DLakomy commented Mar 29, 2025

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 ]
*/
@lwasylow
Copy link
Member

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 :)

@lwasylow lwasylow self-assigned this Mar 29, 2025
@jgebal
Copy link
Member

jgebal commented Mar 29, 2025

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

@jgebal
Copy link
Member

jgebal commented Mar 29, 2025

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

@lwasylow
Copy link
Member

I will look at it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants