Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Turning off alias options in Advanced settings does not remove aliased table name from SQL|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||UNCONFIRMED ---||QA Contact:|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description ianst 2010-03-14 19:47:30 UTC
Using JDBC to define and access connection to an existing database. In advanced settings deselected "Append the table alias name on SELECT statements" - this automatically "unselected" use AS keyword option. In Create in Query Design View selected columns from a table and then viewed SQL The query generated after selecting the columns for the query looks as follows: SELECT "@ID", "CHEQUE", "BANK", "AMOUNT", "PERIOD" FROM "CHEQUES" "CHEQUES" While the "AS" is not included (deselected in Advanced settings) the table name "CHEQUES" is still aliased despite the setting in Advanced Settings. Amending the SQL to remove the redundant alias results in the query running as expected i.e SELECT "@ID", "CHEQUE", "BANK", "AMOUNT", "PERIOD" FROM "CHEQUES" Kind regards Ian Stuart
Comment 1 r4zoli 2010-03-15 13:38:11 UTC
Ian, What is the difference in query results?
Comment 2 ianst 2010-03-16 10:02:23 UTC
Hi Thanks for checking out this issue. Some databases do not support aliasing table names in queries to the same name as the table. Hence the query in examples 1 and 2 fail. Example 1. SELECT "@ID", "CHEQUE", "BANK", "AMOUNT", "PERIOD" FROM "CHEQUES" AS "CHEQUES" Example 2 SELECT "@ID", "CHEQUE", "BANK", "AMOUNT", "PERIOD" FROM "CHEQUES" "CHEQUES" In the above example 2, the "AS" keyword is not included because it has been "unchecked" in the advanced settings and saved. However neither of these queries return any results because the table is still aliased ( "CHEQUES" AS "CHEQUES" - "CHEQUES" "CHEQUES") even though the Advanced settings have been modified to not append aliases. Example 3 - query modified manually to remove AS "CHEQUES" alias SELECT "@ID", "CHEQUE", "BANK", "AMOUNT", "PERIOD" FROM "CHEQUES" Example 3 query does return a result set because there is no alias appended to the query. So, to answer your question : The first 2 examples do not return a result set, the second example returns exactly what is expected. It seems that the GUI interface is not saving the advanced settings related to appending aliases - at least not when I create a new database connection, connect to it, change advance settings and file the database. When reconnecting to the database the Advanced setting Append the table alias name on SELECT statements remains "checked". Hope this clarifies the issue a bit better. Rgds Ian
Comment 3 r4zoli 2010-03-16 15:12:20 UTC
The option "Use keyword AS before table alias name", says about adding and removing "AS" before table alias name, if I know correctly it is only for removing "AS" keyword not touching alias name. (The help text is missing is another issue). If you think with this option the alias needs to be removed, when switched off, it is an enhancement not a defect. I tested with MySQL 5.1 and JDBC 5.1, OOo 3.2 on win7.
Comment 4 ianst 2010-03-16 18:01:31 UTC
Thank you kindly for following up on this. Possibly refer to other issues where this has been reported - I'll have a look at all the references made in regard to aliasing and the responses as soon as I have a chance - but aliasing is something that has come up quite often and solutions have pointed to Advanced Settings and setting the aliasing options as described in this issue. Please have a look at Advanced Settings in OOBASE - There are two options related to the use of aliased table names which work in conjunction with each other. By default on defining a new database, the options "Append table alias name on SELECT statements " and option "use keyword AS before table names" are checked. If you uncheck the "Append table alias name on SELECT statements" then the "Use keyword AS before table names" is also unchecked. This would imply that unchecking these options will no longer result in table names being appended to queries as aliases, and secondly, that the "AS" keyword will also not be used (quite logical - if you are not appending table aliases to queries then you won't need the "AS" option) . If you only "uncheck" the "AS" option then the implication is that the "AS" keyword will not be used in statements but that table aliases will be appended. The purpose of these two options, as far as I can determine from other postings and from a logical observation, is to allow for the turning off of aliasing either completely or to just remove the "AS" option - otherwise why would the two options be available. It would seem to be at least inconsistent if not a defect. Rgds Ian
Comment 5 ianst 2010-03-16 18:20:45 UTC
Hi Below are some examples of issues re aliasing and the purpose of Advanced Settings which I think clearly indicate the intended use of Append table names and the use of "AS" - i.e that both options can modify the use of aliasing. Regards Ian Please see Issue 104961 Refer to below from Frank - note the second paragraph << From: firstname.lastname@example.org <email@example.com> Date: Wed, 6 Dec 2006 13:01:27 +0100 (MET) Content-Type: multipart/mixed; boundary="----=_Part_109_29221778.1165406487462" Subject: new/CWS dba22ui : advanced database setting: "Use keyword AS before table alias Product: Database Access Type: new Title: advanced database setting: "Use keyword AS before table alias names" Posted by: firstname.lastname@example.org Affected: - Effective from: CWS dba22ui *Flags* ------- API/ BASIC [ ] Configuration [ ] File format change [ ] Help/ Guide [x] Performance test [ ] Translation [x] UI relevant [x] *Description* ------------- In the advanced database settings (menu: Edit / Database / Advanced Settings, dialog page: special settings) a new option is available, called "Use keyword AS before table alias names". This option is enabled if and only if "Append the table alias name on SELECT statements" option is checked, and itself checked by default for newly created databases. When checked, SQL statements generated by Base will use the AS keyword as follows: SELECT * FROM "table" AS "table" When not checked, the above statement would look like SELECT * FROM "table" "table" The first option is more human readable, the second option is required by some databases (Oracle ODBC, in particular). --------------------------------------------------- http://dba.openoffice.org/howto/AppendTableAlias.html Table Aliases for data sources Note: The below information is outdated, it applies only to OpenOffice.org versions prior to 2.0. The Problem When working with select statements, OpenOffice.org usually uses an alias name for tables such as in "SELECT * FROM <table> aliasname" Here "aliasname"" is a so-called table alias. However, some databases do not allow such aliases. The statement then would be "SELECT * FROM <table>". Such databases usually reject statements with aliases. The Solution OpenOffice.org features the disabling as well as the enabling of this behavior. Table aliases can be enabled on a per-data-source basis. For this, the "Info" property of a data source should contain a name-value-pair with Name: AppendTableAlias Value: FALSE Unfortunately, there is no user interface, yet, for doing so. You could use the Basic macro provided below, until we get OOo 2.0 ui, which adds the setting for a data source of your choice. Note that this feature will be first available in version OpenOffice.org 2.0. The Macro The following macro disables the use of table alias names for a data source of your choice. You can also download this macro in the downloads section. REM ***** BASIC ***** Option Explicit Sub Main Dim sDataSourceName as String sDataSourceName = InputBox( "Please enter the name of the data source:" ) AppendTableAlias(sDataSourceName ) End Sub Sub AppendTableAlias( sDataSourceName as String ) ' the data source context (ehm - the service name is historical :) Dim aContext as Object aContext = createUnoService( "com.sun.star.sdb.DatabaseContext" ) If ( Not aContext.hasByName( sDataSourceName ) ) Then MsgBox "There is no data source named " + sDataSourceName + "!" Exit Sub End If ' the data source Dim aDataSource as Object aDataSource = aContext.getByName( sDataSourceName ) ' append the new AppendTableAlias flag Dim bFlag as Boolean bFlag = FALSE Dim aInfo as Variant aInfo = aDataSource.Info aInfo = AddInfo( aInfo, "AppendTableAlias", bFlag ) ' and write back aDataSource.Info = aInfo ' flush (not really necessary, but to be on the safe side :) aDataSource.flush End Sub Function AddInfo( aOldInfo() as new com.sun.star.beans.PropertyValue,sSettingsName as String, aSettingsValue as Variant ) as Variant Dim nLower as Integer Dim nUpper as Integer nLower = LBound( aOldInfo() ) nUpper = UBound( aOldInfo() ) ' look if the setting is already present Dim bNeedAdd as Boolean bNeedAdd = TRUE Dim i As Integer For i = nLower To nUpper If ( aOldInfo( i ).Name = sSettingsName ) Then aOldInfo( i ).Value = aSettingsValue bNeedAdd = FALSE End If Next i ' allocate the new array Dim nNewSize as Integer nNewSize = ( nUpper - nLower ) If bNeedAdd Then nNewSize = nNewSize + 1 Dim aNewInfo( nNewSize ) as new com.sun.star.beans.PropertyValue ' copy the elements (a simply copy does not work in Basic) For i = nLower To nUpper aNewInfo( i ) = aOldInfo( i ) Next i ' append the new setting, if necessary If ( bNeedAdd ) Then aNewInfo( nUpper + 1 ).Name = sSettingsName aNewInfo( nUpper + 1 ).Value = aSettingsValue End If AddInfo = aNewInfo() End Function ---------------------------------------------------------------------------
Comment 6 r4zoli 2010-03-17 09:18:33 UTC
Just I know what caused me some disorientation, on MySQL JDBC settings "Append the table alias name on SELECT statements" not present. (May be a separate issue.) What type of database you use with JDBC connection, and which JDBC driver, which cause your problem? Summing up, the bug is: If you unselect "Append the table alias name on SELECT statements" and consequently "Use keyword AS before table alias name" grayed out (unselected), the alias remains in query, but the keyword AS removed. Workaround, edit query in SQL mode to remove alias, then query working as desired.
Comment 7 ocke.janssen 2010-12-03 06:49:22 UTC
I just tested a OOo 3.3 and there the table name isn't appended. I used a db connected to Access. Which database do you use?
Comment 8 Rob Weir 2013-02-02 02:58:55 UTC
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download Please report back the outcome of your testing, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.