Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 09.09.2013 22:03, Andrea Pescetti wrote:

On 03/09/2013 Herbert Duerr wrote:

That's a good idea. Here is a first very rough draft:
https://blogs.apache.org/roller-ui/authoring/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now


That link is behind an editor password. Here's the public preview link:

https://blogs.apache.org/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now


And the post is nice too! I would just add something about how people
can help. I assume the best way is to contact the QA list and say that
they could contribute testcases.


Good idea. And thanks for the feedback! I'm afraid I have to postpone 
working on it until we released 4.0.1, though.



Since testcases must be attached anyway, one way to get them could be to
open a specific issue in Bugzilla and attach testcases to is as they
become available.


Yes. And I'm sure that we already have quite a few test documents in 
bugzilla that need just a few tweaks to be usable for automated this 
automated formula testing.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Shemil Hashan]

I need help on steps to make the build on fedora 19.
Please give me steps in simple language with terminal code.
It is urgent. I'm a student of University of Moratuwa,doing a project to
give predictive text for Open Office.
Reply me ASAP,im stuck on the build.


On Mon, Sep 9, 2013 at 4:03 PM, Andrea Pescetti  wrote:

> On 03/09/2013 Herbert Duerr wrote:
>
>> That's a good idea. Here is a first very rough draft:
>> https://blogs.apache.org/**roller-ui/authoring/preview/**
>> OOo/?previewEntry=writing_**spreadsheet_test_cases_now
>>
>
> That link is behind an editor password. Here's the public preview link:
>
> https://blogs.apache.org/**preview/OOo/?previewEntry=**
> writing_spreadsheet_test_**cases_now
>
> And the post is nice too! I would just add something about how people can
> help. I assume the best way is to contact the QA list and say that they
> could contribute testcases.
>
> Since testcases must be attached anyway, one way to get them could be to
> open a specific issue in Bugzilla and attach testcases to is as they become
> available.
>
> Regards,
>   Andrea.
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Andrea Pescetti]

On 03/09/2013 Herbert Duerr wrote:

That's a good idea. Here is a first very rough draft:
https://blogs.apache.org/roller-ui/authoring/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now


That link is behind an editor password. Here's the public preview link:

https://blogs.apache.org/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now

And the post is nice too! I would just add something about how people 
can help. I assume the best way is to contact the QA list and say that 
they could contribute testcases.


Since testcases must be attached anyway, one way to get them could be to 
open a specific issue in Bugzilla and attach testcases to is as they 
become available.


Regards,
  Andrea.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 02.09.2013 21:25, Andrea Pescetti wrote:

On 27/08/2013 Herbert Duerr wrote:

A good idea. I just wrote a task [1] and a script [2] that takes a
slightly modified version of Regina's sample document and checks whether
all of its tests pass. This script is now part of the Functional
Verification Test (a.k.a. FVT).
[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802


Really nice stuff! Where's the blog post?

Seriously, with this simple framework we make testcase preparation
accessible to almost all Calc users, so let's give it the visibility it
deserves.


That's a good idea. Here is a first very rough draft:
https://blogs.apache.org/roller-ui/authoring/preview/OOo/?previewEntry=writing_spreadsheet_test_cases_now

Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Andrea Pescetti]

On 27/08/2013 Herbert Duerr wrote:

A good idea. I just wrote a task [1] and a script [2] that takes a
slightly modified version of Regina's sample document and checks whether
all of its tests pass. This script is now part of the Functional
Verification Test (a.k.a. FVT).
[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802


Really nice stuff! Where's the blog post?

Seriously, with this simple framework we make testcase preparation 
accessible to almost all Calc users, so let's give it the visibility it 
deserves.


Regards,
  Andrea.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 28.08.2013 16:04, Rob Weir wrote:

On Wed, Aug 28, 2013 at 10:00 AM, Herbert Duerr  wrote:

[...]
Yes, just keep the test-ids of the rows with intermediate results empty.
They will be ignored.


Marking lines to ignore with content in a specific column -- this is
so FORTRAN.  I love it.


I think the approach is quite intuitive and there is nothing retro about 
it: Only results that are interesting enough to be reported if they fail 
are checked. These reports should of course contain the name of the 
failed test. If there is no name then there isn't anything worth reporting.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Wed, Aug 28, 2013 at 10:00 AM, Herbert Duerr  wrote:
> On 28.08.2013 14:30, Rob Weir wrote:
>>
>> On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr  wrote:
>>>
>>> On 27.08.2013 15:29, Rob Weir wrote:


 On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:
>
>
> [...]
>
> A good idea. I just wrote a task [1] and a script [2] that takes a
> slightly
> modified version of Regina's sample document and checks whether all of
> its
> tests pass. This script is now part of the Functional Verification Test
> (a.k.a. FVT).
>
> [1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
> [2] http://svn.apache.org/r1517802
>
> Adding further test spreadsheets is simple. They need a "TestID" row
> and
> a
> "TestOK" row like [3]. Such new test spreadsheets have to be added to
> [4]'s
> testFormulaDocs() method to be picked up.
>

 Nice.  If we can have test cases written by non-programmers, and then
 automated testing of these sheets, then we'll have the best of both
 worlds.

 I assume we'd want to agree on a test template.
>>>
>>>
>>>
>>> Here is a suggested test template. It is small and fulfills the
>>> requirements
>>> of containing exactly one sheet and having two columns named "TestID" and
>>> "TestOK". Please use a monospaced font for your mail client to view it:
>>>
>>>   A   B   CD   E   F
>>> 1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
>>> 2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
>>> 3  "Seven"  3   4 7 =B3+C3 =(D3=E3)
>>>
>>>
>>
>> You are able to then introspect the sheet, similar to JUnit, and
>> report the results of each row?  That would be good.
>
>
> Yes, the script uses Liu Zhe's test framework based on junit to introspect
> the sheet.
>
> It only looks for the identifiers "TestID" and "TestOK" so it doesn't matter
> in which columns they are. For performance reason it doesn't check every one
> of the 32 million possible cell positions for these markers but only the
> first 8x8 cells for now. These limits can be increased, but as the test
> documents are also intended for human consumption keeping these markers at
> the start of the test document is a reasonable restriction.
>
>
>> A few complicating factors to consider:
>>
>> 1) Not all functions take two parameters.  Some take more, some less,
>> and many are variable number.  So if the test driver is sensitive to
>> only the label "TestID" and "TestOK" (and not the column index) this
>> would be best.
>
>
> Yes. The other columns are completely ignored. They are just for making the
> calculation clean and obvious for the reader of the spreadsheet.
>
>
>> 2) Some functions operate on ranges and require additional data, stuff
>> that cannot easily be fit into a single row.  For example, DCOUNT(),
>> VLOOKUP(), etc.  Where should we put that test data, so it will not
>> mess up the automation?  Would it be safe to mark the end of the test
>> cases by a blank row, and then any extra test data after that?
>
>
> Rows with empty test-ids are ignored. The test-id of a row is the text
> content of the cell "TestID" column.
>
>
>> 3) Then we have array functions, for example matrix functions like
>> MMULT().  These don't return a single value, but return values into a
>> range.  Maybe in those cases we would do the calculation in an special
>> "test data" area of the sheet, and then have test cases, one per row,
>> to test each value of the returned matrix.  So a 2x2 matrix would have
>> 4 test rows.
>
>
> Yes, just keep the test-ids of the rows with intermediate results empty.
> They will be ignored.
>

Marking lines to ignore with content in a specific column -- this is
so FORTRAN.  I love it.

-Rob


>
> Herbert
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 28.08.2013 14:30, Rob Weir wrote:

On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr  wrote:

On 27.08.2013 15:29, Rob Weir wrote:


