Bug 60900 - Internal HSQLDB: nested select failed with 'ORDER' and 'LIMIT' keyword
Summary: Internal HSQLDB: nested select failed with 'ORDER' and 'LIMIT' keyword
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.6.5.2 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-02-15 14:18 UTC by Jérôme Bouat
Modified: 2013-03-01 16:21 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Queries in this database solve the problem with the internal HSQLDB in this bug-report. (4.35 KB, application/vnd.sun.xml.base)
2013-03-01 16:20 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jérôme Bouat 2013-02-15 14:18:35 UTC
I can't execute the below nested select :
---
SELECT * FROM ( SELECT * FROM "lyon" ORDER BY "eclairement_lux" DESC LIMIT 300 ) ORDER BY "eclairement_lux" ASC LIMIT 1
---
Comment 1 Robert Großkopf 2013-02-21 20:07:24 UTC
This problem couldn't be solved with the internal HSQLDB. Whith MySQL and a direct connection it works. Direct SQL to the internal HSQLDB doesn't work. In HSQLDB subselects with "ORDER BY" and "LIMIT" won't work. So the bug could only be solved by changing the internal database.
I can confirm, but I would say, that this isn't our bug. But let us see, what other people think about it.
Comment 2 Jérôme Bouat 2013-02-22 09:26:20 UTC
According to bug #31398, I can't insert my temporary results into a table in order to replace this nested select by 2 sucessives queries.

Do you know a workaround in order to implement this query only with SQL ?
Comment 3 Robert Großkopf 2013-02-22 15:28:19 UTC
(In reply to comment #2)
> 
> Do you know a workaround in order to implement this query only with SQL ?

You could create a view with 
SELECT * FROM "lyon" ORDER BY "eclairement_lux" DESC LIMIT 300
Then you could get the right order and the right limit.
Comment 4 Jérôme Bouat 2013-02-22 16:03:41 UTC
Does a view is a query ? If yes, that's what I did. However, querying the view builds a nested "select" : the "select" which is made on the view contains the "subselect" which defined the view.
Comment 5 Robert Großkopf 2013-03-01 16:20:59 UTC
Created attachment 75743 [details]
Queries in this database solve the problem with the internal HSQLDB in this bug-report.

The problem with the nested select and keywords "ORDER"and "LIMIT" couldn't be solved by LO when using the internal HSQLDB. The database shows a way to get the same result as wished in this bug-report. So I will change this report to "NOTOURBUG". Hope, that this is the right decision for the reporter, too. If not, feel free to reopen this bug.