Description

hirerajan

opened on Sep 4, 2020

Issue body actions

Describe the bug
Lets say you have type of an object with three layers, i.e grand parent, parent and child type.
for example m_package_tab

m_package_obj( a varchar2(2), b number, t_parent_tab( p_ame varchar2(100), c_child_tab(name varchar2,age number)))

and lets say for our data, our child_tab we have null data,
and when you compare the data using following, you will get an error saying Fetched column value is NULL

ut.expect(anydata.convertcollection(l_package_tab)).to_equal(anydata.convertcollection(l_test_package_tab)).unordered;

OUTPUT:
ORA-01405: fetched column value is NULL
ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 336
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 159
ORA-06512: at "SYS.DBMS_CRYPTO", line 86
ORA-06512: at "UTP3.UT_UTILS", line 868
ORA-06512: at line 1
ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 331
ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 384
ORA-06512: at "UTP3.UT_DATA_VALUE_ANYDATA", line 129
ORA-06512: at "UTP3.UT_EQUAL", line 229
ORA-06512: at "UTP3.UT_EXPECTATION", line 26
ORA-06512: at "UTP3.UT_EXPECTATION_COMPOUND", line 146

Workout Around
ut.expect( xmltype( anydata.convertCollection(l_package_tab ) ).getClobVal() ).to_equal( xmltype(anydata.convertCollection(l_test_package_tab) ).getClobVal() );

There is also problem with workout Around as the order of the output does not match. I cannot use .unordered with perhaps three layer nested loop or with xmltype. Not sure why.

utPLSQL version : v3.1.10.3349

Activity

1. pesse commented on Nov 12, 2020

   

   pesse

   on Nov 12, 2020

   Member

   More actions

   Hey @hirerajan , could you provide a complete example?
   This seems to be a rather complex setup, you could assist us greatly with looking into it
2. hirerajan commented on Jan 20, 2021

   

   hirerajan

   on Jan 20, 2021

   Author

   More actions

   @pesse ,
   Excuse my UT and formatting , for the shake of this testing, I quickly wrote following program that I was able to replicate. I am directly using table data instead of setting up test data on unit test.

   Let me know if this helps.
   CREATE OR REPLACE TYPE INTEGER_TAB_OBJ AS OBJECT (VALUE INTEGER); /
