Skip to content
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

Add option to cursor comparison to ignore the order of the columns #779

Closed
oklimberg opened this Issue Nov 8, 2018 · 10 comments

Comments

Projects
None yet
4 participants
@oklimberg
Copy link

oklimberg commented Nov 8, 2018

It would be nice to have such a feature, so the user could simply make a statemement like select * from table1 rather than specifying the order of the columns by hand like select col1, col2 from table1.

I got a test case, where I check if certain entries have been copied from table1 to table2.
Both tables will certainly get new columns over the time and I woud like to write the test in a way, that I do not have to add the new columns there as well.
Specifying the order manually would not check new columns, unless the test is also updated.

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Nov 8, 2018

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 ?

@lwasylow lwasylow added the question label Nov 8, 2018

@oklimberg

This comment has been minimized.

Copy link
Author

oklimberg commented Nov 9, 2018

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.
@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Nov 9, 2018

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.

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Nov 9, 2018

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)

@lwasylow lwasylow added enhancement and removed question labels Nov 9, 2018

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Nov 9, 2018

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.

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Nov 9, 2018

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 ?

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Nov 9, 2018

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.

@mathewbutler

This comment has been minimized.

Copy link

mathewbutler commented Nov 9, 2018

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

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Nov 9, 2018

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

@jgebal jgebal added this to the v3.1.4 milestone Nov 20, 2018

@lwasylow lwasylow self-assigned this Dec 7, 2018

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Dec 11, 2018

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

@jgebal jgebal added the in progress label Jan 14, 2019

@jgebal jgebal closed this in #801 Mar 15, 2019

@jgebal jgebal removed the in progress label Mar 19, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.