Bug: Spreadsheet calculations in footer fail on empty search

There appears to be some issue with using functions from the SpreadSheetPlugin in a formatted search to a table. When a search does not return values, an empty row in the resultant table which causes most calculations referring to the results in the footer row to fail. See example below...

I guess the solution would be to sommehow make sure that if the row before a calculation is not a table row, then the calculations render as space.

Test case

Compare the successful query

Installation	Description	Computed
Total: 3	Corporate: 3	Corporate: 3
Motorola, Global Software Group	Corporate	Corporate
Motorola, Semiconductor Products UK	Corporate	Corporate
Motorola, Semiconductor Products US	Corporate	Corporate

with the same but returning no results...

Environment

-- ThomasWeigert - 29 May 2003

Follow up

I have seen this problem at work as well. This is not a bug in the SpreadSheetPlugin, it does what it is told to do. The problem is that an empty search returns an empty line, breaking apart the table header and footer into two tables.

Example that illustrates the problem:

|*Topic*|
%SEARCH{ ... format="| $topic |" }%
| Total: %CALC{"$ROW(-2)"}% |

The %SEARCH{}% occupies one line. In case there is one hit it fills that line (without introducing a extra new line; for two hits it returns two lines etc; for no hits it returns nothing (ergo the empty line).

The correct solution to this problem is to get rid of the extra line if there is no hit. That needs to be investigated. I'd happily fix that if someone brings a patch.

Fix record

Following your suggestion I came up with the following, relatively simple, strategy:

The extra newline is introduced by the newline after the search in the raw topic. If the search is unsuccessful, an empty string is returned. This suggests that we would not get a newline if we were to just have the trailing row follow the search without a newline, as shown below.

Installation	Description	Computed
Total: 0	%CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%: 1 Description: 1	%CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%: 1 Computed: 1

This gets rid of the empty line, but if the search did return something, we have a messed up table, as we now miss a trailing newline. Note that TWiki::Search::searchWeb strips that trailing newline for formatted search. To avoid that, we can introduce an option to %SEARCH% that avoids this. (I am not sure whether leaving the newline in will ever cause a problem, but for backwards compatability, this may be the safer route. The attached diffs to TWiki.pm and Search.pm support this extension.

We are still left with the problem that the spreadsheet calculation does not work right in all cases. In the case of the empty search, we find that the routine TWiki::Plugins::SpreadSheetPlugin::getTableRange has an illegal cell range where the lower end comes before the upper end. This would not be a problem were it not for the lines in this routine that try to fix up this case by swapping the lower and upper limits of the range, thus taking a slice of the table that is incorrect. If we just comment out those two lines, as in the included patch, everything works as expected. (I cannot fully predict the impact of this change. However, I have a set of test cases for the SpreadSheetPlugin, which all passed.)

I cannot demonstrate the result here, due to the needed patch. However, what happens now is that the calc lines are left blank in the case the search was unsuccessful, as simulated below.

Installation	Description	Computed
Total: 0

which could have been generated by the formatted search (note the new keyword newline)

%TABLE{sort="on" initsort="1" initdirection="down" headerrows="1" footerrows="1"}%
|*Installation*|*Description*|*Computed*|
%SEARCH{ "Weigert;Private;Firewalled" scope="true" regex="on" web="Main" nototal="on" nosearch="on" newline="false" format="|[[$web.$topic][$formfield(TWikiInstalledAt)]] |$formfield(TypeOfOrganization) | $percntCALC{\"$T(R$ROW():C$COLUMN(-1))\"}$percnt |" }%| Total: %CALC{"$ROW(-2)"}% | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}%  | %CALC{"$COUNTITEMS( R2:C$COLUMN()..R$ROW(-1):C$COLUMN() )"}% |

-- ThomasWeigert - 03 Jun 2003