Issue 110117

Ian,
What is the difference in query results?

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

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.

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

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: frank.schoenheit@sun.com <frank.schoenheit@sun.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: frank.schoenheit@sun.com
      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

---------------------------------------------------------------------------

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.

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?

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.