[Bug 60848] New: SUMPRODUCT fails when first arg is of form --(...)

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 60848] New: SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

            Bug ID: 60848
           Summary: SUMPRODUCT fails when first arg is of form --(...)
           Product: POI
           Version: 3.15-FINAL
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

The following formula works fine in POI (using version 3.15):

=SUMPRODUCT(X17:X1048576,--(O17:O1048576>=30))

However, reversing the order of the args, it fails with "Invalid arg type
for SUMPRODUCT: (org.apache.poi.ss.formula.eval.ErrorEval)":

=SUMPRODUCT(--(O17:O1048576>=30),X17:X1048576)

Either way works fine in Excel.

Also described at https://lists.apache.org/list.html?dev@...:2017-03

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |NEEDINFO

--- Comment #1 from Dominik Stadler <[hidden email]> ---
Do you have a sample file/sample code or unit-test that reproduces the problem?
This would make it a lot easier for developers to reproduce and work on a fix.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #2 from [hidden email] ---
It's as simple as evaluating a sumproduct whose first arg starts with --.

--- Sample files ---
 bad.xlsx: in A3, set formula to
     =SUMPRODUCT(--(B5:B20))

--- Test results ---
Running the sample code below, you will get:
  java sampleApp good.xlsx
    <prints "No error">
  java sampleApp bad.xlsx
    <prints "Invalid arg type for
SUMPRODUCT:(org.apache.poi.ss.formula.eval.ErrorEval)">

--- Sample code ---

    private static final String FILE_NAME = "test.xlsx";

    public static void main(String[] args) {
        try {
            FileInputStream excelFile = new FileInputStream(new
File(FILE_NAME));
            Workbook workbook = new XSSFWorkbook(excelFile);

            // get the cell with the offending formula (cell A3 in sheet 0)
            Cell c = workbook.getSheetAt(0).getRow(2).getCell(0);

            FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();

            evaluator.evaluateFormulaCell(c);

            System.out.println("No error");
        } catch(Exception e) {
            System.out.println("There was an error");
            System.out.println(e.getMessage());
        }
    }

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #3 from [hidden email] ---
Created attachment 34860
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=34860&action=edit
bad.xlsx

bad.xlsx as described in prior comment. It has no contents other than one
formula in cell A3: =SUMPRODUCT(--(B5:B20))

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #4 from [hidden email] ---
Please ignore the reference to "good.xlsx" in comment 2. It was meant to be
removed before it was sent.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

Greg Woolsey <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|3.15-FINAL                  |3.16-dev

--- Comment #5 from Greg Woolsey <[hidden email]> ---
Thanks for the simple test case.  This is deeper than just the SUMPRODUCT()
function.  By the time it gets to evaluating the function, the argument has
already evaluated to an error.

The problem is that the "Unary Minus" operator, "-", doesn't properly handle an
array argument.  This is actually a problem with the underlying utility class
OperandResolver, which needs to return an array instead of a single value when
the input is an array for cases like this, but perhaps not for some other
functions.

This will require some digging to fully define.  To start with, the unary
functions should at least call a new operand static method that can return an
array of values.  UnaryPlusEval has the same bug as UnaryMinusEval, although
the plus and minus evaluation logic is slightly different according to the
comments in the code.

It will take someone digging through the Excel function specs to see what other
functions can silently operate on an array and return an array for use by array
functions like SUMPRODUCT, and testing/checking the POI handling of each.

Patches are always welcome.  If one of my users indicates we need this it will
end up in my queue, but otherwise it's a bit too big for me to tackle at the
moment.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #6 from [hidden email] ---
Thank you for the comment

Out of curiosity, why is the processing wrt the first argument different from
that wrt subsequent arguments? Note that if we do:
   =SUMPRODUCT(A5:A20, --(B5:B20))

then even though the problematic argument is still there, it works just be
relegating it to the position of argument 2 instead of argument 1.

Is it possible to take whatever process is being applied to arguments 2+ and
apply that to argument 1?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #7 from Greg Woolsey <[hidden email]> ---
The current logic is completely dependent on the internal class type of of the
first argument.  As I mentioned, the problem is with the unary negation
operator (minus).  I haven't tested it, but I suspect the second argument is
actually evaluating to a single value instead of an array, and projecting that
across the first argument array, rather than taking the pairwise product of
arrays of equal size.  There won't be a simple fix.  

You are welcome to check out the source code and play with it.  If you come up
with a fix and unit test, we'd love to incorporate a patch.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #8 from [hidden email] ---
I will try to take a look. the main (at least initial) limitation on my ability
to do so is that I'm pretty unfamiliar with the Java build process (I'm mostly
a C programmer and stick with emacs + cc + make).

Will have to do some research on how to get code cycle times down for this kind
of endeavor. Any suggestions much appreciated.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #9 from [hidden email] ---
If any developers are interested in fixing this for a $ bounty, please send me
a proposal at [hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #10 from [hidden email] ---
the plot thickens.

Using the below example, when cell A3 is changed to be:
    =SUMPRODUCT(--(B4:B6))

then, as before, evaluation of that throws an error.

However, when cell A3 contains
    =SUMPRODUCT(--(B3:B5))

then, unlike before, no error is thrown, but the resulting value is not correct
and only appears to consider the cell B3.

Not sure if this indicates two separate issues that need to be fixed, but it
seems odd that one throws an error and the other does not just by moving the
referenced range up or down a row.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #11 from [hidden email] ---
Looking at the syntax in
https://msdn.microsoft.com/en-us/library/dd906358(v=office.12).aspx, then,
assuming that "Unary Minus" is equivalent in the POI grammer as "sign" in the
MSFT site's grammar, it seems possible that the grammar in POI should not be
classifying the leading "-" as "Unary Minus", and instead should be classifying
it as whatever in the POI grammar is equivalent to "prefix-operator".

See in particular the line that reads:

  nospace-argument-expression = "("  expression  ")" / constant /
prefix-operator argument-expression ...


in other words, "--(xx)" should be parsed as


  prefix-operator argument-expression
                    |
                  prefix-operator argument-expression
                                    |
                                  "("  expression  ")"


(note that I've ignored the difference between nospace-argument-expression and
argument-expression and treated them as equivalent for brevity)

Would you agree that the expression should parsed differently, as shown above?
I'm not familiar enough with how POI implemented the grammar to know whether
there is some other way to deal with this which is different from how the MSFT
site's grammar works... but I would think that the formula parsing is hairy
enough that it would be optimal to use the exact same grammar as specified in
the MSFT site. It might be a fundamental change to make, but there are a number
of other formula-parsing bugs in POI that would likely be more easily addressed
taking that approach.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

--- Comment #12 from [hidden email] ---
Calling all coders who want more money: who is willing to fix this bug for a
bounty?

I have to admit I am surprised that there hasn't been more attention being paid
to what appears to be a fundamental problem in how this library parses
formulas, which not only doesn't work, but also doesn't notify the user that it
doesn't work, and instead just sends back bad data. If nothing else, there
should be raised some Exception raised saying "I can't calculate this
correctly, so I'm returning an error". Otherwise how can anyone take this
library seriously for any "real" work?

Also, why is this bug's status set to "NEEDINFO"? We already have all the info
needed to reproduce. I get it that the solution isn't found yet, but "NEEDINFO"
makes it sound like it's not ready for a developer to look at, which is not the
case.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 60848] SUMPRODUCT fails when first arg is of form --(...)

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=60848

Mark Murphy <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |NEW

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]