Bug Hunting Session
Bug 72645 - LibreOffice Calc inconsitent results from GETPIVOTDATA
Summary: LibreOffice Calc inconsitent results from GETPIVOTDATA
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta2
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.0.2 target:4.1.5
Keywords:
Depends on:
Blocks: mab4.1
  Show dependency treegraph
 
Reported: 2013-12-12 16:10 UTC by Ceri Mitchell
Modified: 2014-01-19 15:11 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheets used for testing (17.70 KB, application/zip)
2013-12-12 16:10 UTC, Ceri Mitchell
Details
PivotTableExcel.xls (14.50 KB, application/vnd.ms-excel)
2013-12-12 16:17 UTC, Ceri Mitchell
Details
PivotTableLibreOffice.ods (15.05 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-12 16:18 UTC, Ceri Mitchell
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ceri Mitchell 2013-12-12 16:10:45 UTC
Created attachment 90672 [details]
Spreadsheets used for testing

Versions used for the testing:

Version: 4.2.0.0.beta2+
Build ID: 02180aed7dc0b8c5f9cc23b319adc2386a9aab69

Version 4.0.6.2 (Build ID: 2e2573268451a50806fcd60ae2d9fe01dd0ce24)

MS Excel 2003

Inconsistent results from GETPIVOTDATA function between LibreOffice 4.0.6.2 and 4.2.0.0.beta2+ (and Excel which acts as 4.0.6.2)

2 Spreadsheets attached:

PivotTableExcel.xls, when opened with LibreOffice 4.2.x GETPIVOTDATA cannot access the pivot table data unless the string "Sum of " is inserted into the Data Field argument. Works OK with 4.0.6.2

PivotTableLibreOffice.xls, PivotTable created with LibreOffice, shows inconsitencies beween 4.0.6.2 and 4.2.x handling strings with upper/lowercase and spaces. Works OK with 4.0.6.2
Comment 1 Ceri Mitchell 2013-12-12 16:17:16 UTC
Created attachment 90673 [details]
PivotTableExcel.xls
Comment 2 Ceri Mitchell 2013-12-12 16:18:05 UTC
Created attachment 90674 [details]
PivotTableLibreOffice.ods
Comment 3 m.a.riosv 2013-12-12 20:07:01 UTC
Hi Ceri, thanks for reporting.

In relation with the need to define the all restrictions you can see developer comment in:

https://bugs.freedesktop.org/show_bug.cgi?id=68666#c9

the other issue is about case sensitivity, I don't know if this change is intended or not.

Please Kohei, can you inform what is right about case issue.
Comment 4 m.a.riosv 2013-12-12 21:44:57 UTC
Well taking a look into Open Document Format v1.2 approved as a OASIS Standard on 29 September 2011 http://docs.oasis-open.org/office/v1.2/OpenDocument-v1.2.odt.

As I understand.

For the first syntax:
"Field and member names are case-insensitive."

For the second it is not clear:
"Each of the other entries specifies a constraint in the form Field[Member] (with literal characters [ and ])"
"The possible function names are the same as in the table:function attribute of the <table:data-pilot-subtotal> element, case-insensitive"

But the function seems case-insensitive with the first syntax B5 in the attached file PivotTableLibreOffice.ods and not with the second B4.

I think it is a bug about case-insensitive.
Comment 5 Ceri Mitchell 2013-12-13 09:44:11 UTC
Hi, thanks for the reply.

Sorry I started looking at an issue with GETPIVOTDATA importing Pivot tables from Excel and found some other issues along the way, so I listed them all.
LibreOffice 4.0.6 handles all of this OK, 4.2.x does not.

I waited for the fix for bug 69518 before retesting and reporting.

Here they are broken down:


PivotTableLibreOffice.ods:

1. Cell A2, Different results returned by 4.0.6 and 4.2.  You may be right with Bug 68666, not sure.

2. Cells A4 and A5 demonstrate that the second format of GETPIVOTDATA seems to be case sensitive.

3. Cell A10, Different handling of Target Value format with spaces at the end of the field item string. 4.2.x behaves differently to 4.0.6


PivotTableExcel.ods
This is the bug I was looking for, which I think is with using GETPIVOTDATA with a Pivot Table created in Excel. Again it works OK in 4.0.6.

When this spreadsheet is opened with 4.2.x none of the valid test GETPIVOTDATA examples work (A1 to A7,A10,A12,A17).
Only cell A15 works and this is where I have inserted the text "Sum of " into the TargetField string.
Comment 6 Kohei Yoshida 2014-01-07 00:22:17 UTC
This bug report consists of multiple bugs, actually.  We'll need to handle them one at a time.
Comment 7 Kohei Yoshida 2014-01-07 00:22:45 UTC
I'll take it.
Comment 8 Commit Notification 2014-01-07 05:51:21 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8728f8e8705cfb6875a315aef85ec6004604e702

fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree.



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 9 Commit Notification 2014-01-07 05:51:36 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=17b00767948f7add229ec589c06cd8c898032ffa

fdo#72645: Case-insensitive string comparison in GETPIVOTDATA.



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 10 Commit Notification 2014-01-07 05:59:03 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree.


It will be available in LibreOffice 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 11 Commit Notification 2014-01-07 05:59:17 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

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

fdo#72645: Case-insensitive string comparison in GETPIVOTDATA.


It will be available in LibreOffice 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 12 Kohei Yoshida 2014-01-07 13:45:59 UTC
Backport request for 4.1 is underway on gerrit:

https://gerrit.libreoffice.org/#/c/7284/
https://gerrit.libreoffice.org/#/c/7285/

Meanwhile I'll mark this fixed.
Comment 13 Kohei Yoshida 2014-01-07 16:00:06 UTC
Just for the sake of documenting it, there are two ways you can legally get a result value via GETPIVOTDATA.  One is to specify full path to the subtotal value i.e. in this specific example you specify the Type being either M or F and the Name.

Alternative way is to specify only one field name / member pair whose support was dropped during the rework (by accident).  The 2nd way works if and only if that pair occurs only once within the pivot table output *and* it has a corresponding subtotal value displayed in the table.  And this should work both the old syntax and the new syntax.
Comment 14 Commit Notification 2014-01-08 12:53:15 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree.


It will be available in LibreOffice 4.1.5.

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 2014-01-08 12:57:38 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#72645: Case-insensitive string comparison in GETPIVOTDATA.


It will be available in LibreOffice 4.1.5.

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 Ceri Mitchell 2014-01-09 09:36:03 UTC
Confirmed working in:
Version: 4.2.1.0.0+
Build ID: 8308f6e5f50bcdd5cd4e5511a8a3bd5c64c93e2b
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-01-08_09:46:01

Many thanks
Comment 17 m.a.riosv 2014-01-19 15:11:32 UTC
Sorry for the delay in check.

Seems solved for:
Win7x64
Version: 4.2.0.2 Build ID: cd65d6220c5694ee7012d7863bcde3455c9e3c30
Version: 4.2.1.0.0+ Build ID: d72321b665f54946cf603e6f30740f31151c898f
         TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-01-16_02:01:06
Version: 4.3.0.0.alpha0+ Build ID: 42f551d524a1df46f6a311d5897ac30bd8fc1aaf
         TinderBox: Win-x86@39, Branch:master, Time: 2014-01-15_22:44:37

A little difference: In A21 now (from 4.2) results in a value 21 while in LibreOffice 4.0.6 and in Excel-(as reported) gives a #REF!