Home -> Mailing Lists -> Oracle-L -> RE: Testing Oracle fitness for purpose/internal test suite

RE: Testing Oracle fitness for purpose/internal test suite

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Thu, 10 Nov 2016 10:48:21 +0000
Message-ID: <VI1PR07MB1390016105F44B43B39DF95DA1B80_at_VI1PR07MB1390.eurprd07.prod.outlook.com> 








I'm just saying that if you say developers shouldn't use listagg because there was once a bug in it then you'd run out of features to use!



It would be better to use the test suite to say we can't use version 12.a.b.c because our application uses listagg.




Sent from my Windows Phone




From: Mark W. Farnham<mailto:mwf_at_rsiz.com>
Sent: ý10/ý11/ý2016 10:23


To: dombrooks_at_hotmail.com<mailto:dombrooks_at_hotmail.com>; woodwardinformatics_at_strychnine.co.uk<mailto:woodwardinformatics_at_strychnine.co.uk>; 'ORACLE-L'<mailto:oracle-l_at_freelists.org>
Subject: RE: Testing Oracle fitness for purpose/internal test suite



It can be useful to slap together examples of the code patterns in your applications. Often, they can be described in the comments and reference the documentation to do dual duty as “go-bys” for the cases where tracing through sixty pages of documentation to assemble the almost bnf of Oracle’s manual specification is better conveyed by simple examples to handle the simple cases.



But the main point is to constitute a regression test. Sometimes that means some complicated code to show corner cases that had become buggy in some past release. Complexities of code merges and backports sometimes cause temporarily cured bugs to rise from the dead.



IF you build such a suite, commenting which chunks are intended as go-bys and which are corner case demonstrations with a notation to NOT code like that is useful.



Being able to run a suite of tests with a known output that can be diff-ed versus the correct prior results is often a useful step that lets you know not to bother with your code suite integration test. And you have a nice degenerate case in hand to give along with your SR.



Of course such test suites can also be mis-used.



A test suite at a former client still includes (last I checked) a nice little speed test for varrays for a specific purpose. For that purpose they were far and away the easiest way to program. Unfortunately they were very slow. So the code went into the regression test with a note to watch for the performance of that use to improve so it could in the future be used to replace the complex alternative code solution.



Your mileage may vary. I suppose a regression test suite might be somewhat askew from a fitness for purpose test.



mwf



From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dominic Brooks
Sent: Thursday, November 10, 2016 4:40 AM
To: woodwardinformatics_at_strychnine.co.uk; ORACLE-L
Subject: RE: Testing Oracle fitness for purpose/internal test suite



Interesting.


Trouble is that if you follow that thinking then for some of the bugs out there, e.g wrong results not necessarily ORA-00600, you could come to the conclusion that you shouldn't use SELECT at all!



Sent from my Windows Phone




From: Michael D O'Shea/Woodward Informatics Ltd<mailto:woodwardinformatics_at_strychnine.co.uk>
Sent: ý10/ý11/ý2016 08:53


To: ORACLE-L<mailto:oracle-l_at_freelists.org>
Subject: Testing Oracle fitness for purpose/internal test suite



Recently I encountered a very large organisation that had a comprehensive suite of unit tests that tested stock "Oracle" SQL & PL/SQL behaviour. Yes I really did write that. The client likely had a suite of unit tests that tested their in-house developed code too but we were discussing testing of stock Oracle functionality. In my book, I test my own code, and I expect the vendor to test and certify their software is OK and "does what it says on the tin". I found the client behaviour atypical to say the least. Their issue was that they had commited to Oracle, but there was a quality confidence issue.



Their tests appeared to be done for two reasons.



    
  
  1.   To confirm Oracle patch/patch sets or version upgrades did not introduce unexpected behaviour, or changes to existing RDBMS behaviour, that would manifest itself in their critical business systems.
  
  2.   To lay the law down in terms of their internal coding standards, "though shall not use LISTAGG" for example.






