Updated documentation for whitespace vs null compare.

Updated tests o represent the issue more clearly. Closes #880
utPLSQL · jgebal · Feb 23, 2020 · Nov 10, 2019 · Nov 10, 2019 · Nov 10, 2019
commit d33bd80f4a1b5c7e1c5188fa04ac5fcdfde9bf73
diff --git a/docs/userguide/expectations.md b/docs/userguide/expectations.md
@@ -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 a NULL column/attribute from a column/attribute containing whitespace-only when comparing compound data. This is due to Oracle limitation on of XMLType., utPLSQL is not able to distinguish between  
+  See [issue #880](https://github.com/utPLSQL/utPLSQL/issues/880) for details. *Note: This behavior might be fixed in future relreases, 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

diff --git a/test/ut3_user/expectations/test_expectations_cursor.pkb b/test/ut3_user/expectations/test_expectations_cursor.pkb
@@ -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
@@ -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;

diff --git a/test/ut3_user/expectations/test_expectations_cursor.pks b/test/ut3_user/expectations/test_expectations_cursor.pks
@@ -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;