Bug 119035 - INDIRECT uses R1C1 syntax when uses 2-argument syntax with empty second argument
Summary: INDIRECT uses R1C1 syntax when uses 2-argument syntax with empty second argument
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1 all versions
Hardware: All All
: medium normal
Assignee: Ilhan Yesil
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-08-01 12:09 UTC by Mike Kaganski
Modified: 2018-08-31 08:08 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
sample file (deleted)
2018-08-08 07:41 UTC, Xisco Faulí
Details
sample file (7.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-08-08 07:42 UTC, Xisco Faulí
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mike Kaganski 2018-08-01 12:09:30 UTC
Steps:

1. Put 1 to A1
2. Put `=INDIRECT("A1")` to A2, `=INDIRECT("A1";)` to A3, `=INDIRECT("R1C1")` to A4, `=INDIRECT("R1C1";)` to A5.

Expected: A2 and A3 should show `1`; A4 and A5 should show #REF! (because in all formulas, the second parameter is not given, so all formulas should interpret their strings as using A1 syntax).

Actual: A2 and A5 are `1`, A3 and A4 are #REF!, so when there is an argument separator present with absent optional second argument, it is treated as 0.

Tested with Version: 6.1.0.2 (x64)
Build ID: b3972dcf1284967612d5ee04fea9d15bcf0cc106
CPU threads: 12; OS: Windows 10.0; UI render: GL; 
Locale: ru-RU (ru_RU); Calc: CL

See INDIRECT documentation:

https://helponline.libreoffice.org/latest/en-US/text/scalc/01/04060109.html#bm_id3153181

http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018426_715980110

*But* MS Excel (at least 2016) does the same. So possibly this is intentional to be compatible (bug-to-bug?).
Comment 1 Jacques Guilleron 2018-08-07 13:50:18 UTC
Hi Mike,

Reproduced with
LO 6.0.5.2 Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
Threads CPU : 2; OS : Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: CL
and
LO  3.5.3.2 Version ID : 235ab8a-3802056-4a8fed3-2d66ea8-e241b80
So probably inherited from OOo.
Comment 2 Xisco Faulí 2018-08-08 07:41:09 UTC Comment hidden (obsolete)
Comment 3 Xisco Faulí 2018-08-08 07:42:10 UTC Comment hidden (obsolete)
Comment 4 Xisco Faulí 2018-08-08 07:42:36 UTC
Created attachment 144021 [details]
sample file
Comment 5 Xisco Faulí 2018-08-08 07:44:39 UTC
Also reproduced in

Version: 4.3.0.0.alpha1+
Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

In

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

A3 to A5 give Error...
Comment 6 himajin100000 2018-08-19 05:56:45 UTC
How LibreOffice handles missing parameter(but how should Libreoffice handle is another question):

https://opengrok.libreoffice.org/xref/core/sc/inc/compiler.hxx?r=346ba4be#174
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/compiler.cxx?r=85f141b5#4546

https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1493
https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1506
https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1521

https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1602
https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1631
https://opengrok.libreoffice.org/xref/core/formula/source/core/api/FormulaCompiler.cxx?r=ff4d6a7e#1661

https://opengrok.libreoffice.org/xref/core/include/formula/token.hxx?r=346ba4be#394
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr4.cxx?r=a0a8c958#2129

Evaluation stack is, as its name indicates, "stack" and LIFO

https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=346ba4be#7901

=INDIRECT("R1C1";) => two arguments(svString and svMissing) and the second parameter is treated as 0

=INDIRECT("R1C1") => only one argument, as the number of parameters is not 2, line 7904 will not be reached

in the same way

=POWER(,5) is POWER(0,5)
=POWER(5,) is POWER(5,0)

but 
=EXP() is not =EXP(0),so  Err:511 will be produced.
Comment 7 Eike Rathke 2018-08-31 07:58:35 UTC
(In reply to Mike Kaganski from comment #0)
> `=INDIRECT("A1";)` to A3,
> `=INDIRECT("R1C1";)` to A5.
> 
> Expected: A2 and A3 should show `1`; A4 and A5 should show #REF! (because in
> all formulas, the second parameter is not given,
This is not true, the second parameter is given but with an empty argument (resulting in an ocMissing opcode).

> so all formulas should
> interpret their strings as using A1 syntax).
No, because an ocMissing evaluates to 0 (in most cases, sometimes different).

> Actual: A2 and A5 are `1`, A3 and A4 are #REF!, so when there is an argument
> separator present with absent optional second argument, it is treated as 0.
Which is correct.

> *But* MS Excel (at least 2016) does the same. So possibly this is
> intentional to be compatible (bug-to-bug?).
Exactly, but it's not a bug, it's a more or less general Excel behaviour with empty arguments.
Comment 8 himajin100000 2018-08-31 08:08:28 UTC
note:
https://gerrit.libreoffice.org/#/c/59823/