|
Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing |
| Summary: | SRB generates wrong 'order by' on report based on query with MS Access | ||
|---|---|---|---|
| Product: | Base | Reporter: | eremmel <jhf.remmelzwaal> |
| Component: | ReportBuilder | Assignee: | marc.neumann |
| Status: | CLOSED FIXED | QA Contact: | issues@dba <issues> |
| Severity: | Trivial | ||
| Priority: | P3 | CC: | issues |
| Version: | OOO320m12 | ||
| Target Milestone: | 3.4.0 | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Issue Type: | DEFECT | Latest Confirmation in: | --- |
| Developer Difficulty: | --- | ||
This issue is also identified on Dev300m75. It is remarkable that with internal HSQLDB the following ORDER BY is created in case of a derived table: ORDER BY "SYSTEM_SUBQUERY"."ID". So SRB is relying on the meta data API of the database, and this gives trouble with MSAccess. confirm, set target and send to the right developer @oj: is this not the same issue as issue 110535? Have a look to this post as well http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=29440. This user is also experiencing problems with grouping in SRB, but is using a MySQL database and he got the error "Every derived table must have its own alias". I do not have access to MySQL so can not prepare this further. @msc: yes, mostly. But I don't know why we have an extra order page in the wizard. Grouping are handled as order by, so to add an extra order column would also add an extra group. I tend to remove that page. The other error is that the wizard add an explicit order by at the sql statement, which seems to be wrong. I target this one to 3.4. Because it would require an UI change. @oj: I might misunderstand you about the remark on order by (called 'Sort Options' in the wizard?). I notice that the selected grouping-columns are also shown as predefined columns in the SortOptions. The only thing is you can select the order (asc, desc). It makes however sense to add additional sort columns for the order of the rows put at the inner grouping level. Those sort-columns are not related to grouping at all. It might be handsome to move the selected order (asc/desc) of the grouping columns to the 'grouping' section in the wizard and leave the sort options for *aditional* sorting at the inner grouping level. I have to rethink about the dialogs. Fixed in cws dba34b. Now when inserting a group no extra order by will be inserted anymore. This is done implicitly by the report builder for each group. Please verify. Thanks. verified in CWS dba34b find more information about this CWS, like when it is available in the master builds, in EIS, the Environment Information System: http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fdba34b |
When I've created a Query2 for MSAccess like SELECT `ID`, `Value` FROM ( SELECT `T1`.`ID`, `T1`.`Value` FROM `T2`, `T1` WHERE `T2`.`ID` = `T1`.`ID` ) `X` and make a report with grouping on field ID the query text is copied to the report source and extended with an order by. But this fails: SELECT `ID`, `Value` FROM ( SELECT `T1`.`ID`, `T1`.`Value` FROM `T2`, `T1` WHERE `T2`.`ID` = `T1`.`ID` ) `X` ORDER BY `T1`.`ID` It looks like SRB is using the meta-data API but this is returning unexpected information. For a reproduction see issue 110535. It contains a Base document with the example Query2 and the corresponding MSAccess database. 1 Make a report with the wizard 2 Select Query 2 as source 3 Select grouping on ID 4 Finish report 5 When one tries to open the report an error is shown. 6 Open report and check the data source query text and note the wrong field specification on the ORDER BY. Note that one can also specify in (most?) databases an ORDER BY with field-sequence numbers according the sequence-number of that field in the SELECT clause.