CREATE OR REPLACE TYPE INTEGER_OBJ_TAB IS TABLE OF INTEGER_TAB_OBJ;

   CREATE TABLE continent ( continent_id NUMBER, continent_name VARCHAR2 (25) );

   --drop table country;
   CREATE TABLE country ( country_id CHAR (2), country_name VARCHAR2 (40), continent_id NUMBER );

   --drop table locations;

   CREATE TABLE locations ( location_id NUMBER (4), street_address VARCHAR2 (40), postal_code VARCHAR2 (12), city VARCHAR2 (30), state_province VARCHAR2 (25), country_id CHAR (2) );

   Insert into CONTINENT (CONTINENT_ID, CONTINENT_NAME) Values (1, 'North America'); Insert into CONTINENT (CONTINENT_ID, CONTINENT_NAME) Values (2, 'Asia'); Insert into CONTINENT (CONTINENT_ID, CONTINENT_NAME) Values (3, 'Africa'); COMMIT;

   Insert into COUNTRY (COUNTRY_ID, COUNTRY_NAME, CONTINENT_ID) Values ('1 ', 'USA', 1); Insert into COUNTRY (COUNTRY_ID, COUNTRY_NAME, CONTINENT_ID) Values ('2 ', 'Canada', 1); Insert into COUNTRY (COUNTRY_ID, COUNTRY_NAME, CONTINENT_ID) Values ('3 ', 'Japan', 2); Insert into COUNTRY (COUNTRY_ID, COUNTRY_NAME, CONTINENT_ID) Values ('4 ', 'Sudhan', 3); COMMIT;

   Insert into LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) Values (1, '100 Broadway', '02474', 'Arlington', 'MA', '1 '); Insert into LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) Values (2, '200 Indian School Rd', '85016', 'Phoenix', 'AZ', '1 '); Insert into LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) Values (3, '100 Toyota', '12345', 'Tokyo', 'TK', '3 '); COMMIT;

   DROP TYPE location_typ_obj FORCE; CREATE or replace TYPE location_typ_obj AS OBJECT ( location_id NUMBER (4), street_address VARCHAR2 (40), postal_code VARCHAR2 (12), city VARCHAR2 (30), state_province VARCHAR2 (25) ); /

   CREATE or replace TYPE nt_location_typ_tab AS TABLE OF location_typ_obj; -- nested table type REATE TYPE country_typ_obj AS OBJECT ( country_id CHAR (2), country_name VARCHAR2 (40), locations nt_location_typ_tab ); -- inner nested table /

   CREATE TYPE nt_country_typ_tab AS TABLE OF country_typ_obj; / CREATE TYPE continent_typ_obj AS OBJECT ( continent_id NUMBER, continent_name VARCHAR2 (25), countries nt_country_typ_tab ); CREATE TYPE nt_continent_typ_tab AS TABLE OF continent_typ_obj; /

   CREATE OR REPLACE PACKAGE location_pkg AS FUNCTION get_continent_details (p_continent_id_tab IN integer_obj_tab) RETURN nt_continent_typ_tab; END location_pkg;

   CREATE OR REPLACE PACKAGE BODY location_pkg AS FUNCTION get_continent_details (p_continent_id_tab IN integer_obj_tab) RETURN nt_continent_typ_tab AS l_nt_continent_typ_tab nt_continent_typ_tab; BEGIN SELECT continent_typ_obj ( c.continent_id, c.continent_name, CAST ( MULTISET ( SELECT country_id, country_name, CAST ( MULTISET ( SELECT location_id, street_address, postal_code, city, state_province FROM locations l WHERE l.country_id = cy.country_id) AS nt_location_typ_tab) FROM country cy WHERE cy.continent_id = c.continent_id) AS nt_country_typ_tab)) BULK COLLECT INTO l_nt_continent_typ_tab FROM continent c , table (p_continent_id_tab) t where c.continent_id = t.value ; RETURN l_nt_continent_typ_tab; END; END location_pkg; /

   CREATE OR REPLACE PACKAGE MA_RELEASE.ut_location_pkg
   IS
   --%suite (Issue example on Nested UDT )
   --%suitepath (com.location.pkg)
   --%rollback(manual)

   --%context(Postive Scenarios)

   --%test(Get List of all Continent)
   PROCEDURE get_continent_countries;

   --%endcontext

   END ut_location_pkg;
   /

   CREATE OR REPLACE PACKAGE body MA_RELEASE.ut_location_pkg
   IS

   procedure get_continent_countries
   as
   l_continent_id_tab integer_obj_tab;
   l_nt_continent_typ_tab nt_continent_typ_tab;
   l_nt_continent_typ_tab_ordered nt_continent_typ_tab;
   l_test_nt_continent_typ_tab nt_continent_typ_tab;
   l_test_nt_continent_typ_tab_ordered nt_continent_typ_tab;
   begin

    l_continent_id_tab := integer_obj_tab (integer_tab_obj(1)
                                          --,integer_tab_obj(2)
                                          );
    
     -- actual
     l_nt_continent_typ_tab := location_pkg.get_continent_details (
                       p_continent_id_tab  => l_continent_id_tab);
   
   
     -- expected
     l_test_nt_continent_typ_tab := nt_continent_typ_tab(                            
                            continent_typ_obj(1,
                                              'North America',
                                              nt_country_typ_tab(country_typ_obj('1','USA',nt_location_typ_tab(location_typ_obj(1,'100 Broadway','02474','Arlington','MA'),
                                                                                                               location_typ_obj(2,'200 Indian School Rd','85016','Phoenix','AZ')
                                                                                 )
                                                                )
                                               )));
                                                                
                                                                
     select value (x)
     bulk collect into l_test_nt_continent_typ_tab_ordered
     from table (l_test_nt_continent_typ_tab) x
     order by x.continent_id desc;
    
     --we can revisit it again to use original comparison, ways to get arround is using xmltype but it has its own problem
     --ut.expect(xmltype(anydata.convertCollection(l_test_nt_continent_typ_tab)).getClobVal()).to_equal(xmltype(anydata.convertCollection(l_test_nt_continent_typ_tab_ordered)).getClobVal());
     ut.expect(anydata.convertcollection(l_nt_continent_typ_tab)).to_equal(anydata.convertcollection(l_test_nt_continent_typ_tab)).unordered;
   
   END get_continent_countries;
   

   END ut_location_pkg;
   /

   ORA-01405: fetched column value is NULL
   ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 336
   ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 159
   ORA-06512: at "SYS.DBMS_CRYPTO", line 86
   ORA-06512: at "UTP3.UT_UTILS", line 868
   ORA-06512: at line 1
   ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 331
   ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 384
   ORA-06512: at "UTP3.UT_DATA_VALUE_ANYDATA", line 129
   ORA-06512: at "UTP3.UT_EQUAL", line 229
   ORA-06512: at "UTP3.UT_EXPECTATION", line 26
   ORA-06512: at "UTP3.UT_EXPECTATION_COMPOUND", line 146
   ORA-06512: at "MA_RELEASE.UT_LOCATION_PKG", line 40
   ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 336
   ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 159
   ORA-06512: at "SYS.DBMS_CRYPTO", line 86
   ORA-06512: at "UTP3.UT_UTILS", line 868
   ORA-06512: at line 1
   ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 331
   ORA-06512: at "UTP3.UT_DATA_VALUE_REFCURSOR", line 384
   ORA-06512: at "UTP3.UT_DATA_VALUE_ANYDATA", line 129
   ORA-06512: at "UTP3.UT_EQUAL", line 229
   ORA-06512: at "UTP3.UT_EXPECTATION", line 26
   ORA-06512: at "UTP3.UT_EXPECTATION_COMPOUND", line 146
   ORA-06512: at "MA_RELEASE.UT_LOCATION_PKG", line 40
   ORA-06512: at line 6
