Re: [office-formula] Creating the test suite

["David A. Wheeler" <dwheeler@dwheeler.com>]

Quick background for all: Daniel Carrera has volunteered to
rewrite the test spreadsheet generator; his program will take the
test cases in our .odt spec and generate a .ods spreadsheet.
If your're curious, the old code is here:
http://www.openformula.org/Automatically_Generated_Conformance_Test
and an example of a generated spreadsheet is here:
http://www.oasis-open.org/apps/org/workgroup/office-formula/download.php/16827/openformula-test-20060221.odt

Daniel Carrera asked:
> This is a question for David I guess. Some of the tests are supposed to
> produce an error. For example, see page 64, the test =1/0. I can't
> figure out what I should put on the test spreadsheet, under the Expected
> result column. Should I just put the string "Error"?
> 
> How is the test suite supposed to check error tests? Would it be a
> better idea to replace the Expression by =ISERROR(1/0) ? Ditto for all
> expressions that are supposed to give an error.

That'd be fine, as long as the "Correct" field gives the right answer.
My generator noticed if the expected result was Error or NA; if it was,
it wrapped the expression with ISERROR() or ISNA() respectively,
and then changed the expected result to True.
I'll email you the generated spreadsheet, so you can see what I did.

In my generated spreadsheet, I generated the following fields:
Section: The text of the lowest-level heading (SUM, Limits, etc.).. so we can find it!
Expression: A copy of the expression test case. I did modify it by wrapping it
   with ISERROR(..) or ISNA(...) if the expected results were Error or NA.
Expected Result:  The expected result.  Note that you have to get the type
   right here.
Level: 1,2,3, etc.  "Levels" are going away, but we will have groups.
    It might make sense to have a "Group" field, as a placeholder,
    so you can query how well you're doing for various groups.
Comment: A copy of the comment of the test case
Actual Result: A formula that forces the computation of the expression
Correct?: A formula that compares the expected result with the actual result.

In _my_ code, the "Correct?" field equation simply compared if
the actual=expected.  That's not good enough now; for text comparisons,
it should be EXACT(actual;expected).

Section 2.3 of the spec ("Test Cases") is _Supposed_ to be clear enough
about all this.  If it isn't, it's a spec bug, so let's fix it.  I'll try to make
some modifications of section 2.3 to make that clearer and post it today.
One advantage of you redoing this is that you'll notice all the stuff in
section 2.3 that was assumed but not written down... so as you find
mysteries there, let's clarify it.  In fact, now that I look at it, it
isn't as clear as it should be.

> > Just to let you all know that I'll take a shot at writing a program to
> > produce the test suite from the OpenFormula spec. To David's dismay, I'm
> > using an XSLT approach :)  I'm making good progress. I'll keep you
> > posted.

Since you're writing it, I figure you get to pick your poison,
er, tools. I've written XSLT code myself.  XSLT processors are
widely available, so there shouldn't be a problem running it.

--- David A. Wheeler