[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 Eike Rathke changed: What|Removed |Added Status|RESOLVED|CLOSED -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 Eike Rathke changed: What|Removed |Added Regression By|Luboš Luňák | Resolution|--- |NOTABUG Keywords|regression | Status|NEW |RESOLVED --- Comment #16 from Eike Rathke --- Sorry, but this is plain wrong. (In reply to ady from comment #15) > But, that > does not mean that the results will be different according to whatever goes > in the list after the first matched value. Yes it does. Because the search does a binary search picking elements to compare with the query and decide into which direction the picking should continue. If length and/or content of the search vector vary the elements picked differ and thus does the result which is _arbitrary_ depending on unsorted content. The algorithm does not iterate from the beginning to stop at a found element. The search vector MUST be strictly sorted for the binary search to yield a meaningful result. > IOW, the result shall still be repeatable. No, wrong expectation. See https://en.wikipedia.org/wiki/Binary_search_algorithm to get an idea how that works in general. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297
--- Comment #15 from ady ---
(In reply to Winfried Donkers from comment #14)
> In the sample files that have been attached, none of the ranges looks sorted
Indeed, that was specifically mentioned.
> to me. Then all results for MATCH with 3rd argument 1, -1 or empty have no
> real meaning.
That is not completely accurate. It is true that the lookup array is expected
to be sorted, and when it isn't, the results are not "predictable". That means
that the result cannot be expressed beforehand in some generic simple words,
but rather that it depends on a case-by-case basis. But, that does not mean
that the results will be different according to whatever goes in the list after
the first matched value. IOW, the result shall still be repeatable. That is why
I added an attachment, to demonstrate exactly this non-repeatable behavior. Let
me emphasize the point...
If I am using "1" (ascending) search, and the lookup array starts with
{0;1;whatever}, then when the lookup value is "1" (without quotation marks),
the result shall be "2" (without quotation marks), always. The result cannot
vary depending on the _next_ values. MATCH() should find the first value that
accomplishes the conditions. Attachment 185129 demonstrates that this is not
currently the case (the result varies, depending on values that are _after_ the
first position that accomplishes the conditions), and if you check with LO
7.3.x (and older), you should obtain "2" as result.
So, yes, there is no generic way to know the result beforehand as a generic
rule, but the results shall still follow the conditions/rules; they are not
random, or vary according to the number of empty cells _after_ the first "OK"
value.
Please review attachment 185129 again, in order to spot these nonsensical
varying results.
--
You are receiving this mail because:
You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #14 from Winfried Donkers --- Using MATCH with 3rd argument 1, -1 or leaving 3rd argument empty (which then defaults to 1) requires the range that is searched to be sorted. If the range is not sorted , the result is not defined and can be different for different applications or even versions. Also, when the ordering is ascending, the last match is returned and when the ordering is descending, the first match is returned. See "https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1018430_715980110;. In the sample files that have been attached, none of the ranges looks sorted to me. Then all results for MATCH with 3rd argument 1, -1 or empty have no real meaning. AFAICS this is not a bug. FWIW, I am currently working on a change in the code that does the searching for MATCH, LOOKUP, HLOOKUP and VLOOKUP to handle searching for XLOOKUP as well. These changes could produce different results again. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 raal changed: What|Removed |Added Blocks||108827 Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=108827 [Bug 108827] [META] Calc functions bugs and enhancements -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297
--- Comment #13 from ady ---
Created attachment 185129
--> https://bugs.documentfoundation.org/attachment.cgi?id=185129=edit
bug tdf 153297 demo MATCH problem in LO74x
Attaching simple sample file (18KB) demonstrating the problem when the third
argument for MATCH() is "1", for LO version 7.4.x.
There are problems with both "sorted" possible 3rd argument (1 and -1). This
file shows only the ascending case(s), for illustration purposes.
* Different results for MATCH(), depending on how long the lookup array is.
* Different results for MATCH(), depending on how many empty cells are part of
the lookup array, but also depending on their position in the lookup array list
(not just depending on amount).
* The values that differ in each lookup array are all located after the first
lookup value.
* In each lookup array, all values until the first lookup value (1) are exactly
the same ({0;1}); yet, MATCH() results are different (with LO 7.4.x).
--
You are receiving this mail because:
You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 raal changed: What|Removed |Added Keywords|bibisectRequest |bibisected, bisected CC||l.lu...@collabora.com, ||r...@post.cz OS|Windows (All) |All Regression By||Luboš Luňák --- Comment #12 from raal --- This seems to have begun at the below commit. Adding Cc: to Luboš Luňák ; Could you possibly take a look at this one? Thanks ad819761e169e4d54c1ac4e720fb01aea39add49 is the first bad commit commit ad819761e169e4d54c1ac4e720fb01aea39add49 Author: Jenkins Build User Date: Tue May 10 16:34:06 2022 +0200 source sha:6a5464b800aa0b0ce35d602fd008b555d96a94af https://git.libreoffice.org/core/+/6a5464b800aa0b0ce35d602fd008b555d96a94af -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297
--- Comment #11 from ady ---
FWIW, apparently the problem with the MATCH(1;AA2:AA400;1) function in 7.4.x+
(ATM) seems to be in how it reacts when the list is not really completely
sorted (e.g. ascending in this case).
1. If the list of values is {0;1;0;0...} (i.e. not ascending in its entirety),
the result of the above MATCH seems to be wrong (not "2"). The search/lookup
goes down to the end of the list (they are all zeroes/empty after the first
"1").
2. If the list is entirely ascending {0;1;2;3...} the result is correct ("2").
3. When initially loading attachment 185057, the MATCH function in $DOWN.A1 is
not recalculated, so it (still) results in "1" (=2-1).
4. On (hard) recalculation, the cell $DOWN.A1 is recalculated to 398 (=399-1).
5. Manually replacing $DOWN.A1 with a "1" brings the rest of the depending
cells' values to their original (non-empty) values.
For some reason, MATCH() fails when the lookup array is not actually sorted
according to what the third argument of MATCH() would expect. In theory, in
this case MATCH() should still find the (first) "1" located in $LIST.AA3
anyway, but it doesn't, just because the list is not entirely sorted.
--
You are receiving this mail because:
You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #10 from ady --- (In reply to m.a.riosv from comment #9) > Don't need to change anything, the issue is with MATCH() in DOWN.A1 > returning a different value in 7.4 (399) than in 7.3 (1) Great. I just was trying to follow what Yurij posted (which I quoted above). Anyway. There is a recent bug 152774 that received commits also recently, related to MATCH(), but I don't think it affected version 7.4.x (yet?). In spite of that, perhaps the 2 devs that committed patches there might be able to look at this too, also related to MATCH(). -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #9 from m.a.riosv --- Don't need to change anything, the issue is with MATCH() in DOWN.A1 returning a different value in 7.4 (399) than in 7.3 (1) -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #8 from ady --- (In reply to m.a.riosv from comment #7) > (In reply to m.a.riosv from comment #5) > >... > > The issue in that file is in DOWN.A1 a formula with MATCH in it. I indeed saw that. Let's quote Yurij: "When changing any parameter in the "LIST"..." But there is no mention about what exactly was changed in order to replicate the reported problem. So, I repeat here the question: Which exact cells were modified in order to get that unexpected result? Which modification was made in those specific cells? For example, in attachment 185057 posted in bug 153298, in the worksheet named "DOWN", cell A1 contains the formula: $DOWN.A1: =MATCH(1;$LIST.$AA$2:$LIST.$AA$400;1)-1 or: $DOWN.A1: =MATCH(1,$LIST.$AA$2:$LIST.$AA$400,1)-1 So, in the range $LIST.$AA$2:$LIST.$AA$400 in the "LIST" worksheet, what was that Yurij changed in order to get the problem in the "DOWN" worksheet? Which specific cell in "LIST"? What was either deleted or modified in "LIST"? Similarly, in your attachment 185075 posted in comment 4, you used MATCH with whole columns, multiple times referring to the same column. That is, while MATCH is using the same column and with "1" as third argument. This means that you might understand what you yourself posted, but other users cannot replicate the original problem because there is that small detail I asked before. If either Yurij or m.a.riosv could reply with that little piece of info, I'll gladly try to replicate the problem (too). -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #7 from m.a.riosv --- (In reply to m.a.riosv from comment #5) >... > The issue in that file is in DOWN.A1 a formula with MATCH in it. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #6 from ady --- It has not been so easy for me to understand exactly where the problem is, so I am about to post some kind of summary, up to this point (or at least I'll try), FWIW. First, there is a duplicate of this bug report, already marked as such, bug 153298. There is one original attachment 185057 posted in bug 153298. That file weights around 4.35MB. Then m.a.riosv posted a different file, attachment 185075, which weights around 1.05MB. According to OP (Yurij), using LO 7.4.x, when some data in some worksheet (named "LIST", and up to worksheet "Ser") is modified in some way, some of the formulas in another worksheet named "DOWN" result in what looks like "empty" cells (or something similar to that). This problem is not present in version 7.3.7 of LO. What neither Yurij nor m.a.riosv were able to explain (or I could not understand them, sorry) in simple steps (up to this point) is, which exact cells you modify in order to get that unexpected result, and which modification you made. For example, something like: _ "go to worksheet "LIST", to cell A1 and change the content to "xwy" (without quotation marks)". Another possible example would be: "go to worksheet "LIST", to cell AA10 and delete its content. If someone could provide a clear step-by-step procedure, indicating file name, worksheet (tab) name, cell address, and which specific change was made in that cell, I don't know how to try to replicate whatever issue results in worksheet "DOWN" (or in whatever sheet and cell the problem is ultimately seen). Could someone please be so kind and provide such clear step-by-step procedure? -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 QA Administrators changed: What|Removed |Added Keywords||bibisectRequest -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 m.a.riosv changed: What|Removed |Added Keywords||regression Severity|normal |critical -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 --- Comment #5 from m.a.riosv --- Sample file from reporter in duplicate bug https://bugs.documentfoundation.org/show_bug.cgi?id=153297 The issue in that file is in DOWN.A1 a formula with MATCH in it. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 153297] A complex formula does not work in Calc 7.4.x; (MATCH function)
https://bugs.documentfoundation.org/show_bug.cgi?id=153297 m.a.riosv changed: What|Removed |Added Summary|A complex formula does not |A complex formula does not |work in Calc 7.4.x |work in Calc 7.4.x; (MATCH ||function) Status|NEEDINFO|NEW -- You are receiving this mail because: You are the assignee for the bug.
