[Libreoffice-bugs] [Bug 141579] New: Create Query in Design View Query Builder build SQL that aliases table name to itself causing some ODBC and JDBC database queries to fail

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=141579

            Bug ID: 141579
           Summary: Create Query in Design View Query Builder  build SQL
                    that aliases table name to itself causing some ODBC
                    and JDBC database queries to fail
           Product: LibreOffice
           Version: Inherited From OOo
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Base
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: ianbstuart at gmail.com

Description:
Hi

When defining a query in Base as described in the summary, the SQL that is
created always aliases table names to themselves.

EG

SELECT "CREF", "CHEQUE", "BANK", "AMOUNT", "PERIOD" FROM "CHEQUES" AS "CHEQUES"

The table "CHEQUES" is aliased as "CHEQUES" i.e "to itself"

The Advanced Settings UI for the database has an option to "Append the table
name on SELECT statements" - in the definition of this database the option is
not selected.

However the query is created with an alias irrespective of the "Append the
table name on SELECT statments" setting and the submitted SQL fails to return a
result set with the following error

The data content could not be loaded.
/home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/commontools/dbtools.cxx:751

UniVerse/SQL: syntax error.  Unexpected filename.  Token was "CHEQUES". Scanned
command was FROM "CHEQUES" AS "CHEQUES" 
/home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/drivers/jdbc/Object.cxx:175

However if \AS "CHEQUES"\ is removed from the SQL (by turning of the designer
and editing the SQL) or the alias is specified as a "real" alias (an alias
implying that something is known by another identity) the SQL submitted via
either ODBC or JDBC results in a query result set.

Suggestions:
Many BI tools such as Cognos will create a unique alias name - in this example
something like AS "T1.CHEQUES" :

Perhaps an option could be offered to alias a table and not just a column - see
issue 73627, or

The UI option to append a table alias, when not checked could suppress the use
of an alias




Steps to Reproduce:
1.Install the UniVerse database from Rocket Software
2.Install Rocket Software JDBC driver
3.add JDBC archive
4.create a query

Actual Results:

The data content could not be loaded.
/home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/commontools/dbtools.cxx:751

UniVerse/SQL: syntax error.  Unexpected filename.  Token was "CHEQUES". Scanned
command was FROM "CHEQUES" AS "CHEQUES" 
/home/abuild/rpmbuild/BUILD/libreoffice-7.1.1.2/connectivity/source/drivers/jdbc/Object.cxx:175

Expected Results:
The query should not have been created with the table being aliased



Reproducible: Always


User Profile Reset: No



Additional Info:
See issue 110117 in OpenOffice bug report - dates back to 2010 (and before)

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210409/c0464db3/attachment.htm>