Bug 96372 - Filters do not work on Hsqldb subquery result when subquery has alias.
Summary: Filters do not work on Hsqldb subquery result when subquery has alias.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Queries
  Show dependency tree / graph
 
Reported: 2015-12-09 23:26 UTC by Saren Tasciyan
Modified: 2022-12-07 07:16 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
Example DB of this issue (3.06 MB, application/zip)
2015-12-13 13:44 UTC, Saren Tasciyan 		Details
Start the queries - try the auto-filter (3.78 KB, application/vnd.oasis.opendocument.database)
2015-12-13 18:08 UTC, Robert Großkopf 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Saren Tasciyan 2015-12-09 23:26:50 UTC 
Hi,

I have a wierd problem with Libreoffice Base. I had a working database and forms. One of the forms has nested subforms, which are linked to master form via master and slave fields. Those forms acquire their data through queries. I wanted to have row numbers in one of my query, which was not supported by the default hsqldb provided by Libreoffice. Therefore I used a newer version. Everything worked and I could generate a column with row numbers in my query:

SELECT ROWNUM( ) "Pos", "b".* FROM ( SELECT "Services"."ID", "Services"."InvoiceID", "Services"."ProductID", "Services"."Count", "Products"."ID", "Products"."Name", "Products"."Description", "Products"."Price", "Products"."Price" * "Products"."Count" "Total" FROM "Products", "Products" WHERE "Services"."ProductID" = "Products"."ID" ) AS "b"

which is linked to a query:
SELECT "Invoices"."ID", .....

They are linked as such:
Master: "ID"
Slave: "Invoice"."ID"

But I get this following error from HSQLDB:
SQL Status: 42501
Error code: -5501

user lacks privilege or object not found: SYSTEM_SUBQUERY.InvoiceID

I think the problem is the subquery in "b".*, which somehow worked before but not after the update. I did not use that database for a long time. So I am not sure if is really after the update. But at LO 4.x.x it used to work.

Now the interesting part is, if I simply use the sub query alone:
SELECT "Services"."ID", "Services"."InvoiceID", "Services"."ProductID", "Services"."Count", "Products"."ID", "Products"."Name", "Products"."Description", "Products"."Price", "Products"."Price" * "Products"."Count" "Total" FROM "Products", "Products" WHERE "Services"."ProductID" = "Products"."ID"

It works perfectly but then I can't have the ROWNUM() function.
I suppose this is a bug from LO but if it is not, please correct me.
Comment 1 Alex Thurgood 2015-12-10 10:37:33 UTC 
@Saren : we would really need a minimal test ODB file that worked before in 4.4.x, and now no longer works in 5.x

I am assuming that you used the default embedded hsqldb 1.8, but if you are using the ROWNUM() function then you have switched to an external hsqldb.jar (1.2 or 1.3 ?)

Please provide more detailed information on your setup and the test file, otherwise it is unlikely that we will be able to reproduce the behaviour.
Comment 2 Alex Thurgood 2015-12-10 10:39:50 UTC 
In fact, a quick search in Google reveals that ROWNUM() only became available in hqldb 2.2, so you are necessarily using an external hsqldb.jar or else you have bound it in some way (replaced the original LO provided hsqldb.jar ?)
Comment 3 Saren Tasciyan 2015-12-10 11:24:47 UTC 
Hi Alex,

