Issue 114451

Summary: Raise the 512 token limit in OOo Calc formulas
Product: Calc Reporter: ghe <ghe>
Component: editingAssignee: AOO issues mailing list <issues>
Status: UNCONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: elish, eric.bachard, issues
Version: OOO320m12Keywords: needhelp
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Long formula (8192 bytes / 7050 tokens) none

Description ghe 2010-09-11 11:58:02 UTC 
Hi,

I'm working with very long formulas and I frequently bump into the 512 token
limit in OOo Calc formulas, which raises the error "512".  This limit seems
somewhat arbitrary, and its value (512 tokens) did not change at least since OOo
2.3 (ie since 2007).

The corresponding limit in MS Excel is in characters (ie not tokens):

- Excel up to 2003 (ie in Excel 11 and earlier): 1024 characters
- starting from Excel 2007 (ie in Excel 12 and 13): 8192 characters

As a consequence, files created with Excel 2007 or 2010 and containing very long
formulas do not open correctly in OOo: the formula will be cut after 512 tokens,
raising an error.

I attach an example of (admittedly useless) 8192 character long formula, to be
pasted into A2 for instance.  It works when it is pasted in an Excel cell, but
will not work in Calc, because it has 7050 tokens.

Now that we have 1 mega-rows in Calc files (which may seem useless to most
users), would it be possible to also raise that limit to 8192 tokens (which will
probably also seem useless to most users, but may be useful for some, and in any
case enhances compatibility)?

Thanks in advance,

--ghe
Comment 1 ghe 2010-09-11 12:00:58 UTC 
Created attachment 71645 [details]
Long formula (8192 bytes / 7050 tokens)
Comment 2 eric.bachard 2010-09-11 15:10:34 UTC 
Hi,

Not a real answer, but as immediate workaround, I'd suggest a factorization of
your formula ;)
Comment 4 Edwin Sharp 2013-12-24 15:38:05 UTC 
There is no justification for such long formulas.
Comment 5 ghe 2013-12-24 16:00:41 UTC 
Who are you to decide that "there is no justification for such long formulas"?!
Did you at least read the comments above: "files created with Excel 2007 or 2010 [and now also Excel 2013] and containing very long formulas do not open correctly in OOo: the formula will be cut after 512 tokens, raising an error"?
Given that the corresponding limit is 8192 characters in the latest Excels, there is a possibility (whether you like it or not) that a user opens an Excel file and that it doesn't work correctly because of that bug.
In short, there are no reasons to reject this bug and to mark it as "wontfix".
All this seems a very good justification, besides the fact that some people (for example me) DO use such long formulas.
Comment 6 Edwin Sharp 2013-12-24 17:09:08 UTC 
Please provide a real life example for this request.
Or use Excel.
Without further information, this issue will be closed per QA rules.
Comment 7 ghe 2013-12-25 19:45:49 UTC 
> Please provide a real life example for this request.