The following contrived and silly code snippet returns the length of a VARCHAR2 aggregated using LISTAGG. The LENGTH(y) is 4*999+3=3999. All good (again this is contrived code, but from memory it is the type of scripted code the organisation had in their test suite).



SQL>
SQL>
SQL> set serveroutput on size 100000
SQL> set timing on
SQL>
SQL> declare




  2    xx char(999) := 'char not varchar2 so padded';
  3    y varchar2(32767);  --PL/SQL limit 32767, not 4000
  4  begin


  5   select distinct listagg(x4, ',')

  6     within group (order by 1)
  7       over (partition by 1)
  8     into y
  9      from (
 10                       select xx x4 from dual
 11             union all select xx    from dual
 12             union all select xx    from dual
 13             union all select xx    from dual
 14           );




 15    dbms_output.put_line(to_char(length(y)));
 16  end;


 17  /


3999


PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

SQL>
SQL>
SQL>







This is the expected behaviour, and 3999 the expected VARCHAR2 length (4 * 999 = 3996 + 3 for the 3 commas).




Something like the next test they performed is shown below. Although surprising to me, it was the expected behaviour - the limit on LISTAGG is 4000 bytes not 32k, but it was a test "pass".




SQL>
SQL>
SQL>
SQL>
SQL> declare




  2    xx char(1000) := 'char not varchar2 so padded';
  3    y varchar2(32767);  --PL/SQL limit 32767, not 4000
  4  begin


  5   select distinct listagg(x4, ',')

  6     within group (order by 1)
  7       over (partition by 1)
  8     into y
  9      from (
 10                       select xx x4 from dual
 11             union all select xx    from dual
 12             union all select xx    from dual
 13             union all select xx    from dual
 14           );




 15    dbms_output.put_line(to_char(length(y)));
 16  end;


 17  /


declare


*



ERROR at line 1:


ORA-01489: result of string concatenation is too long
ORA-06512: at line 5



Elapsed: 00:00:00.03


SQL>






The next test was a failure for the client and showcased their concerns.






SQL>
SQL>
SQL>
SQL>
SQL>
SQL> declare




  2    xx char(2001) := 'char not varchar2 so padded';
  3    y varchar2(32767);  --PL/SQL limit 32767, not 4000
  4  begin


  5   select distinct listagg(x4, ',')

  6     within group (order by 1)
  7       over (partition by 1)
  8     into y
  9      from (
 10                       select xx x4 from dual
 11             union all select xx    from dual
 12             union all select xx    from dual
 13             union all select xx    from dual
 14           );




 15    dbms_output.put_line(to_char(length(y)));
 16  end;


 17  /


declare


*



ERROR at line 1:


ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []



Elapsed: 00:00:00.08


SQL>



SQL>





While clearly this is just a plain old Oracle bug (an edge case - 2000 vs. 2001 bytes, half the 4000 byte limit), the test result confirmed the client confidence issue in internal Oracle testing and fitness for purpose, with a knock-on effect that their internal coding standards documentation prohibited the use of LISTAGG (I do not know whether they raised the issue with Oracle but this isn't my point), and other built-in functionality.




My question to you all is "Has anyone else encountered the behaviour where clients run a suite of automated tests to test out-of-the-box Oracle stock functionality, in otherwords running what are in effect unit tests that should be performed by the Oracle prior to shipping, not the client?". Despite my views of quality and testing at Oracle, I still cannot get my head around a client testing out-of-the-box functionality with their own test suite like this.





I am not sure what version of Oracle the client was using, but I have just retested using the following version (below) and can reproduce the behaviour.




Regards



Mike


http://www.strychnine.co.uk





SQL>
SQL>
SQL>
SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise 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 Solaris: Version 12.1.0.2.0 - Production                                          0
NLSRTL Version 12.1.0.2.0 - Production                                                    0


Elapsed: 00:00:00.02

SQL>
SQL>
SQL>









--
http://www.freelists.org/webpage/oracle-l


Received on Thu Nov 10 2016 - 11:48:21 CET












Original text of this message