Calling a query with at least one alias defined causes syntax error.
Please provide clear simple reproducible steps and a test document. Marking as NEEDINFO - once you do the above mark as UNCONFIRMED. Thanks
Created attachment 105833 [details] test databases (.mdb and .odb) The attachement contains two databases: ms acces (mdb) and coupled with it libreoffice base (odb). Please try to run both queries from odb file. You will discover that query which contains an alias do not work properly.
Couldn't test it, because I haven't any possibility to connect to an *.mdb-file here. I will change the title, because it is misleading. Aliases with the internal HSQLDB works here without any problem.
My understanding is that this can only be tested by someone on Windows
@Andrzej : do you have an "Advanced" connection properties tab with your ODB file ? Right mouse button click on empty space in the main Query window where the list of queries is given. Choose Database > Advanced. Do you see a dialog that lets you select further options ?
Created attachment 108103 [details] Advanced connection properties tab
Does it make any difference to your problem if you click - use keyword AS before table alias names and/or - append the table alias name on select statements
No. There is no difference. I have just checked all combinantions of the options.
OK, thanks, but we still need someone else to be able to try and reproduce on Windows. I'm on OSX and Linux so can't test
I can confirm with LO 4.3.5, win7. Look at the SQL - bad quotation marks ` , should be "
Hi, I have tested with different quotation style. The issue remains the same:( But... I have discovered a strange behaviour of one of the checkboxes on "advanced connection properties" tab. Checkbox named "Use keyword AS before table alias names" despite beeing checked after exit the tab remains unchecked. Could anyone confirm it? Andrzej
Created attachment 111471 [details] Advanced settings tab
(In reply to Andrzej Sygiel from comment #11) > Hi, > I have discovered a strange behaviour of one of the checkboxes on > "advanced connection properties" tab. Checkbox named "Use keyword AS before > table alias names" despite beeing checked after exit the tab remains > unchecked. Could anyone confirm it? Hello, I can confirm with Version: 4.5.0.0.alpha0+ Build ID: 7f476fea47f06a7f8cc961dd4f6595a524346fa5 TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-12-27_23:36:28 Please create new bugreport for this issue.
New report created. Bug 87840.
Adding self to CC if not already on
I can confirm with LO 4.3.5.x but it works correct with LO 4.2.8.2. The incorrect behavior is for both non- and direct SQL. I'm using the 'Microsoft Access' driver. With 4.2.8.2 it accepts both SELECT `A`.`f` AS "F" FROM `A` SELECT `A`.`f` AS `F` FROM `A`
*** Bug 89418 has been marked as a duplicate of this bug. ***
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.5 or 5.1.2 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT - Update the version field - Reply via email (please reply directly on the bug tracker) - Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2016-04-16
Tested LO 5.1.2.2 Windows on W7 with negative results: Bug is still there. Regression: I reported before that bug does not happen with LO 4.2.8.2 (See comment 16). Additional information: It looks like that something is wrong with the parser in Base when database type is MSAccess. The reported error with 5.1.2.2 is: Syntax error (missing operator) in query expression '`A`.`f` `F`' A modification to the queries listed in comment 16 result in accepting the aliases: 1: Adding on the first field/expression an add empty string. OK: SELECT '' + `A`.`f` AS "F" FROM `A` OK: SELECT `A`.`f` + '' AS "F" FROM `A` 2: Adding selection of all columns with * OK: SELECT *, `A`.`f` AS "F" FROM `A` BUT: column ordering is "F", <other columns> iso <other columns>, "F" OK: SELECT *, `f` AS "F" FROM `A` BUT: column ordering is "F", <other columns> iso <other columns>, "F" OK: SELECT `A`.`f` AS "F", * FROM `A` FAIL: SELECT `A`.*, `A`.`f` AS "F" FROM `A` OK: SELECT `A`.*, ''+`A`.`f` AS "F" FROM `A` Note: This bug prevent every user that needs MSAccess from going to LO 5.x.
Adding keyword 'preBisect' as this regression was introduced before branch 4.4 and therefore it can't be bibisected as there's no bibisect repository for this branch.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.4.1 or 5.3.6 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170929
Given bug 107360, it is unlikely that this will be ever be fixed for 32bit MDB databases, as not even Microsoft provides the support for these anymore in its 64bit OS and drivers. LO relies on the drivers that MS provides. If the problem is still present in an all-64bit version (64bit OS, 64bit accdb >=2010, 64bit database acess drivers, 64bit LO) then it might be worth revisiting the question. I suggest closing this, and other similar reports as either WONTFIX, or NOTOURBUG, or else WFM in 64bit everything and Access >= 2010.
I validated the reported issue against LO 5.4.2.2 (x64) with an Access 2007 database (*.accdb). The issue remains the same.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Bug is still available ----------------------- Version: 6.0.5.2 (x64) Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16 CPU threads: 4; OS: Windows 6.1; UI render: default; Locale: en-US (en_US); Calc: group Regression ---------- In comment 16 is reported that it worked with version LO 4.2.8.2. NOTE ---- I tested this not with an old MS Access database, but with an MS Access 2007 database.
Unfortunately, the bug still exists in LO 6.1.1.2 running on Windows 10 64 bit. 'AS' statement doesn't work with database in MS Access 2003 format. Also "use keyword AS before table alias names" in database advanced options still unchecked...
Tried with bibisect repo win32-4.3: the first commit already gives me failed query. The data content could not be loaded. Syntax error (missing operator) in query expression '`Name` `Given Name`' So this is 4.2.0.0alpha1 and I don't understand, why 4.2.8 would work. I did not modify the SQL in any way.
Dear Andrzej Sygiel, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
bibisected with win32-4.3 SELECT `Name` AS `Given Name`, `Surname` FROM `Table1` Version: 4.3.1.0.0+ Build ID: 0d5d8c22f7be41d408d8ee4012ef1a6f4368423e author Julien Nabet commit 0d5d8c22f7be41d408d8ee4012ef1a6f4368423e Resolves fdo#81213: Wrong table-clause generated by reportbuilder Oracle doesn't accept "AS" for making an alias from a table See https://bugs.freedesktop.org/show_bug.cgi?id=81213#c2 https://bugs.freedesktop.org/show_bug.cgi?id=81213#c4 https://bugs.freedesktop.org/show_bug.cgi?id=81213#c5 for more information (thank you Lionel) Cherry-picked from 568778874429595855b435792e5ebecd52956dae Change-Id: I33c86d78b2590116d4af46ffd3e54c3c791268ea Reviewed-on: https://gerrit.libreoffice.org/10469 *** In win32-4.3oldest, a SELECT statement without AS resulted in a syntax error. Version: 4.2.0.0.alpha1+ Build ID: fc8f44e82de4ebdd50ac5fbb9207cd1a59a927e3 SELECT `Name` `Given Name`, `Surname` FROM `Table1`
Let's ask Julien what he thinks.
(In reply to Buovjaga from comment #31) > Let's ask Julien what he thinks. I gave it a try with: https://gerrit.libreoffice.org/c/core/+/186210 Perhaps just reverting partly the patch for ado part could make it.
(In reply to Andrzej Sygiel from comment #8) > No. There is no difference. I have just checked all combinantions of the > options. Note that these options probably do not take effect immediately, but require at least closing the .odb file and reopening it (and on GNU/Linux at least there is an annoying bug that even though the .odb file looks closed, it is not closed until all of LibreOffice is closed, so requires actually restarting LibreOffice; possibly the Microsoft Windows version of LibreOffice does not have that bug). If you use a saved query (either explicitly as a query saved as a query in LibreOffice, or implicitly as a source of report or form or controlbox or some such, you will also need to force a refresh of the query SQL text: just make any change to the query, save it, make the opposite change, save it again. Then rerun the query / report / ... again.
Fundamentally, we have to set one of the behaviours as default: either putting the AS or not putting it, and people that need the opposite from the default must set the behaviour they require in the advanced options. It seems to me that since the vast majority of DBMS work fine without the "AS", and a sizeable amount require the *absence* of the AS, not putting the "AS" is the correct default, as we did in 2014 in issue 81213, as that will give maximum compatibility. If I recall correctly, not putting the "AS" is the SQL standard conformant behaviour, but my memory might be corrupted on that. If (with the caveats in comment 33 for proper testing) the checkbox in the "advanced settings" do not work, and LibreOffice does NOT put the "AS" even though the checkbox telling it to do so is checked, then *that* is the bug that needs to be corrected, not changing the default back and forth each time a user files a bug...
By the way, Version: 4.5.0.0.alpha0+ Build ID: fef5c2b81d1ac7d64e01521da792f470b03a1841 The Advanced Settings dialog has been changed.