Bug 96426 - [1] space as intersection in Excel A1 (or R1C1) formula syntax gives Err:509 or Err:508
Summary: [1] space as intersection in Excel A1 (or R1C1) formula syntax gives Err:509 ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium minor
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:5.2.0 target:5.1.4 target:5.4....
Keywords:
Depends on:
Blocks:
 
Reported: 2015-12-11 20:13 UTC by Bob Briscoe
Modified: 2017-05-29 08:07 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Bob Briscoe 2015-12-11 20:13:46 UTC
==Summary==

The operator for the intersection of two ranges is '!' in Calc A1 formula syntax and ' ' <SPACE> in Excel A1 formula syntax. With the appropriate formula syntax selected, a range intersection using '!' in Calc A1 syntax works, but a range intersection using <SPACE> in Excel A1 syntax gives an error.

If the ranges are specified indirectly as "names", and there are parenthesis round the operation, the error is Err:508 (Pair missing). With no parentheses it gives Err:509 (missing operator).
If the ranges are specified in Excel A1 syntax (not as "names"), the error is always Err:509 (missing operator), irrespective of whether there are parentheses.

This is a bug, because the formula with a space for an intersection operator is perfectly valid Excel A1 formula syntax, so an Error code is an incorrect output.

NB: Nothing in this bug is about import/export from MS Excel. It is solely about the Excel A1 formula syntax within native Calc. As long as the formulae are entered in Calc, the bug is independent of which format the sheet is saved in (it is even reproducible before saving the file). 

The following examples are available with screen-shots added here: <http://homefarmparham.co.uk/tmp/calc/IntersectCalcBug.html>

==Expected Behaviour==
With Calc A1 Formula Syntax, the '!' operator in a formula gives the intersection of two ranges. For example a cell containing the formula: 
   =($B:$B!$2:$2)*2 
should resolve to 28, if the contents of B2 is the number 14.

With Excel A1 Formula Syntax (selected via Tools>Options>Formula), the <space> operator in a formula is meant to be equivalent, giving the intersection of two ranges. For example:
   =($B:$B $2:$2)*2
should be equivalent.

==Actual Behaviour==

With Excel A1 Formula Syntax selected, when typing the formula, Calc displays the two ranges as expected; as intersecting coloured boxes around the ranges and it colours the characters in the formula to match. However, on entering the formula (type <enter> or click the tick), the cell displays "Err:509" (missing operator)

If the ranges are defined as names (using Insert>Names>Define), e.g.
   horiz     $2:$2
   vert      $B:$B
then an intersection formula is defined with parentheses around these names, for example:
   =(horiz vert)*2 
it returns "Err:508" (pair missing). However, a similar formula without parentheses such as 
   =horiz vert
returns "Err:509" (missing operator)

==Absence of Any Intersection Operator in Excel A1 Formula Syntax==

Incidentally, with Excel A1 Formula Syntax selected, attempting to use '!' as an intersection operator correctly gives a #NAME? error. So there is no working intersection operator at all in Calc's Excel A1 Formula Syntax.

==System build==

Libre Office
Version: 5.0.3.2
Build ID: 1:5.0.3~rc2-0ubuntu1~trusty2
Locale: en-GB (en_GB.UTF-8)
Comment 1 Buovjaga 2015-12-16 09:39:17 UTC
Reproduced.

Win 7 Pro 64-bit Version: 5.2.0.0.alpha0+
Build ID: 014633f83e44ae8ba33087b6f38e8e253e281969
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-12-15_06:21:44
Locale: fi-FI (fi_FI)
Comment 2 Eike Rathke 2016-04-12 15:55:50 UTC
Oh yeah, conditionally significant whitespace as operator, great fun.. also in OOXML. One has to bow before the wisdom of Excel devs.
Comment 3 Commit Notification 2016-04-23 13:55:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=26adceb098134d918f6d57c8687ab057e24adc39

Resolves: tdf#96426 significant whitespace as intersection in Excel syntax

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 4 Commit Notification 2016-04-23 18:46:29 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b0992e11905e36a64edeb92a13acfde5837c1878

fully check for adjacent RPN end, tdf#96426 follow-up

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 5 Commit Notification 2016-04-23 18:46:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e0875f8e348a3aca036bc0cc629fb038fabc8062

