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

Different rows not reported when comparing collections of non objects #835

Closed
blitzkopf opened this Issue Feb 16, 2019 · 10 comments

Comments

Projects
None yet
3 participants
@blitzkopf
Copy link

blitzkopf commented Feb 16, 2019

When reporting differences in collections of base datatypes ( number, varchar2 ) the different rows are not reported the system only says:

Rows: [ 1 differences ]
        All rows are different as the columns are not matchin

It looks like it is getting confused by the fact that the rows are not object.
When reporting differences in collections of object types the different rows will be reported:

Rows: [ 1 differences ]
        Row No. 2 - Actual:   <ID>3</ID>
        Row No. 2 - Expected: <ID>2</ID>

I tried taking a look at the code that decides that the the rows are of different type, but I'm not familiar enough with the methods used to figure it out.

Here is a test case

create or replace type num_list is varray(14) of number;
/
create or replace type str_list is varray(14) of varchar2(100);
/
create or replace type t_obj as object(ID number);
/
create or replace type obj_list as varray(14) of t_obj;
/

create or replace package test_collections as 

	--%suite(compare collection types)

  --%test(equal num_lists)
  procedure equal_num_lists;

  --%test(not equal num_lists)
  procedure not_equal_num_lists;
	
  --%test(equal str_lists)
  procedure equal_str_lists;

  --%test(not equal str_lists)
  procedure not_equal_str_lists;
	

  --%test(equal obj_lists)
  procedure equal_obj_lists;

  --%test(not equal obj_lists)
  procedure not_equal_obj_lists;
	
end test_collections;
/

create or replace package body test_collections as 

  nl_expected num_list := num_list(1,2,3);
	sl_expected str_list := str_list('A','B','C');
	ol_expected obj_list := obj_list(t_obj(1),t_obj(2),t_obj(3));

  procedure equal_num_lists is 
	begin
	  ut.expect( anydata.convertCollection(num_list(1,2,3)) ).to_equal(anydata.convertCollection(nl_expected) );
	end;
		 
  procedure not_equal_num_lists is 
	begin
	  ut.expect( anydata.convertCollection(num_list(1,3,3)) ).to_equal(anydata.convertCollection(nl_expected) );
	end;
	
  procedure equal_str_lists is 
	begin
	  ut.expect( anydata.convertCollection(str_list('A','B','C')) ).to_equal(anydata.convertCollection(sl_expected) );
	end;

  procedure not_equal_str_lists is 
	begin
	  ut.expect( anydata.convertCollection(str_list('A','C','C')) ).to_equal(anydata.convertCollection(sl_expected) );
	end;
	
	procedure equal_obj_lists is 
	begin
	  ut.expect( anydata.convertCollection(obj_list(t_obj(1),t_obj(2),t_obj(3))) ).to_equal(anydata.convertCollection(ol_expected) );
	end;

  procedure not_equal_obj_lists is 
	begin
	  ut.expect( anydata.convertCollection(obj_list(t_obj(1),t_obj(3),t_obj(3))) ).to_equal(anydata.convertCollection(ol_expected) );
	end;
	
end test_collections;
/

begin
	ut.run('test_collections');
end;
/

which gives this output:

compare collection types
  equal num_lists [,984 sec]
  not equal num_lists [,019 sec] (FAILED - 1)
  equal str_lists [,015 sec]
  not equal str_lists [,01 sec] (FAILED - 2)
  equal obj_lists [,06 sec]
  not equal obj_lists [,26 sec] (FAILED - 3)
 
Failures:
 
  1) not_equal_num_lists
      Actual: ursus.num_list [ count = 3 ] was expected to equal: ursus.num_list [ count = 3 ]
      Diff:
      Rows: [ 1 differences ]
        All rows are different as the columns are not matching.
      at "URSUS.TEST_COLLECTIONS", line 14 	  ut.expect( anydata.convertCollection(num_list(1,3,3)) ).to_equal(anydata.convertCollection(nl_expected) );
      
       
  2) not_equal_str_lists
      Actual: ursus.str_list [ count = 3 ] was expected to equal: ursus.str_list [ count = 3 ]
      Diff:
      Rows: [ 1 differences ]
        All rows are different as the columns are not matching.
      at "URSUS.TEST_COLLECTIONS", line 24 	  ut.expect( anydata.convertCollection(str_list('A','C','C')) ).to_equal(anydata.convertCollection(sl_expected) );
      
       
  3) not_equal_obj_lists
      Actual: ursus.obj_list [ count = 3 ] was expected to equal: ursus.obj_list [ count = 3 ]
      Diff:
      Rows: [ 1 differences ]
        Row No. 2 - Actual:   <ID>3</ID>
        Row No. 2 - Expected: <ID>2</ID>
      at "URSUS.TEST_COLLECTIONS", line 34 	  ut.expect( anydata.convertCollection(obj_list(t_obj(1),t_obj(3),t_obj(3))) ).to_equal(anydata.convertCollection(ol_expected) );
      
       
