Download it now!
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 treegraph
 
Reported: 2015-12-09 23:26 UTC by Saren Tasciyan
Modified: 2018-12-06 17:05 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

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
** 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