Do you want to see a formula that is longer than 512 tokens?  Here is one: "=IF(NOT(AND(OR(ISBLANK(A3);ISNUMBER(A3+0);TRIM(A3)="A";TRIM(A3)="");OR(ISBLANK(B3);ISNUMBER(B3+0);TRIM(B3)="A";TRIM(B3)="");OR(ISBLANK(C3);ISNUMBER(C3+0);TRIM(C3)="A";TRIM(C3)="");OR(ISBLANK(D3);ISNUMBER(D3+0);TRIM(D3)="A";TRIM(D3)="");OR(ISBLANK(E3);ISNUMBER(E3+0);TRIM(E3)="A";TRIM(E3)="");OR(ISBLANK(F3);ISNUMBER(F3+0);TRIM(F3)="A";TRIM(F3)="");OR(ISBLANK(H3);ISNUMBER(H3+0);TRIM(H3)="A";TRIM(H3)="");OR(ISBLANK(I3);ISNUMBER(I3+0);TRIM(I3)="A";TRIM(I3)="");OR(ISBLANK(J3);ISNUMBER(J3+0);TRIM(J3)="A";TRIM(J3)="")));"?";IF(AND(OR(TRIM(A3)="";ISBLANK(A3));OR(TRIM(B3)="";ISBLANK(B3));OR(TRIM(C3)="";ISBLANK(C3));OR(TRIM(D3)="";ISBLANK(D3));OR(TRIM(E3)="";ISBLANK(E3));OR(TRIM(F3)="";ISBLANK(F3));OR(TRIM(H3)="";ISBLANK(H3));OR(TRIM(I3)="";ISBLANK(I3));OR(TRIM(J3)="";ISBLANK(J3)));"";IF(OR(TRIM(A3)="A";TRIM(B3)="A";TRIM(C3)="A";TRIM(D3)="A";TRIM(E3)="A";TRIM(F3)="A";TRIM(H3)="A";TRIM(I3)="A";TRIM(J3)="A");"A";(IF(OR(TRIM(A3)="";ISBLANK(A3));0;A3.5)+IF(OR(TRIM(B3)="";ISBLANK(B3));0;B3*2.5)+IF(OR(TRIM(C3)="";ISBLANK(C3));0;C3*3)+IF(OR(TRIM(D3)="";ISBLANK(D3));0;D3*9)+IF(OR(TRIM(E3)="";ISBLANK(E3));0;E3*3)+IF(OR(TRIM(F3)="";ISBLANK(F3));0;F3*6)+IF(OR(TRIM(H3)="";ISBLANK(H3));0;H3*7.5)+IF(OR(TRIM(I3)="";ISBLANK(I3));0;I3*8)+IF(OR(TRIM(J3)="";ISBLANK(J3));0;J3*7.5))/(IF(OR(TRIM(A3)="";ISBLANK(A3));0;1.5)+IF(OR(TRIM(B3)="";ISBLANK(B3));0;2.5)+IF(OR(TRIM(C3)="";ISBLANK(C3));0;3)+IF(OR(TRIM(D3)="";ISBLANK(D3));0;9)+IF(OR(TRIM(E3)="";ISBLANK(E3));0;3)+IF(OR(TRIM(F3)="";ISBLANK(F3));0;6)+IF(OR(TRIM(H3)="";ISBLANK(H3));0;7.5)+IF(OR(TRIM(I3)="";ISBLANK(I3));0;8)+IF(OR(TRIM(J3)="";ISBLANK(J3));0;7.5)))))"

It is extracted from a "real life example" (I'm not allowed to put the file online).

If this is not enough, please define "real life example".

N.B.: Please do not reply that I could split this formula in two.  I know very well that this can be done, but for a number of reasons, that would be too long to explain here, it is not a viable option.

> Or use Excel.

Well, that's a quite strange advice in an OpenOffice forum!

> Without further information, this issue will be closed per QA rules.

What specific Quality Assurance (!) rule do you refer to?

I'm quite surprised that it takes so long (more than three years since my original post!) and that it is necessary to argument that much for a simple change on only one line in the OpenOffice code base: "#define MAXCODE 512" to "#define MAXCODE 8192" in compiler.hxx.

As I wrote earlier this change may seem useless to most users (like the 1048576 rows), but may be useful for some, and in any case enhances compatibility (with Excel).
Comment 8 Edwin Sharp 2013-12-25 20:16:31 UTC 
1. Calc is not an Excel clone.
2. Calc is not and never will be perfectly compatible with Excel.
3. The community Forum reception of this enhancement request wasn't so enthusiastic.
4. This Bugzilla issue is in Unconfirmed status for more than three years with just one comment and just one vote.
5. The author of the request is evasive when asked about how such a formula is generated and why it can not be split.
6. The author himself admits this request is "useless to most users".
7. The author seems to be able to patch the code by himself.

IMHO this request should be discarded.
Let's see what others think...
Comment 9 ghe 2013-12-25 22:07:44 UTC 
I'm sorry, but you seem to deal with that issue in an aggressive manner; I honestly don't understand why.  I can only repeat that it's a simple request, that would enhance compatibility with Excel, and that would be useful for some users.

FYI, the formula above was generated by a script, but I don't see why explaining the details of why I need it would convince you.  Nothing is *necessary* in Calc or Excel, because it is always *possible* (given that it is Turing-complete, even without macros) to do anything you might need in another way.  This means that what we are discussing here is nothing more than convenience.

The row limit increase (from 64K to 1M) in OOo 3.3 was also meant to enhance compatibility with Excel, and is useful to some users; I bet that you would have voted against it because "there is no justification for that many rows".

See the corresponding bug in LibreOffice, and how professionally it has been dealt with (and resolved): https://bugs.freedesktop.org/show_bug.cgi?id=35411 .