Download it now!
Bug 81659 - "Expand references when new columns/rows are inserted" spoils (grouped) range references when row is inserted
Summary: "Expand references when new columns/rows are inserted" spoils (grouped) range...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta1
Hardware: All Linux (All)
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:4.5.0 target:4.3.7 target:4.4.2
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2014-07-23 06:31 UTC by Tom
Modified: 2015-12-17 08:27 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
LO Calc 4.2.5.2 example (19.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-23 06:31 UTC, Tom
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tom 2014-07-23 06:31:13 UTC
Created attachment 103318 [details]
LO Calc 4.2.5.2 example

If an empty row is inserted in a consecutive row of formulas that use the built-in function "SUM" with a range argument while the option "Expand references when new columns/rows are inserted" is enabled, the formula in the rows below the inserted row are spoiled.

Example attached:
1) 1st sheet = initial spreadsheet
2) 2nd sheet = result after adding row 6 while option "Expand ..." is ENabled
   The formulas in D7, D8, D9, D10 and D11 are wrong.
   E.g. "D7" should read "=SUM(A7:C7).
   The first argument of SUM() was obviously not incremented.
3) 3rd sheet = result after adding row 6 while option "Expand ..." is DISabled
   The formulas in D7, D8, D9, D10 and D11 are correct.

LibreOffice Calc 4.2.4.2 has the same problem but
LibreOffice Calc 4.1.6.2 does not have this problem.

Regards
Tom
Comment 1 ign_christian 2014-07-23 10:13:24 UTC
Reproduced with LO 4.3.0.3, 4.2.6.1, 4.2.0.0.beta1 on Ubuntu 12.04 x86

Not reproduced with LO 4.1.6.2
Comment 2 Joel Madero 2014-07-30 03:57:27 UTC
e02439a3d6297a1f5334fa558ddec5ef4212c574 is the first bad commit
commit e02439a3d6297a1f5334fa558ddec5ef4212c574
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Thu Oct 17 10:15:34 2013 +0000

    source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
    
    commit 6b8393474974d2af7a2cb3c47b3d5c081b550bdb
    Author:     Luboš Luňák <l.lunak@suse.cz>
    AuthorDate: Tue Jun 4 13:24:59 2013 +0200
    Commit:     Luboš Luňák <l.lunak@suse.cz>
    CommitDate: Wed Jun 5 16:01:43 2013 +0200
    
        fix gcc inline assembler operands usage
    
        Apparently whoever did these didn't get the gcc docs and specified
        every operand only as input, and then added volatile, explicit
        initialization and what not until it worked. Specify output operands
        correctly instead.
        I couldn't verify all assembler variants, as I don't know them,
        but the ones I don't know had at least some proper usage of output
        operands, so I'll assume those are all correct.
    
        Change-Id: I2910308b5e00cce8db756496df50ed26cfe35bb6

:100644 100644 49c023e2d36621396be9608a0a64bb670ac44e56 0057ddf8fa7aac0cc36b2b4cc3d47cb3b222e3de M	ccache.log
:100644 100644 1f724a450a2ba6123dc2772c4779157757705897 072f6720e641bd0f70bebcb3c47cfc6a55159e50 M	commitmsg
:100644 100644 058e9f056380574e4073b082180e87b9646d4e8b ecff2407bcf7e1bbf59642b9e021df82e44f526c M	dev-install.log
:100644 100644 115a24f6a9e45b293b22377dc409001a05069581 2b2cbad4f516bc1d6abed9e776c86ea90e3e757f M	make.log
:040000 040000 2d7a96e6b40994d4cff6f226ff2d54947490dabc 4ca7969f1b45eb47375749fa2b77e239eeb9e77b M	opt


# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574
# good: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15
git bisect good 8f4aeaad2f65d656328a451154142bb82efa4327
# good: [9995fae0d8a24ce31bcb5e9cd0459b69cfbf7a02] source-hash-8600bc24bbc9029e92bea6102bff2921bc10b33e
git bisect good 9995fae0d8a24ce31bcb5e9cd0459b69cfbf7a02
# good: [8ad82bc1416a07501651e8d96fe268e47d3931d3] source-hash-13821254f88d2c5488fba9fe6393dcf4ae810db4
git bisect good 8ad82bc1416a07501651e8d96fe268e47d3931d3
# good: [d084d250b04446535ca1d7c29cf2062e6bd042b3] source-hash-688f72e3a2c3ef923389bbd21f6aea3afe1114db
git bisect good d084d250b04446535ca1d7c29cf2062e6bd042b3
# good: [c2069a369d738078124812312d51f21ea1ce2421] source-hash-f160e4935c474a5293b3d3c11b3d538efb4767a0
git bisect good c2069a369d738078124812312d51f21ea1ce2421
# good: [a0f20bc04a32a7791ba765d2de2f44f1b74033d1] source-hash-1de66ba440855050a794b3b2a8647c1b02c210b8
git bisect good a0f20bc04a32a7791ba765d2de2f44f1b74033d1
# good: [a48fbf799e4d4d555fe383b7233c804f573eca4e] source-hash-bb6ecd8b40313b7cc83d4e619029f4e001334a52
git bisect good a48fbf799e4d4d555fe383b7233c804f573eca4e
# good: [e7cbc9b8764280fd4799e234ca32925e91547a82] source-hash-31b35ed6bb7fe77f3f276b00fefce112a620b6ac
git bisect good e7cbc9b8764280fd4799e234ca32925e91547a82
# first bad commit: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
Comment 3 Jean-Baptiste Faure 2014-09-04 05:51:34 UTC
There is obviously a change, but where is the bug or incorrect behavior? In the current version or in the previous?

