xref: /trunk/audit/openformula-function-gaps/function-semantic-test-plan.md (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
1<!--
2 Licensed to the Apache Software Foundation (ASF) under one
3 or more contributor license agreements.  See the NOTICE file
4 distributed with this work for additional information
5 regarding copyright ownership.  The ASF licenses this file
6 to you under the Apache License, Version 2.0 (the
7 "License"); you may not use this file except in compliance
8 with the License.  You may obtain a copy of the License at
9
10   http://www.apache.org/licenses/LICENSE-2.0
11
12 Unless required by applicable law or agreed to in writing,
13 software distributed under the License is distributed on an
14 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 KIND, either express or implied.  See the License for the
16 specific language governing permissions and limitations
17 under the License.
18-->
19
20OpenFormula Function Compatibility Gap Audit — Semantic Test Plan
21=================================================================
22
23Branch: openformula
24Starting commit: 6a2f742073
25Audit type: Read-only source inspection (tests prepared, NOT executed)
26
27Updated 2026-11-18 to match the code after the post-merge review. The SG.*
28rows were replaced by 99 rows at the end of
29test/odf-validate/calcval-cases14.tsv, checked against Excel 16 where Excel
30has the function and run on macOS and Linux builds (all match). Expected
31values below that were wrong are corrected; where AOO and Excel differ on
32purpose, the row note says so.
33
34This document provides a test matrix for all 15 audited functions.
35Tests are designed but not run. Each test distinguishes:
36
37  1. Formula parser rejection
38  2. Unknown function
39  3. Wrong argument count
40  4. Evaluation failure
41  5. Incorrect numerical result
42  6. Incorrect error result
43  7. Import failure
44  8. Export failure
45  9. Missing platform service
46
47Expected results are derived from ODF 1.4 Part 4 (where applicable)
48or independently calculated mathematical examples. No test result is
49claimed as verified.
50
511\. FINDB
52--------
53
54Defined by ODF 1.2 and 1.4 (§6.7.2: "The same as FIND, but using byte
55positions"). Byte model: DBCS (CJK characters count 2 bytes).
56
57Test cases (rows `FINDB.*` in `test/odf-validate/calcval-cases14.tsv`):
58  a) =FINDB("b";"abc") — basic byte position (2)
59  b) =FINDB("b";"abcb";3) — start position (4)
60  c) =FINDB("x";"abc") — not found (#VALUE!)
61  d) =FINDB("B";"abc") — case-sensitive (#VALUE!)
62  e) =FINDB("a";"abc";0) — start < 1 (#VALUE!)
63  f) =FINDB("a";"abc";4) — start past the text (#VALUE!)
64  g) =FINDB("国";"中国") — DBCS second char (3)
65  h) =FINDB("b";"a中b") — mixed ASCII/DBCS (4)
66
67Implemented: ScFindB() (interpr1.cxx) uses the AOO DBCS byte model
68  (DBCS=2 bytes, ASCII=1) via getLengthB, lcl_BytePosToCharIndex,
69  lcl_CharIndexToBytePos. Case-sensitive, no wildcards. The two text
70  arguments were popped in the wrong order until 2026-11-18.
71  Excel 16 counts a CJK character as 1 byte unless a DBCS language is set,
72  so g) and h) give 2 and 3 there.
73
74Purpose: Confirm FINDB byte-position semantics and DBCS handling.
75
762\. REPLACEB
77-----------
78
79Defined by ODF 1.2 and 1.4 (§6.7.6: "As REPLACE, but using byte
80positions").
81
82Test cases (rows `REPLACEB.*` in `test/odf-validate/calcval-cases14.tsv`):
83  a) =REPLACEB("abc";2;1;"X") — basic replace (aXc)
84  b) =REPLACEB("abc";2;0;"X") — zero count inserts (aXbc)
85  c) =REPLACEB("abc";4;1;"X") — start after the end appends (abcX)
86  d) =REPLACEB("abc";2;10;"X") — count past the end (aX)
87  e) =REPLACEB("abc";0;1;"X") — start < 1 (error)
88  f) =REPLACEB("abc";2;-1;"X") — negative count (error)
89  g) =REPLACEB("中国";3;2;"日") — second DBCS character (中日)
90
91Implemented: ScReplaceB() (interpr1.cxx) uses the AOO DBCS byte model
92  via getLengthB, lcl_BytePosToCharIndex, lcl_CharIndexToBytePos.
93  Clamps position/count, handles DBCS boundaries.
94
95Purpose: Confirm REPLACEB byte-position replace and boundary handling.
96
973\. SEARCHB
98----------
99
100Defined by ODF 1.2 and 1.4 (§6.7.8: "As SEARCH, but using byte
101positions").
102
103Test cases (rows `SEARCHB.*` in `test/odf-validate/calcval-cases14.tsv`):
104  a) =SEARCHB("B";"abc") — case-insensitive (2)
105  b) =SEARCHB("b";"abcb";3) — start position (4)
106  c) =SEARCHB("x";"abc") — not found (#VALUE!)
107  d) =SEARCHB("国";"中国") — DBCS second char (3)
108
109Implemented: ScSearchB() (interpr1.cxx) uses the AOO DBCS byte model
110  via getLengthB, lcl_BytePosToCharIndex, lcl_CharIndexToBytePos.
111  Case-insensitive like SEARCH; regular expressions when the Calc option
112  for them is on. AOO 4 has no ? and * wildcards, so the earlier wildcard
113  cases (=SEARCHB("a?c";"abc") expected 1) were wrong. The two text
114  arguments were popped in the wrong order until 2026-11-18.
115
116Purpose: Confirm SEARCHB byte-position search.
117
1184\. DATEDIF
119----------
120
121ODF 1.4 Part 4 §6.10.3. Excel-compatible date difference function.
122
123Test cases (rows `DATEDIF.*` in `test/odf-validate/calcval-cases14.tsv`):
124  a) =DATEDIF(DATE(2024;1;1);DATE(2024;12;31);"d") — days difference (365)
125  b) =DATEDIF(DATE(2024;1;15);DATE(2024;6;14);"m") — complete months (4)
126  c) =DATEDIF(DATE(2024;1;31);DATE(2024;2;29);"m") — no complete month (0)
127  d) =DATEDIF(DATE(2020;6;15);DATE(2024;6;14);"y") — complete years (3)
128  e) =DATEDIF(DATE(2024;2;29);DATE(2025;2;28);"y") — leap-day anniversary (0)
129  f) =DATEDIF(DATE(2024;1;15);DATE(2024;3;10);"md") — Excel borrow from the
130     month before the end month (24; the old expectation 26 was wrong)
131  g) =DATEDIF(DATE(2024;1;31);DATE(2024;3;1);"md") — Excel gives -1, so does AOO
132  h) =DATEDIF(DATE(1995;5;3);DATE(2009;4;28);"yd") — 361, as Excel
133  i) =DATEDIF(DATE(2024;12;31);DATE(2024;1;1);"d") — start after end (error, as Excel)
134  j) =DATEDIF(DATE(2024;1;1);DATE(2024;12;31);"D") — upper case accepted (365)
135  k) "x" and "" formats (error); time fractions ignored
136  l) xls: written and read (BIFF 351); xlsx: read
137
138Purpose: Validate DATEDIF semantics; see datedif-semantic-design.md.
139Run 2026-11-18 on macOS and Linux builds and against Excel 16 (630 cases,
140one difference, documented there).
141
1425\. ISOWEEKNUM
143-------------
144
145ODF 1.4: Defined by ODF 1.4 Part 4. ISO 8601 week numbering.
146
147Test cases (rows `ISOWEEKNUM.*` in `test/odf-validate/calcval-cases14.tsv`):
148  a) =ISOWEEKNUM(DATE(2024;12;29)) — Sunday, week 52
149  b) =ISOWEEKNUM(DATE(2024;12;30)) — ISO week 1 of 2025 (1)
150  c) =ISOWEEKNUM(DATE(2020;12;31)) — ISO week 53
151  d) =ISOWEEKNUM(DATE(2021;1;3)) — still week 53 of 2020
152  e) =ISOWEEKNUM(DATE(2026;1;1)) — Thursday, week 1 (an earlier row expected 53)
153  f) =ISOWEEKNUM() — wrong arg count (error)
154  g) =WEEKNUM(DATE(2024;12;30)) — WEEKNUM still needs two arguments (error)
155
156Fixed: Added separate opcode SC_OPCODE_ISOWEEKNUM (416) and
157  ScIsoWeekNum() (interpr2.cxx) implementing ISO 8601 via
158  Date::GetWeekOfYear(MONDAY, 4). Takes exactly 1 argument (date).
159  Does not affect existing WEEKNUM (ScGetWeekOfYear) which keeps
160  2-argument SUN/MON mode semantics. On ODF import, ISOWEEKNUM with one
161  argument becomes ocIsoWeekNum (FormulaCompiler::Factor); with two it
162  stays WEEKNUM (checked with an .ods holding both forms).
163
164Purpose: Validate ISO 8601 week numbering at year boundaries. 300 random
165  dates matched Excel 16.
166
1676\. DDE
168------
169
170Application-level DDE function. Platform-dependent.
171
172Test cases:
173  a) =DDE("soffice";"table";"A1") — creates DDE link (platform-dependent)
174  b) =DDE("nonexistent";"topic";"item") — unavailable application (error expected)
175  c) =DDE("app";"topic") — 2 args (wrong arg count, needs 3)
176  d) DDE on Linux without DDE support — missing platform service
177  e) DDE in headless/server mode — DDE disabled in server mode
178     (appdde.cxx:494, 526)
179
180Purpose: Confirm DDE works where platform supports it, fails gracefully
181  otherwise. Not a pure formula test — requires DDE subsystem.
182
1837\. ODDFPRICE
184------------
185
186Analysis AddIn function. Odd-period bond price. ODF 1.2/1.4 §6.12.31.
187
188Test cases (rows `ODDFPRICE.*` in `test/odf-validate/calcval-cases14.tsv`):
189  a) Excel's short odd first period example: 113.597717474079
190  b) long odd first periods, semiannual and annual
191  c) first coupon not on the maturity's coupon schedule (error, as Excel)
192  d) redemption 0 (error)
193  e) XLS/XLSX: no mapping (add-in function)
194
195Implemented 2026-11-18 in analysishelper.cxx GetOddfprice() (trunk threw
196  #87380#). Checked against Excel 16 on random cases with all bases and
197  frequencies, short and long periods, many month ends: see
198  function-implementation-roadmap.md, Group 3, for the 14 differences in
199  4524 ODDFPRICE/ODDFYIELD cases.
200
201Purpose: Confirm the add-in matches Excel.
202
2038\. ODDFYIELD
204------------
205
206Analysis AddIn function. Odd-period bond yield. ODF 1.2/1.4 §6.12.32.
207
208Test cases (rows `ODDFYIELD.*`):
209  a) Excel's example: 0.0772455415978 (Excel 0.077245541597)
210  b) ODDFYIELD of an ODDFPRICE result gives the yield back (0.1492)
211  c) redemption 0 (error)
212
213Implemented 2026-11-18: bracketed Illinois iteration on GetOddfprice().
214  In the 222 cases where the two yields differed, feeding both to Excel's
215  ODDFPRICE gave back the price within 1e-10 for AOO's yield and within
216  1.8e-7 for Excel's.
217
218Purpose: Confirm the add-in matches Excel.
219
2209\. RRI
221------
222
223Rate of return per period: RRI(N; Pv; Fv) = (Fv/Pv)^(1/N) - 1.
224ODF 1.2/1.4 §6.12.44, constraint N > 0.
225
226Test cases:
227  a) =RRI(10;100;200) — 0.0717734625362931 (row RRI.basic; Excel the same)
228  b) =ZGZ(10;100;200) — old name still read (row RRI.zgz)
229  c) =RRI(0;100;200) — N = 0 (ODF: error; not run)
230  d) =RRI(-5;100;200) — N < 0 (ODF: error; not run)
231  e) =RRI(10;-100;200) — negative Pv (not run)
232  f) =RRI(10;100) — wrong arg count (error)
233  g) xls: `_xlfnodf.RRI` (AOO reads it back, Excel #NAME?); xlsx
234     `_xlfn.RRI` is read
235
236Expected: ScZGZ() (interpr2.cxx) computes pow(Fv/Pv, 1/N) - 1
237  with NO validation. c) to e) are not checked by the code.
238
239Purpose: Confirm validation gaps in RRI implementation.
240
24110\. ERROR.TYPE
242--------------
243
244ODF 1.4: Returns error number (1=#NULL!, 2=#DIV/0!, ..., 7=#N/A).
245
246Test cases (rows `ERROR.TYPE.*` and `ERRORTYPE.*`):
247  a) =ERROR.TYPE(1/0) — 2
248  b) =ERROR.TYPE(NA()) — 7
249  c) =ERROR.TYPE(AM1), AM1 = NA() — 7; =ERROR.TYPE(AM2), AM2 = 1/0 — 2
250  d) =ERROR.TYPE(SQRT(-1)) — 3 (AOO gives Err:502, which the xls export
251     writes as #VALUE!; Excel gives 6)
252  e) =ERROR.TYPE(5), =ERROR.TYPE("t") — non-error → #N/A
253  f) =ERROR.TYPE() — wrong arg count (error)
254  g) =ERRORTYPE(1/0) — 532 and =ERRORTYPE(NA()) — 32767 (legacy, unchanged)
255
256  The old cases with error literals (=ERROR.TYPE(#REF!) etc.) do not parse
257  in AOO's English API grammar (Err:511/520), so they were dropped.
258
259Fixed 2026-11-18: ERROR.TYPE is its own opcode (ocErrorTypeODF, 419);
260  ScErrorType( true ) maps with lcl_ErrorTypeODF() like
261  XclTools::GetXclErrorCode (unlisted errors give 7). H.4 had changed
262  ERRORTYPE instead; that is undone.
263
264Purpose: Validate error-code mapping to ODF return values.
265
26611\. NUMBERVALUE
267---------------
268
269ODF 1.4: Converts text to number using explicit separators.
270
271Test cases (rows `NUMBERVALUE.*`):
272  a) =NUMBERVALUE("1.234";".";",") — 1.234
273  b) =NUMBERVALUE("1.234,56";",";".") — 1234.56
274  c) =NUMBERVALUE("1 234.56";".";" ") — space grouping sep: 1234.56
275  d) =NUMBERVALUE("1.234,5";".";",") — group sep after the decimal sep (error)
276  e) =NUMBERVALUE("  1 2 3  ";".";",") — whitespace removed: 123
277  f) =NUMBERVALUE("12%";".";",") — 0.12; "1.5%%" — 0.00015
278  g) =NUMBERVALUE(".5";".";",") — 0.5; "-1.5e3" — -1500; "1e" (error)
279  h) =NUMBERVALUE("1.2.3";".";",") — second decimal sep (error)
280  i) =NUMBERVALUE("";".";",") — error (Excel 0)
281  j) =NUMBERVALUE("1,5";",,";".") — DecimalSeparator longer than 1 (error;
282     Excel uses the first character)
283  k) =NUMBERVALUE("1,5";",";",.") — DecimalSeparator in GroupSeparator (error)
284  l) =NUMBERVALUE("1234.5";".";"") — no group separator (1234.5; Excel error)
285  m) =NUMBERVALUE("1,5";",") — locale group sep dropped when it equals the
286     decimal sep (1.5)
287
288Expected: ScNumberValue() (interpr1.cxx) implements the ODF steps (see
289  function-implementation-roadmap.md, Group 6). Before H.1 there was no
290  evaluator; the H.1 version removed group separators everywhere and
291  replaced every decimal separator.
292
293Purpose: Confirm the ODF transformation steps.
294
29512\. GETPIVOTDATA
296----------------
297
298Extracts data from a DataPilot (pivot) table.
299
300Test cases:
301  a) =GETPIVOTDATA("Sales";$A$1:C$10;"Region";"West") — field/item pair
302  b) =GETPIVOTDATA("Sales";$A$1) — old syntax with constraint string
303  c) =GETPIVOTDATA("Sales";$A$1:C$10;"Region";"West";"Year";2024) — multiple filters
304  d) =GETPIVOTDATA("Nonexistent";$A$1:C$10) — missing field (error)
305  e) =GETPIVOTDATA("Sales";$A$1:C$10;"Region";"Nonexistent") — missing item (error)
306  f) =GETPIVOTDATA() — wrong arg count (error)
307  g) =GETPIVOTDATA("Sales";A1:C10;"Region";"West") — odd arg count (error)
308  h) XLS import (BIFF8) of GETPIVOTDATA — recognized (function ID 358)
309  i) XLSX import of GETPIVOTDATA — formulabase.cxx has an import-only row
310     (not run)
311  j) XLSX export — there is no xlsx export
312  k) Refresh pivot table then recalc — DataPilot dependency test
313
314Expected: ScGetPivotData() (interpr2.cxx) uses
315  pDok->GetDPAtBlock() to locate DataPilot object.
316
317Purpose: Confirm DataPilot dependency.
318
31913\. FDIST
320---------
321
322ODF FDIST (§6.18.22) is the left tail with an optional Cumulative; AOO's
323FDIST is ODF LEGACY.FDIST (§6.18.23, right tail). Since 2026-11-18 the ODF
324function is F.DIST in AOO (ocFDistLeft, 417); FDIST is unchanged.
325
326Test cases (rows `F.DIST.*`, `FDIST.*`):
327  a) =F.DIST(2.5;5;10) — 0.8979977233557302 (Excel F.DIST the same)
328  b) =F.DIST(2.5;5;10;0) — density 0.0935947544496855
329  c) =F.DIST(-1;5;10) — 0 (ODF; Excel #NUM!)
330  d) =F.DIST(0;1;5;0) — pole (error); =F.DIST(0;2;5;0) — 1
331  e) =F.DIST(2.5;0;10) — r1 < 1 (error)
332  f) =F.DIST(2.5;5;10)+FDIST(2.5;5;10) — 1
333  g) =FDIST(2.5;5;10) — 0.10200227664427 (legacy right tail)
334  h) =FDIST(2.5;5;10;1) — legacy takes three arguments (error)
335
336Expected: ScFDistLeft() and ScFDist() (interpr3.cxx). xls: F.DIST as
337  `_xlfn.F.DIST` (Excel calculates 4 arguments), FDIST as BIFF 281, and
338  `_xlfnodf.FDIST` from an older xls reads as FDIST.
339
340Purpose: Confirm left and right tail.
341
34214\. FINV
343--------
344
345ODF FINV (§6.18.24) is the inverse of the left tail; AOO's FINV is ODF
346LEGACY.FINV (§6.18.25). Since 2026-11-18 the ODF function is F.INV in AOO
347(ocFInvLeft, 418); FINV is unchanged.
348
349Test cases (rows `F.INV.*`, `FINV.*`):
350  a) =F.INV(0.5;5;10) — 0.931933160851048
351  b) =F.INV(0;5;10) — 0; =F.INV(1;5;10) — error
352  c) =F.INV(F.DIST(2.5;5;10);5;10) — 2.5
353  d) =FINV(0.1;5;10) — 2.52164068620962 (legacy)
354
355Expected: ScFInvLeft() uses ScFDistLeftFunction + lcl_IterateInverse;
356  ScFInv() unchanged.
357
358Purpose: Confirm the inverses.
359
36015\. HARMEAN
361-----------
362
363Harmonic mean: H = n / Σ(1/xᵢ), counting only positive values.
364
365Test cases:
366  a) =HARMEAN(1;2;4) — 3 / (1/1 + 1/2 + 1/4) = 3/1.75 ≈ 1.714
367  b) =HARMEAN(1;0;2) — zero value → errIllegalArgument
368  c) =HARMEAN(1;-2;4) — negative value → errIllegalArgument
369  d) =HARMEAN(5) — single value → 5
370  e) =HARMEAN() — wrong arg count (error)
371  f) =HARMEAN(A1:A5) where A1:A5 contains text — text cells skipped
372  g) =HARMEAN(A1:A5) where A1:A5 contains error — error propagates
373
374Expected: ScHarMean() (interpr3.cxx) counts only x > 0 values,
375  sets errIllegalArgument for x <= 0, skips empty/text cells.
376
377Purpose: Confirm implementation is correct. English help exists
378  (04060182.xhp; HARMITTEL is only the help ID name). Not run.
379
380Cross-Function Test: Byte-Oriented Text Family
381----------------------------------------------
382
383FINDB, SEARCHB, REPLACEB (412–414) join LENB, RIGHTB, LEFTB, MIDB
384(400–403). All seven are written to xls as Excel's own functions (BIFF
385205–211).
386
387Purpose: Confirm the whole byte-oriented text family works.
388
389Test Execution Note
390-------------------
391
392Originally designed but not executed. Since 2026-11-18 the rows named
393above are run by test/odf-validate/calcval.py on macOS and Linux builds;
394cases marked "not run" are still hypotheses. DDE, GETPIVOTDATA and HARMEAN
395were not run.
396