Bug 47349 - EDITING: Can't point and select cell from sheet (to set formula reference) with split column/row enabled (comment 20 / 31)
Summary: EDITING: Can't point and select cell from sheet (to set formula reference) wi...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high normal
Assignee: Justin L
URL:
Whiteboard: BSA target:7.1.0 target:24.2.0
Keywords:
: 51731 78476 105652 106133 112514 113800 (view as bug list)
Depends on:
Blocks: Cell-Formula Cell-Freeze
  Show dependency treegraph
 
Reported: 2012-03-15 05:11 UTC by mail
Modified: 2023-06-14 03:37 UTC (History)
16 users (show)

See Also:
Crash report or crash signature:


Attachments
Test case (9.26 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-03-17 11:45 UTC, eisa01
Details
47349_debug.diff: debugging lines highlighting key parts in the problem. (18.81 KB, patch)
2020-11-07 08:32 UTC, Justin L
Details
freezePanes-splitWIndow.ods: same problem still remains for split window. (9.14 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-11-07 08:37 UTC, Justin L
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mail 2012-03-15 05:11:02 UTC
Problem description: 

On a multi-sheet spreadsheet, when Window:Freeze is set on a sheet, Calc does not register the address of a cell in another sheet when inputting a formula.

Steps to reproduce:
1. Set Window-Freeze
2. Start entering a formula in a cell.  Press "=" and click the tab of another sheet, then click a cell in that sheet.  The input bar does not show the clicked address.
3. Press Return.  The formula cell contains only "="

To avoid this, unfreeze the window and enter the formula.

Platform (if different from the browser): 
              
Browser: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_5_8) AppleWebKit/534.50.2 (KHTML, like Gecko) Version/5.0.6 Safari/533.22.3

This is in LibO 3.5.1 RC2
Comment 1 sasha.libreoffice 2012-06-08 00:30:24 UTC
Thanks for bugreport
reproduced in 3.3.4 and 3.5.4 on Fedora 64 bit (Windows not tested)
Changing version to 3.3.4 as most early reproduced
Comment 2 Rainer Bielefeld Retired 2012-07-05 04:25:43 UTC
*** Bug 51731 has been marked as a duplicate of this bug. ***
Comment 3 Rainer Bielefeld Retired 2012-07-05 04:37:15 UTC
[Reproducible] with parallel installation of Master "LOdev " 3.7.0.0.alpha0+   - WIN7 Home Premium (64bit) ENGLISH UI [Build ID: 3985521]" (tinderbox: W2008R2@16-minimal_build, pull time 2012-06-24):

When I reference to cell in a different sheet from a cell in a "Frozen" sheet (outside the "Headings area" I will get a string "=" instead of reference to contents in different sheet.
Problem only appears in the first 2 columns right from "freeze line"

Same when 'Split Window'

No problem when I open a second Window for the document (for second referenced sheet) and switch to second window after "=" for reference.

Already reproducible with OOo 3.2, so inherited from OOo

@Spreadsheet Team:
Please set Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug
Comment 4 Sandro Santilli 2012-07-05 05:52:37 UTC
If this is the same as bug #51731 you should only get this problem with cells which are 2 columns from the one from which Window-Freeze was started.
Can you confirm this ? (ie: 3rd column off the freeze it is fine)

Also bug #51731 lists another workaround: clicking in the formula input widget before selecting the target cell (to reset proper focus)
Comment 5 mail 2012-07-05 10:02:07 UTC
That is correct.  Only the first 2 columns are affected.  Seems to be immaterial which row.

This is on LO 3.5.4.2, OS X 10.7.4

On 5 Jul 2012, at 06:52, bugzilla-daemon@freedesktop.org wrote:

> https://bugs.freedesktop.org/show_bug.cgi?id=47349
> 
> --- Comment #4 from strk@keybit.net 2012-07-05 05:52:37 UTC ---
> If this is the same as bug #51731 you should only get this problem with cells
> which are 2 columns from the one from which Window-Freeze was started.
> Can you confirm this ? (ie: 3rd column off the freeze it is fine)
> 
> Also bug #51731 lists another workaround: clicking in the formula input widget
> before selecting the target cell (to reset proper focus)
> 
> -- 
> Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email
> ------- You are receiving this mail because: -------
> You reported the bug.
Comment 6 QA Administrators 2015-01-05 17:51:49 UTC Comment hidden (obsolete)
Comment 7 Buovjaga 2015-01-23 16:42:51 UTC
No problem anymore.

Win 7 Pro 64-bit Version: 4.5.0.0.alpha0+
Build ID: 07e84cae983c08afdba03018413a19d01abb3006
TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-01-19_06:15:38
Comment 8 eisa01 2018-03-17 11:41:49 UTC
*** Bug 105652 has been marked as a duplicate of this bug. ***
Comment 9 eisa01 2018-03-17 11:45:26 UTC
Created attachment 140671 [details]
Test case
Comment 10 eisa01 2018-03-17 11:48:00 UTC
Reopening this as I don't think it ever was fixed (see comment on bug 105652)

Attached is a test case from bug 105652, but with clearer repro instructions.

Use the keyboard to start the formula entry, do not use the formula bar as that does not expose the bug
Comment 11 Holger Klene 2018-04-03 22:27:48 UTC
*** Bug 106133 has been marked as a duplicate of this bug. ***
Comment 12 Aurimas Fišeras 2019-01-30 09:07:17 UTC
*** Bug 78476 has been marked as a duplicate of this bug. ***
Comment 13 [REDACTED] 2020-01-05 23:25:08 UTC
Still an issue - see also question on https://ask.libreoffice.org/en/question/223897/calc-transpose-fails-if-freeze-rows-columns-set/
Comment 14 Kevin Suo 2020-09-17 15:00:32 UTC
*** Bug 113800 has been marked as a duplicate of this bug. ***
Comment 15 Kevin Suo 2020-09-17 15:10:21 UTC
*** Bug 112514 has been marked as a duplicate of this bug. ***
Comment 16 Julien Nabet 2020-09-17 18:46:53 UTC
On pc Debian x86-64 with master sources updated today, I can reproduce this if I freeze a column, not if I freeze a row.
Comment 17 Kevin Suo 2020-11-02 11:50:25 UTC
This is the most annoying bug I have ever seen, as an extensive libreoffice Calc user in my daily life. What else debuging steps should we do (with a dbgutil build with symbols enabled) to help this bug fixed quickly? Could some devs give hints?
Comment 18 Justin L 2020-11-06 13:47:06 UTC
Upgrading to high importance - lots of CCs and more than 5 duplicates and has been reported for both Windows and Linux.

A partial work-around is to click the mouse after the = (still in the cell). Yes, the cursor is already blinking there, but clicking there anyway refocuses it, and then it will pick up the other sheet reference.  HOWEVER, when you return to the original sheet and click in a different cell, it does NOT replace the other sheet reference, but ADDs it in front.

In some of my testing, it worked on sheets that were farther way, but not on directly adjacent sheets on either side. However, that wasn't always true.

A key area to start debugging at looks to be sc/source/ui/app/inputhdl.cxx
void ScInputHandler::SetReference
    ESelection aSel = pActiveView->GetSelection();
    if ( aSel.nStartPara == 0 && aSel.nStartPos == 0 )
        return;
since nStartPos is reported as zero in the problematic columns.
Comment 19 Justin L 2020-11-07 08:32:36 UTC
Created attachment 167073 [details]
47349_debug.diff: debugging lines highlighting key parts in the problem.

A regression-inviting patch is proposed at http://gerrit.libreoffice.org/c/core/+/105429.
Comment 20 Justin L 2020-11-07 08:37:38 UTC
Created attachment 167074 [details]
freezePanes-splitWIndow.ods: same problem still remains for split window.

While my patch does seem to fix a logic error in the previous code (at least to my uninformed mind), there is still something more fundamental that would be a better fix, as shown by this split-instead-of-freeze example.
Comment 21 Kevin Suo 2020-11-07 16:02:43 UTC
(In reply to Justin L from comment #19)
Thanks very much for the patch. I will build and test hard these days to see whether this patch does cause regressions.

Should this be set ASSIGNED accordingly?
Comment 22 Kevin Suo 2020-11-08 08:05:19 UTC
I have tested the patch and it works - so far I have not found any regression caused by this patch. Will continue test these days.

However, as Justin has said, this is a "regression-invite" fix, and may cause some unknown regression errors. As a result, I have prepared a test package in the following link:
https://go.suokunlong.cn:88/dl/libreoffice/daily/2020-11-08-test_tdf_47349_linux64.tar.bz2

This package contains the binary with and without that patch, and was compiled on Fedora 30 linux 64bit. Those who have a Linux machine are welcome to test. If you find a bug which exists in the one with the patch, but does not exist in the one without the patch, then that is certainly a regression caused by this commit. The best way to test is to use this build in your daily work (as what I have been doing), but be warned that it may cause data loss!

Those who encounter slowness to download may give me an email, so that I can try to put the binary in a server outside China.
Comment 23 Xisco Faulí 2020-11-12 15:07:24 UTC
Hi Justin,
LibreOffice 7.1 branch will be created on week 47 ( https://wiki.documentfoundation.org/ReleasePlan/7.1 ).
I would propose to submit you patch to master after the branch off so we are plenty of time to test it
What do you think ?
Comment 24 Justin L 2020-11-12 15:28:22 UTC
(In reply to Xisco Faulí from comment #23)
> I would propose to submit your patch to master after the 7.1 branch off
Well, I certainly agree it should NOT be done before that. However, I don't want it pushed even in 7.2 without a calc-expert review.
Comment 25 Kevin Suo 2020-11-12 15:52:41 UTC
I do not know the code, but I have applied the patch even on 6.4 branch and have been used this for 5 days without noticing any regression, including on mission critical daily work. However, my test was on Fedora 32, not sure about other platforms.
Comment 26 Commit Notification 2020-11-17 18:30:21 UTC
Justin Luth committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/fed6c4c70da6b35d72b670c8f4d8e866cdac21e4

tdf#47349 sc ui: bPosVisible only for fully visible

It will be available in 7.1.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 27 Kevin Suo 2020-11-17 21:20:52 UTC
Justin: Could you backport this to 7.0 branch as well? It shoild be just a cherry-pick. I am sure it is safe as I already tested it, but We certainly need Eike's review.
Comment 28 Justin L 2020-11-18 08:49:19 UTC
(In reply to Kevin Suo from comment #27)
> Justin: Could you backport this to 7.0 branch as well?
Definitely not. There would not be enough time to fix in 7 if it does end up causing problems for the many millions situations that you would not have thought of testing. In fact, I was hoping it wouldn't even make it into 7.1 - but oh well. I think my commit message made it pretty clear that I take no responsibility or credit for anything related to this patch.
Comment 29 Kevin Suo 2020-11-18 09:08:33 UTC
(In reply to Justin L from comment #28)
Understood. Thank you very much anyway for fixing this!
Comment 30 Eike Rathke 2020-11-19 15:24:47 UTC
(In reply to Justin L from comment #28)
> I was hoping it wouldn't even make it into 7.1
You are underestimating your capabilities :p
But I agree we shouldn't backport it to 7-0
Comment 31 Justin L 2020-11-30 08:48:30 UTC
The same problem still exists if splitting is used instead of freezing, as indicated in Comment 20. Re-opening this bug instead of adding a new one because it contains good information and debugging, and because the fundamental issue likely would also have solved the freeze problem too.
Comment 32 Justin L 2021-12-14 16:40:30 UTC
repro 7.4+
Comment 33 Justin L 2023-03-09 19:08:51 UTC
I spent some time again in comment 20's attachment 167074 [details].

I noted that the focus seems to be on the bottom left quadrant (BL). Anytime the formula building starts from here, it work. Anytime the formula building starts from a different quadrant that is fully VISIBLE in BL, it doesn't work. But, if BL doesn't have the target cell as fully visible, then the formula building works on other sheets. (BL is used for an un-split view.)

For example, when this document opens, my BL sees J7..AC17 (and part of 18).
TL also can see AA2, so building a formula in there works. So does TL AC18, but not TL AC17.

Similarly, TR AD14 works, but not TR AC14.

(Adding another sheet with a split in it complicates things, BTW. The active cells in each have to be in the same quadrant before it works.)

I can "fix" the problem in ScTabView::ZoomChanged() with

if (!SC_MOD()->IsFormulaMode())
    UpdateEditView()

Or perhaps more to the point, in ScTabView::SetTabNo
 
-   RefreshZoom();
-   UpdateVarZoom();
    if ( bRefMode )
+    else
+    {
+        RefreshZoom();
+        UpdateVarZoom();
+    }
Comment 34 Justin L 2023-06-10 16:59:11 UTC
Fixed in 24.2 with commit 30662ae380e3d31cc8904fcb1ceeb2592504834d.
Comment 35 Tex2002ans 2023-06-14 03:37:06 UTC
Hey Justin L,

It seems like your latest commit accidentally referenced:

- tdf#347349

when you meant:

- tdf#47349

(Notice the extra '3' in beginning!)

Unsure if that commit's Bug # needs to be updated for future reference/knowledge/findability.