Bug 143809 - Err:502 in cells referencing named expressions after save-and-reload
Summary: Err:502 in cells referencing named expressions after save-and-reload
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.7.2 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.3.0 target:7.2.1 target:7.1.6
Keywords:
Depends on:
Blocks:
 
Reported: 2021-08-10 13:46 UTC by Mike Kaganski
Modified: 2021-08-15 16:26 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
Sample spreadsheet with named expressions (13.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-08-10 13:46 UTC, Mike Kaganski
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2021-08-10 13:46:01 UTC
Created attachment 174182 [details]
Sample spreadsheet with named expressions

The attached document contains some named expressions. The cells that refer to those expressions (Billing.A1:A2, Tickets.D1:D2) show Err:502 now; but when the named expressions were entered initially, the formulas worked.

The document-scope named expressions are:

> INF:       (2^53-1)*2^(1024-53)
> LAST_ROW:  MATCH(INF;$Tickets.$A:$A)
> DURATIONS: $Tickets.$A$2:INDEX($Tickets.$A:$A;LAST_ROW)
> IDS:       $Tickets.$B$2:INDEX($Tickets.$B:$B;LAST_ROW)

Even as simple formula as =SUM(DURATIONS) produces an error.
Comment 1 Mike Kaganski 2021-08-10 14:17:01 UTC
It looks related to INF. Replacing its reference in LAST_ROW by its expression, like this:

> LAST_ROW:  MATCH((2^53-1)*2^(1024-53);$Tickets.$A:$A)

fixes the problem. It turned out that I made a mistake while preparing the example, moving IFERROR's last argument out of the argument list - that's unrelated; the correct formula in Billing.A1 / Tickets.D1 is

> =SUMPRODUCT(IFERROR(CEILING.MATH(DURATIONS;300);0))

and in Billing.A2 / Tickets.D2 is

> =SUMPRODUCT(IFERROR(CEILING(SUMIFS(DURATIONS;IDS;IDS);300)/COUNTIFS(IDS;IDS);0))

Why could INF cause this? There's no built-in function named so.
Comment 2 Eike Rathke 2021-08-11 13:13:33 UTC Comment hidden (off-topic)
Comment 3 Eike Rathke 2021-08-11 13:15:55 UTC Comment hidden (off-topic)
Comment 4 Eike Rathke 2021-08-11 13:18:33 UTC Comment hidden (off-topic)
Comment 5 Eike Rathke 2021-08-11 13:41:35 UTC Comment hidden (obsolete)
Comment 6 Mike Kaganski 2021-08-11 13:42:47 UTC
(In reply to Eike Rathke from comment #5)
> Apart from that the formulas in D1 and D2 are wrong lacking the second
> parameter of IFERROR()

Right - see comment 1 ;)

Thanks for looking into this, Eike!
Comment 7 Eike Rathke 2021-08-11 14:48:26 UTC
Old problem, I reproduced loading with 5.3.7 already.
Probably the nesting *and* order encountered.
Comment 8 Eike Rathke 2021-08-11 15:47:17 UTC
HAHAHA no..

"INF" *is* a (hidden) symbol.. it's the XMLSchema-2 infinity representation recognized by rtl::math::stringToDouble() that returns HUGE_VAL (0x7fffffffffffffff) and sets rtl_math_ConversionStatus_OutOfRange for which we set FormulaError::IllegalArgument at the named expression's token array, so it persists and can't be caught by IFERROR().

If name and usage are changed (in two steps) from INF to, for example, INFIN, things work..
Comment 9 Commit Notification 2021-08-11 20:22:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b37fd7f38165dadc5b1a674b73f4b18824e4789e

Resolves: tdf#143809 "INF" may be a named expression or DB area name

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Eike Rathke 2021-08-11 20:24:22 UTC
Pending review https://gerrit.libreoffice.org/c/core/+/120352 for 7-2
Comment 11 Commit Notification 2021-08-12 15:17:50 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/3dd3c1ed99a014cb98a7a68668b9426020094ec2

Resolves: tdf#143809 "INF" may be a named expression or DB area name

It will be available in 7.2.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2021-08-12 21:51:06 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/18771471f75685a6d3838a22d16dcff5c398e652

tdf#143809: sc_subsequent_filters: Add unittest

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2021-08-15 16:26:11 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/c5dbe94eb71acd9ab19cd9a8d561dd75da82d3d7

Resolves: tdf#143809 "INF" may be a named expression or DB area name

It will be available in 7.1.6.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.