Skip to content

Build your own AI office with Univer Office SDK. Bring editing, calculation, collaboration, and file processing into your product for people and AI agents. Built on an extensible open-source core.

30.5 KB
Raw Blame
/**
 * Copyright 2023-present DreamNum Co., Ltd.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

import { cellToRange, CellValueType, DateSystem } from '@univerjs/core';
import { describe, expect, it, vi } from 'vitest';
import { ErrorType } from '../../../basics/error-type';
import { compareToken } from '../../../basics/token';
import { CellReferenceObject } from '../../reference-object/cell-reference-object';
import { ColumnReferenceObject } from '../../reference-object/column-reference-object';
import { RangeReferenceObject } from '../../reference-object/range-reference-object';
import { RowReferenceObject } from '../../reference-object/row-reference-object';
import { ArrayValueObject } from '../../value-object/array-value-object';
import { ErrorValueObject } from '../../value-object/base-value-object';
import { BooleanValueObject, NullValueObject, NumberValueObject, StringValueObject } from '../../value-object/primitive-object';
import { valueObjectCompare } from '../object-compare';
import {
    convertTonNumber,
    filterSameValueObjectResult,
    getPairedRangeAndCriteriaResult,
    isSingleValueObject,
    objectValueToCellValue,
} from '../value-object';

describe('Test object cover', () => {
    it('preserves error-looking text through runtime serialization', () => {
        expect(objectValueToCellValue(StringValueObject.create('#REF!'))).toEqual({ v: '#REF!', t: CellValueType.FORCE_STRING });
        expect(objectValueToCellValue(ErrorValueObject.create(ErrorType.REF))).toEqual({ v: '#REF!', t: CellValueType.STRING });
    });

    it.each([
        ['1', '<>1', false, true],
        ['01', '<>1', false, true],
        ['1', '01', true, true],
        ['1', '1.0', true, true],
        ['1e0', '=1', true, true],
        ['1', '>0', true, false],
        ['\t1', '=1', true, false],
        ['\t1', '\t1', false, true],
        ['0x10', '0x10', false, true],
        ['Infinity', '<>Infinity', true, false],
        ['2026-1-1', '<>2026-1-1', true, true],
    ])('matches Excel numeric and text criteria: %s, %s', (text, criteria, numericMatch, textMatch) => {
        const range = ArrayValueObject.create({
            calculateValueList: [[NumberValueObject.create(1)], [StringValueObject.create(text)]],
            rowCount: 2,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const criterion = StringValueObject.create(criteria);
        const comparison = valueObjectCompare(range, criterion) as ArrayValueObject;
        const result = filterSameValueObjectResult(comparison, range, criterion);
        expect(result.toValue()).toEqual([[numericMatch], [textMatch]]);
    });

    it('Function convertTonNumber', () => {
        expect(convertTonNumber(BooleanValueObject.create(true)).getValue()).toBe(1);
        expect(convertTonNumber(BooleanValueObject.create(false)).getValue()).toBe(0);
    });

    it('Boolean values are not equal to text values', () => {
        expect(BooleanValueObject.create(false).compare(StringValueObject.create(''), compareToken.EQUALS).getValue()).toBe(false);
        expect(BooleanValueObject.create(false).compare(StringValueObject.create(''), compareToken.NOT_EQUAL).getValue()).toBe(true);
    });

    it('uses the supplied date system when matching date criteria against serial numbers', () => {
        const range = ArrayValueObject.createByArray([[0]]);
        const criteria = StringValueObject.create('1904-1-1').withDateSystem(DateSystem.Date1904);
        const comparison = valueObjectCompare(range, criteria) as ArrayValueObject;

        expect(comparison.get(0, 0)?.getValue()).toBe(true);
        const result = filterSameValueObjectResult(comparison, range, criteria);

        expect(result.get(0, 0)?.getValue()).toBe(true);
    });

    it('Function isSingleValueObject', () => {
        expect(isSingleValueObject(BooleanValueObject.create(true))).toBe(true);
        expect(isSingleValueObject(BooleanValueObject.create(false))).toBe(true);
        expect(isSingleValueObject(NumberValueObject.create(1))).toBe(true);
        expect(isSingleValueObject(StringValueObject.create('Univer'))).toBe(true);
        expect(isSingleValueObject(NullValueObject.create())).toBe(true);
        expect(isSingleValueObject(ErrorValueObject.create(ErrorType.VALUE))).toBe(true);

        expect(isSingleValueObject(ArrayValueObject.create(/*ts*/ `{
            1, 3;
            8, 7
        }`))).toBe(false);
        expect(isSingleValueObject(ArrayValueObject.create(/*ts*/ `{
            1
        }`))).toBe(true);

        expect(isSingleValueObject(new CellReferenceObject('A1'))).toBe(true);
        expect(isSingleValueObject(new RowReferenceObject('1:1'))).toBe(false);
        expect(isSingleValueObject(new ColumnReferenceObject('A:A'))).toBe(false);
        expect(isSingleValueObject(new RangeReferenceObject(cellToRange(0, 1)))).toBe(true);
        expect(isSingleValueObject(new RangeReferenceObject({ startRow: 0, endRow: 1, startColumn: 0, endColumn: 1 }))).toBe(false);
    });

    it('Function objectValueToCellValue', () => {
        expect(objectValueToCellValue(NumberValueObject.create(1))).toStrictEqual({ v: 1, t: CellValueType.NUMBER });
        expect(objectValueToCellValue(StringValueObject.create('Univer'))).toStrictEqual({ v: 'Univer', t: CellValueType.STRING });
        expect(objectValueToCellValue(StringValueObject.create('0'))).toStrictEqual({ v: '0', t: CellValueType.STRING });
        expect(objectValueToCellValue(BooleanValueObject.create(true))).toStrictEqual({ v: 1, t: CellValueType.BOOLEAN });
        expect(objectValueToCellValue(BooleanValueObject.create(false))).toStrictEqual({ v: 0, t: CellValueType.BOOLEAN });
        expect(objectValueToCellValue(NullValueObject.create())).toStrictEqual({ v: null });
        expect(objectValueToCellValue(null)).toStrictEqual({ v: null });
        expect(objectValueToCellValue(ErrorValueObject.create(ErrorType.VALUE))).toStrictEqual({ v: ErrorType.VALUE, t: CellValueType.STRING });
        expect(objectValueToCellValue(StringValueObject.create('0').withCustomData({ test: 'abc' }))).toStrictEqual({ v: '0', t: CellValueType.STRING, custom: { test: 'abc' } });
    });
});

