Wrong result when using `ROW()` function inside a named expression

[thilgen]

Description

I am seeing unexpected results with Named Expressions.

Given an expression

const getRowExpression = "=ROW()"

And a Named Expression registered on sheet 0

engine.addNamedExpression('GetRowNumber', getRowExpression, 0)

The following usage returns different results

const engine = HyperFormula.buildFromArray([
  [getRowExpression, "=GetRowNumber"],
  [getRowExpression, "=GetRowNumber"],
  [getRowExpression, "=GetRowNumber"],
  [getRowExpression, "=GetRowNumber"],
  [getRowExpression, "=GetRowNumber"],
])

A1:A5 => [0, 1, 2, 3, 4, 5]
B1:B5 => [2, 2, 2, 2, 2, 2]

I expected these values to be equal.

You can see a full example on JSFiddle (hyperformula-named-references-bug)

Or you can drop the following unit test into hyperformula/test/named-expressions.spec.ts on either the hyperformula::master or hyperformula:develop branches

  describe('bug', () => {
    it('basic example', () => {
      const getRowExpression = "=ROW()"

      const engine = HyperFormula.buildFromArray([
        [getRowExpression, "=GetRowNumber"],
        [getRowExpression, "=GetRowNumber"],
        [getRowExpression, "=GetRowNumber"],
        [getRowExpression, "=GetRowNumber"],
        [getRowExpression, "=GetRowNumber"],
      ])

      engine.addNamedExpression('GetRowNumber', getRowExpression, 0)

      console.log(engine.getRangeValues(AbsoluteCellRange.spanFrom(adr('A1', 0), 2, 5)))

      expect(engine.getCellValue(adr('A1', 0))).toEqual(1)
      expect(engine.getCellValue(adr('A2', 0))).toEqual(2)
      expect(engine.getCellValue(adr('A3', 0))).toEqual(3)
      expect(engine.getCellValue(adr('A4', 0))).toEqual(4)
      expect(engine.getCellValue(adr('A5', 0))).toEqual(5)

      /* all the values in B are equal to 2 - maybe an issue with caching the named expression? */
      expect(engine.getCellValue(adr('B1', 0))).toEqual(1)
      expect(engine.getCellValue(adr('B2', 0))).toEqual(2)
      expect(engine.getCellValue(adr('B3', 0))).toEqual(3)
      expect(engine.getCellValue(adr('B4', 0))).toEqual(4)
      expect(engine.getCellValue(adr('B5', 0))).toEqual(5)
    })

    it('moderate example', () => {
      const accountLookupExpression = "=IFNA(VLOOKUP(INDEX(ledger!$A:$A, ROW()),accounts!$A:$B,2,FALSE()),\"\")"

      const engine = HyperFormula.buildFromSheets({
        ledger: [
          [23, accountLookupExpression, "=AccountLookup"],
          [30, accountLookupExpression, "=AccountLookup"],
          [13, accountLookupExpression, "=AccountLookup"],
        ],
        accounts: [
          [23, "Office Supplies"],
          [13, "Rent"],
          [26, "Gas"],
          [20, "Maintenance"],
          [30, "Capital Improvements"],
        ]
      })

      engine.addNamedExpression('AccountLookup', accountLookupExpression, 0)

      console.log(engine.getRangeValues(AbsoluteCellRange.spanFrom(adr('A1', 0), 3, 3)))

      expect(engine.getCellValue(adr('B1', 0))).toEqual('Office Supplies')
      expect(engine.getCellValue(adr('B2', 0))).toEqual('Capital Improvements')
      expect(engine.getCellValue(adr('B3', 0))).toEqual('Rent')

      /* all the values in C are equal to 'Capital Improvements' - maybe an issue with caching the named expression? */
      expect(engine.getCellValue(adr('C1', 0))).toEqual('Office Supplies')
      expect(engine.getCellValue(adr('C2', 0))).toEqual('Capital Improvements')
      expect(engine.getCellValue(adr('C3', 0))).toEqual('Rent')
    })
  })

Steps to reproduce

1. Run the provided JSFiddle (hyperformula-named-references-bug)
2. Drop the provided unit test into named-expressions.spec.ts.

Your environment

• HyperFormula version: 2.3.1, hyperformula::master, hyperformula:develop
• Browser Name and version: Chrome
• Operating System: Mac

[sequba]

@thilgen I can confirm it is a bug. Thank you for reporting it. We will work on it in one of the upcoming releases.

If the fix would be very hard, we can just describe it in the docs as a limitation for our named expressions feature.