Thanks for the help. I can immediately tell you that I used a split database and added a more recent version of hsqldb.jar (can't really remember if it was 2.2 or 2.3) to the classpath of LO. As the issue occurred I downloaded 2.3.3 and added that to LO (had a different file size then the previous one), which did not work either.

When I am back to my other computer, I will make my file anonymous, mask the data and add it as attachment. Is this sufficient? Is it also OK that my file is in German? I could also change field names etc... if necessary. I translated the SQL commands posted here to make it more "understandable". Maybe, you prefer to have the original file.
Comment 4 Alex Thurgood 2015-12-10 11:48:55 UTC 
(In reply to Saren Tasciyan from comment #3)


> 
> When I am back to my other computer, I will make my file anonymous, mask the
> data and add it as attachment. Is this sufficient? Is it also OK that my
> file is in German? I could also change field names etc... if necessary. I
> translated the SQL commands posted here to make it more "understandable".
> Maybe, you prefer to have the original file.


German is fine, I can understand it. More problematic for me might be tying in the latest hsqldb.jar, as there were issues with that on OSX and LO, and I don't know whether they've been solved (I fear not), in which case I won't be able to test.
Comment 5 Saren Tasciyan 2015-12-10 12:27:43 UTC 
I will simply send you the whole folder with the DB and 2 hsqldb.jar files (new one and the latest one), maybe the slightly older one works in OS X.
Comment 6 Saren Tasciyan 2015-12-13 13:44:32 UTC 
Created attachment 121257 [details]
Example DB of this issue

README:
Besides the DB itself, there are 2 versions of hsqldb.jar and sqltool.jar libraries. The "hsqldb.jar" and "sqltool.jar" are the most recent ones (2.3.3). The *-bit_older.jar libraries support ROWNUM function (so they must be 2.2 or newer) but I do not know exactly which version they are.

The issue occurs in "Rechnungen - Viewer" form, which uses certain queries for data retrieval.
Comment 7 Robert Großkopf 2015-12-13 17:13:04 UTC 
I have tried this:
Open the query "Query_Rechnung_Leistungen"
Execute the query.
Go to any field and try to start AutoFilter.

Filter doesn't work.

Now try this:
Query: SELECT * FROM (SELECT "Leistungen"."ID" FROM "Leistungen") AS "a"

Filter doesn't work also.

This bug appears any time you will filter a query with a subquery.

Now try this:
Query: SELECT * FROM "Query_Rechnung_Leistungen"

Filter works in this query, which shows the whole content of the original query. You could use this query in the form and everything will work. No Idea why it won't work directly with the query ...

I will set this bug to "NEW" and test a little bit if it's an old bug ...
Comment 8 Robert Großkopf 2015-12-13 17:14:52 UTC 
Forgotten to write down my system:
OpenSUSE Leap 42.1, 64bit rpm Linux, tested with LO 5.0.2.2 (Version supported by SUSE)
Comment 9 Robert Großkopf 2015-12-13 18:08:24 UTC 
Created attachment 121271 [details]
Start the queries - try the auto-filter

The buggy behavior appears with every version I have installed, also old versions like LO 3.6.7 or LO 4.1.6.

The bug appears with a subquery. 
If the subquery has no alias set the query could be filtered by auto-filter.
If there is set an alias it won't work.

1 - Works:
SELECT * FROM (SELECT * FROM "Table")
2 - Works not:
SELECT "a".* FROM (SELECT * FROM "Table") AS "a"

The buggy behavior has nothing to do with the external database.
Together with the internal database LO will be very instable - could be it crashes after trying to filter the second query.
Comment 10 Saren Tasciyan 2016-01-06 22:03:17 UTC 
Thanks for the assistance. I could solve the issue as you have shown - by encapsulating query and calling it from another one. Now it works. I am happy to be able to contribute to LibreOffice.
Comment 11 Alex Thurgood 2016-01-07 15:19:49 UTC 
So, bug or no bug ?
Comment 12 Saren Tasciyan 2016-01-07 22:10:57 UTC 
I would say it is a bug but there is a workaround.
Comment 13 Alex Thurgood 2016-01-08 12:55:37 UTC 
Per Robert's comment, setting to Inherited from OOo
Comment 14 QA Administrators 2017-03-06 14:45:06 UTC Comment hidden (obsolete)
Comment 15 Robert Großkopf 2017-03-06 20:07:47 UTC 
Bug still exists with LO 5.3.1.1, OpenSUSE 42.1 Leap, 64bit rpm Linux.
Comment 16 QA Administrators 2018-03-07 03:42:12 UTC Comment hidden (obsolete)
Comment 17 QA Administrators 2020-12-06 04:03:05 UTC Comment hidden (obsolete)
Comment 18 QA Administrators 2022-12-07 03:22:31 UTC Comment hidden (obsolete)
Comment 19 Robert Großkopf 2022-12-07 07:16:14 UTC 
Bug is still the same in LO 7.4.3.2 on OpenSUSE 15.3 64bit rpm Linux.