Bug 37391 - Invalid formula written to file
Summary: Invalid formula written to file
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.2 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-05-19 21:18 UTC by Andreas J Guelzow
Modified: 2011-08-13 15:19 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
sample file (7.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-05-19 21:18 UTC, Andreas J Guelzow
Details
sample file containing [$Sheet1.$#REF!$1] and [$Sheet1.$A$#REF!] (7.75 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-05-20 09:15 UTC, Andreas J Guelzow
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas J Guelzow 2011-05-19 21:18:51 UTC
Created attachment 46925 [details]
sample file

The attached file was created from scratch in localc (2 sheets, define ProblemName as a name for A1 on Sheet1, enter =ProblemName on Sheet2, delete Sheet1)

The file contains in content.xml
<table:named-expression table:name="ProblemName" table:base-cell-address="$Sheet2.$A$1" table:expression="[$#REF!.$A$1]"/></table:named-expressions>

Based on OpenDocument v1.2 cs01 which the file claims to use:

19.635 table:expression: (...) The value of this attribute may have a namespace prefix, followed by a ":" (U+003A, COLON), followed by an expression. If the namespace prefix is missing it defaults to the "urn:oasis:names:tc:opendocument:xmlns:of:1.2" namespace.

So the expression [$#REF!.$A$1] needs to follow the OpenFormula specification as described in OpenDocument v1.2 but it is not valid according to that specification (see part II 5.8).
Comment 1 Yifan Jiang 2011-05-19 22:32:29 UTC
Well, Koehi is the expert for this :) Thanks for taking a review.
Comment 2 Andreas J Guelzow 2011-05-20 09:14:12 UTC
LibreOffice Calc 3.3.2 will also create stuff like 

<table:named-expression table:name="ProblemName" table:base-cell-address="$Sheet1.$A$1" table:expression="[$Sheet1.$#REF!$1]"/><table:named-expression table:name="ProblemName2" table:base-cell-address="$Sheet1.$A$5" table:expression="[$Sheet1.$A$#REF!]"/>

both [$Sheet1.$#REF!$1] and  [$Sheet1.$A$#REF!] aren't valid OpenFormula either.
Comment 3 Andreas J Guelzow 2011-05-20 09:15:10 UTC
Created attachment 46959 [details]
sample file containing [$Sheet1.$#REF!$1] and [$Sheet1.$A$#REF!]
Comment 4 Rainer Bielefeld Retired 2011-06-09 01:59:56 UTC
No "real" problem reproducible with "LibreOffice 3.4.0  – WIN7  Home Premium  (64bit) English UI [OOO340m1 (Build:12)]", but may be I have a wrong idea concerning the problem? I created a document as reported , deleted Sheet1, saved, added new Sheet1, redefined "ProblemName" and everything worked fine.

But of course it might be a wrong ODF syntax problem


@Andreas J Guelzow:
It seems you expect some other syntax. It would be more easy to follow your considerations if you would write them in a way like:
- content.xml 
 -- actual: "xxy"
 -- expected: "xxx"
    because ODF ...

@Regina:
Can you assist?
Comment 5 Regina Henschel 2011-06-09 06:23:16 UTC
Andreas J Guelzow is right.

Currently we have: table:expression="[$#REF!.$A$1]"
This has a ReferenceError inside of parts of a RangeAddress. That is not allowed. A reference is either a kind of RangeAddress or a ReferenceError, but you cannot mix them up.

So the file content has to be either
table:expression="[#REF!]"
or of the kind
table:expression="[(Source? RangeAddress)]"
where (Source? RangeAddress) is BNF notation as in chapter 5.8.

There had been some complains in OOo Issuetraker, that the original address information get lost, when the referenced source vanishes. So I would prefer
table:expression="[(Source? RangeAddress)]"
The table:table-cell child would still be <text:p>#REF!</text:p>. Doing it this way, the spreadsheet will work immediately without changes after the missing referenced source has been restored. But I'm not quite sure, whether this is really allowed.

The other solution Andreas J Guelzow mentioned, would mean, that we do not use the OpenFormula syntax in this cases, but define our own syntax and mark the value of the attribute with our own namespace prefix. But I think, that would contradict our aim to support ODF.
Comment 6 Andreas J Guelzow 2011-06-09 08:37:02 UTC
Rainer, I did give what was contained in the LO created file and how to create said file with LO. I also explained why I think this is not valid ODF1.2. There are lots of ways to fixing that so I can't really tell you what is "expected".
Comment 7 Rainer Bielefeld Retired 2011-06-21 21:41:26 UTC
The arguments seem conclusive to me.

@Kohei:
Can you please have a look?
Comment 8 Kohei Yoshida 2011-08-08 21:07:38 UTC
Just to add a background reference, the change in the spec appears to be the direct result of this issue

http://tools.oasis-open.org/issues/browse/OFFICE-2750?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

The older version of the spec used to allow this syntax.

The current spec says

http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part2.html#__RefHeading__1017946_715980110
Comment 9 Andreas J Guelzow 2011-08-08 23:01:19 UTC
It is not clear to me to which "change in spec" you are referring. ODF 1.1 did not address formulae at all so ODF 1.2 will be the first specification addressing this issue.

There may have been some draft documents that would have allowed table:expression="[$#REF!.$A$1]" but never any approved spec.

Moreover, LibreOffice Calc also saves expressions such as:
<table:table-cell table:formula="of:=#ref!1" office:value-type="float" office:value="0"><text:p>#NAME?</text:p></table:table-cell>

As far as I know there was never a draft that allowed table:formula="of:=#ref!1" since the Error was only allowed to replace the sheet name.
Comment 10 Kohei Yoshida 2011-08-09 06:45:41 UTC
(In reply to comment #9)
> It is not clear to me to which "change in spec" you are referring. ODF 1.1 did
> not address formulae at all so ODF 1.2 will be the first specification
> addressing this issue.
> 
> There may have been some draft documents that would have allowed
> table:expression="[$#REF!.$A$1]" but never any approved spec.

Yes, I was talking about 1.2 draft spec vs 1.2 cs01.

> Moreover, LibreOffice Calc also saves expressions such as:
> <table:table-cell table:formula="of:=#ref!1" office:value-type="float"
> office:value="0"><text:p>#NAME?</text:p></table:table-cell>
> 
> As far as I know there was never a draft that allowed
> table:formula="of:=#ref!1" since the Error was only allowed to replace the
> sheet name.

So, this bug is not just about the expression inside of <table:named-expression> but also applies to the expression inside of other elements as well, I presume?

If so, we should probably check all the places that are affected, and fix it in one go since those places probably do share the same code that generates their respective expression.