[HyperFormula] Conformance: engine & comparison operators

[aninde]

Description

We need to check conformance of comparison operators

• <
• >
• <=
• >=
• =
• <>
• all error types
• ?
• : (range)
• ~
• &
• Comparrision to Excel
  ['"A"', '"B"', '=A1>B1']`
  Text A, Text B, Formuła
• with enabled CASE_SENSITIVE
• with disabled CASE_SENSITIVE
• Does all operators are supported
• Can parameters be a reference to another cell
• Are the correct errors returned when something went wrong
• How does it behave when parameters are out of bounds (eg. -1 when it should be >0)

Test cases:

Types

• type comparison
  • same types
  • different types
• errors handle

Objects

• null

Numbers

• null
• NaN,
• Infinity,
• - Infinity,
• -0, which the engine should not support. He shouldn't have crashed either
• how zero suppression is handled In JS we can write a number as .3, but in hyperformula it is a string, not always number
• precision
• very big numbers
• very small numbers
• the maximum and minimum number served. If the type is number then there will be a JS limit, but there is no such limit in the string.

Stage 1

• all Errors handling
• data types
• type conversion

Stage 2

New issue.

Documentation

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/localeCompare
#1 (comment)

Parsing text to value:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/complex-parsing.xls

All operators and types combinations:
https://gitlab.gnome.org/GNOME/gnumeric/blob/master/samples/excel/operator.xls
GS v. https://docs.google.com/spreadsheets/d/1uwMSAOssKTo_rpbFDObPgux7BW3pIL3IiZp-FiJd1Xk/edit#gid=0

Requirements

https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017966_715980110

[wojciechczerniak]

Escaped strings: "aaa"aaaa"
Unicode characters: let's say how two emoji are compared
Unicode with code "\u1223" or however it's supported

[aninde]

BUG REPORT draft

Description

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

CASE 1

A1 = true
A2 = null

Operator	Expected	Result	Correctness
A1=A2	FALSE	FALSE
A1>A2	TRUE	{"type": "VALUE"}	NO
A1>=A2	FALSE	{"type": "VALUE"}	NO
A1<=A2	TRUE	{"type": "VALUE"}	NO
A1<>A2	TRUE	TRUE
A1+A2	1	{"type": "VALUE"}	NO
A1-A2	1	{"type": "VALUE"}	NO
A1*A2	0	{"type": "VALUE"}	NO
A1/A2	#DIV/0!	{"type": "VALUE"}	NO
A1^A2	1	{"type": "VALUE"}	NO
A1&A2	TRUE	TRUE
+A1	0	{"type": "VALUE"}	NO
-A1	0	{"type": "VALUE"}	NO
A1%	0	Symbol() - EmptyCell	NO

Steps to reproduce

it('TRUE and BLANK should be supported by all comparison operators', () => {
        const engine = createEngine([
            ['true', null, '=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%']
        ]);

        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(0); // UNARY PLUS   
        expect(engine.getCellValue('P1')).toEqual(0); // UNARY MINUS  
        expect(engine.getCellValue('R1')).toEqual(0); // PERCENTAGE  
    })

Links

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

[aninde]

Excel - assumptions

Biggest and smallest number

The maximum number 1.79769313486232E+308
The minimum positive number that can be stored is 2.2250738585072E-308

Cases in which we adhere to IEEE 754

Underflow: occurs when a number is generated that is too small to be represented.
In IEEE and Excel, the result is 0 (with the exception that IEEE has a concept of -0, and Excel does not).

Overflow: occurs when a number is too large to be represented: #NUM in Excel.

Cases in which we do not adhere to IEEE 754:

Denormalized numbers: A denormalized number is indicated by an exponent of 0. In that case, the entire number is stored in the mantissa and the mantissa has no implicit leading 1.
As a result, you lose precision, and the smaller the number, the more precision is lost.

Positive/Negative ∞: Infinities occur when you divide by 0. Excel does not support infinities, rather, it gives a #DIV/0! error in these cases.

NaN: is used to represent invalid operations (such as infinity/infinity, infinity-infinity, or the square root of -1). NaNs allow a program to continue past an invalid operation.
Excel instead immediately generates an error such as #NUM! or #DIV/0!.

Precision

The amount of precision possible may vary depending on the size of the number (the mantissa) being manipulated.
In the case of Excel, although Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it can only do so within 15 digits of precision. This limitation is a direct result of strictly following the IEEE 754 specification and is not a limitation of Excel. This level of precision is found in other spreadsheet programs as well.

Examples

Big numbers

A1: 1.2E+200
B1: 1E+100
C1: =A1+B1  

Result:
1.20E+200 in Excel 
1.20E+200 in GS 

Small numbers

A1: 0.000123456789012345
B1: 1
C1: =A1+B1 

Result:
1,000123457 in Excel
1.000123457 in GS

Source

https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
https://support.office.com/pl-pl/article/specyfikacje-i-ograniczenia-programu-excel-1672b34d-7043-467e-8e27-269d656771c3?redirectSourcePath=%252fen-us%252farticle%252fExcel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f&ui=pl-PL&rs=pl-PL&ad=PL
Based on standard IEEE 754 https://steve.hollasch.net/cgindex/coding/ieeefloat.html

[aninde]

Is empty cell a null?

Description

Zero and BLANK should return true, after using EQUAL operator and false with NOT QUAL.

A1 = 0
A2 = null

Operator	HyperFormula	Excel	GS	Libra Calc	Gnumeric
= (EQUAL)	false	true	true	true	true
<>(NOT EQUAL)	true	false	false	false	false

References

4.7 Empty Cell

An empty cell is neither zero nor the empty string, and an empty cell can be distinguished from cells containing values (including zero and the empty string). An empty cell is not the same as an Error, in particular, it is distinguishable from the Error #N/A (not available).
https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1018022_715980110