Skip to content

Updated documentation for whitespace vs null compare. #1039

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.

Sign up for GitHub

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

Merged
merged 3 commits into from
Jan 4, 2020
Merged

Updated documentation for whitespace vs null compare. #1039

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
d33bd80
Updated documentation for whitespace vs null compare.
jgebal Jan 2, 2020
5510555
Fixed documentation.
jgebal Jan 3, 2020
d708443
Fixed documentation typos.
jgebal Jan 4, 2020
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
3 changes: 2 additions & 1 deletion docs/userguide/expectations.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1114,7 +1114,8 @@ utPLSQL is capable of comparing compound data-types including:
- It is possible to compare PL/SQL records, collections, varrays and associative arrays. To compare this types of data, use cursor comparison feature of utPLSQL and TABLE operator in SQL query
- On Oracle 11g Release 2 - pipelined table functions are needed (see section [Implicit (Shadow) Types in this artcile](https://oracle-base.com/articles/misc/pipelined-table-functions))
- On Oracle 12c and above - use [TABLE function on nested tables/varrays/associative arrays of PL/SQL records](https://oracle-base.com/articles/12c/using-the-table-operator-with-locally-defined-types-in-plsql-12cr1)

- utPLSQL is not able to distinguish between NULL and whitespace-only column/attribute value when comparing compound data. This is due to Oracle limitation on of XMLType.
See [issue #880](https://github.com/utPLSQL/utPLSQL/issues/880) for details. *Note: This behavior might be fixed in future releases, when utPLSQL is no longer depending on XMLType for compound data comparison.*

utPLSQL offers advanced data-comparison options, for comparing compound data-types. The options allow you to:
- define columns/attributes to exclude from comparison
Expand Down
56 changes: 21 additions & 35 deletions test/ut3_user/expectations/test_expectations_cursor.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2639,7 +2639,7 @@ Check the query and data for errors.';
end;


procedure insginificant_whitespace1 is
procedure space_only_vs_empty is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
Expand All @@ -2650,79 +2650,65 @@ Check the query and data for errors.';
select column_value t1 from table(ut_varchar2_list(' '));
--Assert
ut3.ut.expect( l_actual ).to_equal( l_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;

procedure insginificant_whitespace2 is
procedure tab_only_vs_empty is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for
select ' t ' t1 from dual;
select column_value t1 from table(ut_varchar2_list(''));

open l_actual for
select 't' t1 from dual;
select column_value t1 from table(ut_varchar2_list(chr(9)));
--Assert
ut3.ut.expect( l_actual ).to_equal( l_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;

procedure insginificant_whitespace3 is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for
select 't ' t1 from dual;

open l_actual for
select 't' t1 from dual;
--Assert
ut3.ut.expect( l_actual ).to_equal( l_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;
procedure insginificant_whitespace4 is

procedure insignificant_start_end_space is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for
select ' t' t1 from dual;
select ' t ' t1 from dual;

open l_actual for
select 't' t1 from dual;
--Assert
ut3.ut.expect( l_actual ).to_equal( l_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;

procedure insginificant_whitespace5 is
procedure double_vs_single_start_end_ws is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for
select ' ' t1 from dual;
select ' t ' t1 from dual;

open l_actual for
select '' t1 from dual;
select ' t ' t1 from dual;
--Assert
ut3.ut.expect( l_actual ).to_equal( l_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;
end;

procedure nulltowhitespace is
procedure leading_tab_vs_space is
l_actual sys_refcursor;
l_expected sys_refcursor;
begin
open l_expected for
select cast(null as varchar2(2)) t1 from dual;
select ' t' t1 from dual;

open l_actual for
select ' ' t1 from dual;
select chr(9)||'t' t1 from dual;
--Assert
ut3.ut.expect( l_actual ).to_equal( l_expected );
ut.expect(ut3_tester_helper.main_helper.get_failed_expectations_num).to_be_greater_than(0);
end;

end;
procedure number_from_dual is
l_actual sys_refcursor;
l_expected sys_refcursor;
Expand Down
36 changes: 16 additions & 20 deletions test/ut3_user/expectations/test_expectations_cursor.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -417,31 +417,27 @@ create or replace package test_expectations_cursor is

--%test(Check that column name accept non xml characters fix #902)
procedure nonxmlchar_part_of_colname;


/*Oracle Bug not readin properly in xmltable */
--%test ( Compare insiginificant whitespaces scenario 1 )


/*Oracle Bug not reading properly in XMLTable - See - Issue #880 */
--%disabled
--%test ( Space-only string is not equal empty string )
procedure space_only_vs_empty;

/*Oracle Bug not reading properly in XMLTable - See - Issue #880 */
--%disabled
procedure insginificant_whitespace1;
--%test ( Tab-only string is not equal empty string )
procedure tab_only_vs_empty;

--%test ( Compare insiginificant whitespaces scenario 2 )
procedure insginificant_whitespace2;
--%test ( Insignificant start/end whitespaces are considered )
procedure insignificant_start_end_space;

--%test ( Compare insiginificant whitespaces scenario 3 )
procedure insginificant_whitespace3;
--%test ( Double and single leading/trailing space is distinguished )
procedure double_vs_single_start_end_ws;

--%test ( Compare insiginificant whitespaces scenario 4 )
procedure insginificant_whitespace4;
--%test ( Leading Tab vs. Space is distinguished )
procedure leading_tab_vs_space;

/*Oracle Bug not readin properly in xmltable */
--%test ( Compare insiginificant whitespaces scenario 5 )
--%disabled
procedure insginificant_whitespace5;

/*Oracle Bug not readin properly in xmltable */
--%test ( Compare null to whitespace )
--%disabled
procedure nulltowhitespace;

--%test(Check precision of number from dual #907)
procedure number_from_dual;
Expand Down