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 | Reporter: | ianbstuart |
Component: | Base | Assignee: | Not Assigned <libreoffice-bugs> |
Status: | UNCONFIRMED --- | ||
Severity: | normal | CC: | robert |
Priority: | medium | ||
Version: | Inherited From OOo | ||
Hardware: | All | ||
OS: | All | ||
Whiteboard: | |||
Crash report or crash signature: | Regression By: | ||
Bug Depends on: | |||
Bug Blocks: | 104790 |
Description
ianbstuart
2021-04-09 07:44:35 UTC
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. |