describe('COUNTIFS array criteria', () => {
    it.each(['text', 'numeric'])('avoids copying densely matched %s ranges', (kind) => {
        const size = 200;
        const [common, rare] = kind === 'text' ? ['common', 'rare'] : ['1', '2'];
        const range = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i < 180 ? common : rare]));
        const groups = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i % 2]));
        const criterion = kind === 'text' ? StringValueObject.create('common') : NumberValueObject.create(1);
        const criteria = ArrayValueObject.createByArray(Array.from({ length: 20 }, () => [criterion.getValue()]));
        const params = { formulaName: 'COUNTIFS', maxColumnLength: 1, isNumberSensitive: true };
        const reads = vi.spyOn(ArrayValueObject.prototype, 'get');
        try {
            for (let i = 0; i < 20; i++) {
                expect(getPairedRangeAndCriteriaResult([range, criterion, groups, NumberValueObject.create(1)], {
                    ...params,
                    maxRowLength: 1,
                })[0][0].getValue()).toBe(90);
            }
            const fullScanReads = reads.mock.calls.length;
            reads.mockClear();
            const result = getPairedRangeAndCriteriaResult([range, criteria, groups, NumberValueObject.create(1)], {
                ...params,
                maxRowLength: 20,
            });
            expect(result.map((row) => row[0].getValue())).toEqual(new Array(20).fill(90));
            // Index construction may scan once, but must not add range copies for every output.
            expect(reads.mock.calls.length).toBeLessThanOrEqual(fullScanReads + size * 2);
        } finally {
            reads.mockRestore();
        }
    });

    it.each(['text', 'numeric'])('does not rescan the entire lookup range for each %s criterion', (kind) => {
        const range = ArrayValueObject.createByArray(Array.from({ length: 200 }, (_, i) => [kind === 'text' ? `ID-${i}` : `${10000000000 + i}`]));
        const criteria = ArrayValueObject.createByArray(Array.from({ length: 100 }, (_, i) => [kind === 'text' ? `id-${i}` : 10000000000 + i]));
        const roles = ArrayValueObject.createByArray(Array.from({ length: 200 }, (_, i) => [i % 2 ? 'Staff' : 'Manager']));
        const reads = vi.spyOn(range, 'get');
        try {
            const result = getPairedRangeAndCriteriaResult([range, criteria, roles, StringValueObject.create('Manager')], {
                formulaName: 'COUNTIFS',
                maxRowLength: 100,
                maxColumnLength: 1,
                isNumberSensitive: true,
            });
            expect(result.map((row) => row.map((value) => value.getValue())))
                .toEqual(Array.from({ length: 100 }, (_, i) => [i % 2 ? 0 : 1]));
            // A larger input must not turn each criterion into a full-range scan.
            expect(reads.mock.calls.length).toBeLessThan(1000);
        } finally {
            reads.mockRestore();
        }
    });
});