3. pesse commented on Jun 21, 2021

   

   pesse

   on Jun 21, 2021

   Member

   More actions

   I ran and reformatted the example a bit:

   create or replace type integer_tab_obj as object
   (
   	value integer
   );
   /
   create or replace type integer_obj_tab is table of integer_tab_obj;
   /
   
   create table continent
   (
   	continent_id   number,
   	continent_name varchar2(25)
   );
   
   --drop table country;
   create table country
   (
   	country_id   char(2),
   	country_name varchar2(40),
   	continent_id number
   );
   
   --drop table locations;
   
   create table locations
   (
   	location_id    number(4),
   	street_address varchar2(40),
   	postal_code    varchar2(12),
   	city           varchar2(30),
   	state_province varchar2(25),
   	country_id     char(2)
   );
   
   insert into continent (continent_id, continent_name) values (1, 'North America');
   insert into continent (continent_id, continent_name) values (2, 'Asia');
   insert into continent (continent_id, continent_name) values (3, 'Africa');
   commit;
   
   insert into country (country_id, country_name, continent_id) values ('1 ', 'USA', 1);
   insert into country (country_id, country_name, continent_id) values ('2 ', 'Canada', 1);
   insert into country (country_id, country_name, continent_id) values ('3 ', 'Japan', 2);
   insert into country (country_id, country_name, continent_id) values ('4 ', 'Sudhan', 3);
   commit;
   
   insert into locations (location_id, street_address, postal_code, city, state_province, country_id)
   	values (1, '100 Broadway', '02474', 'Arlington', 'MA', '1 ');
   insert into locations (location_id, street_address, postal_code, city, state_province, country_id)
   	values (2, '200 Indian School Rd', '85016', 'Phoenix', 'AZ', '1 ');
   insert into locations (location_id, street_address, postal_code, city, state_province, country_id)
   	values (3, '100 Toyota', '12345', 'Tokyo', 'TK', '3 ');
   commit;
   
   --drop type location_typ_obj force;
   create or replace type location_typ_obj as object
   (
   	location_id    number(4),
   	street_address varchar2(40),
   	postal_code    varchar2(12),
   	city           varchar2(30),
   	state_province varchar2(25)
   ); /
   
   create or replace type nt_location_typ_tab as table of location_typ_obj; -- nested table type
   /
   cREATE TYPE country_typ_obj AS OBJECT ( country_id CHAR (2), country_name VARCHAR2 (40), locations nt_location_typ_tab ); -- inner nested table
   /
   
   create type nt_country_typ_tab as table of country_typ_obj;
   /
   create type continent_typ_obj as object
   (
   	continent_id   number,
   	continent_name varchar2(25),
   	countries      nt_country_typ_tab
   );
   create type nt_continent_typ_tab as table of continent_typ_obj;
   /
   
   create or replace package location_pkg as
   	function get_continent_details(p_continent_id_tab in integer_obj_tab) return nt_continent_typ_tab;
   end location_pkg;
   
   create or replace package body location_pkg as
   	function get_continent_details(p_continent_id_tab in integer_obj_tab) return nt_continent_typ_tab as
   		l_nt_continent_typ_tab nt_continent_typ_tab;
   	begin
   		select
   			continent_typ_obj(c.continent_id, c.continent_name, cast(
   			    multiset(
   			    	select
   					  country_id,
   					  country_name,
   					  cast(multiset(
   					    select location_id, street_address, postal_code, city, state_province
   					     from locations l
   					     where
   					  	   l.country_id = cy.country_id) as nt_location_typ_tab)
   					  from country cy
   					  where
   					   cy.continent_id = c.continent_id) as nt_country_typ_tab))
   			bulk collect into l_nt_continent_typ_tab
   			from continent c, table (p_continent_id_tab) t
   			where
   				c.continent_id = t.value;
   		return l_nt_continent_typ_tab;
   	end;
   end location_pkg;
   /
   
   create or replace package ut_location_pkg
   is
   --%suite (Issue example on Nested UDT )
   --%suitepath (com.location.pkg)
   --%rollback(manual)
   
   --%context(Postive Scenarios)
   
   --%test(Get List of all Continent)
   	procedure get_continent_countries;
   
   --%endcontext
   
   end ut_location_pkg;
   /
   
   create or replace package body ut_location_pkg
   is
   
   	procedure get_continent_countries
   	as
   		l_continent_id_tab                  integer_obj_tab;
   		l_nt_continent_typ_tab              nt_continent_typ_tab;
   		l_nt_continent_typ_tab_ordered      nt_continent_typ_tab;
   		l_test_nt_continent_typ_tab         nt_continent_typ_tab;
   		l_test_nt_cont_typ_tab_ordered nt_continent_typ_tab;
   	begin
   
   		l_continent_id_tab := integer_obj_tab(integer_tab_obj(1)
   			--,integer_tab_obj(2)
   			);
   
   		-- actual
   		l_nt_continent_typ_tab := location_pkg.get_continent_details(
   			p_continent_id_tab => l_continent_id_tab);
   
   
   		-- expected
   		l_test_nt_continent_typ_tab := nt_continent_typ_tab(
   			continent_typ_obj(
   			    1,
   				'North America',
   				nt_country_typ_tab(country_typ_obj('1', 'USA', nt_location_typ_tab(
   					location_typ_obj(1, '100 Broadway', '02474', 'Arlington', 'MA'),
   				 	location_typ_obj(2, '200 Indian School Rd', '85016', 'Phoenix', 'AZ')
   				 )))
   			)
   		);
   
   		select
   			value(x) bulk collect
   			into l_test_nt_cont_typ_tab_ordered
   			from table (l_test_nt_continent_typ_tab) x
   			order by x.continent_id desc;
   
   		--we can revisit it again to use original comparison, ways to get arround is using xmltype but it has its own problem
   		--ut.expect(xmltype(anydata.convertCollection(l_test_nt_continent_typ_tab)).getClobVal()).to_equal(xmltype(anydata.convertCollection(l_test_nt_continent_typ_tab_ordered)).getClobVal());
   		ut.expect(anydata.convertcollection(l_nt_continent_typ_tab))
   		    .to_equal(anydata.convertcollection(l_test_nt_continent_typ_tab)).unordered;
   
   	end get_continent_countries;
   end ut_location_pkg;
   /
   
   
   select * from table(ut.run('ut_location_pkg'));

   It's probably related to #1082