Finished in 1,351811 seconds
6 tests, 3 failed, 0 errored, 0 disabled, 0 warning(s)

@jgebal jgebal added the bug label Feb 16, 2019

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Feb 16, 2019

Thanks for reporting this bug.
It's a gap in our self-testing. Let me see why we have that issue.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Feb 16, 2019

Just for completeness:

  • What version of utPLSQL are you on? select ut.version() from dual;
  • What version of database version are you on? select * from v$version;
@blitzkopf

This comment has been minimized.

Copy link
Author

blitzkopf commented Feb 16, 2019

utPLSQL version v3.1.3.2402

Oracle Version:

BANNER CON_ID
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Feb 16, 2019

I think I've found what is causing the issue, now just need to figure out how to fix it properly.
Thanks for reporting the problem - it allows us to make the product better.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Feb 16, 2019

The issue is with how different data-types are represented in XML.
function ut_compound_data_helper.get_rows_diff tries to extract the individual attributes of type.
The trouble is, there are no elements for table of scalars.
Need to investigate a bit more to understand how to fix this nicely.
Probably will need to check if element of collection is scalar or object and perform different operations for scalar.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Feb 16, 2019

This is the line causing issues:
table( xmlsequence( extract(r.item_data,'/*/*') ) ) s

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Feb 26, 2019

I will look into that when working on pulling anydata under refcursor.
I see that behaviour in my current development so will have to address that.

@lwasylow lwasylow self-assigned this Feb 26, 2019

@lwasylow lwasylow added this to the v3.1.4 milestone Feb 26, 2019

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Mar 1, 2019

Currently still in feature branch but changes will look like:

compare collection types
  equal num_lists [.157 sec]
  not equal num_lists [.226 sec] (FAILED - 1)
  equal str_lists [.168 sec]
  not equal str_lists [.234 sec] (FAILED - 2)
  equal obj_lists [.257 sec]
  not equal obj_lists [.138 sec] (FAILED - 3)
 
Failures:
 
  1) not_equal_num_lists
      Actual: ut3.num_list [ count = 3 ] was expected to equal: ut3.num_list [ count = 3 ]
      Diff:
      Rows: [ 1 differences ]
        Row No. 2 - Actual:   <NUM_LIST>3</NUM_LIST>
        Row No. 2 - Expected: <NUM_LIST>2</NUM_LIST>
      at "UT3.TEST_COLLECTIONS.NOT_EQUAL_NUM_LISTS", line 14 	  ut.expect( anydata.convertCollection(num_list(1,3,3)) ).to_equal(anydata.convertCollection(nl_expected) );
      
       
  2) not_equal_str_lists
      Actual: ut3.str_list [ count = 3 ] was expected to equal: ut3.str_list [ count = 3 ]
      Diff:
      Rows: [ 1 differences ]
        Row No. 2 - Actual:   <STR_LIST>C</STR_LIST>
        Row No. 2 - Expected: <STR_LIST>B</STR_LIST>
      at "UT3.TEST_COLLECTIONS.NOT_EQUAL_STR_LISTS", line 24 	  ut.expect( anydata.convertCollection(str_list('A','C','C')) ).to_equal(anydata.convertCollection(sl_expected) );
      
       
  3) not_equal_obj_lists
      Actual: ut3.obj_list [ count = 3 ] was expected to equal: ut3.obj_list [ count = 3 ]
      Diff:
      Rows: [ 1 differences ]
        Row No. 2 - Actual:   <ID>3</ID>
        Row No. 2 - Expected: <ID>2</ID>
      at "UT3.TEST_COLLECTIONS.NOT_EQUAL_OBJ_LISTS", line 34 	  ut.expect( anydata.convertCollection(obj_list(t_obj(1),t_obj(3),t_obj(3))) ).to_equal(anydata.convertCollection(ol_expected) );
      
       
Finished in 1.342277 seconds
6 tests, 3 failed, 0 errored, 0 disabled, 0 warning(s)
 
@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Mar 14, 2019

@lwasylow - I believe this is now getting resolved with your PR, right?

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented Mar 14, 2019

@jgebal Yes thats correct, results above are generated using my PR.

@jgebal jgebal closed this in #801 Mar 15, 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.