Description: In Calc 6.3.0.4: Issuing a right arrow either manually or via macro code causes a large column jump on a mostly protected sheet. Right arrow navigation should halt at the last unprotected column ("N" in this case) as it does in version 6.2.5. Instead, a further right arrow causes a jump to column "BM". Further right arrows allow column jumps to the right even though all those cells are all protected. Steps to Reproduce: 1.Open the file and press right arrow to column "N" (7 times) 2.Pressing once more (8th time) will cause an unexpected cursor jump to column "BM". 3.In this partially protected sheet, columns beyond "N" should not be selectable. Actual Results: Same as Above! Expected Results: In previous LO Calc version (6.2.5), the cursor would not move further after pressing the right arrow key once the last unprotected column was reached (expected behavior). Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info:
Created attachment 153367 [details] Partially protected spreadsheet file with macros. This file can be used to demonstrate the issue described in the bug report. This file is publicly available on my website and therefore has no associated privacy concerns.
reproducible with: Version: 6.3.0.4 (x64) Build ID: 057fc023c990d676a43019934386b85b21a9ee99 CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc: but *not* reproducible with: Version: 6.2.6.2 (x64) Build ID: 684e730861356e74889dfe6dbddd3562aae2e6ad CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: de-DE (de_DE); UI-Language: en-US Calc:
seems to have started with: https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=7282014e362a1529a36c88eb308df8ed359c2cfa commit 7282014e362a1529a36c88eb308df8ed359c2cfa [log] author Noel Grandin <noel.grandin@collabora.co.uk> Fri Feb 01 15:15:16 2019 +0100 committer Mike Kaganski <mike.kaganski@collabora.com> Fri Apr 05 13:43:52 2019 +0200 tree 2776ad9601f494330076ac58c08554e719c6ab3a parent df30a4515b1303b0891baa53754fa9b3e47e0c02 [diff] tdf#50916 Makes numbers of columns dynamic. /cygdrive/d/sources/bibisect/bibisect-win32-6.3 $ git bisect bad d53ef167063b0e2c18e7e703af4407b40c664fb2 is the first bad commit commit d53ef167063b0e2c18e7e703af4407b40c664fb2 Author: Norbert Thiebaud <nthiebaud@gmail.com> Date: Fri Apr 5 04:58:29 2019 -0700 source sha:7282014e362a1529a36c88eb308df8ed359c2cfa source sha:7282014e362a1529a36c88eb308df8ed359c2cfa :040000 040000 c4c16af7ac031bce3a437b19126c311eb2be7761 a8f451eec155bd2b6c5aa3762b50b0dcb346fdf6 M instdir /cygdrive/d/sources/bibisect/bibisect-win32-6.3 $ git bisect log # bad: [18f926e8e18b3d855c2f79ef279febbeb846b8cd] source sha:13152ad88b24cadc836a829b4424a72a152ca9b1 # good: [ea94942caaf195b8d8b2d5c2abb523359ab390e7] source sha:a20a2d7e0d28658f2d9089da076961a599833a28 git bisect start 'master' 'oldest' # good: [3aea60569b9190400409ebb93f0a5d323b6fc5d4] source sha:47ce4b87d8a13fc340794ffd9a10d5bd6a15e644 git bisect good 3aea60569b9190400409ebb93f0a5d323b6fc5d4 # bad: [3b794d71dd796e467baef082c140bdc77c69c979] source sha:47d25dc5abe000ce751cb1e4dbd1f85f7198ca05 git bisect bad 3b794d71dd796e467baef082c140bdc77c69c979 # good: [8adbffa485cdd6d5e13106e5f55e70249f46a4f6] source sha:15e9e6d12aa2d49e114ec0cf8326f2264ccf2640 git bisect good 8adbffa485cdd6d5e13106e5f55e70249f46a4f6 # bad: [446d84046fe885e09f7cb71061f6c80ff83137e3] source sha:5bd1caf14c8e297db229e9060a584386247e62b1 git bisect bad 446d84046fe885e09f7cb71061f6c80ff83137e3 # good: [290c14249daec35d65309db03bbb2d8dd9577869] source sha:a67125aa5c5ef8f2a19dcbcad778cd66a304761b git bisect good 290c14249daec35d65309db03bbb2d8dd9577869 # bad: [f6df399b757e904217e23ef90ec5c2959a06120b] source sha:bdc5cfab8106d73af3452155cedf732972bd3a91 git bisect bad f6df399b757e904217e23ef90ec5c2959a06120b # good: [1810944f7cb6564d91faa6a0382f93c2e568cbed] source sha:ec2222e54dd6a496fd7931a966eee272172025d2 git bisect good 1810944f7cb6564d91faa6a0382f93c2e568cbed # good: [a75e186c5c38e92920febd251b46c62d5d97123c] source sha:ae1e23651a6d2e97d39bbe46bab83b7982d19611 git bisect good a75e186c5c38e92920febd251b46c62d5d97123c # good: [d9241b2e69509012daad3a175057b92f19b4dc68] source sha:5d0700bd3afef6d39b63fe813aaa0ac856ff5785 git bisect good d9241b2e69509012daad3a175057b92f19b4dc68 # good: [60ea1bb50f32aaf457e89c283df73e940f768fea] source sha:3e0092031b73bad107e3122d5d4be2f5bd487744 git bisect good 60ea1bb50f32aaf457e89c283df73e940f768fea # good: [8ad9247fb583df581282a091ee744bd1b247b89f] source sha:6aff7fe6d5a02f44ec43e63672ae56166f9b9cb6 git bisect good 8ad9247fb583df581282a091ee744bd1b247b89f # good: [ab220a076cad3a184f2f357192861dabc1f96e3c] source sha:df30a4515b1303b0891baa53754fa9b3e47e0c02 git bisect good ab220a076cad3a184f2f357192861dabc1f96e3c # bad: [d53ef167063b0e2c18e7e703af4407b40c664fb2] source sha:7282014e362a1529a36c88eb308df8ed359c2cfa git bisect bad d53ef167063b0e2c18e7e703af4407b40c664fb2 # first bad commit: [d53ef167063b0e2c18e7e703af4407b40c664fb2] source sha:7282014e362a1529a36c88eb308df8ed359c2cfa
*** Bug 127246 has been marked as a duplicate of this bug. ***
Feb 1, 2020: Testing on 6.3.4 and 6.4.0 shows the problem persists. Thanks to any and all who are working on a fix. Daniel Baran (timespreader.com)
Can anyone address the likelihood of this one being fixed? There does not seem to be any indication of activity. If anyone has ideas for mitigating it from my end (i.e. a work-around), I would be grateful for that also. Thanks, DB
Noel Grandin committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/7ae33e175007d04021f5e911e3f7f6cbd0966804 tdf#126904 calc right arrow large unexpected column jump in protected sheet It will be available in 7.0.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.
Created attachment 158322 [details] This file is an updated version of the original test case. This file is an updated version of the original ODS workbook that revealed the bug. The bug is evident when using this file in LO 6.3.5.2 (and seemingly anything after 6.2.8). It can be used for testing of patched, or other future releases if desired.
Verified in Version: 7.0.0.0.alpha0+ Build ID: c57d6d39c80844c9d4c6bfed85cc151e52a67b34 CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded @Noel, thanks for fixing this issue!!
Noel Grandin committed a patch related to this issue. It has been pushed to "libreoffice-6-4": https://git.libreoffice.org/core/commit/c3a1880bd26f80272829205b221ebd2b8d1cfafe tdf#126904 calc right arrow large unexpected column jump in protected sheet It will be available in 6.4.3. 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.
Noel Grandin committed a patch related to this issue. It has been pushed to "libreoffice-6-3": https://git.libreoffice.org/core/commit/c91d0dd01d884916c699bb70219c792aba704846 tdf#126904 calc right arrow large unexpected column jump in protected sheet It will be available in 6.3.6. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/976cabe84f3b6e5591ccf2b043d72cbca3e31ba0 tdf#126904: Add unittest It will be available in 7.0.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.
Noel Grandin committed a patch related to this issue. It has been pushed to "libreoffice-6-4-2": https://git.libreoffice.org/core/commit/a9f33ad967aeb7582e6522b81beebc8c15cc5016 tdf#126904 calc right arrow large unexpected column jump in protected sheet It will be available in 6.4.2. 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.
I have verified in: Version: 7.0.0.0.alpha0+ (x64) Build ID: c63148ba139bd6b9ae7a0f9e24e51f29e5370963 CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded Much appreciation for this fix! PS: Unfortunately, while doing the test I may have discovered a new / different bug. I have filed a separate report on that - Bug 131296.
Verified in: Version: 6.4.2.2 (x64) Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3 CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded All macros and other features working as expected!
The following updated test result applies to both of the following versions: Version: 6.4.2.2 Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3 CPU threads: 4; OS: Linux 5.3; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded Version: 6.4.2.2 (x64) Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3 CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded The original (column jump) problem seems to be resolved when the file is first opened. However, after executing the "End of Day" macro, the column jump problem returns. This does not happen in: Version: 6.2.8.2 (x64) Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee CPU threads: 8; OS: Windows 10.0; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded Steps to reproduce: 1. Open the workbook. 2. Press - Right Arrow to column "N". Further Right Arrows do nothing (expected). 3. Execute "End of Day" macro, then return to Sheet1. 4. Right Arrows beyond column "N" cause a large jump to column "BM" (unexpected)
Please see Comment 16 Also getting the same result in: Version: 7.0.0.0.alpha0+ (x64) Build ID: c63148ba139bd6b9ae7a0f9e24e51f29e5370963 CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: threaded
Please, create a follow-up report
Just to be clear; Are you asking me to create a "new" bug report on this, or something else? If something else, please describe what exactly a "follow-up report" is.
(In reply to Daniel Baran from comment #19) > Just to be clear; > Are you asking me to create a "new" bug report on this, or something else? > If something else, please describe what exactly a "follow-up report" is. Yes, please, create a new report
The follow-up report describing similar behavior to this bug is: Bug 131455.
This is actually not a mostly protected sheet. Editting the Default cell style (after unlocking the document) shows that the default style has protection turned off => only cells with explicit styles that turn protection on are locked, but most of the sheet is not protected. So originally this worked as intended. I will revert this change.
(In reply to Luboš Luňák from comment #22) > This is actually not a mostly protected sheet. Editting the Default cell > style (after unlocking the document) shows that the default style has > protection turned off => only cells with explicit styles that turn > protection on are locked, but most of the sheet is not protected. > > So originally this worked as intended. I will revert this change. Hi Lubos, I don't get it, checking with an older version Version: 5.2.0.3 Build ID: 7dbd85f5a18cfeaf6801c594fc43a5edadc2df0c CPU Threads: 8; OS Version: Linux 5.10; UI Render: default; Locale: en-US (es_ES.UTF-8) for instance, the behaviour is the same as in master ( with the fix in place )
Created attachment 178706 [details] test document with password removed This is sc/qa/unit/uicalc/data/tdf126904.ods, with password removed. Open it, cancel Tools->Protect Sheet, go to e.g. cell CA4, right-click->Format Cells->Cell protection => the cell is not protected => jumping to those cells is correct. That's also why there's the follow-up "bug". I don't have an old LO version to check, but I expect it'll be the same.
Like Xisco, I don't get it either. While it's true that with the sheet protection removed, the column jump does nor occur, the original workbook was not designed to operate in that fashion. There is partial protection by design. Some cells are protected, some are not. In that mode, the column jump will occur. I've just retested this by reinstalling 6.3.0.4 in a "parallel" install. It's hard to see how that could be an expected behavior. Also, changing this status at this late date, seems odd considering the unexpected behavior was resolved in subsequent releases (quite some time ago). Current behavior (in 7.2.5 and 7.3.1) is exactly as expected.
As already said, the cursor is jumping to cells in the BM column because they are not protected. If you want it to work as you expect, fix your document.
Here's the thing; Attachment 178706 [details] is NOT my document. That is an altered version posted by someone else and not my doing. My last attachment was 158322. When I test with that, things get interesting. In LO 6.3.0.4: If I unlock Sheet1, cell protection is switched on up to column BL. Cells beyond are UNprotected. In LO 7.2.5.2: If I do the exact same test on the exact same file; Sheet1, cell protection is switched on up to AND BEYOND column BL. Cells beyond ARE protected. So there is clearly a difference in the way these two LO versions are interpreting protection settings on the very same file. It's imortant to note also, that these same workbooks did not exhibit the unexpected behavior in much earlier versions of LO either. So the behavior came, and then went, not by altering the workbooks, but by changes made to LibreOffice. This tells me that a bug was fixed somewhere between 6.3 and 7.2. That fix remains valid in 7.3 testing I have done as well, and I am grateful for that. Lastly; I could probably workup a stripped down and unlocked file that reproduces what I've just decribed, but it hardly seems worth it since this problem seems to have been resolved for some time now.
Since I had taken the time to setup the parallel install utility, I did one further test. This time in LO 6.1.2.1. As I anticipated, the 158322 file works normally in it. And as with LO 7.2.5.2, Sheet1 cell protection is switched on up to AND BEYOND column BL. Cells beyond BL ARE protected. So this is further indication that some LO versions between 6.1 and 7.2 (e.g. 6.3) were not seeing the cell protections that had been applied to the original spreadsheet file.
(In reply to Daniel Baran from comment #27) > Attachment 178706 [details] is NOT my document. > That is an altered version posted by someone else and not my doing. > My last attachment was 158322. Attachment #158322 [details] is inded a bit different, it protects first 1024 columns. So with software that supports only up to 1024 columns it looks like everything is protected, but the Default cell style still has protection turned off. So e.g. with software that supports more than 1024 columns any cells beyond that limit are not protected. LibreOffice 7.4 will most likely support 16384 columns, so cursor will jump to cells in the AMK column there, as those are not protected. Perhaps part of the 16384 column support will be extending attributes of column 1024 to all new columns, which would cover the problem with this document, but fundamentally the document has unprotected cells, and the proper way to deal with it is to fix the document. It's possible that some older LO versions had a bug in handling of protected cells, but change from commit #10 is incorrect and will be reverted.
Luboš Luňák committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/1e7be382b1f400801d350067e4dfd40d4cfd2db3 revert/fix the incorrect fix for tdf#126904 It will be available in 7.4.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.
I'm seeing now that a recommended method of [formatting all the cells in a sheet] may not be a "best practice". While the link below is quite an old post, it is one of the first things found when searching the subject and seemingly from an authoritative source. https://ask.libreoffice.org/t/how-do-you-blanket-format-all-cells-in-calc-spreadsheet-in-one-go/3355 So it seems like that advice should be depricated if possible and replaced with the "Default Style" method. I have located the Default Style / Cell Protection setting, but I have to say, it's pretty obscure: F11, Right-Click "Default", Modify etc. It would be good for that to be less buried: e.g. "Cell Style" could be right in the tree under "Default". I realize a User Interface gripe is not the main topic here, but there is a connection. Hopefully using this method going forward will minimize the recurrence of this problem.
After completing this recommendation in a number of workbooks, I had a realization. I'm setting protections for cells (and in columns) which don't yet exist. It's not too hard to see how end users might not anticipate that requirement.
You're setting something that should be set by default. You can create a new blank document and check that the Default style there has protection set. I don't know how come it's changed for your documents, whether you changed it at some point, or it was a bug in old LO versions or something else, but it's not normally supposed to be that way.
Thanks Luboš, While I don't think I changed default protections previously, these workbooks have been amended over a number of years and with multiple LibreOffice versions. So it's quite possible there are some legacy carry-over issues as you suggest. I have audited all of my documents regarding this, and will certainly be checking it as standard operating procedure. Your insights are much appreciated. DB
I have been convinced that it's a best practice to set protections via default cell styles. However, I found that doing that in the test case 158322 does not correct the column jumping behavior in LO 6.3.0.4 (where it was originally observed). So it seems that there was more to this than the [default cell styles] settings. I'm not sure if this test result is meaningful at this point, so I am posting it just in case. Version: 6.3.0.4 (x64) Build ID: 057fc023c990d676a43019934386b85b21a9ee99 CPU threads: 8; OS: Windows 10.0; UI render: GL; VCL: win; Locale: en-US (en_US); UI-Language: en-US Calc: CL The unexpected behavior has not been seen in any of the recent LO versions.
In an effort to create the simplest possible test, I did the following in 6.3.0.4: Open a new blank file. Default cell protection is ON (in styles) as expected. Select a small range (e.g. A1:C10) and unprotect the cells. Then protect the sheet. What I found to be critical, is what options are chosen while protecting the sheet. If you tick BOTH "Select protected cells" and "Select unprotected cells", column navigation behavior is as expected. If you tick ONLY "Select unprotected cells", while protecting the sheet, the column jump behavior is present. So the default cell style protection did not prevent the behavior. While this has been fixed in later LO versions, I thought it might be of interest.
Based on Comment 33, I created the test in Comment 36. The result is that the originally described unexpected behavior is still present. It seems appropriate that someone else should try to reproduce this.
The test in comment 36 seems to refute the label of "INVALID". It is appropriate that someone should reproduce this simple test and report the result.
6.3.0.4 is old and no longer supported, so its status is irrelevant.
The claim in Comment 39 is astonishing. The status of this bug has now been altered (TWO YEARS) after it seems to have been fixed. I have reasonably challenged the explanation given for that change, and provided a simple means for testing it's veracity. It is now claimed (a MERE 21 DAYS later) that it's "old and no longer supported, so its status is irrelevant". If that is true now, it would have been true 21 days ago also. So what is the reasoning for this change? This exercise in bug tracking should be, and mostly is, about process. Reckless revision of a resolved status is a corruption of the process.