Bug 101562 - Formula referencing a whole column in another sheet does not update once deleting a line in that sheet
Summary: Formula referencing a whole column in another sheet does not update once dele...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.0.3 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 target:5.2.3 target:5.1....
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2016-08-16 14:12 UTC by Marcelo
Modified: 2016-10-18 06:29 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
formula does not update referencing whole column of another sheet (9.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-08-16 14:12 UTC, Marcelo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Marcelo 2016-08-16 14:12:48 UTC
Created attachment 126856 [details]
formula does not update referencing whole column of another sheet

Description:

Sheet1
   A       B
1  stock
2  search  result
3  s1      r1
4  s2      r2
5  s3      r3

Sheet2
   A       B
1   s2      =INDEX(Sheet1.$B:$B;MATCH(A1;Sheet1.$A:$A;0))

B1 returns r2.
Deleting line 1 of Sheet1, B2 returns r3.
This does not happen if searched/founded range is limited. 

F9 does not update only CTRL+SHIFT+F9.
Comment 1 Buovjaga 2016-09-17 20:12:41 UTC
Reproduced from scratch according to steps.

B2 still returns r2 after deletion in LibreOffice 3.6.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha0+
Build ID: cfde3fd966ae9ef35fe685759e7e19bf0b8466ab
CPU Threads: 8; OS Version: Linux 4.7; UI Render: default; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on September 16th 2016

Arch Linux 64-bit
Version 3.6.7.2 (Build ID: e183d5b)
Comment 2 Marcelo 2016-09-18 15:21:44 UTC Comment hidden (obsolete)
Comment 3 Marcelo 2016-09-18 15:26:10 UTC
Instead of just deleting line 1 of sheet 1, try this:
- open worksheet, delete line 1 twice or;
- open worksheet, insert new line at line 1, delete inserted line 1.

Then you see the reference error on Sheet 2.
Comment 4 raal 2016-09-20 14:35:57 UTC
Hello Eike,
this commit did the regression:
author    Eike Rathke <erack@redhat.com>    2015-12-09 19:58:22 (GMT)
committer    Eike Rathke <erack@redhat.com>    2015-12-09 20:14:20 (GMT)
commit    cfecdd6199710921f8fd921f615203c9e34c551e (patch)
tree    e986f1191f25170f3f8c62ebf5a6fd87797259c6
parent    154521777f8c551f10ffbe414891df9d2a529ae3 (diff)
sticky end col/row anchor for range references, tdf#92779

Please take a look. Thanks.

3f3106eb58363bf0fef38c7ac2c004e3cfe41c7e is the first bad commit
commit 3f3106eb58363bf0fef38c7ac2c004e3cfe41c7e
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Thu Dec 10 10:30:24 2015 -0800
    source sha:cfecdd6199710921f8fd921f615203c9e34c551e
    source sha:cfecdd6199710921f8fd921f615203c9e34c551e
:040000 040000 189323b015cca6a81537042e5c63faf041d1554d 1dddd8f41262b7e802daea09a7b92e910ce656a2 M      instdir

 git bisect log
# bad: [6380ca07b05f68dedcaa379302cfe1fa478571c4] source sha:60b74fe1775e647545d2da1fcc58a4c63ec18aa5
# good: [1f670510f08cb800cbae2a1dd6ea70d3542e4721] source sha:49c2b9808df8a6b197dec666dfc0cda6321a4306
git bisect start 'origin/master' 'oldest'
# bad: [38f37b8ec1a2d199bb957cfd2581df7d1b273b74] source sha:c0da1080b61a1d51654fc34fdaeba373226065ff
git bisect bad 38f37b8ec1a2d199bb957cfd2581df7d1b273b74
# bad: [6998931a34ad75eb555f882fbed223e585548721] source sha:1fbd073828ef52f5206aed4643226bae9fb85f4f
git bisect bad 6998931a34ad75eb555f882fbed223e585548721
# bad: [0ea9534d0f4c90833e004706de7c11abe5bc522d] source sha:97361478e64f11330f7f7aea819d60c7562fb0f6
git bisect bad 0ea9534d0f4c90833e004706de7c11abe5bc522d
# good: [f1485fe9fdb687868c722bcb082adecc451f7a91] source sha:ebe80ac6713b67b1801ede3d1e9038cb2c93cb11
git bisect good f1485fe9fdb687868c722bcb082adecc451f7a91
# bad: [df62dac2764f25a1365653cd038f0e44e6f15485] source sha:7201810cb88811b05b938cc3db3232d9cf84c82e
git bisect bad df62dac2764f25a1365653cd038f0e44e6f15485
# good: [1c171d239864f3db766df4a4e6385874e576b577] source sha:0cde3ca230364492aa6b7f634b97178164268728
git bisect good 1c171d239864f3db766df4a4e6385874e576b577
# bad: [084972d0530b761c6608d022a084fd5df304258a] source sha:ed09d4f55d752dbc7d815fdc90e6cbe2656690b7
git bisect bad 084972d0530b761c6608d022a084fd5df304258a
# bad: [1343025eef474777529b5220261b8aa5019e022a] source sha:fbd329800340a496def839181858964b1e3709a5
git bisect bad 1343025eef474777529b5220261b8aa5019e022a
# bad: [0faede5adf6eefaba91a65903b55e4bb884c43c9] source sha:fb8a3fac5d448451794804a7470be45fa14da453
git bisect bad 0faede5adf6eefaba91a65903b55e4bb884c43c9
# bad: [3f3106eb58363bf0fef38c7ac2c004e3cfe41c7e] source sha:cfecdd6199710921f8fd921f615203c9e34c551e
git bisect bad 3f3106eb58363bf0fef38c7ac2c004e3cfe41c7e
# good: [1f2fc74d4a15085d5423fc526dad50fd1ef592c3] source sha:eb38493c4101d8777052267ee83a5d4f883dd9ac
git bisect good 1f2fc74d4a15085d5423fc526dad50fd1ef592c3
# good: [4351c37193ea362325a3937553a8765fd77fc9b9] source sha:689962feae2054f965a7378c3408b0ccfad2bbd5
git bisect good 4351c37193ea362325a3937553a8765fd77fc9b9
# good: [92c39bdcf6329d4c02c8b2ed2a04e3f94e703fc0] source sha:154521777f8c551f10ffbe414891df9d2a529ae3
git bisect good 92c39bdcf6329d4c02c8b2ed2a04e3f94e703fc0
# first bad commit: [3f3106eb58363bf0fef38c7ac2c004e3cfe41c7e] source sha:cfecdd6199710921f8fd921f615203c9e34c551e
Comment 5 Xisco Faulí 2016-09-26 15:54:16 UTC
Adding Cc: to Eike Rathke
Comment 6 Eike Rathke 2016-10-06 20:24:58 UTC
Investigating.
Comment 7 Eike Rathke 2016-10-06 20:53:30 UTC
I can't reproduce in neither 5.1.5, 5.2.2 or current master, or I misunderstood.
Loaded attached document.
On Sheet1 deleted row 1, the one containing "Stock" in A1.
Sheet1.E3:F4 display r2,3;r2,3
Sheet2.E4:F5 display r2,3;r2,3
Comment 8 Xisco Faulí 2016-10-06 21:15:15 UTC
Hi Eike,
I've just tried it in

Version: 5.3.0.0.alpha0+
Build ID: ae3ec79354f7b4967e736c6a4cd7c08fc52e2b7d
CPU Threads: 4; OS Version: Linux 4.2; UI Render: default; 
Locale: ca-ES (ca_ES.UTF-8); Calc: group

and I can reproduce it doing:

1. Delete row 1 in sheet 1
2. Check that value has changed to 3 in sheet 2
3. Type Ctrl + Z in sheet 1
4. Check that value has changed to 4 in sheet 2
5. Delete row 1 in sheet 1 again
6. Check value in sheet 2 --> It's still 4

I hope it helps
Regards
Comment 9 Eike Rathke 2016-10-06 21:25:15 UTC
Ah well, with undo in between yes, the original description doesn't mention that though..
Comment 10 Eike Rathke 2016-10-07 16:52:30 UTC
To reproduce without Undo, the test case document has to be recalculated once (Shift+Ctrl+F9) before deleting the row, which populates the lookup cache used for MATCH that is not updated in this scenario.
Comment 11 Commit Notification 2016-10-07 17:52:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=180fe3e991432a5ab1ef573686ff9b35c732756b

tdf#101562 inserting within an entire col/row reference needs to flag change

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2016-10-07 17:53:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=866eb4a7f93414932b8669d1a6afe0611655dfb4

Resolves: tdf#101562 ScRefUpdate::Update() needs to flag sticky even unchanged

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Eike Rathke 2016-10-07 18:05:35 UTC
Pending review
https://gerrit.libreoffice.org/29595 for 5-2
https://gerrit.libreoffice.org/29596 for 5-1
https://gerrit.libreoffice.org/29597 for 5-1-6
Comment 14 Commit Notification 2016-10-07 18:39:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=21d8c021b3b0a8cdb946daf9116be64a497fdb91

unit test for tdf#101562

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2016-10-10 21:30:41 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d6992fdd03adc713323ef1f3e3840d1003ded8b8&h=libreoffice-5-2

Resolves: tdf#101562 ScRefUpdate::Update() needs to flag sticky even unchanged

It will be available in 5.2.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2016-10-10 21:32:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=84a8aa8b28599db4ef452416e930690949217d2c&h=libreoffice-5-1

Resolves: tdf#101562 ScRefUpdate::Update() needs to flag sticky even unchanged

It will be available in 5.1.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2016-10-18 06:29:07 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=74e0e87c06867aeb60c0b9c64a4be8953920751f&h=libreoffice-5-1-6

Resolves: tdf#101562 ScRefUpdate::Update() needs to flag sticky even unchanged

It will be available in 5.1.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.