4. pesse

   added

   bug

   on Jun 21, 2021
5. jgebal

   pinned this issue on Nov 11, 2021
6. lwasylow

   self-assigned this

   on Jan 26, 2022
7. lwasylow commented on Jan 26, 2022

   

   lwasylow

   on Jan 26, 2022

   Member

   More actions

   Issue is due to hash being run on null.
8. lwasylow

   added a commit that references this issue on Jan 26, 2022

   Fixing issue when a hash was tried to be generated on the null value …

   3c3d320
9. lwasylow

   linked a pull request that will close this issueFeature/1082 failure when comparing nested objects #1179on Jan 26, 2022
10. lwasylow commented on Jan 28, 2022

    

    lwasylow

    on Jan 28, 2022

    Member

    More actions

    The root cause of this issue is that some of the types are treated by Oracle engine as user defined and cannot be described by one of built-in Oracle types. This comparison in such scenarios has to be done by transforming data type from user defined into clob representation extracted from XML. This representation is then compared using hash function which fails when null is passed.

    Reacted by Jacek Gębal
11. lwasylow

    closed this as completedin #1179on Jan 29, 2022
12. jgebal

    unpinned this issue on Feb 11, 2022
13. jgebal

    added this to the 3.1.12 milestone on Feb 22, 2022

Issue actions