Download it now!
Bug 83454 - Aliases do not work in queries of a database connected to a *.mdb-file
Summary: Aliases do not work in queries of a database connected to a *.mdb-file
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.1.2 release
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: preBibisect, regression
: 89418 (view as bug list)
Depends on:
Blocks: Database-MS_Access
  Show dependency treegraph
 
Reported: 2014-09-03 19:54 UTC by Andrzej Sygiel
Modified: 2020-09-20 20:24 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
test databases (.mdb and .odb) (13.43 KB, application/zip)
2014-09-06 09:41 UTC, Andrzej Sygiel
Details
Advanced connection properties tab (113.42 KB, image/png)
2014-10-20 09:47 UTC, Andrzej Sygiel
Details
Advanced settings tab (34.25 KB, image/png)
2014-12-29 14:47 UTC, Andrzej Sygiel
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andrzej Sygiel 2014-09-03 19:54:49 UTC
Calling a query with at least one alias defined causes syntax error.
Comment 1 Joel Madero 2014-09-03 20:56:09 UTC
Please provide clear simple reproducible steps and a test document.

Marking as NEEDINFO - once you do the above mark as UNCONFIRMED. Thanks
Comment 2 Andrzej Sygiel 2014-09-06 09:41:16 UTC
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.
Comment 3 Robert Großkopf 2014-09-30 19:03:12 UTC
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.
Comment 4 Alex Thurgood 2014-10-20 07:21:17 UTC
My understanding is that this can only be tested by someone on Windows
Comment 5 Alex Thurgood 2014-10-20 07:26:56 UTC
@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 ?
Comment 6 Andrzej Sygiel 2014-10-20 09:47:10 UTC
Created attachment 108103 [details]
Advanced connection properties tab
Comment 7 Alex Thurgood 2014-10-20 14:09:12 UTC
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
Comment 8 Andrzej Sygiel 2014-10-20 15:12:57 UTC
No. There is no difference. I have just checked all combinantions of the options.
Comment 9 Alex Thurgood 2014-10-20 16:21:01 UTC
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
Comment 10 raal 2014-12-29 12:19:51 UTC
I can confirm with LO 4.3.5, win7.
Look at the SQL -  bad quotation marks ` , should be "
Comment 11 Andrzej Sygiel 2014-12-29 14:46:21 UTC
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
Comment 12 Andrzej Sygiel 2014-12-29 14:47:05 UTC
Created attachment 111471 [details]
Advanced settings tab
Comment 13 raal 2014-12-29 16:42:52 UTC
(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.
Comment 14 Andrzej Sygiel 2014-12-29 21:05:06 UTC
New report created. Bug 87840.
Comment 15 Alex Thurgood 2015-01-03 17:40:37 UTC Comment hidden (no-value)
Comment 16 eremmel 2015-01-17 18:43:57 UTC
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`
Comment 17 raal 2015-02-20 20:35:10 UTC
*** Bug 89418 has been marked as a duplicate of this bug. ***
Comment 18 tommy27 2016-04-16 07:27:26 UTC Comment hidden (obsolete)
Comment 19 eremmel 2016-04-16 12:29:17 UTC
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.
Comment 20 Xisco Faulí 2016-09-13 11:26:35 UTC
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.
Comment 21 Xisco Faulí 2017-09-29 08:50:28 UTC Comment hidden (obsolete)
Comment 22 Alex Thurgood 2017-10-20 14:09:25 UTC
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.
Comment 23 eremmel 2017-10-21 07:20:54 UTC
I validated the reported issue against LO 5.4.2.2 (x64) with an Access 2007 database (*.accdb). The issue remains the same.
Comment 24 QA Administrators 2018-10-22 02:50:38 UTC Comment hidden (obsolete)
Comment 25 eremmel 2018-10-22 15:31:33 UTC
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.
Comment 26 Anton Vakulenko 2019-02-05 11:01:37 UTC
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...
Comment 27 Buovjaga 2019-04-15 15:41:12 UTC
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.