Hi @oklimberg
We have a two extra options called .join_by or .unordered and we are working on new one called .to_include.
Have a look at : http://utplsql.org/utPLSQL/latest/userguide/advanced_data_comparison.html

the example is a little misleading but order clause there is not required.

procedure unordered_tst is
    l_actual   sys_refcursor;
    l_expected sys_refcursor;
begin
    open l_expected for select username, user_id from all_users
    union all
    select 'TEST' username, -600 user_id from dual;
    open l_actual   for select username, user_id from all_users
    union all
    select 'TEST' username, -610 user_id from dual;
    ut.expect( l_actual ).to_equal( l_expected ).unordered;
end;

Above test will result in two differences of one row extra and one row missing.

      Diff:
      Rows: [ 2 differences ]
      Missing:  <ROW><USERNAME>TEST</USERNAME><USER_ID>-600</USER_ID></ROW>
      Extra:    <ROW><USERNAME>TEST</USERNAME><USER_ID>-610</USER_ID></ROW>

Is that what you looking for ?

Hi @lwasylow

i already checked out the currently available options, but they do not provide the functionality I proposed.

I would like to have an extended_option, e.g. unordered_columns, to make the following test pass.

PROCEDURE unordered_cloumn_tst
IS
	l_actual sys_refcursor;
	l_expected sys_refcursor;
BEGIN
	OPEN l_expected FOR SELECT 'TEST1' username, -600 user_id FROM dual
	UNION ALL
	SELECT 'TEST2' username, -610 user_id FROM dual;
	OPEN l_actual FOR SELECT -600 user_id, 'TEST1' username FROM dual
	UNION ALL 
	SELECT -610 user_id, 'TEST2' username FROM dual;
	ut.expect( l_actual ).to_equal( l_expected ).unordered_columns;
END;

Using only to_eqal, the test above will fail with the following message:

Failures:
 
  1) unordered_cloumn_tst
      Actual: refcursor [ count = 2 ] was expected to equal: refcursor [ count = 2 ]
      Diff:
      Columns:
        Column <USERNAME> is misplaced. Expected position: 1, actual position: 2.
        Column <USER_ID> is misplaced. Expected position: 2, actual position: 1.
      Rows: [ 2 differences ]
        All rows are different as the columns are not matching.

Currently, utPLSQL performs compound data comparison (cursor/object/nested table) by column/attribute position and name.
It a column/attribute is misplaced in result-set expectation will fail, as shown in example by @oklimberg

The ask is to have unordered_columns so that data-comparison is done by column name only and column position is ignored.

Sounds like a feature to be implemented as additional option to compound data comparison.

Is there any reason why we should not make that behavior default for comparing with options:

• unordered
• joinby
• to_contain

I think the column position is only relevant in standard matcher where the data has to be ordered (due to implementation)

Hmmm...
Maybe that's a good idea.
But we then would loose capability to check column ordering.
We would then need to have ordered_columns
I wonder if having colums as unordered should be the side effect of applying the join_by etc. or should we force it to be explicit.

Is there a specific requirement to check columns ordering ?
I mean in terms of the testing we are testing a data set retrieved rather than column order.
I think that having another attribute would be not very user friendly e.g.
.unordered.include('TEST').unordered_columns

I would say for any check unordered the columns should be unordered too.
I think question is are there any tests in live that check columns order or we look only or data sets ?

The place where order matters is when you have a PLSQL function that returns a refcursor.

If you want to fetch it, you need a record type that matches the cursor result columns by position and by data-type.
Oracle does not match columns of result set by name.

When you fetch into record, order of columns is essential.
If your record expects id, valid_from, valid_to but your cursor returns: id, valid_to, valid_from then your record has data totally wrong.

Similar scenario is when you do union/minus/intersect in SQL or when you do insert as select *.

I do agree that unordered_columns is good to make the framework more easy to use in some scenarios, however I still see huge value and importance of column order in result set for some scenarios.

If there’s no performance difference then suggest the default to be unordered (ie match by name. ) with capability of forcing specific order match.

I think also default should be unordered and if there is a specific scenario pass new option .ordered_columns

Its been agreed that to not break existing functionality and to mirror logic of SQL statement where order of columns matter (e.g. union all) we will provide an option to explicit force unordered columns via
unordered_columns or short uc , current logic will remain default option