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
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 Type Accomodation 3000 Accounting Services 1200 American Express 20 Bank charges 78 Bicycles 30 etc... In Calc 3.6 all the amounts are in ascending order from top to bottom, and are not associated with the types. Queries follow..................... ExpensesPersonalTotalsByYearBase 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 ExpensesPersonalByYearBase 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 YearTypesExpensesBase SELECT `YearsBase`.`yr`, `Type`.`TypeId`, `Type`.`Type` FROM `YearsBase`, `Movement` INNER JOIN `Type` ON `Movement`.`MovementId` = `Type`.`MovementId` WHERE ( ( ( `Movement`.`CreditExp` ) = TRUE ) ) YearsBase SELECT LEFT( `From`, 4 ) AS `yr` FROM `ReportingPeriod` WHERE ( ( ( `ReportingPeriod`.`CalendarYear` ) = TRUE ) )
*** This bug has been marked as a duplicate of bug 53640 ***