Best regards. JBF
Comment 4 GerardF 2014-09-04 07:32:00 UTC
(In reply to comment #3)
> There is obviously a change, but where is the bug or incorrect behavior? In
> the current version or in the previous?
> 
> Best regards. JBF

When inserting new row with "Expand references ...." enabled,
References in formulae must be expanded if:
+ new row is adjacent to references
+ original reference contains at least 2 rows.

Example 1:
Formula =SUM(A2:B3)and insert row before row 2 make formula becomes =SUM(A2:B4)
(there is 2 rows in the SUM range)
Example 2:
Formula =SUM(A2:B2)and insert row before row 2 make formula stay =SUM(A2:B2)
(there is 1 row in the SUM range)

Plus : this bug change also references when row inserted is *not* adjacent to the range.
Inserting a new row in row 2 with formula =SUM(A5:B5) in C5 change the formula in =SUM(A5:B6)

Same behaviour for columns.

This is indeed a major bug.
Comment 5 GerardF 2014-10-28 09:31:12 UTC
still broken in all 4.2.x, 4.3.x and 4.4 master.

This basic functionality forces me to stay in 4.1.6
Comment 6 Matthew Francis 2015-01-02 02:48:44 UTC
I get a different result for the bibisect which ends here in 43all:

# first bad commit: [ba096f438393091574da98fe7b8e6b05182a8971] source-hash-8499e78ca03c792f4fa2650e02b519094ba0baa8
Comment 7 Matthew Francis 2015-01-02 03:15:35 UTC
It looks like the behaviour changed as of the below commit.

commit 27d02ddc7fc4e11bc4429839729ea3d6f7322245
Author: Kohei Yoshida <kohei.yoshida@gmail.com>
Date:   Fri Jul 19 20:31:08 2013 -0400

    Properly handle optional edge expansion of referenced ranges.
    
    Change-Id: I499189f4f76eee4b963f643364d1fad26cf69785
Comment 8 Eike Rathke 2015-02-23 20:48:41 UTC
The formula in D7 =SUM(A6:C7) is correct, that's exactly what expand references is supposed to do when inserting a row directly above/below an existing range reference. However, the formulas in D8:D11 are wrong, they should not had been adjusted.

Taking.
Comment 9 GerardF 2015-02-24 10:13:07 UTC
(In reply to Eike Rathke from comment #8)
> The formula in D7 =SUM(A6:C7) is correct, that's exactly what expand
> references is supposed to do when inserting a row directly above/below an
> existing range reference.

No. See my comment 4:
"When inserting new row with "Expand references ...." enabled,
References in formulae must be expanded if:
+ new row is adjacent to references
*** + original reference contains at least 2 rows.***
Comment 10 Eike Rathke 2015-02-24 12:12:04 UTC
Ah, true, I overlooked this little but important detail :)  Thanks for the heads-up.
Comment 11 Commit Notification 2015-02-26 15:18:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0cd15b4494f8e8abe67a258fb10189135bf5a8ac

split formula grouping for reference edge expansion, tdf#81659 related

It will be available in 4.5.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 2015-02-26 15:22: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=23b0112ecea2f8796a4e237e9061de1a36997a30

tdf#81659 check that references are at least 2 cols/rows to expand edge

It will be available in 4.5.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 2015-02-26 16:14:31 UTC
Pending review
https://gerrit.libreoffice.org/14655 for 4-4
https://gerrit.libreoffice.org/14656 for 4-3
Comment 14 Commit Notification 2015-02-27 12:07:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=58deeaea725eca0e8140b09420d5144d5d3f800c&h=libreoffice-4-3

Resolves: tdf#81659 handle expand reference edge correctly

It will be available in 4.3.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 15 Commit Notification 2015-02-27 12:07:30 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e8f059f918faf8a44787f8f0bdf61217f4439d7f&h=libreoffice-4-4

Resolves: tdf#81659 handle expand reference edge correctly

It will be available in 4.4.2.

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 Robinson Tryon (qubit) 2015-12-17 08:27:13 UTC
Migrating Whiteboard tags to Keywords: (bibisected)
[NinjaEdit]