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)
Have tested this with internal HSQLDB (LO 7.1.2.2) on OpenSUSE 15.2 64bit rpm Linux. There doesn't appear an alias for the table automatically. Then I tried it with MariaDB/JDBC on the same machine. It sets an alias, because the table will be defined by `database`.`tablename`. So it creates SELECT `ID`, `Name` FROM `test`.`Person` `Person` There is no option to append the tablename for MariaDB, only to set "AS" for an alias there. Have no other connection to other databases working here, so I can't test special behavior of other databases.
This bug still occurs at LibreOffice version 7.4.3.2 on OpenSuse leap 15.5 and Windows 10 To summarise: In LibreOffice Base when using Edit\Database\Advanced Settings there is an option to select/de-select "Append the table alias name on SELECT statements" as well the option "Use keyword AS before table alias names" Even when these options are "not" checked the resulting SQL query includes the aliasing of the table being selected with same name. EG: SELECT "AT_ID", "NAME1" FROM "CM" "CM" The only option related to using a table alias that is honoured is the "Use keyword AS before table alias names" When "Append the table alias name on SELECT statements" is "not" checked and and "Use keyword AS before table alias names" "is" checked: SELECT "AT_ID", "NAME1" FROM "CM" AS "CM" From this one can deduce that the option "Append the table alias name on SELECT statements" is being ignored. The database using this JDBC driver requires that if a table is aliased it be to a unique name eg T1.CM - this is commonly done in many BI tools as well Because the options are not being honoured the query has to be manually edited to remove the alias. This can get complex when there many tables in the query.