On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:


[...]

A good idea. I just wrote a task [1] and a script [2] that takes a
slightly
modified version of Regina's sample document and checks whether all of
its
tests pass. This script is now part of the Functional Verification Test
(a.k.a. FVT).

[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802

Adding further test spreadsheets is simple. They need a "TestID" row and
a
"TestOK" row like [3]. Such new test spreadsheets have to be added to
[4]'s
testFormulaDocs() method to be picked up.



Nice.  If we can have test cases written by non-programmers, and then
automated testing of these sheets, then we'll have the best of both
worlds.

I assume we'd want to agree on a test template.



Here is a suggested test template. It is small and fulfills the requirements
of containing exactly one sheet and having two columns named "TestID" and
"TestOK". Please use a monospaced font for your mail client to view it:

  A   B   CD   E   F
1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
3  "Seven"  3   4 7 =B3+C3 =(D3=E3)




You are able to then introspect the sheet, similar to JUnit, and
report the results of each row?  That would be good.


Yes, the script uses Liu Zhe's test framework based on junit to 
introspect the sheet.


It only looks for the identifiers "TestID" and "TestOK" so it doesn't 
matter in which columns they are. For performance reason it doesn't 
check every one of the 32 million possible cell positions for these 
markers but only the first 8x8 cells for now. These limits can be 
increased, but as the test documents are also intended for human 
consumption keeping these markers at the start of the test document is a 
reasonable restriction.



A few complicating factors to consider:

1) Not all functions take two parameters.  Some take more, some less,
and many are variable number.  So if the test driver is sensitive to
only the label "TestID" and "TestOK" (and not the column index) this
would be best.


Yes. The other columns are completely ignored. They are just for making 
the calculation clean and obvious for the reader of the spreadsheet.



2) Some functions operate on ranges and require additional data, stuff
that cannot easily be fit into a single row.  For example, DCOUNT(),
VLOOKUP(), etc.  Where should we put that test data, so it will not
mess up the automation?  Would it be safe to mark the end of the test
cases by a blank row, and then any extra test data after that?


Rows with empty test-ids are ignored. The test-id of a row is the text 
content of the cell "TestID" column.



3) Then we have array functions, for example matrix functions like
MMULT().  These don't return a single value, but return values into a
range.  Maybe in those cases we would do the calculation in an special
"test data" area of the sheet, and then have test cases, one per row,
to test each value of the returned matrix.  So a 2x2 matrix would have
4 test rows.


Yes, just keep the test-ids of the rows with intermediate results empty. 
They will be ignored.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Wed, Aug 28, 2013 at 4:51 AM, Herbert Duerr  wrote:
> On 27.08.2013 15:29, Rob Weir wrote:
>>
>> On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:
>>>
>>> [...]
>>>
>>> A good idea. I just wrote a task [1] and a script [2] that takes a
>>> slightly
>>> modified version of Regina's sample document and checks whether all of
>>> its
>>> tests pass. This script is now part of the Functional Verification Test
>>> (a.k.a. FVT).
>>>
>>> [1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
>>> [2] http://svn.apache.org/r1517802
>>>
>>> Adding further test spreadsheets is simple. They need a "TestID" row and
>>> a
>>> "TestOK" row like [3]. Such new test spreadsheets have to be added to
>>> [4]'s
>>> testFormulaDocs() method to be picked up.
>>>
>>
>> Nice.  If we can have test cases written by non-programmers, and then
>> automated testing of these sheets, then we'll have the best of both
>> worlds.
>>
>> I assume we'd want to agree on a test template.
>
>
> Here is a suggested test template. It is small and fulfills the requirements
> of containing exactly one sheet and having two columns named "TestID" and
> "TestOK". Please use a monospaced font for your mail client to view it:
>
>  A   B   CD   E   F
> 1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
> 2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
> 3  "Seven"  3   4 7 =B3+C3 =(D3=E3)
>
>

You are able to then introspect the sheet, similar to JUnit, and
report the results of each row?  That would be good.

A few complicating factors to consider:

1) Not all functions take two parameters.  Some take more, some less,
and many are variable number.  So if the test driver is sensitive to
only the label "TestID" and "TestOK" (and not the column index) this
would be best.

2) Some functions operate on ranges and require additional data, stuff
that cannot easily be fit into a single row.  For example, DCOUNT(),
VLOOKUP(), etc.  Where should we put that test data, so it will not
mess up the automation?  Would it be safe to mark the end of the test
cases by a blank row, and then any extra test data after that?

3) Then we have array functions, for example matrix functions like
MMULT().  These don't return a single value, but return values into a
range.  Maybe in those cases we would do the calculation in an special
"test data" area of the sheet, and then have test cases, one per row,
to test each value of the returned matrix.  So a 2x2 matrix would have
4 test rows.

Regards,

-Rob

>>  For example, one
>> approach is to bootstrap the tests like this:
>>
>> 1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
>> handful of other core functions written in Java, not requiring a test
>> spreadsheet.
>
>
> Core functionionality must be checked in one of the pure automatic test
> suites like BVT ("Build Verification Tests") or FVT ("Functional
> Verification Tests") of course.
>
> The idea that additionally to this traditional tests there should also be
> test cases suitable for both manual and automated testing and that can be
> understood, checked and enhanced by non-developers opens options for testers
> and power-users that were not open before to non-developers.
>
>
>> 2) Then write the test cases for the specialized functions in test
>> documents, and reserve a cell (A1 for example) to return 1 if all
>> tests in the document pass, 0 otherwise.
>
>
> The new test script checks the results of each individual sub-test and the
> test log reports each failed test. Having a specialized cell that summarizes
> all the sub-tests into one result is possible of course. For the example
> above a cell =(SUM(F2:F3)=COUNT(F2:F3)) would provide just that. It isn't
> needed for the new automatism though.
>
> The test spreadsheets should mostly be developed to be most useful for
> manual testing and for extending by testers and power users while keeping
> the requirements ("one sheet", "one TestID column" and "one TestOK column")
> for the new automation at a minimum. Adding more requirements again would be
> counterproductive as lowering the barrier of entry is the central point of
> the new mechanism.
>
>
>> 3) Automate the loading and evaluation of the test documents.
>>
>> (Since the test documents would depend on correct operation of IF(),
>> we cannot use the test documents to test IF() itself)
>
>
> A build where simple things like IF() don't work should never make it into
> manual testing. Testers should expect things like that to work.
>
>
> Herbert
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apa

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 27.08.2013 15:29, Rob Weir wrote:

On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:

[...]
A good idea. I just wrote a task [1] and a script [2] that takes a slightly
modified version of Regina's sample document and checks whether all of its
tests pass. This script is now part of the Functional Verification Test
(a.k.a. FVT).

