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@lists.freedesktop.org
Reporter: ianbstu...@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._______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
