Re: Testing Oracle fitness for purpose/internal test suite

I agree with your conclusions, Dom.

I have to say that I don't think I've ever come across a test suite for RDBMS core functionality. I certainly have seen test suites that run a known version of the application against the new release and track both errors and performance changes. One particular conversation was quite amusing when the client's app process had gone from 5 minutes in 10.2 to just under a minute in 11.x (can't recall which x now) but produced different results. They were adamant that this was a wrong results bug whereas, in fact, it was because a wrong results bug had been fixed :).

On Thu, Nov 10, 2016 at 10:48 AM, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> 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 <mwf_at_rsiz.com>
> Sent: ‎10/‎11/‎2016 10:23
> To: dombrooks_at_hotmail.com; woodwardinformatics_at_strychnine.co.uk;
> 'ORACLE-L' <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
> <woodwardinformatics_at_strychnine.co.uk>
> *Sent: *‎10/‎11/‎2016 08:53
> *To: *ORACLE-L <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>
>
>
>
>
>
>
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


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