[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802

Adding further test spreadsheets is simple. They need a "TestID" row and a
"TestOK" row like [3]. Such new test spreadsheets have to be added to [4]'s
testFormulaDocs() method to be picked up.



Nice.  If we can have test cases written by non-programmers, and then
automated testing of these sheets, then we'll have the best of both
worlds.

I assume we'd want to agree on a test template.


Here is a suggested test template. It is small and fulfills the 
requirements of containing exactly one sheet and having two columns 
named "TestID" and "TestOK". Please use a monospaced font for your mail 
client to view it:


 A   B   CD   E   F
1  TestIDAddend1 Addend2 ExpectedSum CalculatedSum  TestOK
2   "OnePlusOne"1   1 2 =B2+C2 =(D2=E2)
3  "Seven"  3   4 7 =B3+C3 =(D3=E3)


 For example, one
approach is to bootstrap the tests like this:

1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
handful of other core functions written in Java, not requiring a test
spreadsheet.


Core functionionality must be checked in one of the pure automatic test 
suites like BVT ("Build Verification Tests") or FVT ("Functional 
Verification Tests") of course.


The idea that additionally to this traditional tests there should also 
be test cases suitable for both manual and automated testing and that 
can be understood, checked and enhanced by non-developers opens options 
for testers and power-users that were not open before to non-developers.



2) Then write the test cases for the specialized functions in test
documents, and reserve a cell (A1 for example) to return 1 if all
tests in the document pass, 0 otherwise.


The new test script checks the results of each individual sub-test and 
the test log reports each failed test. Having a specialized cell that 
summarizes all the sub-tests into one result is possible of course. For 
the example above a cell =(SUM(F2:F3)=COUNT(F2:F3)) would provide just 
that. It isn't needed for the new automatism though.


The test spreadsheets should mostly be developed to be most useful for 
manual testing and for extending by testers and power users while 
keeping the requirements ("one sheet", "one TestID column" and "one 
TestOK column") for the new automation at a minimum. Adding more 
requirements again would be counterproductive as lowering the barrier of 
entry is the central point of the new mechanism.



3) Automate the loading and evaluation of the test documents.

(Since the test documents would depend on correct operation of IF(),
we cannot use the test documents to test IF() itself)


A build where simple things like IF() don't work should never make it 
into manual testing. Testers should expect things like that to work.


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Tue, Aug 27, 2013 at 8:44 AM, Herbert Duerr  wrote:
> On 25.08.2013 16:12, Andrea Pescetti wrote:
>>
>> On 19/08/2013 Herbert Duerr wrote:
>>>
>>> An example of a test case where a formula ("addition") is checked is in
>>> [1]. This file is clean and easy enough that it could be used as a
>>> template for more formula checks.
>>> [1]
>>>
>>> http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java
>>>
>>
>> I find it quite verbose (well, it's Java) and too hard for an "ordinary"
>> QA volunteer, who could probably write a simple ODT file to check a
>> bugfix in formula handling but couldn't write a Java testcase.
>>
>> Of course, this approach has also huge benefits, like automation.
>>
>> Perhaps it is possible to convert a few key test cases, like Regina's
>> ODT file in the blocker bug
>> https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework?
>
>
> A good idea. I just wrote a task [1] and a script [2] that takes a slightly
> modified version of Regina's sample document and checks whether all of its
> tests pass. This script is now part of the Functional Verification Test
> (a.k.a. FVT).
>
> [1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
> [2] http://svn.apache.org/r1517802
>
> Adding further test spreadsheets is simple. They need a "TestID" row and a
> "TestOK" row like [3]. Such new test spreadsheets have to be added to [4]'s
> testFormulaDocs() method to be picked up.
>

Nice.  If we can have test cases written by non-programmers, and then
automated testing of these sheets, then we'll have the best of both
worlds.

I assume we'd want to agree on a test template.  For example, one
approach is to bootstrap the tests like this:

1) Have the fundamental test cases for IF(), TRUE(), FALSE() and a
handful of other core functions written in Java, not requiring a test
spreadsheet.

2) Then write the test cases for the specialized functions in test
documents, and reserve a cell (A1 for example) to return 1 if all
tests in the document pass, 0 otherwise.

3) Automate the loading and evaluation of the test documents.

(Since the test documents would depend on correct operation of IF(),
we cannot use the test documents to test IF() itself)

Would something like this work?

-Rob


> [2]
> http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/data/uno/sc/fvt/FormulaTest1.ods
> [3]
> http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java?view=markup
>
> Herbert
>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 25.08.2013 16:12, Andrea Pescetti wrote:

On 19/08/2013 Herbert Duerr wrote:

