Booleans and BLANK/EMPTY with operators gives wrog results

[aninde]

Description

There are some differences in the results between HyperFormula and Excel/Gnumeric for booleans and blank/empty values used with arithmetic and comparison operators.

Case 1

A1 = true
A2 = null

Steps to reproduce

const data =
['=A1=B1', '=A1>B1', '=A1<B1', '=A1>=B1', '=A1<=B1', '=A1<>B1', '=A1+B1', '=A1-B1', '=A1*B1', '=A1/B1', '=A1^B1', '=A1&B1', '=+A1', '=-A1', '=A1%'];

function createEngine(data: any[][]) {
const engine = HyperFormula.buildFromArray(data);

return {
getCellValue(cellAddress: string) {
return engine.getCellValue(adr(cellAddress));
}
}
};
it('TRUE and BLANK should be supported by all comparison operators', () => {
const engine = createEngine([
['true', null, ...data]
]);
expect(engine.getCellValue('C1')).toEqual(false); // EQUAL
expect(engine.getCellValue('D1')).toEqual(true); // GT
expect(engine.getCellValue('E1')).toEqual(false); // LT
expect(engine.getCellValue('F1')).toEqual(true); // GTE
expect(engine.getCellValue('G1')).toEqual(false); // LTE
expect(engine.getCellValue('H1')).toEqual(true); // NOT EQUAL
expect(engine.getCellValue('I1')).toEqual(1); // ADD
expect(engine.getCellValue('J1')).toEqual(1); // SUB
expect(engine.getCellValue('K1')).toEqual(0); // MULT
expect(engine.getCellValue('L1')).toEqual(new CellError(ErrorType.DIV_BY_ZERO)); // DIV
expect(engine.getCellValue('M1')).toEqual(1); // EXP
expect(engine.getCellValue('N1')).toEqual('true'); // CONCAT
expect(engine.getCellValue('O1')).toEqual(true); // UNARY PLUS
expect(engine.getCellValue('P1')).toEqual(-1); // UNARY MINUS
expect(engine.getCellValue('Q1')).toEqual(0.01); // PERCENTAGE
});

Invalid results are in bold.

Name	Operator	Expected	Result
EQUAL	A1=A2	FALSE	FALSE
GT	A1>A2	TRUE	{ type: 'VALUE' }
LT	A1<A2	FALSE	{ type: 'VALUE' }
GTE	A1>=A2	TRUE	{ type: 'VALUE' }
LTE	A1<=A2	FALSE	{ type: 'VALUE' }
NOT EQUAL	A1<>A2	TRUE	TRUE
ADD	A1+A2	1	TRUE
SUB	A1-A2	1	{ type: 'VALUE' }
MULT	A1*A2	0	{ type: 'VALUE' }
DIV	A1/A2	#DIV/0!	{ type: 'VALUE' }
EXP	A1^A2	1	{ type: 'VALUE' }
CONCAT	A1&A2	EMPTY_VALUE	{ type: 'VALUE' }
UNARY +	+A1	TRUE	TRUE
UNARY -	-A1	-1	{ type: 'VALUE' }
PERC	A1%	0.01	{ type: 'VALUE' }

Case 2

A1 = null
A2 = true

Steps to reproduce

it('BLANK and TRUE should be supported by all comparison operators', () => {
        const engine = createEngine([
            [null, 'true', ...data]
        ]);

        expect(engine.getCellValue('D1')).toEqual(false); // GT    
        expect(engine.getCellValue('E1')).toEqual(true); // LT   
        expect(engine.getCellValue('F1')).toEqual(true); // GTE   
        expect(engine.getCellValue('G1')).toEqual(false); // LTE  
        expect(engine.getCellValue('J1')).toEqual(-1); // SUB  
        expect(engine.getCellValue('L1')).toEqual(new CellError(ErrorType.DIV_BY_ZERO)); // DIV   
        expect(engine.getCellValue('M1')).toEqual(1); // EXP  
    });

Name	Operator	Expected	Result
GT	A1>A2	FALSE	{ type: 'VALUE' }
LT	A1<A2	TRUE	{ type: 'VALUE' }
GTE	A1>=A2	TRUE	{ type: 'VALUE' }
LTE	A1<=A2	FALSE	{ type: 'VALUE' }
SUB	A1-A2	-1	{ type: 'VALUE' }
DIV	A1/A2	#DIV/0!	{ type: 'VALUE' }
EXP	A1^A2	1	{ type: 'VALUE' }

Links

https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/operator.xls

[aninde]

Edit: I change the Expected result for Case 1 to proper one.

[wojciechczerniak]

Related issue #127

[izulin]

solved #163

[wojciechczerniak]

Closing as done. @aninde please verify

[aninde]

Still, ADD, SUB, MULT DIV, EXP, UNARY +, UNARY -, Percentage returns #VALUE! instead of proper results. No matter if I change null to emptyValue, the results are the same.

with ['true', EmptyValue or null, ...data]
ADD should return 1, but returns #VALUE!
SUB should return 1, but returns #VALUE!
MULT should return 0, but returns #VALUE!
DIV should return '#DIV/0!', but returns #VALUE!
EXP should return 1, but returns #VALUE!
UNARY PLUS should return 0, but returns #VALUE!
UNARY MINUS should return -1, but returns #VALUE!
PERCENTAGE should return 0.01, but returns #VALUE!

['null', 'true', ...data]
SUB should return -1, but returns #VALUE!
MULT should return 0, but returns #VALUE!
DIV should return '0', but returns #VALUE!
EXP should return 1, but returns #VALUE!

@izulin do you like this form of report or I should improve it?

[izulin]

AFAIR 'true' and true are not the same thing, string 'true' should not be coerced to boolean true.
The same with string 'null' which should not behave like EmptyValue.

[aninde]

AFAIR 'true' and true are not the same thing, string 'true' should not be coerced to boolean true.

True, tests pass. Thank you for the repair. I am closing the issue.

The same with string 'null' which should not behave like EmptyValue.

I will verify it later.