describe('Conditional aggregate indexed equality semantics', () => {
    it.each(['COUNTIFS', 'SUMIFS', 'AVERAGEIFS', 'MINIFS', 'MAXIFS'])(
        '%s preserves broadcast row and column criteria when reusing tuples',
        (formulaName) => {
            const keys = ArrayValueObject.createByArray([['a'], ['a'], ['b'], ['b']]);
            const groups = ArrayValueObject.createByArray([[1], [2], [1], [2]]);
            const amounts = ArrayValueObject.createByArray([[10], [20], [30], [40]]);
            const criteria = ArrayValueObject.createByArray([['a'], ['b'], ['a']]);
            const groupCriteria = ArrayValueObject.createByArray([[1, 2, 1]]);
            const params = { formulaName, isNumberSensitive: true, targetRange: amounts };
            const result = getPairedRangeAndCriteriaResult([keys, criteria, groups, groupCriteria], {
                ...params,
                maxRowLength: 3,
                maxColumnLength: 3,
            });
            const expected = ['a', 'b', 'a'].map((key) => [1, 2, 1].map((group) => getPairedRangeAndCriteriaResult([
                keys,
                StringValueObject.create(key),
                groups,
                NumberValueObject.create(group),
            ], { ...params, maxRowLength: 1, maxColumnLength: 1 })[0][0].getValue()));
            expect(result.map((row) => row.map((value) => value.getValue()))).toEqual(expected);
        }
    );

    it.each(['COUNTIFS', 'SUMIFS', 'AVERAGEIFS', 'MINIFS', 'MAXIFS'])(
        '%s narrows later array conditions and reuses complete condition tuples',
        (formulaName) => {
            const size = 200;
            const keys = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i]));
            const groups = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i % 3 ? 'major' : 'minor']));
            const amounts = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i - 50]));
            const criteria = ArrayValueObject.createByArray(Array.from({ length: 100 }, (_, i) => [i]));
            const groupCriteria = ArrayValueObject.createByArray(Array.from({ length: 100 }, () => ['major']));
            const params = { formulaName, maxColumnLength: 1, maxRowLength: 100, isNumberSensitive: true, targetRange: amounts };
            const reads = vi.spyOn(ArrayValueObject.prototype, 'get');
            try {
                const sparse = getPairedRangeAndCriteriaResult([groups, groupCriteria, keys, criteria], params);
                const sparseReads = reads.mock.calls.length;
                const expected = criteria.mapValue((criterion) => getPairedRangeAndCriteriaResult([
                    groups,
                    StringValueObject.create('major'),
                    keys,
                    criterion,
                ], { ...params, maxRowLength: 1 })[0][0]);
                expect(sparse.map((row) => row[0].getValue())).toEqual(expected.toValue().map((row) => row[0]));
                expect(sparseReads).toBeLessThan(6000);

                const parity = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i % 2]));
                const pairCriteria = ArrayValueObject.createByArray(Array.from({ length: 100 }, (_, i) => [i % 2]));
                const repeatedGroups = ArrayValueObject.createByArray(Array.from({ length: 100 }, (_, i) => [i % 4 < 2 ? 'major' : 'minor']));
                reads.mockClear();
                const repeated = getPairedRangeAndCriteriaResult([groups, repeatedGroups, parity, pairCriteria], params);
                const repeatedReads = reads.mock.calls.length;
                const expectedRepeated = pairCriteria.mapValue((criterion, row) => getPairedRangeAndCriteriaResult([
                    groups,
                    repeatedGroups.get(row, 0)!,
                    parity,
                    criterion,
                ], { ...params, maxRowLength: 1 })[0][0]);
                expect(repeated.map((row) => row[0].getValue())).toEqual(expectedRepeated.toValue().map((row) => row[0]));
                expect(repeatedReads).toBeLessThan(20000);
                amounts.set(1, 0, NumberValueObject.create(999));
                const recalculated = getPairedRangeAndCriteriaResult([groups, repeatedGroups, parity, pairCriteria], params);
                expect(recalculated[1][0].getValue()).toEqual(getPairedRangeAndCriteriaResult([
                    groups,
                    StringValueObject.create('major'),
                    parity,
                    NumberValueObject.create(1),
                ], { ...params, maxRowLength: 1 })[0][0].getValue());
            } finally {
                reads.mockRestore();
            }
        }
    );

    it.each(['COUNTIFS', 'SUMIFS', 'AVERAGEIFS', 'MINIFS', 'MAXIFS'])(
        '%s narrows array criteria after a broad scalar condition and reuses repeated dense results',
        (formulaName) => {
            const size = 200;
            const keys = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [`00${i}`]));
            const groups = ArrayValueObject.createByArray(Array.from({ length: size }, () => ['keep']));
            const amounts = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i + 1]));
            const criteria = ArrayValueObject.createByArray(Array.from({ length: 100 }, (_, i) => [i]));
            const params = { formulaName, maxColumnLength: 1, maxRowLength: 100, isNumberSensitive: true, targetRange: amounts };
            const reads = vi.spyOn(ArrayValueObject.prototype, 'get');
            try {
                const sparse = getPairedRangeAndCriteriaResult([
                    groups,
                    ArrayValueObject.createByArray([['keep']]),
                    keys,
                    criteria,
                ], params);
                expect(sparse.map((row) => row[0].getValue())).toEqual(
                    Array.from({ length: 100 }, (_, i) => formulaName === 'COUNTIFS' ? 1 : i + 1)
                );
                expect(reads.mock.calls.length).toBeLessThan(6000);
                reads.mockClear();
                const dense = getPairedRangeAndCriteriaResult([
                    groups,
                    ArrayValueObject.createByArray(Array.from({ length: 100 }, () => ['keep'])),
                ], params);
                const expected: Record<string, number> = { COUNTIFS: size, SUMIFS: 20100, AVERAGEIFS: 100.5, MINIFS: 1, MAXIFS: size };
                expect(dense.map((row) => row[0].getValue())).toEqual(new Array(100).fill(expected[formulaName]));
                expect(reads.mock.calls.length).toBeLessThan(6000);
            } finally {
                reads.mockRestore();
            }
        }
    );

    it.each(['COUNTIFS', 'SUMIFS', 'AVERAGEIFS', 'MINIFS', 'MAXIFS'])(
        '%s preserves row order, errors, blanks and paired array criteria',
        (formulaName) => {
            const range = ArrayValueObject.createByArray([
                ['01', 1, '1.0', 2],
                ['x', 'X', '', 0],
                [true, null, 'a*b', 'x'],
                ['other', 'other', 'other', 'other'],
                ['other', 'other', 'other', 'other'],
            ]);
            const amounts = ArrayValueObject.createByArray([
                [1e16, 1, -1e16, 0.1],
                [2, 3, 4, 5],
                [6, 7, 8, 9],
                [10, 11, 12, 13],
                [14, 15, 16, 17],
            ]);
            amounts.set(2, 3, ErrorValueObject.create(ErrorType.NA));
            const criteria = ArrayValueObject.createByArray([[1, 'x', '', 'a~*b'], ['missing', '>=0', '*', 0]]);
            const groups = ArrayValueObject.createByArray(Array.from({ length: 5 }, () => [1, 1, 1, 1]));
            const groupCriteria = ArrayValueObject.createByArray([[1, 1, 1, 1], [1, 1, 2, 1]]);
            const params = { formulaName, isNumberSensitive: true, targetRange: amounts };
            const result = getPairedRangeAndCriteriaResult([range, criteria, groups, groupCriteria], {
                ...params,
                maxRowLength: 2,
                maxColumnLength: 4,
            });
            const expected = criteria.mapValue((criterion, row, column) => getPairedRangeAndCriteriaResult([
                range,
                criterion,
                groups,
                groupCriteria.get(row, column)!,
            ], { ...params, maxRowLength: 1, maxColumnLength: 1 })[0][0]);
            expect(result.map((row) => row.map((value) => value.getValue()))).toEqual(expected.toValue());
        }
    );

    it('preserves duplicates, numeric text, paired two-dimensional criteria and fallback comparisons', () => {
        const range = ArrayValueObject.create({
            calculateValueList: [
                [StringValueObject.create('Alpha'), StringValueObject.create('ALPHA'), NumberValueObject.create(1)],
                [StringValueObject.create('1'), StringValueObject.create('01'), StringValueObject.create('Beta')],
                [NullValueObject.create(), StringValueObject.create(''), ErrorValueObject.create(ErrorType.NA)],
                [BooleanValueObject.create(true), NumberValueObject.create(0.1 + 0.2), StringValueObject.create('a*b')],
            ],
            rowCount: 4,
            columnCount: 3,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const groups = ArrayValueObject.createByArray([[1, 2, 1], [1, 1, 2], [1, 1, 1], [1, 1, 1]]);
        const criteria = [
            [StringValueObject.create('=alpha'), NumberValueObject.create(1), StringValueObject.create('01')],
            [StringValueObject.create('a*'), StringValueObject.create('>0'), NumberValueObject.create(0.3)],
            [StringValueObject.create(''), ErrorValueObject.create(ErrorType.NA), BooleanValueObject.create(true)],
            [StringValueObject.create('missing'), StringValueObject.create('a~*b'), StringValueObject.create('<>Beta')],
        ];
        const criteriaArray = ArrayValueObject.create({
            calculateValueList: criteria,
            rowCount: 4,
            columnCount: 3,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const groupCriteria = ArrayValueObject.createByArray([[1, 1, 1], [1, 1, 1], [1, 1, 1], [2, 1, 1]]);
        const result = getPairedRangeAndCriteriaResult([range, criteriaArray, groups, groupCriteria], {
            formulaName: 'COUNTIFS',
            maxRowLength: 4,
            maxColumnLength: 3,
            isNumberSensitive: true,
        });
        const expected = criteria.map((row, r) => row.map((criterion, c) => getPairedRangeAndCriteriaResult([
            range,
            criterion,
            groups,
            groupCriteria.get(r, c)!,
        ], {
            formulaName: 'COUNTIFS',
            maxRowLength: 1,
            maxColumnLength: 1,
            isNumberSensitive: true,
        })[0][0].getValue()));
        expect(result.map((row) => row.map((value) => value.getValue()))).toEqual(expected);
        expect(expected[0]).toEqual([1, 3, 3]);
        expect(expected[1][2]).toBe(1);
    });

    it.each([DateSystem.Date1900, DateSystem.Date1904])('preserves text normalization and date-system %s criteria', (dateSystem) => {
        const values = [
            StringValueObject.create('Straße'),
            StringValueObject.create('STRASSE'),
            StringValueObject.create('é'),
            StringValueObject.create('e\u0301'),
            StringValueObject.create('1e-310'),
            NumberValueObject.create(0),
            StringValueObject.create('\t1'),
            StringValueObject.create('0x10'),
            NumberValueObject.create(1),
            NumberValueObject.create(16),
            StringValueObject.create('1904-1-1').withDateSystem(dateSystem),
            NumberValueObject.create(44561.99999999999, 'yyyy-mm-dd').withDateSystem(dateSystem),
        ];
        const range = ArrayValueObject.create({
            calculateValueList: values.map((value) => [value]),
            rowCount: values.length,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const criteria = [
            StringValueObject.create('strasse'),
            StringValueObject.create('é'),
            NumberValueObject.create(0),
            StringValueObject.create('1e-310'),
            NumberValueObject.create(1),
            StringValueObject.create('0x10'),
            StringValueObject.create('1904-1-1').withDateSystem(dateSystem),
            NumberValueObject.create(44562, 'yyyy-mm-dd').withDateSystem(dateSystem),
        ];
        const criteriaArray = ArrayValueObject.create({
            calculateValueList: criteria.map((value) => [value]),
            rowCount: criteria.length,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const params = { formulaName: 'COUNTIFS', maxRowLength: criteria.length, maxColumnLength: 1, isNumberSensitive: true };
        const result = getPairedRangeAndCriteriaResult([range, criteriaArray], params);
        const expected = criteria.map((criterion) => getPairedRangeAndCriteriaResult([range, criterion], { ...params, maxRowLength: 1 })[0][0].getValue());
        expect(result.map((row) => row[0].getValue())).toEqual(expected);
        expect(expected.slice(0, 2)).toEqual([2, 2]);
        const zeroMatches = dateSystem === DateSystem.Date1904 ? 3 : 2;
        expect(expected.slice(2, 4)).toEqual([zeroMatches, zeroMatches]);
        expect(expected[7]).toBe(1);
    });

    it('rebuilds its lookup after range data changes', () => {
        const range = ArrayValueObject.createByArray([['Alpha'], ['Beta']]);
        const criteria = ArrayValueObject.createByArray([['Alpha'], ['Beta']]);
        const params = { formulaName: 'COUNTIFS', maxRowLength: 2, maxColumnLength: 1, isNumberSensitive: true };
        expect(getPairedRangeAndCriteriaResult([range, criteria], params).map((row) => row[0].getValue())).toEqual([1, 1]);
        range.set(0, 0, StringValueObject.create('Beta'));
        expect(getPairedRangeAndCriteriaResult([range, criteria], params).map((row) => row[0].getValue())).toEqual([0, 2]);
    });

    it.each(['COUNTIFS', 'SUMIFS', 'AVERAGEIFS', 'MINIFS', 'MAXIFS'])(
        '%s aggregates indexed positions like a single criterion evaluation',
        (formulaName) => {
            const keys = ArrayValueObject.createByArray([['a'], ['b'], ['a'], ['c'], ['b'], ['a']]);
            const amounts = ArrayValueObject.createByArray([[1], [2], [3], [4], [5], [6]]);
            const criteria = ArrayValueObject.createByArray([['a'], ['b'], ['x'], [null]]);
            const params = {
                formulaName,
                maxRowLength: 4,
                maxColumnLength: 1,
                isNumberSensitive: true,
                targetRange: amounts,
            };
            const result = getPairedRangeAndCriteriaResult([keys, criteria], params);
            const expected = [StringValueObject.create('a'), StringValueObject.create('b'), StringValueObject.create('x'), NullValueObject.create()]
                .map((criterion) => getPairedRangeAndCriteriaResult([keys, criterion], { ...params, maxRowLength: 1, maxColumnLength: 1 })[0][0].getValue());
            expect(result.map((row) => row[0].getValue())).toEqual(expected);
        }
    );

    it('keeps repeated criteria keys distinct when text contains the key separator', () => {
        // Excel stores U+0000 in OOXML as `_x0000_`, so imported cells can contain it.
        // These two criteria tuples must not share a cache key.
        const nul = String.fromCharCode(0);
        const firstKey = `A${nul}s:date1900:B`;
        const secondKey = `B${nul}s:date1900:C`;
        const keys = ArrayValueObject.createByArray([[firstKey], ['A']]);
        const groups = ArrayValueObject.createByArray([['C'], [secondKey]]);
        const amounts = ArrayValueObject.createByArray([[31], [37]]);

        const result = getPairedRangeAndCriteriaResult([
            keys,
            ArrayValueObject.createByArray([[firstKey], ['A']]),
            groups,
            ArrayValueObject.createByArray([['C'], [secondKey]]),
        ], {
            formulaName: 'SUMIFS',
            maxRowLength: 2,
            maxColumnLength: 1,
            isNumberSensitive: true,
            targetRange: amounts,
        });

        expect(result.map((row) => row[0].getValue())).toEqual([31, 37]);
    });

    it('keeps date-formatted serials distinct from plain numbers in cached criteria', () => {
        // Date comparisons round the serial, so `1` formatted as a date matches a
        // date-formatted 1.000000001 while a plain `1` does not.
        const range = ArrayValueObject.create({
            calculateValueList: [[NumberValueObject.create(1.000000001, 'yyyy-mm-dd')]],
            rowCount: 1,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const amounts = ArrayValueObject.create({
            calculateValueList: [[NumberValueObject.create(7)]],
            rowCount: 1,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const criteria = ArrayValueObject.create({
            calculateValueList: [[NumberValueObject.create(1, 'yyyy-mm-dd')], [NumberValueObject.create(1)]],
            rowCount: 2,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });

        const result = getPairedRangeAndCriteriaResult([range, criteria], {
            formulaName: 'SUMIFS',
            maxRowLength: 2,
            maxColumnLength: 1,
            isNumberSensitive: true,
            targetRange: amounts,
        });

        expect(result.map((row) => row[0].getValue())).toEqual([7, 0]);
    });

    it('keeps date-formatted serials that round to different dates distinct', () => {
        // Date comparison rounds the serial at 1e-8, so these two values land on
        // different dates even though they scale to the same 12-digit number.
        const lower = 45000.00000000499;
        const upper = 45000.000000005006;
        const range = ArrayValueObject.create({
            calculateValueList: [[NumberValueObject.create(lower, 'yyyy-mm-dd')]],
            rowCount: 1,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const amounts = ArrayValueObject.create({
            calculateValueList: [[NumberValueObject.create(7)]],
            rowCount: 1,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });
        const criteria = ArrayValueObject.create({
            calculateValueList: [
                [NumberValueObject.create(lower, 'yyyy-mm-dd')],
                [NumberValueObject.create(upper, 'yyyy-mm-dd')],
            ],
            rowCount: 2,
            columnCount: 1,
            unitId: '',
            sheetId: '',
            row: -1,
            column: -1,
        });

        const result = getPairedRangeAndCriteriaResult([range, criteria], {
            formulaName: 'SUMIFS',
            maxRowLength: 2,
            maxColumnLength: 1,
            isNumberSensitive: true,
            targetRange: amounts,
        });

        expect(result.map((row) => row[0].getValue())).toEqual([7, 0]);
    });

    it('evaluates the repeated blank cells of a whole-column criteria range once', () => {
        const size = 200;
        const keyCount = 20;
        const range = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [`k${i % keyCount}`]));
        const amounts = ArrayValueObject.createByArray(Array.from({ length: size }, (_, i) => [i + 1]));
        const criteria = ArrayValueObject.createByArray([
            ...Array.from({ length: keyCount }, (_, i) => [`k${i}`]),
            ...Array.from({ length: 80 }, () => [null]),
        ]);
        const params = {
            formulaName: 'SUMIFS',
            maxRowLength: 100,
            maxColumnLength: 1,
            isNumberSensitive: true,
            targetRange: amounts,
        };

        const reads = vi.spyOn(range, 'get');
        try {
            const result = getPairedRangeAndCriteriaResult([range, criteria], params);
            const expected = Array.from({ length: 100 }, (_, i) => {
                if (i >= keyCount) {
                    return 0;
                }
                let total = 0;
                for (let row = i; row < size; row += keyCount) {
                    total += row + 1;
                }
                return total;
            });
            expect(result.map((row) => row[0].getValue())).toEqual(expected);
            // Blank criteria repeat, so they must reuse one evaluation instead of rescanning per cell.
            expect(reads.mock.calls.length).toBeLessThan(size * 10);
        } finally {
            reads.mockRestore();
        }
    });
});