An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.
[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java



I find it quite verbose (well, it's Java) and too hard for an "ordinary"
QA volunteer, who could probably write a simple ODT file to check a
bugfix in formula handling but couldn't write a Java testcase.

Of course, this approach has also huge benefits, like automation.

Perhaps it is possible to convert a few key test cases, like Regina's
ODT file in the blocker bug
https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework?


A good idea. I just wrote a task [1] and a script [2] that takes a 
slightly modified version of Regina's sample document and checks whether 
all of its tests pass. This script is now part of the Functional 
Verification Test (a.k.a. FVT).


[1] https://issues.apache.org/ooo/show_bug.cgi?id=123119
[2] http://svn.apache.org/r1517802

Adding further test spreadsheets is simple. They need a "TestID" row and 
a "TestOK" row like [3]. Such new test spreadsheets have to be added to 
[4]'s testFormulaDocs() method to be picked up.


[2] 
http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/data/uno/sc/fvt/FormulaTest1.ods
[3] 
http://svn.apache.org/viewvc/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java?view=markup


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Andrea,

Andrea Pescetti schrieb:

On 19/08/2013 Herbert Duerr wrote:

An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.
[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java



I find it quite verbose (well, it's Java) and too hard for an "ordinary"
QA volunteer, who could probably write a simple ODT file to check a
bugfix in formula handling but couldn't write a Java testcase.

Of course, this approach has also huge benefits, like automation.

Perhaps it is possible to convert a few key test cases, like Regina's
ODT file in the blocker bug
https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework?
In this case we have the advantage that we know the expected result, so
ExpectedData() needn't be reimplemented in Java, but it can just
function as a simple operand->result mapping.


I have reworked the testfile in a way, that it shows in a single cell, 
whether there is a regression in calculating. Perhaps someone from the 
QA team can make a test case of it?


Kind regards
Regina

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Andrea Pescetti]

On 19/08/2013 Herbert Duerr wrote:

An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.
[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java


I find it quite verbose (well, it's Java) and too hard for an "ordinary" 
QA volunteer, who could probably write a simple ODT file to check a 
bugfix in formula handling but couldn't write a Java testcase.


Of course, this approach has also huge benefits, like automation.

Perhaps it is possible to convert a few key test cases, like Regina's 
ODT file in the blocker bug 
https://issues.apache.org/ooo/show_bug.cgi?id=122997 to this framework? 
In this case we have the advantage that we know the expected result, so 
ExpectedData() needn't be reimplemented in Java, but it can just 
function as a simple operand->result mapping.


Regards,
  Andrea.

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Inge Wallin]

On Friday, August 16, 2013 13:32:30 Rob Weir wrote:
> Moving this topic to its own thread.
> 
> It should be possible to code a very thorough set of test cases in a
> spreadsheet, without using macros or anything fancy.  Just careful
> reading of the ODF 1.2 specification and simple spreadsheet logic.
> 
> I'd like to share an example of this that I created for one of the ODF
> Plugfests.  This is a test of a single function -- YEARFRAC.  You
> probably have never touched this function, but it exhibits all the
> pathological behavior, in a purer form, of the other financial
> functions.  Specifically, it is a pure test of our "date counting
> conventions", the various ways that accountants handle date
> calculations.
> 
> The test document is here:
> 
> http://www.robweir.com/basis-test.xls
> 
> (I did it in XLS format since I wanted to make sure Microsoft could
> use it at the Plugfest as well.  At that time they were not able to
> read ODF formulas.)
> 
> This is likely the most complicated set of test cases of any
> spreadsheet formula.  So if we can test YEARFRAC this way then we can
> test any function this way.
> 
> Column C is the formula to evaluate.  Column F is the expected value,
> which is calculated by hand, according to the ODF standard.  And
> colu,mn G reports whether they match or not.  (This would be a good
> place for us to use conditional formatting as well, though in the
> Plugfest case I needed to make the spreadsheet be as vanilla as
> possible so every editor could load it)
> 
> Note that this is an exhaustive set of test cases that aim to test
> every corner of the formula.  It is a torture test.  Excel gets all
> the test cases right.  Not a surprise, since we took Excel's behavior
> as normative when writing this part of the standard.

Good test!

So how is AOO doing?

Just FYI, Calligra Sheets nails the test. :)

-Inge


> If we used an approach like this on the other spreadsheet functions,
> we could have a semi-automated test suite that would practically
> guarantee that Calc is free of calculations errors.  Once we're
> written the test cases, a modest upfront investment, it will benefit
> us with every release we do.  Heck, it would benefit LibreOffice,
> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
> they might already have such test cases defined internally.
> 
> Anyone interesting in helping with this kind of test case development?
> 
> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
> we're not starting from a  perfect score.  But we should find an easy
> way to report on regressions.
> 
> -Rob
> 
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[sebb]

On 19 August 2013 11:41, Regina Henschel  wrote:
> Hi Jan,
>
> janI schrieb:
>
>> On 19 August 2013 12:24, Regina Henschel  wrote:
>>
> [..]
>
>>> But for both kind of testing there exists a problem with "expected
>>> values". For example, calculation of PMT needs expm1 and log1p, or
>>> calculation of LINEST needs lot of matrix calculation. It is not
>>> impossible
>>> to calculate this with Java, but who will do it? And when you use
>>> constant
>>> expected values, from where do you get them and how to ensure, that they
>>> are valid?
>>>
>>
>> Just one simple question, how do you do manually today ? use the same
>> constants.
>
>
> I use my MuPad 3.1 and sometimes calculate values on
> http://www.wolframalpha.com/
>
> Or I compare the values to those from Gnumeric and Excel. Having the same
> value does not mean that they are correct, but the other way round, having
> different values means that I have to investigate it.
>
>
>>
>> We should not try to invent a total new road, just do what we do today
>> more
>> efficiently.
>
>
> Suggestions?

If there are a *lot* of similar calculations that need to be tested,
then it might be worth investing some time looking at at ways to
expresss these simply as a script, and then write a parser to drive
the existing test code from the script, rather than having to update
the Java test code.

The advantage would be that just about anyone can create test cases,
and they are easy to check by hand.

However the parser may be a lot of work; I don't know.

Maybe there is already a suitable script language which already has
suitable examples?

Also there are various script interpreters that already undestand some
calculations, for example Commons JEXL.
It should be possible to use them to check the test cases, and
possibly even use as a basis for parsing the test cases.

> Kind regards
> Regina
>
>
>
>
> -
> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
>
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Jan,

janI schrieb:

On 19 August 2013 12:24, Regina Henschel  wrote:


[..]

But for both kind of testing there exists a problem with "expected
values". For example, calculation of PMT needs expm1 and log1p, or
calculation of LINEST needs lot of matrix calculation. It is not impossible
to calculate this with Java, but who will do it? And when you use constant
expected values, from where do you get them and how to ensure, that they
are valid?



Just one simple question, how do you do manually today ? use the same
constants.


I use my MuPad 3.1 and sometimes calculate values on 
http://www.wolframalpha.com/


Or I compare the values to those from Gnumeric and Excel. Having the 
same value does not mean that they are correct, but the other way round, 
having different values means that I have to investigate it.




We should not try to invent a total new road, just do what we do today more
efficiently.


Suggestions?

Kind regards
Regina




-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[janI]

On 19 August 2013 12:24, Regina Henschel  wrote:

> Hi Herbert,
>
> Herbert Duerr schrieb:
>
>  On 16.08.2013 21:37, Regina Henschel wrote:
>>
>>> Rob Weir schrieb:
>>>
 Moving this topic to its own thread.

 It should be possible to code a very thorough set of test cases in a
 spreadsheet, without using macros or anything fancy.  Just careful
 reading of the ODF 1.2 specification and simple spreadsheet logic.


>>> Following the spec is not enough. For example, if the accuracy decreases
>>> from 14 digits to 7 digits, that is not covered by the spec.
>>>
>>> 
>>>
>>>  If we used an approach like this on the other spreadsheet functions,
 we could have a semi-automated test suite that would practically
 guarantee that Calc is free of calculations errors.  Once we're
 written the test cases, a modest upfront investment

>>>
>>> "modest"? One function a day and you need more than a year.
>>>
>>> , it will benefit
>>>
 us with every release we do.  Heck, it would benefit LibreOffice,
 Gnumeric, Calligra as well, maybe even Microsoft and Google, though
 they might already have such test cases defined internally.

>>>
>>> I see a problem in how such a test suite is made available. And how the
>>> results for a special release are collected.
>>>
>>> The problem with the current test cases is, that I do not know where
>>> they are, how they are to use and how to generate new ones. It is a
>>> closed book, only for insiders.
>>>
>>
>> An example of a test case where a formula ("addition") is checked is in
>> [1]. This file is clean and easy enough that it could be used as a
>> template for more formula checks.
>>
>> [1]
>> http://svn.apache.org/repos/**asf/openoffice/trunk/test/**
>> testuno/source/fvt/uno/sc/**formula/**AddtionOperatorInFormula.java
>>
>>
>> The general topic on getting started with test automation is covered in
>> [2].
>>
>> [2] 
>> http://wiki.openoffice.org/**wiki/QA/test_automation_guide
>>
>>  [..]
>
>
>> There is no need to develop a new framework. Please check Zhe Liu's
>> wonderful work on test automation that I referenced above [2] that is
>> already available in our "test/" directory. It is very powerful, clean
>> and relatively easy to use.
>>
>
> That is was I meant with "closed". This cannot be done by everyone.
>
> To get a wider testing it must be easy, so easy as "download the file,
> open it and see, whether something is red".
>
> It seems "automatic test" and "manual test" have to be separated.
>
>
> But for both kind of testing there exists a problem with "expected
> values". For example, calculation of PMT needs expm1 and log1p, or
> calculation of LINEST needs lot of matrix calculation. It is not impossible
> to calculate this with Java, but who will do it? And when you use constant
> expected values, from where do you get them and how to ensure, that they
> are valid?
>

Just one simple question, how do you do manually today ? use the same
constants.

We should not try to invent a total new road, just do what we do today more
efficiently.

rgds
jan I

>
> Kind regards
> Regina
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Herbert,

Herbert Duerr schrieb:

On 16.08.2013 21:37, Regina Henschel wrote:

Rob Weir schrieb:

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.



Following the spec is not enough. For example, if the accuracy decreases
from 14 digits to 7 digits, that is not covered by the spec.




If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment


"modest"? One function a day and you need more than a year.

, it will benefit

us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.


I see a problem in how such a test suite is made available. And how the
results for a special release are collected.

The problem with the current test cases is, that I do not know where
they are, how they are to use and how to generate new ones. It is a
closed book, only for insiders.


An example of a test case where a formula ("addition") is checked is in
[1]. This file is clean and easy enough that it could be used as a
template for more formula checks.

[1]
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java


The general topic on getting started with test automation is covered in
[2].

[2] http://wiki.openoffice.org/wiki/QA/test_automation_guide


[..]


There is no need to develop a new framework. Please check Zhe Liu's
wonderful work on test automation that I referenced above [2] that is
already available in our "test/" directory. It is very powerful, clean
and relatively easy to use.


That is was I meant with "closed". This cannot be done by everyone.

To get a wider testing it must be easy, so easy as "download the file, 
open it and see, whether something is red".


It seems "automatic test" and "manual test" have to be separated.


But for both kind of testing there exists a problem with "expected 
values". For example, calculation of PMT needs expm1 and log1p, or 
calculation of LINEST needs lot of matrix calculation. It is not 
impossible to calculate this with Java, but who will do it? And when you 
use constant expected values, from where do you get them and how to 
ensure, that they are valid?


Kind regards
Regina

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 19.08.2013 11:18, janI wrote:

On 19 August 2013 10:43, Herbert Duerr  wrote:

[...]
There is no need to develop a new framework. Please check Zhe Liu's
wonderful work on test automation that I referenced above [2] that is
already available in our "test/" directory. It is very powerful, clean and
relatively easy to use.


not only does it look very powerful, the documentation is easy to read and
seems very complete.

Am I correct in assuming, this is not used today for our current testing ?


Its quite easy for everyone who is able to checkout and build AOO to run 
them [1]. The "official" Apache don't run them yet. My local buildbot 
does daily BVT (Build Verification Tests) and FVT (Functional 
Verification Tests) tests.


The full test suite of BVT (Build), FVT (Functional), SVT (System), and 
PVT (Performance) tests takes about 24h on dedicated powerful hardware. 
And there are still too many false positives resulting from timeouts, 
connection problems, wrong slot IDs, etc.



If a couple of the testers started to use that and update/expand with their
tests, it would cost nearly the same time as the test does today, but with
the huge benefit, that we can repeat the test as often as we like.

big +1 from me, to get us running down this path.


Agreed. The framework is a great asset that should be leveraged. If we 
can eliminate the still too frequent false positives then we could send 
automatic reports to the mailing list.


[1] 
http://wiki.openoffice.org/wiki/QA/test_automation_guide#Getting_started_with_command_line


Herbert

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[janI]

On 19 August 2013 10:43, Herbert Duerr  wrote:

> On 16.08.2013 21:37, Regina Henschel wrote:
>
>> Rob Weir schrieb:
>>
>>> Moving this topic to its own thread.
>>>
>>> It should be possible to code a very thorough set of test cases in a
>>> spreadsheet, without using macros or anything fancy.  Just careful
>>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>>
>>>
>> Following the spec is not enough. For example, if the accuracy decreases
>> from 14 digits to 7 digits, that is not covered by the spec.
>>
>> 
>>
>>  If we used an approach like this on the other spreadsheet functions,
>>> we could have a semi-automated test suite that would practically
>>> guarantee that Calc is free of calculations errors.  Once we're
>>> written the test cases, a modest upfront investment
>>>
>>
>> "modest"? One function a day and you need more than a year.
>>
>> , it will benefit
>>
>>> us with every release we do.  Heck, it would benefit LibreOffice,
>>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>>> they might already have such test cases defined internally.
>>>
>>
>> I see a problem in how such a test suite is made available. And how the
>> results for a special release are collected.
>>
>> The problem with the current test cases is, that I do not know where
>> they are, how they are to use and how to generate new ones. It is a
>> closed book, only for insiders.
>>
>
> An example of a test case where a formula ("addition") is checked is in
> [1]. This file is clean and easy enough that it could be used as a template
> for more formula checks.
>
> [1] http://svn.apache.org/repos/**asf/openoffice/trunk/test/**
> testuno/source/fvt/uno/sc/**formula/**AddtionOperatorInFormula.java
>
> The general topic on getting started with test automation is covered in
> [2].
>
> [2] 
> http://wiki.openoffice.org/**wiki/QA/test_automation_guide
>
>  Anyone interesting in helping with this kind of test case development?
>>>
>>
>> There exist some files already in Bugzilla. I used to make test
>> documents, when working on functions. I think, that they can be extended
>> to work in a way, that a simple look on it will tell errors. But I have
>> no ready collection on my PC and most will be already deleted from my PC
>> in the meantime.
>>
>> One problem is, that comparisons with constants have to be written in a
>> way, that they are independent from local. Eike has once corrected one
>> of my test spreadsheets that way.
>>
>>
>>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>>> we're not starting from a  perfect score.  But we should find an easy
>>> way to report on regressions.
>>>
>>
>> If you will automate this, you will need to develop a frame. But
>> automation is not the total solution. Testing can be a way to bring user
>> into the community. And tests have to cover different languages and
>> scripts. I remember errors reported to LibreOffice, where a time
>> calculation was wrong only in special locals. To extend a testing frame
>> to consider this would be very expensive.
>>
>
> There is no need to develop a new framework. Please check Zhe Liu's
> wonderful work on test automation that I referenced above [2] that is
> already available in our "test/" directory. It is very powerful, clean and
> relatively easy to use.
>

not only does it look very powerful, the documentation is easy to read and
seems very complete.

Am I correct in assuming, this is not used today for our current testing ?

If a couple of the testers started to use that and update/expand with their
tests, it would cost nearly the same time as the test does today, but with
the huge benefit, that we can repeat the test as often as we like.

big +1 from me, to get us running down this path.


rgds
jan I.


> Herbert
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Herbert Duerr]

On 16.08.2013 21:37, Regina Henschel wrote:

Rob Weir schrieb:

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.



Following the spec is not enough. For example, if the accuracy decreases
from 14 digits to 7 digits, that is not covered by the spec.




If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment


"modest"? One function a day and you need more than a year.

, it will benefit

us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.


I see a problem in how such a test suite is made available. And how the
results for a special release are collected.

The problem with the current test cases is, that I do not know where
they are, how they are to use and how to generate new ones. It is a
closed book, only for insiders.


An example of a test case where a formula ("addition") is checked is in 
[1]. This file is clean and easy enough that it could be used as a 
template for more formula checks.


[1] 
http://svn.apache.org/repos/asf/openoffice/trunk/test/testuno/source/fvt/uno/sc/formula/AddtionOperatorInFormula.java


The general topic on getting started with test automation is covered in [2].

[2] http://wiki.openoffice.org/wiki/QA/test_automation_guide


Anyone interesting in helping with this kind of test case development?


There exist some files already in Bugzilla. I used to make test
documents, when working on functions. I think, that they can be extended
to work in a way, that a simple look on it will tell errors. But I have
no ready collection on my PC and most will be already deleted from my PC
in the meantime.

One problem is, that comparisons with constants have to be written in a
way, that they are independent from local. Eike has once corrected one
of my test spreadsheets that way.



Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
we're not starting from a  perfect score.  But we should find an easy
way to report on regressions.


If you will automate this, you will need to develop a frame. But
automation is not the total solution. Testing can be a way to bring user
into the community. And tests have to cover different languages and
scripts. I remember errors reported to LibreOffice, where a time
calculation was wrong only in special locals. To extend a testing frame
to consider this would be very expensive.


There is no need to develop a new framework. Please check Zhe Liu's 
wonderful work on test automation that I referenced above [2] that is 
already available in our "test/" directory. It is very powerful, clean 
and relatively easy to use.


Herbert


-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Re: Example of spreadsheet formula testing

[Rob Weir]

On Fri, Aug 16, 2013 at 4:47 PM, janI  wrote:
> On 16 August 2013 22:14, Rob Weir  wrote:
>
>> On Fri, Aug 16, 2013 at 3:51 PM, janI  wrote:
>> > On 16 August 2013 21:37, Regina Henschel 
>> wrote:
>> >
>> >> Hi Rob,
>> >>
>> >> Rob Weir schrieb:
>> >>
>> >>> Moving this topic to its own thread.
>> >>>
>> >>> It should be possible to code a very thorough set of test cases in a
>> >>> spreadsheet, without using macros or anything fancy.  Just careful
>> >>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>> >>>
>> >>>
>> >> Following the spec is not enough. For example, if the accuracy decreases
>> >> from 14 digits to 7 digits, that is not covered by the spec.
>> >>
>> >> 
>> >>
>> >>  If we used an approach like this on the other spreadsheet functions,
>> >>> we could have a semi-automated test suite that would practically
>> >>> guarantee that Calc is free of calculations errors.  Once we're
>> >>> written the test cases, a modest upfront investment
>> >>>
>> >>
>> >> "modest"? One function a day and you need more than a year.
>> >>
>> >
>> > I think that a bit over the top, you can do quite a lot with an editor
>> :-)
>> >
>> > And dont forget, if it really takes that long, how long does it then take
>> > to test it manually, no less I assume.
>> >
>> > so its a win-win situation, first person that tests functions write the
>> > first macros and so on.
>> >
>> >
>> >>
>> >> , it will benefit
>> >>
>> >>> us with every release we do.  Heck, it would benefit LibreOffice,
>> >>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>> >>> they might already have such test cases defined internally.
>> >>>
>> >>
>> >> I see a problem in how such a test suite is made available. And how the
>> >> results for a special release are collected.
>> >>
>> >> The problem with the current test cases is, that I do not know where
>> they
>> >> are, how they are to use and how to generate new ones. It is a closed
>> book,
>> >> only for insiders.
>> >>
>> >
>> > Thats a matter of documentation, I did not know where build.pl was
>> stored
>> > or how the build system worked before I invested time. Everything is a
>> > closed book and for insider until you know it.
>> >
>> >
>> >>
>> >>
>> >>> Anyone interesting in helping with this kind of test case development?
>> >>>
>> >>
>> >> There exist some files already in Bugzilla. I used to make test
>> documents,
>> >> when working on functions. I think, that they can be extended to work
>> in a
>> >> way, that a simple look on it will tell errors. But I have no ready
>> >> collection on my PC and most will be already deleted from my PC in the
>> >> meantime.
>> >>
>> >
>> > ODF must also as such have test suites to test the specification.
>> >
>>
>> There are test cases, but they are incomplete.  And I think everything
>> must be hand-verified.
>>
>> A short story of what can go wrong.  When the OOXML standard was
>> written the authors included an example calculation for each function,
>> to show what the expected result was.  They also gave a text
>> description and in many cases an equation in mathematical notation.
>>
>> This all looked great.  But when I looked closely I found that the
>> test cases were just copy & paste from Excel output.  And in some
>> cases the result given was not what the mathematical equation said.
>> They were not in synch.  So it was a case of "never go to sea with two
>> compasses", because you then are lost if they disagree.
>>
>> So if we do this we really need to generate the expected values from
>> the specification itself, by hand, or using some other trusted tool,
>> like an HP financial calculator.  If you look at the details of my
>> YEARFRAC test sheet you see that is what I did.As teaches tell
>> their students, "show your work", not just the final result.
>>
>
> I see your point, and I am probably to full blooded a programmer, used to
> specs like those from W3N, nice BN formulas and at the same time a test
> suite thats real big. It used to be a selling point to say that the html
> part of our software had passed the W3C test suites.
>

We use EBNF for the basic formula syntax, but the spec did not include
a formal test suite when it was published.

There were different views on this.  One school of thought was more
like the W3C's :  a test suite for every standard along with feedback
from implementers.  But we also have others coming from an ISO
perspective, with the view that a standard should define things once
and only once, and any redundancy is asking for trouble.   If the
definition says one thing and the example says another, then who is
right?

So we ended up not publishing the test suite.

> But I agree, as you write it, we need to be careful, or to make things
> simple (to get us started), assume our current calc is ok, and just to be
> sure compare with e.g. excel.
>

You really need to take it from all directions.  The test suite could
have errors.  Implementations could have errors.

Re: Example of spreadsheet formula testing

[janI]

On 16 August 2013 22:14, Rob Weir  wrote:

> On Fri, Aug 16, 2013 at 3:51 PM, janI  wrote:
> > On 16 August 2013 21:37, Regina Henschel 
> wrote:
> >
> >> Hi Rob,
> >>
> >> Rob Weir schrieb:
> >>
> >>> Moving this topic to its own thread.
> >>>
> >>> It should be possible to code a very thorough set of test cases in a
> >>> spreadsheet, without using macros or anything fancy.  Just careful
> >>> reading of the ODF 1.2 specification and simple spreadsheet logic.
> >>>
> >>>
> >> Following the spec is not enough. For example, if the accuracy decreases
> >> from 14 digits to 7 digits, that is not covered by the spec.
> >>
> >> 
> >>
> >>  If we used an approach like this on the other spreadsheet functions,
> >>> we could have a semi-automated test suite that would practically
> >>> guarantee that Calc is free of calculations errors.  Once we're
> >>> written the test cases, a modest upfront investment
> >>>
> >>
> >> "modest"? One function a day and you need more than a year.
> >>
> >
> > I think that a bit over the top, you can do quite a lot with an editor
> :-)
> >
> > And dont forget, if it really takes that long, how long does it then take
> > to test it manually, no less I assume.
> >
> > so its a win-win situation, first person that tests functions write the
> > first macros and so on.
> >
> >
> >>
> >> , it will benefit
> >>
> >>> us with every release we do.  Heck, it would benefit LibreOffice,
> >>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
> >>> they might already have such test cases defined internally.
> >>>
> >>
> >> I see a problem in how such a test suite is made available. And how the
> >> results for a special release are collected.
> >>
> >> The problem with the current test cases is, that I do not know where
> they
> >> are, how they are to use and how to generate new ones. It is a closed
> book,
> >> only for insiders.
> >>
> >
> > Thats a matter of documentation, I did not know where build.pl was
> stored
> > or how the build system worked before I invested time. Everything is a
> > closed book and for insider until you know it.
> >
> >
> >>
> >>
> >>> Anyone interesting in helping with this kind of test case development?
> >>>
> >>
> >> There exist some files already in Bugzilla. I used to make test
> documents,
> >> when working on functions. I think, that they can be extended to work
> in a
> >> way, that a simple look on it will tell errors. But I have no ready
> >> collection on my PC and most will be already deleted from my PC in the
> >> meantime.
> >>
> >
> > ODF must also as such have test suites to test the specification.
> >
>
> There are test cases, but they are incomplete.  And I think everything
> must be hand-verified.
>
> A short story of what can go wrong.  When the OOXML standard was
> written the authors included an example calculation for each function,
> to show what the expected result was.  They also gave a text
> description and in many cases an equation in mathematical notation.
>
> This all looked great.  But when I looked closely I found that the
> test cases were just copy & paste from Excel output.  And in some
> cases the result given was not what the mathematical equation said.
> They were not in synch.  So it was a case of "never go to sea with two
> compasses", because you then are lost if they disagree.
>
> So if we do this we really need to generate the expected values from
> the specification itself, by hand, or using some other trusted tool,
> like an HP financial calculator.  If you look at the details of my
> YEARFRAC test sheet you see that is what I did.As teaches tell
> their students, "show your work", not just the final result.
>

I see your point, and I am probably to full blooded a programmer, used to
specs like those from W3N, nice BN formulas and at the same time a test
suite thats real big. It used to be a selling point to say that the html
part of our software had passed the W3C test suites.

But I agree, as you write it, we need to be careful, or to make things
simple (to get us started), assume our current calc is ok, and just to be
sure compare with e.g. excel.


> In any case, I like the idea of a focus here, for two reasons:
>
> 1) It lends itself well to automation
>
> 2) From a user perspective a spreadsheet can do almost anything else
> wrong, but it must not do calculations wrong.  This is the core trust
> of a spreadsheet application.  So it makes sense to really nail this
> area.
>

Once we have that nailed, it more or less exact the same (but much more
complicated) to test writer.

rgds
jan I.


>
>
> >
> >>
> >> One problem is, that comparisons with constants have to be written in a
> >> way, that they are independent from local. Eike has once corrected one
> of
> >> my test spreadsheets that way.
> >>
> >
> > We can simply assume for a start (that would be huge) that automated
> > testing runs in the en-US environment, then if there are problems it is
> > very likely in the translation.
> >
> >

Re: Example of spreadsheet formula testing

[Rob Weir]

On Fri, Aug 16, 2013 at 3:51 PM, janI  wrote:
> On 16 August 2013 21:37, Regina Henschel  wrote:
>
>> Hi Rob,
>>
>> Rob Weir schrieb:
>>
>>> Moving this topic to its own thread.
>>>
>>> It should be possible to code a very thorough set of test cases in a
>>> spreadsheet, without using macros or anything fancy.  Just careful
>>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>>
>>>
>> Following the spec is not enough. For example, if the accuracy decreases
>> from 14 digits to 7 digits, that is not covered by the spec.
>>
>> 
>>
>>  If we used an approach like this on the other spreadsheet functions,
>>> we could have a semi-automated test suite that would practically
>>> guarantee that Calc is free of calculations errors.  Once we're
>>> written the test cases, a modest upfront investment
>>>
>>
>> "modest"? One function a day and you need more than a year.
>>
>
> I think that a bit over the top, you can do quite a lot with an editor :-)
>
> And dont forget, if it really takes that long, how long does it then take
> to test it manually, no less I assume.
>
> so its a win-win situation, first person that tests functions write the
> first macros and so on.
>
>
>>
>> , it will benefit
>>
>>> us with every release we do.  Heck, it would benefit LibreOffice,
>>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>>> they might already have such test cases defined internally.
>>>
>>
>> I see a problem in how such a test suite is made available. And how the
>> results for a special release are collected.
>>
>> The problem with the current test cases is, that I do not know where they
>> are, how they are to use and how to generate new ones. It is a closed book,
>> only for insiders.
>>
>
> Thats a matter of documentation, I did not know where build.pl was stored
> or how the build system worked before I invested time. Everything is a
> closed book and for insider until you know it.
>
>
>>
>>
>>> Anyone interesting in helping with this kind of test case development?
>>>
>>
>> There exist some files already in Bugzilla. I used to make test documents,
>> when working on functions. I think, that they can be extended to work in a
>> way, that a simple look on it will tell errors. But I have no ready
>> collection on my PC and most will be already deleted from my PC in the
>> meantime.
>>
>
> ODF must also as such have test suites to test the specification.
>

There are test cases, but they are incomplete.  And I think everything
must be hand-verified.

A short story of what can go wrong.  When the OOXML standard was
written the authors included an example calculation for each function,
to show what the expected result was.  They also gave a text
description and in many cases an equation in mathematical notation.

This all looked great.  But when I looked closely I found that the
test cases were just copy & paste from Excel output.  And in some
cases the result given was not what the mathematical equation said.
They were not in synch.  So it was a case of "never go to sea with two
compasses", because you then are lost if they disagree.

So if we do this we really need to generate the expected values from
the specification itself, by hand, or using some other trusted tool,
like an HP financial calculator.  If you look at the details of my
YEARFRAC test sheet you see that is what I did.As teaches tell
their students, "show your work", not just the final result.

In any case, I like the idea of a focus here, for two reasons:

1) It lends itself well to automation

2) From a user perspective a spreadsheet can do almost anything else
wrong, but it must not do calculations wrong.  This is the core trust
of a spreadsheet application.  So it makes sense to really nail this
area.


>
>>
>> One problem is, that comparisons with constants have to be written in a
>> way, that they are independent from local. Eike has once corrected one of
>> my test spreadsheets that way.
>>
>
> We can simply assume for a start (that would be huge) that automated
> testing runs in the en-US environment, then if there are problems it is
> very likely in the translation.
>
>
>>
>>
>>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>>> we're not starting from a  perfect score.  But we should find an easy
>>> way to report on regressions.
>>>
>>
>> If you will automate this, you will need to develop a frame. But
>> automation is not the total solution. Testing can be a way to bring user
>> into the community. And tests have to cover different languages and
>> scripts. I remember errors reported to LibreOffice, where a time
>> calculation was wrong only in special locals. To extend a testing frame to
>> consider this would be very expensive.
>>
>> I simply disagree, I come from a company where every bug was turned into
> at least one test case in the framework, that was not all expensive, merely
> giving the developer a slightly different job. Using this philosophy we
> guarantee that a bug

Re: Example of spreadsheet formula testing

[Rob Weir]

On Fri, Aug 16, 2013 at 3:37 PM, Regina Henschel
 wrote:
> Hi Rob,
>
> Rob Weir schrieb:
>
>> Moving this topic to its own thread.
>>
>> It should be possible to code a very thorough set of test cases in a
>> spreadsheet, without using macros or anything fancy.  Just careful
>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>
>
> Following the spec is not enough. For example, if the accuracy decreases
> from 14 digits to 7 digits, that is not covered by the spec.
>
> 
>

This is not hard to check.  When you save an ODF document in version
N, it saves the last-calculated value of each cell as well.  It would
be possible to verify that the same values are returned in AOO version
N as well.  Probably not via AOO itself, but you can access the
last-saved value in the cell via the ODF Toolkit, for example.   Load
sheet, save, compare to sheet saved in previous version (or some other
reference version).

>
>> If we used an approach like this on the other spreadsheet functions,
>> we could have a semi-automated test suite that would practically
>> guarantee that Calc is free of calculations errors.  Once we're
>> written the test cases, a modest upfront investment
>
>
> "modest"? One function a day and you need more than a year.
>

There are ways of making this more efficient.  You group together
functions that are related and have the same basic input values.  For
example, the many bin/oct/hex conversion functions, the logical
IF/AND/XOR functions, the DCOUNT/DMAX/DMIN database functions, etc.
Try to reuse the same test conditions for related functions.

So there is a lot of reuse possible.  But the parts are also very
independent, so the work can be done in parallel by any interested
volunteers.

>
> , it will benefit
>>
>> us with every release we do.  Heck, it would benefit LibreOffice,
>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>> they might already have such test cases defined internally.
>
>
> I see a problem in how such a test suite is made available. And how the
> results for a special release are collected.
>

I was thinking of storing them in SVN, something like
/test-docs/calc/functions or something like that.  I think we want to
make it easy for a tester to download the whole collection of test
sheets without needing to download them individually from a wiki page,
for example.

> The problem with the current test cases is, that I do not know where they
> are, how they are to use and how to generate new ones. It is a closed book,
> only for insiders.
>

Do we have test documents like this today?   I don't want to do redundant work.

>
>>
>> Anyone interesting in helping with this kind of test case development?
>
>
> There exist some files already in Bugzilla. I used to make test documents,
> when working on functions. I think, that they can be extended to work in a
> way, that a simple look on it will tell errors. But I have no ready
> collection on my PC and most will be already deleted from my PC in the
> meantime.
>
> One problem is, that comparisons with constants have to be written in a way,
> that they are independent from local. Eike has once corrected one of my test
> spreadsheets that way.
>

Or we agree on a locale to be used for calculation testing.  There are
only a small number of locale-dependent calculations in Calc.  I have
another test document that triggers all the implementation-dependent,
implementation-defined, locale-defined and undefined behaviors in
OpenFormula.  For those there is no single "correct" answer.  But we
can use it to verify that the calculations do not change unexpectedly
from release to release.

Or put differently, it might make sense to isolate the
locale-sensitive calculations from the locale-insensitive ones and
approach the testing of them differently.

>
>>
>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>> we're not starting from a  perfect score.  But we should find an easy
>> way to report on regressions.
>
>
> If you will automate this, you will need to develop a frame. But automation
> is not the total solution. Testing can be a way to bring user into the
> community. And tests have to cover different languages and scripts. I
> remember errors reported to LibreOffice, where a time calculation was wrong
> only in special locals. To extend a testing frame to consider this would be
> very expensive.
>
> Let me not be misunderstood, I like the idea of collecting test cases.
>

I'm thinking less of "collecting" test cases and more of designing a
test suite with specific coverage goals in mind.  The coverage may not
be 100% if you consider the locale aspect of it.  But it is 100%
within the domain it aims to cover, and by doing that insulates from
defects in that area.

>From a community perspective I think it would have value attracting
those who are more interested in the formal aspects of QA and in
building skills there.  But it would not be interesting to everyone.

Regards,

-Rob

Re: Example of spreadsheet formula testing

[janI]

On 16 August 2013 21:37, Regina Henschel  wrote:

> Hi Rob,
>
> Rob Weir schrieb:
>
>> Moving this topic to its own thread.
>>
>> It should be possible to code a very thorough set of test cases in a
>> spreadsheet, without using macros or anything fancy.  Just careful
>> reading of the ODF 1.2 specification and simple spreadsheet logic.
>>
>>
> Following the spec is not enough. For example, if the accuracy decreases
> from 14 digits to 7 digits, that is not covered by the spec.
>
> 
>
>  If we used an approach like this on the other spreadsheet functions,
>> we could have a semi-automated test suite that would practically
>> guarantee that Calc is free of calculations errors.  Once we're
>> written the test cases, a modest upfront investment
>>
>
> "modest"? One function a day and you need more than a year.
>

I think that a bit over the top, you can do quite a lot with an editor :-)

And dont forget, if it really takes that long, how long does it then take
to test it manually, no less I assume.

so its a win-win situation, first person that tests functions write the
first macros and so on.


>
> , it will benefit
>
>> us with every release we do.  Heck, it would benefit LibreOffice,
>> Gnumeric, Calligra as well, maybe even Microsoft and Google, though
>> they might already have such test cases defined internally.
>>
>
> I see a problem in how such a test suite is made available. And how the
> results for a special release are collected.
>
> The problem with the current test cases is, that I do not know where they
> are, how they are to use and how to generate new ones. It is a closed book,
> only for insiders.
>

Thats a matter of documentation, I did not know where build.pl was stored
or how the build system worked before I invested time. Everything is a
closed book and for insider until you know it.


>
>
>> Anyone interesting in helping with this kind of test case development?
>>
>
> There exist some files already in Bugzilla. I used to make test documents,
> when working on functions. I think, that they can be extended to work in a
> way, that a simple look on it will tell errors. But I have no ready
> collection on my PC and most will be already deleted from my PC in the
> meantime.
>

ODF must also as such have test suites to test the specification.


>
> One problem is, that comparisons with constants have to be written in a
> way, that they are independent from local. Eike has once corrected one of
> my test spreadsheets that way.
>

We can simply assume for a start (that would be huge) that automated
testing runs in the en-US environment, then if there are problems it is
very likely in the translation.


>
>
>> Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
>> we're not starting from a  perfect score.  But we should find an easy
>> way to report on regressions.
>>
>
> If you will automate this, you will need to develop a frame. But
> automation is not the total solution. Testing can be a way to bring user
> into the community. And tests have to cover different languages and
> scripts. I remember errors reported to LibreOffice, where a time
> calculation was wrong only in special locals. To extend a testing frame to
> consider this would be very expensive.
>
> I simply disagree, I come from a company where every bug was turned into
at least one test case in the framework, that was not all expensive, merely
giving the developer a slightly different job. Using this philosophy we
guarantee that a bug never reoccurs and that we test more and more with
every regression.

Testing AOO is actually quite simple than testing live systems, where the
history changes the behavior of the system. Apart from very few test cases,
we can simply restart every time.

We do need a test framework, but that is much more documentation and then
use what we have. In the first line macros, later perhaps scripting through
the extension framework. But alone with macros we have come a long way.

> Let me not be misunderstood, I like the idea of collecting test cases.
>

I could have misunderstood what you wrote, but I know we all have the same
goal, a high quality.

rgds
jan I


> Kind regard
> Regina
>
>
>
>
> --**--**-
> To unsubscribe, e-mail: 
> dev-unsubscribe@openoffice.**apache.org
> For additional commands, e-mail: dev-h...@openoffice.apache.org
>
>

Re: Example of spreadsheet formula testing

[Regina Henschel]

Hi Rob,

Rob Weir schrieb:

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.



Following the spec is not enough. For example, if the accuracy decreases 
from 14 digits to 7 digits, that is not covered by the spec.





If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment


"modest"? One function a day and you need more than a year.

, it will benefit

us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.


I see a problem in how such a test suite is made available. And how the 
results for a special release are collected.


The problem with the current test cases is, that I do not know where 
they are, how they are to use and how to generate new ones. It is a 
closed book, only for insiders.




Anyone interesting in helping with this kind of test case development?


There exist some files already in Bugzilla. I used to make test 
documents, when working on functions. I think, that they can be extended 
to work in a way, that a simple look on it will tell errors. But I have 
no ready collection on my PC and most will be already deleted from my PC 
in the meantime.


One problem is, that comparisons with constants have to be written in a 
way, that they are independent from local. Eike has once corrected one 
of my test spreadsheets that way.




Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
we're not starting from a  perfect score.  But we should find an easy
way to report on regressions.


If you will automate this, you will need to develop a frame. But 
automation is not the total solution. Testing can be a way to bring user 
into the community. And tests have to cover different languages and 
scripts. I remember errors reported to LibreOffice, where a time 
calculation was wrong only in special locals. To extend a testing frame 
to consider this would be very expensive.


Let me not be misunderstood, I like the idea of collecting test cases.

Kind regard
Regina




-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org

Example of spreadsheet formula testing

[Rob Weir]

Moving this topic to its own thread.

It should be possible to code a very thorough set of test cases in a
spreadsheet, without using macros or anything fancy.  Just careful
reading of the ODF 1.2 specification and simple spreadsheet logic.

I'd like to share an example of this that I created for one of the ODF
Plugfests.  This is a test of a single function -- YEARFRAC.  You
probably have never touched this function, but it exhibits all the
pathological behavior, in a purer form, of the other financial
functions.  Specifically, it is a pure test of our "date counting
conventions", the various ways that accountants handle date
calculations.

The test document is here:

http://www.robweir.com/basis-test.xls

(I did it in XLS format since I wanted to make sure Microsoft could
use it at the Plugfest as well.  At that time they were not able to
read ODF formulas.)

This is likely the most complicated set of test cases of any
spreadsheet formula.  So if we can test YEARFRAC this way then we can
test any function this way.

Column C is the formula to evaluate.  Column F is the expected value,
which is calculated by hand, according to the ODF standard.  And
colu,mn G reports whether they match or not.  (This would be a good
place for us to use conditional formatting as well, though in the
Plugfest case I needed to make the spreadsheet be as vanilla as
possible so every editor could load it)

Note that this is an exhaustive set of test cases that aim to test
every corner of the formula.  It is a torture test.  Excel gets all
the test cases right.  Not a surprise, since we took Excel's behavior
as normative when writing this part of the standard.

If we used an approach like this on the other spreadsheet functions,
we could have a semi-automated test suite that would practically
guarantee that Calc is free of calculations errors.  Once we're
written the test cases, a modest upfront investment, it will benefit
us with every release we do.  Heck, it would benefit LibreOffice,
Gnumeric, Calligra as well, maybe even Microsoft and Google, though
they might already have such test cases defined internally.

Anyone interesting in helping with this kind of test case development?

Any ideas on how to fully automate this?  ODF 1.2 is very strict, so
we're not starting from a  perfect score.  But we should find an easy
way to report on regressions.

-Rob

-
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org