Issue 110536

Summary: SRB generates wrong 'order by' on report based on query with MS Access
Product: Base Reporter: eremmel <jhf.remmelzwaal>
Component: ReportBuilderAssignee: 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: ---

Description eremmel 2010-03-31 20:52:32 UTC
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` 

It looks like SRB is using the meta-data API but this is returning unexpected

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
Comment 1 eremmel 2010-03-31 21:27:53 UTC
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.
Comment 2 marc.neumann 2010-04-01 10:26:30 UTC
confirm, set target and send to the right developer

@oj: is this not the same issue as issue 110535?

Comment 3 eremmel 2010-04-10 09:08:34 UTC
Have a look to this post as well 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.
Comment 4 ocke.janssen 2010-07-14 09:12:52 UTC
@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.
Comment 5 eremmel 2010-07-27 11:54:10 UTC
@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.

Comment 6 ocke.janssen 2010-08-23 11:58:17 UTC
I have to rethink about the dialogs.
Comment 7 ocke.janssen 2010-11-04 14:45:04 UTC
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.
Comment 8 ocke.janssen 2010-12-01 08:22:27 UTC
Please verify. Thanks.
Comment 9 marc.neumann 2011-01-26 08:19:32 UTC
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: