Bug 53656 - PIVOTTABLE: Pivot tables that worked in 3.5.n fail in 3.6
Summary: PIVOTTABLE: Pivot tables that worked in 3.5.n fail in 3.6
Status: CLOSED DUPLICATE of bug 53640
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Linux (All)
: high major
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Reported: 2012-08-17 20:06 UTC by tim
Modified: 2012-08-21 14:51 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:
Regression By:


Note You need to log in before you can comment on or make changes to this bug.
Description tim 2012-08-17 20:06:27 UTC
Problem description: 

I have some pivot tables in Calc based on moderately complex queries on a MySQL database, via a database registered in LibreOffice. These used to work.  With 3.6 they return incorrect data.  They seem to decouple the row fields from the data fields, and report the data in purely ascending order independent of the row field.  Thus if I have data 

A 20
B 10
C 15

The Pivot table now incorrectly returns

A 10
B 15
C 20

I have used MySql with JDBC, with ODBC and (in 3.5 only) the native connector (to check if the connection method is the cause).  All 3 options return the correct data when viewed in Base in 3.5 and 3.6, and the correct data in Calc in 3.5, but incorrect data in Calc in 3.6.  

I have also used queries defined within Base itself on the MySQL database, and views defined within MySQL.  The results are the same.  

I have concluded the problem, must lie in the 'enhanced' pivot tables in 3.6.

I have tried to pin down where the problem is in 3.6.  I suspect, but am not yet sure, that it may be queries with Left or Right joins that may be causing the problems.  I have a couple of straightforward pivot tables that seemed to be OK, but all those with more complex underlying structures failed badly.

Unfortunately I need the system on my main PC to return the correct data and have had to revert to 3.5.6.  I can't easily switch between 3.5 and 3.6 to test any hypotheses.

Platform (if different from the browser): 
Browser: Mozilla/5.0 (X11; Linux x86_64) Ubuntu/12.04 64 bit Chromium/18.0.1025.168
Comment 1 tim 2012-08-18 15:16:47 UTC
I have produced extracts of one of the set of queries I use that worked fine in previous versions of Calc.  These are below.  I'm aware some of my queries are exactly 'ideal', but they are derived from old queries I've been using for years on other systems and I haven't seen the need to rationalise them.

The query used by the Pivot Table is the 1st one - ExpensesPersonalTotalsByYearBase.  This sums the expenses for each expense type in ExpensesPersonalByYearBase.  It ensures that there is an entry for every year for every type of expense, even if 0, by linking this data to YearTypesExpensesBase.  That query gets an entry for every type for every year, with the years derived from the YearsBase query.

I then created a pivot table that I filtered by Year, listing the totals for every possible type of expense., for example, a few rows, with year filter at the start, would look like this (with invented numbers!):

yr	             2010
Accomodation	     3000
Accounting Services  1200
American Express       20
Bank charges	       78
Bicycles               30


In Calc 3.6 all the amounts are in ascending order from top to bottom, and are not associated with the types.

Queries follow.....................


SELECT `YearTypesExpensesBase`.`yr`, `YearTypesExpensesBase`.`Type`, SUM( `ExpensesPersonalByYearBase`.`baseamount` ) AS `SumOfbaseamount` FROM { OJ `YearTypesExpensesBase` LEFT OUTER JOIN `ExpensesPersonalByYearBase` ON `YearTypesExpensesBase`.`TypeId` = `ExpensesPersonalByYearBase`.`TypeId` AND `YearTypesExpensesBase`.`yr` = `ExpensesPersonalByYearBase`.`Yr` } GROUP BY `YearTypesExpensesBase`.`yr`, `YearTypesExpensesBase`.`Type` ORDER BY `YearTypesExpensesBase`.`yr` ASC, `YearTypesExpensesBase`.`Type` ASC


SELECT LEFT( `DebitDate`, 4 ) AS `Yr`, ROUND( `Amount` / `baserate`, 2 ) AS `baseamount`, `Type`.`TypeId`, `Type`.`Type` FROM `MyAccounts`.`Movement` AS `Movement`, `MyAccounts`.`Type` AS `Type`, `MyAccounts`.`Class` AS `Class`, `MyAccounts`.`Item` AS `Item` WHERE `Movement`.`MovementId` = `Type`.`MovementId` AND `Class`.`ClassId` = `Item`.`ClassId` AND `Item`.`TypeId` = `Type`.`TypeId` AND ( `Movement`.`CreditExp` ) = TRUE AND ( `Class`.`PersonalExpense` ) = TRUE


SELECT `YearsBase`.`yr`, `Type`.`TypeId`, `Type`.`Type` FROM `YearsBase`, `Movement` INNER JOIN `Type` ON `Movement`.`MovementId` = `Type`.`MovementId` WHERE ( ( ( `Movement`.`CreditExp` ) = TRUE ) )


SELECT LEFT( `From`, 4 ) AS `yr` FROM `ReportingPeriod` WHERE ( ( ( `ReportingPeriod`.`CalendarYear` ) = TRUE ) )
Comment 2 Alex Thurgood 2012-08-21 14:27:36 UTC

*** This bug has been marked as a duplicate of bug 53640 ***