Bug 145407 - Some matrix operations do not seem to run correctly. Maybe an already known problem, or did I mistype something? Already resolved in later versions?
Summary: Some matrix operations do not seem to run correctly. Maybe an already known p...
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.1.4.2 release
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2021-10-30 15:59 UTC by ryek.darkener
Modified: 2021-11-01 14:31 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
.ods file with suspected error (11.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-30 16:01 UTC, ryek.darkener
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ryek.darkener 2021-10-30 15:59:53 UTC
Description:
The example is quite self-explaining. Generate the following data set, beginning at cell B4 (I'm sure it "works" everywhere):

	Date
1	06.10.21 00:00
2	06.10.21 00:00
3	06.10.21 00:00
4	06.10.21 00:00
5	06.10.21 00:00
6	06.10.21 00:00
7	06.10.21 00:00
8	06.10.21 00:00
9	06.10.21 00:00

Enter then the same date into another cell (e.g. F5), and the following formula (I run the German version of LibreOffice):

=SUMME( UND( MONAT(F5)=MONAT(C5:C13));TAG(F5)=TAG(C5:C13) )

The result is: 10
I had expected: 9

The formula should sum up the cells in which the month is "10" and the date is "6". As you see, there are only 9 entries in the data block, all other cells except the cell with the "check-date" and the formula are empty. Together with the "check-date" the sum is 10, but this is not the expected result.
I'm using this type of formula to do some simple searches in rows, columns or even matrixes. Runs fine in EXCEL.

Steps to Reproduce:
1. Fill the cells according description
2. Remember to make the formula a "matrix"
3.

Actual Results:
10

Expected Results:
9


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.1.4.2 / LibreOffice Community
Build ID: a529a4fab45b75fefc5b6226684193eb000654f6
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded
Comment 1 ryek.darkener 2021-10-30 16:01:02 UTC
Created attachment 176028 [details]
.ods file with suspected error
Comment 2 Roman Kuznetsov 2021-10-30 16:42:05 UTC
I confirm the 10 result already in LO 4.2.8 version. But in Excel 2007 I see result 1 in the cell with formula!

I'm not sure what should be right here. Eike, could you please look at it?
Comment 3 Julien Nabet 2021-10-31 09:47:12 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.

If you change cell A32 to "05/10/21", you got "1" in D32
But if you change cell A32 to "05/09/21", you got "0" in D32
and if you change cell A32 to "06/09/21", you got "9" in D32

I'd definitely say there's something wrong here.
Comment 4 m_a_riosv 2021-10-31 13:33:58 UTC
Seems the issue if with AND() in array
{=SUM( (MONTH(C32)=MONTH(C5:C13))*(DAY(C32)=DAY(C5:C13) ))}
avoiding the use of AND works fine.
Comment 5 Julien Nabet 2021-10-31 13:40:13 UTC
(In reply to m.a.riosv from comment #4)
> Seems the issue if with AND() in array
> {=SUM( (MONTH(C32)=MONTH(C5:C13))*(DAY(C32)=DAY(C5:C13) ))}
> avoiding the use of AND works fine.

Good find! I don't know how to find methods related to matrix. It would be great to have some refactoring instead of trying to search in files like:
./source/core/tool/interpr1.cxx
./source/core/tool/interpr2.cxx
./source/core/tool/interpr3.cxx
./source/core/tool/interpr4.cxx
./source/core/tool/interpr5.cxx
./source/core/tool/interpr6.cxx
./source/core/tool/interpr7.cxx
./source/core/tool/interpr8.cxx
or at least a small readme in top of each of this kind of file which have between 550 and 10000 lines!
Comment 6 Michael Warner 2021-10-31 13:56:28 UTC
(In reply to Julien Nabet from comment #5)
> Good find! I don't know how to find methods related to matrix. It would be
> great to have some refactoring instead of trying to search in files like:
> ./source/core/tool/interpr1.cxx
> ./source/core/tool/interpr2.cxx
> ./source/core/tool/interpr3.cxx
> ./source/core/tool/interpr4.cxx
> ./source/core/tool/interpr5.cxx
> ./source/core/tool/interpr6.cxx
> ./source/core/tool/interpr7.cxx
> ./source/core/tool/interpr8.cxx
> or at least a small readme in top of each of this kind of file which have
> between 550 and 10000 lines!

Agree * 1000!
Comment 7 m_a_riosv 2021-10-31 15:08:27 UTC
I can't find now, but I think time ago something about logical functions in matrix or using them inside sumproduct was reported, maybe @Erak remember something.
Comment 8 Eike Rathke 2021-11-01 14:30:16 UTC
First, the expression
=SUM( AND( MONTH(C32)=MONTH(C5:C13));DAY(C32)=DAY(C5:C13) )
probably was intended as
=SUM( AND( MONTH(C32)=MONTH(C5:C13);DAY(C32)=DAY(C5:C13)) )
instead (note that in the former AND() receives one argument and in the latter two arguments).

Second, there seems to be a misconception of AND(), it never returns an array/matrix (neither does OR()), but computes a logical AND of all arguments, including their matrix elements. That has always been the case and will not change. See also
https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#AND

Use SUM() as in comment 4 or
=SUMPRODUCT(MONTH(C32)=MONTH(C5:C13);DAY(C32)=DAY(C5:C13))
as regular formula.