Created attachment 127029 [details] Error message when using a postgres JDBC driver Specifying a column alias on a statement against a postgres database via jdbc driver creates a database syntax error as shown in the attached screen shot. The error occures no matter whether an SQL query, the query designer or a pass-through query is used executed. JDBC-Drivers tested: postgresql-jdbc3-8.3.jar postgresql-jdbc3-9.2.jar postgresql-jdbc4-9.2.jar All the above (SQL query, the query designer or a pass-through query) can be executed if no alias is used. This error does not occure in libreoffice-base 4.2.8-0ubuntu4. All jdbc drivers work.
Created attachment 127030 [details] Error message when using a postgres SDBC driver When using the SDBC-Driver for postgres (libreoffice-sdbc-postgresql (1:5.1.4-0ubuntu1)) the error message is a bit more detailed. It appears, that postgres does not support a column alias directly after the column name without the keyword AS in between. LibreOffice seems to create exactly that syntax (SELECT columnname columnalias FROM tablename). The error only occurs when executing an SQL query or the query designer. Unlike with JDBC-driver the SDBC-driver seems to pass the query through to the DBMS unchanged, if executing a SQL-pass-thru query. So using aliases with SDBC and pass-through works fine.
Please find the setting described in [1]. In this case it needs to be set. If the setting is unchecked and checking it solves the issue, set status to RESOLVED WORKSFORME, otherwise return status to UNCONFIRMED. [1] https://bugs.documentfoundation.org/show_bug.cgi?id=81213#c4
As far as I understand, the settings in bug 81213#c4 applies to the "as" before table aliases, such as select * from TableName TableAlias vs. select * from TableName as TableAlias It's a commonly known problem that different DBMS require different handling of "AS" before table aliases. The related changes also suggest that it applies to table aliases only. Nothing the less I tried the setting. It did not fix the problem. This bug here applies to the "as" before column aliases, such as select ColumnName ColumnAlias from TableName vs. select ColumnName as ColumnAlias from TableName Sofar I'm not aware of any DBMS that cannot handle the "AS" before a ColumnAlias. On the other hand it seems reasonable to strip the syntax, where possible. Unfortunately here it causes problems.
After having another look at the problem, I found that I should have had a glance at the ODB files instead of relying on the UI. As mentioned in the bug 81213, comment 4, I needed to change some other setting an save the file. That would create an entry for the GenerateASBeforeCorrelationName-setting in the new ODB file. After reloading that ODB would _not_ suffer from the described problem. Departing from my earlier impression, table as well as column aliases will be correctly prepended with an "AS" keyword. Even though I find the UI very confusing, the workaround WORKSFORME.