narrow down where a space could be an intersection, tdf#96426 follow-up

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2016-04-23 22:15:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0c5663cfb13f4f55e246d42ac464d5e2c2f23099

first range can be anywhere before second at RPN end, tdf#96426 follow-up

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 8 Eike Rathke 2016-04-25 17:10:00 UTC
Pending review https://gerrit.libreoffice.org/24374 for 5-1
Comment 9 Bob Briscoe 2016-04-27 01:29:34 UTC
(In reply to Eike Rathke from comment #8)
> Pending review https://gerrit.libreoffice.org/24374 for 5-1

Thx for the fix. I'll test the daily build as soon as I can, using our cashbook spreadsheets, which liberally use the intersection operator in Excel formula syntax. I'll also try to contrive some more challenging tests. However, I have bigger problems at the mo, which I need to fix first (crash while not even touching my machine left me with black-screen on boot). So prob a fortnight.

I'm afraid I cannot review the code. I've had a quick look at your diffs, but I'm completely unfamiliar with Libre Office code, so I won't promise to review it -  would be pointless.
Comment 10 Buovjaga 2016-04-27 05:28:55 UTC
(In reply to Bob Briscoe from comment #9)
> I'm afraid I cannot review the code. I've had a quick look at your diffs,
> but I'm completely unfamiliar with Libre Office code, so I won't promise to
> review it -  would be pointless.

It's ok - we are not expecting you to review it :) If you are interested in contributing without coding, see https://wiki.documentfoundation.org/QA/Triage_For_Beginners for an intro to the vast world of quality assurance.
Comment 11 Commit Notification 2016-05-02 18:42:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=edd4370f5ba49a26a526995b6a28f623d68041ce

check presence of token, tdf#96426 follow-up

It will be available in 5.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2016-05-03 09:36:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b8d78268d1c58557122f99d6134a3b1eda7fe6c3&h=libreoffice-5-1

Resolves: tdf#96426 significant whitespace as intersection in Excel syntax

It will be available in 5.1.4.

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

Affected users are encouraged to test the fix and report feedback.
Comment 13 Bob Briscoe 2017-01-02 23:31:39 UTC
THank you for trying to fix this, but it's not completely fixed, I'm afraid.

The fix works with intersections between simple cell ranges, but you get the two errors described in the original bug report when the intersection is defined between variable names (defined as cell ranges using Insert>Names>Define).

See the screenshots at http://homefarmparham.co.uk/tmp/calc/IntersectCalcBugReopen.html in which the red text has been added since the original bug report.

For instance, if you define:
* horiz as Sheet1!$2:$2
* vert as Sheet1!$B:$B

Then, with Excel A1 formula syntax, the following example formulae return the errors shown:
  =(vert horiz)*2   Err:508 (pair missing)
  =vert horiz       Err:509 (missing operator)

_______
I just noticed an extra twist: If an intersection formula is written with a '!' when Calc A1 formula syntax is selected, e.g.:
  =(vert!horiz)*2
a) this works correctly with no errors. 
b) the '!' correctly auto-translates to a ' ' (space) if the formula syntax is switched to Excel A1.
c) And there are still no errors, but...
d) ...if the formula is edited to a different formula with ' ' (space) as an intersection operator, the bug re-appears.
Comment 14 Commit Notification 2017-05-17 13:28:28 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=70cb7757f971c4c16d1b0a5585d95816cd4382e6

Resolves: tdf#96426 more whitespace intersection operator in Excel syntax

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 15 Eike Rathke 2017-05-17 13:34:56 UTC
Pending review https://gerrit.libreoffice.org/37712 for 5-3
Comment 16 Commit Notification 2017-05-17 16:18:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=67a4bb2204c841bf468504fcf5282787abc0e973

add unit test for Excel whitespace operator intersection, tdf#96426

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2017-05-17 16:27:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8326c48a1ac4855970f9232dba582a51461ac0b2

Unit test to ensure that calculating with the intersection works, tdf#96426

It will be available in 5.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 18 Commit Notification 2017-05-29 08:07:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9c89d41ed44c1a4126b82f0c05768f5d640f741b&h=libreoffice-5-3

Resolves: tdf#96426 more whitespace intersection operator in Excel syntax

It will be available in 5.3.4.

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

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