Bug Hunting Session
Bug 81213 - REPORTBUILDER: Wrong table-clause generated by reportbuilder for selects on oracle-db
Summary: REPORTBUILDER: Wrong table-clause generated by reportbuilder for selects on o...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.4 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:4.4.0 target:4.3.1
Keywords:
: 52156 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-07-11 07:35 UTC by Johann Flori-Himpel
Modified: 2015-05-03 14:34 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Johann Flori-Himpel 2014-07-11 07:35:33 UTC
Oracle does not accept the "AS" in select statements like this:

  select WHATEVER from "TABNAME" AS "ALIASNAME" 

This is, what the reportbuilder generates. 


If I ommit the "AS", the statement is executed correctly:

  select WHATEVER from "TABNAME" "ALIASNAME" 


I am using Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

But Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
does not accept the generated syntax too.
Comment 1 Julien Nabet 2014-07-22 09:57:13 UTC
Lionel: searching "AS" in reportbuilder module gives 2 relevant locations:
http://opengrok.libreoffice.org/xref/core/reportbuilder/java/org/libreoffice/report/SDBCReportDataFactory.java#559 + line 565 of this same file.

Should we just remove this "AS" since it's optional for most (all?) RDBMS and illegal at least for Oracle? (I just found this ref http://stackoverflow.com/questions/21145028/how-to-use-the-as-keyword-to-alias-a-table-in-oracle)

BTW: I can make the change obviously but not test it.
Comment 2 Lionel Elie Mamane 2014-07-22 10:32:10 UTC
(In reply to comment #1)
> Lionel: searching "AS" in reportbuilder module gives 2 relevant locations:
> http://opengrok.libreoffice.org/xref/core/reportbuilder/java/org/libreoffice/
> report/SDBCReportDataFactory.java#559 + line 565 of this same file.
> 
> Should we just remove this "AS" since it's optional for most (all?) RDBMS
> and illegal at least for Oracle?

Yes, please do that. However, I think it will not be enough, since this is a backup code branch that I've only ever seen taken with gcj (never with Sun/Oracble Java).

> BTW: I can make the change obviously but not test it.

Activate the database's query logging, you'll see the queries sent to the database.

I don't think this is per se related to the report builder (except the locations you linked to), the report builder does not really create queries. It gets a query as a parameter (the data source), and modifies it a bit (to add/remove ORDER BY clauses for sorting, GROUP BY clauses for groupings, etc, etc). But AFAIK it does that through a query composer UNO service.

So the soure of the stuff is most probably from our general query designer / composer. That can be tested by creating a query with the query designer and switching to SQL view. 

Should be in dbaccess/source/ui/querydesign/ and/or dbaccess/source/core/api/SingleSelectQueryComposer.cxx and/or in dbaccess/source/core/api/querycomposer.cxx.

I'd be most happy if you took care of this.
Comment 3 Lionel Elie Mamane 2014-07-22 10:33:10 UTC
Johann, could you please attach an example .odb file? I want to see the query, and "from where" it is constructed, to check that my conjecture in my previous comment is correct. Thanks in advance.
Comment 4 Lionel Elie Mamane 2014-07-22 10:49:04 UTC
Johann, actually looking a bit at the code, we have a setting for that. Go to Edit / Database / Advanced Settings. In "Special Setting", *uncheck* (unset) the "Use keyword AS before table alias names". Please let us know if it solves the issue *after* you refresh the query used in the report: just make any change to the query, save it, make the opposite change, save it again. And then rerun the report.

Julien: still remove the "AS" in the two places you found. Please also set this setting to *FALSE* by default, in the "general" drivers (those not linked to a specific RDBMS: odbc, jdbc, etc, *not* MySQL, PostgreSQL, ...). It corresponds to "UseKeywordAsBeforeAlias" in files connectivity/registry/*/org/openoffice/Office/DataAccess/Drivers.xcu
Comment 5 Lionel Elie Mamane 2014-07-22 11:12:16 UTC
(In reply to comment #4)
> Julien: still remove the "AS" in the two places you found. Please also set
> this setting to *FALSE* by default, in the "general" drivers (those not
> linked to a specific RDBMS: odbc, jdbc, etc, *not* MySQL, PostgreSQL, ...).
> It corresponds to "UseKeywordAsBeforeAlias" in files
> connectivity/registry/*/org/openoffice/Office/DataAccess/Drivers.xcu

Also "GenerateASBeforeCorrelationName" in dbaccess/source/core/dataaccess/ModelImpl.cxx, function ODatabaseModelImpl::getDefaultDataSourceSettings, change that to false.

And connectivity/source/drivers/odbc/ODriver.cxx, function ODBCDriver::getPropertyInfo, again "GenerateASBeforeCorrelationName"
and same in connectivity/source/drivers/jdbc/JDriver.cxx and connectivity/source/commontools/dbmetadata.cxx function, line "bool doGenerate( true );"


All this hoping that no other widely used RDBMS *requires* this 'AS'...
Comment 6 Julien Nabet 2014-07-22 14:50:26 UTC
Thank you Lionel for all these information, I'll give it a try.
Comment 7 Commit Notification 2014-07-22 16:39:59 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=568778874429595855b435792e5ebecd52956dae

Resolves fdo#81213: Wrong table-clause generated by reportbuilder



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 8 Julien Nabet 2014-07-22 17:18:13 UTC
For 4.3: https://gerrit.libreoffice.org/10469/
for 4.2: https://gerrit.libreoffice.org/10470
I let you check these Lionel before putting this tracker to FIXED.
Comment 9 Commit Notification 2014-07-23 05:34:00 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0d5d8c22f7be41d408d8ee4012ef1a6f4368423e&h=libreoffice-4-3

Resolves fdo#81213: Wrong table-clause generated by reportbuilder


It will be available in LibreOffice 4.3.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 10 Julien Nabet 2014-07-23 08:04:42 UTC
For the record, 4.2 one has been abandonned (see https://gerrit.libreoffice.org/#/c/10470/)
Let's put this one to FIXED now (so from future release 4.3.1)
Comment 11 Robert Großkopf 2015-05-03 14:34:55 UTC
*** Bug 52156 has been marked as a duplicate of this bug. ***