Created attachment 150380 [details]
Test database for grouping and aliases

Testing against LO6212 on macOS 10.14.3 :

In all of the reports that will open (the first 3), I repeatedly see what appears to be HTML table instructions in the left hand column, which indeed appears erroneous (and from what I can tell, not present in the query results). Is this relevant, or was the report designed this way ?


The 1st report TestGroupAlias :

- shows truncated data in the second column

The 2nd report TestGroupNoAlias :
- appears to show the required information in the right hand column

The 3rd report TestNoGroupAlias :
- shows truncated data in the second column

The 4th report TestNoGroupNoAlias :
- generates the following error message :


org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid command
*no column name specified for column number 6 in derived table __LibreOffice_report_result
caused by
'isc_dsql_prepare'

org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid command
*no column name specified for column number 6 in derived table __LibreOffice_report_result
caused by
'isc_dsql_prepare'

	at org.libreoffice.report.pentaho.PentahoReportJob.execute(PentahoReportJob.java:350)
	at org.libreoffice.report.pentaho.SOReportJobFactory$_SOReportJobFactory.execute(SOReportJobFactory.java:217)
Caused by: org.jfree.report.ReportDataFactoryException: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Invalid command
*no column name specified for column number 6 in derived table __LibreOffice_report_result
caused by
'isc_dsql_prepare'

	at org.libreoffice.report.pentaho.StarReportDataFactory.queryData(StarReportDataFactory.java:68)
	at org.jfree.report.data.CachingReportDataFactory.queryData(Unknown Source)
	at org.jfree.report.data.ReportDataRow.createDataRow(Unknown Source)

There is a whole bunch of bug reports against the report builder linked to problems with grouping, although I couldn't see any that seemed to combine both the grouping and the alias issue.

The HTML coding is intentional, the report is used to create the page here: http://dpets.uk/efios/efios/efioscourses.html

(In reply to Alex Thurgood from comment #2)
> Testing against LO6212 on macOS 10.14.3 :
> 
> In all of the reports that will open (the first 3), I repeatedly see what
> appears to be HTML table instructions in the left hand column, which indeed
> appears erroneous (and from what I can tell, not present in the query
> results). Is this relevant, or was the report designed this way ?
> 
> 
> The 1st report TestGroupAlias :
> 
> - shows truncated data in the second column
> 
> The 2nd report TestGroupNoAlias :
> - appears to show the required information in the right hand column
> 
> The 3rd report TestNoGroupAlias :
> - shows truncated data in the second column
> 
> The 4th report TestNoGroupNoAlias :
> - generates the following error message :
> 
> 
> org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid command
> *no column name specified for column number 6 in derived table
> __LibreOffice_report_result
> caused by
> 'isc_dsql_prepare'
> 
> org.libreoffice.report.ReportExecutionException: firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid command
> *no column name specified for column number 6 in derived table
> __LibreOffice_report_result
> caused by
> 'isc_dsql_prepare'
> 
> 	at
> org.libreoffice.report.pentaho.PentahoReportJob.execute(PentahoReportJob.
> java:350)
> 	at
> org.libreoffice.report.pentaho.SOReportJobFactory$_SOReportJobFactory.
> execute(SOReportJobFactory.java:217)
> Caused by: org.jfree.report.ReportDataFactoryException: firebird_sdbc error:
> *Dynamic SQL Error
> *SQL error code = -104
> *Invalid command
> *no column name specified for column number 6 in derived table
> __LibreOffice_report_result
> caused by
> 'isc_dsql_prepare'
> 
> 	at
> org.libreoffice.report.pentaho.StarReportDataFactory.
> queryData(StarReportDataFactory.java:68)
> 	at org.jfree.report.data.CachingReportDataFactory.queryData(Unknown Source)
> 	at org.jfree.report.data.ReportDataRow.createDataRow(Unknown Source)

Yes, these are the results that I get.

For reports some hints:
Report-Builder will analyize the code of the query. This won't work when the code should be executed in direct SQL. And if you switch the analyzing to no, grouping won't work.

Best solution for all reports: Don't user queries. Use views instead. The reports of the test-database will all work with views.

But then another "error" appears. Don't worry about the error-dialogue. The view will be created and could be seen after the tables will be actualized by "View" > "actualize Tables" (or something like this - haven't opened LO in English mode.

- Reportdesign corresponds to legacy reports according to README https://opengrok.libreoffice.org/xref/core/reportdesign/README?r=fe95086e

- Reportbuilder README indicates:
 Report Builder, or "new style reports", replacing legacy reports (in reportdesign).
 Started as an extension called "Sun Report Builder" then "Oracle Report Builder"
 [http://extensions.services.openoffice.org/project/reportdesign],
 which got bundled with LibreOffice, then converted to an optionally installable
 (but installed by default) part of LibreOffice proper.
 
 Uses the Pentaho Reporting Flow Engine of Pentaho BI [http://www.pentaho.com/].

- https://bugs.documentfoundation.org/show_bug.cgi?id=67832 shows that Pentaho and JFreeReports which are related to Reportbuilder haven't been updated since about 6 years.


1) for Reportbuilder, is this possible to debug this if the pb is in external sources? (or perhaps already fixed in these!) 
Knowing that these seem difficult to update and/or don't seem to interest enough people to give it a try, how can we advance here?


2) Reportdesign has been considered legacy since several years, could it be removed?
(Goal: simplify LO code)

(In reply to Julien Nabet from comment #7)
> 2) Reportdesign has been considered legacy since several years, could it be
> removed?
> (Goal: simplify LO code)

Many people who use Ubuntu will get LO without Report-Builder but with the internal legacy reports. Also many people, who use AOO and LO will use this report-engine. So don't remove it. Seems to be a solution which generates a report in a fast and usable way for this people.

(In reply to Robert Großkopf from comment #8)
> (In reply to Julien Nabet from comment #7)
> > 2) Reportdesign has been considered legacy since several years, could it be
> > removed?
> > (Goal: simplify LO code)
> 
> Many people who use Ubuntu will get LO without Report-Builder but with the
> internal legacy reports. Also many people, who use AOO and LO will use this
> report-engine. So don't remove it. Seems to be a solution which generates a
> report in a fast and usable way for this people.

No any longer - at least not with the binaries from TDF. ReportBuilder (to create templates for the Pentaho engine) can not be removed, at least not with the installation here under Ubuntu. 

The legacy reports still run fine. The UI for creating/edit new legacy reports is disabled. Legacy style reports can be created by hand though and added into a Base file via a macro and they work.

I would not be in favor of removing the legacy reports, quite the opposite I would like to see an extension that made them available via the GUI again.

Dear John Talbut,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug