Parse Bug Involving SUMIF and a String with Leading and Trailing Double Quote

[oleibman]

This is:

- [x] a bug report
- [ ] a feature request
- [x] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Calculation should complete normally.

What is the current behavior?

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Worksheet!
D1 -> Formula Error: An unexpected error occurred in C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Cell\Cell.php on line 274

I undid some try catches and added an echo after the assignment to isOperandOrFunction in Calculation around line 3866 to capture more information, so the line numbers after that may be off by a couple of lines. text is substr($formula, $index):
text=SUMIF(A1:A2,B1,C1:C2)
text=A1:A2,B1,C1:C2)
text=:A2,B1,C1:C2)
text=A2,B1,C1:C2)
text=,B1,C1:C2)
text=B1,C1:C2)
text=,C1:C2)
text=C1:C2)
text=:C2)
text=C2)
text=)
text="""TEXT WITH QUOTES"""=""TEXT WITH QUOTES""
text==""TEXT WITH QUOTES""
text=""TEXT WITH QUOTES""
text=TEXT WITH QUOTES""
text=EXT WITH QUOTES""
text=WITH QUOTES""
text=QUOTES""
text=""
text="

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Formula Error: An unexpected error occurred in C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php on line 5086

Call Stack:
0.0003 403360 1. {main}() C:\git\xsumifbug.php:0
0.0327 988336 2. xcondfmt::testSumif() C:\git\xsumifbug.php:20
0.0668 4123144 3. PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue() C:\git\xsumifbug.php:16
0.0668 4123144 4. PhpOffice\PhpSpreadsheet\Calculation\Calculation->calculateCellValue() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Cell\Cell.php:258
0.0668 4123896 5. PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:3237
0.0700 4140248 6. PhpOffice\PhpSpreadsheet\Calculation\Calculation->processTokenStack() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:3444
0.0717 4217728 7. call_user_func_array:{C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:4714}() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:4714
0.0724 4251616 8. PhpOffice\PhpSpreadsheet\Calculation\Statistical\Conditional::SUMIF() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:4714
0.0736 4351984 9. PhpOffice\PhpSpreadsheet\Calculation\Database\DSum::evaluate() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Statistical\Conditional.php:188
0.0742 4356552 10. PhpOffice\PhpSpreadsheet\Calculation\Database\DatabaseAbstract::getFilteredColumn() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Database\DSum.php:42
0.0742 4356552 11. PhpOffice\PhpSpreadsheet\Calculation\Database\DatabaseAbstract::filter() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Database\DatabaseAbstract.php:77
0.0757 4411840 12. PhpOffice\PhpSpreadsheet\Calculation\Database\DatabaseAbstract::executeQuery() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Database\DatabaseAbstract.php:71
0.0758 4412488 13. PhpOffice\PhpSpreadsheet\Calculation\Calculation->_calculateFormulaValue() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Database\DatabaseAbstract.php:145
0.0758 4412896 14. PhpOffice\PhpSpreadsheet\Calculation\Calculation->internalParseFormula() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:3444
0.0786 4418552 15. PhpOffice\PhpSpreadsheet\Calculation\Calculation->raiseFormulaError() C:\git\PhpSpreadsheet\src\PhpSpreadsheet\Calculation\Calculation.php:4180

/php.ln: Exit 255.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $matchText = '"text with quotes"';
        $sheet->getCell('A1')->setValue($matchText);
        $sheet->getCell('A2')->setValue(2);
        $sheet->getCell('B1')->setValue($matchText);
        $sheet->getCell('C1')->setValue(10);
        $sheet->getCell('C2')->setValue(100);
        $sheet->getCell('D1')->setValue('=SUMIF(A1:A2,B1,C1:C2)');
        print_r($sheet->getCell('D1')->getCalculatedValue());

It seems that Calculation is having trouble with the trailing double quote, but I'm not sure how to fix it. If I change matchText so that it doesn't contain the double quotes, everything works.

Which versions of PhpSpreadsheet and PHP are affected?

All PHP. Current, and probably for some time back, versions of PhpSpreadsheet.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Still a problem.

[MarkBaker]

Resolved

[oleibman]

Great work! Thanks. The calculation engine is still too scary for me to work on.

[MarkBaker]

The calculation engine is scary for me too; and I wrote most of it... I had rewritten it to be nice and maintainable, but there was a high performance overhead (4x slower) in that; so for the moment I live with ugly code that is difficult to maintain, but at least performs reasonably.
The branch pruning for lazy IF() evaluaion wasn't my code, and that also scares me.