Bug Hunting Session
Bug 60504 - EDITING: Sort removes brackets in Queries - Sorting doesn't work in correlated Subqueries
Summary: EDITING: Sort removes brackets in Queries - Sorting doesn't work in correlate...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: Other All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:4.2.0 target:4.1.0 target:4.0.5
Keywords:
Depends on: 61166
Blocks:
  Show dependency treegraph
 
Reported: 2013-02-08 16:57 UTC by Robert Großkopf
Modified: 2013-07-11 11:46 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2013-02-08 16:57:32 UTC
https://bugs.freedesktop.org/attachment.cgi?id=71964

Two queries. One works with "Sort" in every field, the other works only with "Sort Ascending" and "Sort Descending" in the subquery.

Create a query with a correlating subquery. 
I have made this in the attachment:
SELECT "ID", "Revenue", "Expense", ( SELECT SUM( "Revenue" ) - SUM( "Expense" ) FROM "Checkout" WHERE "ID" <= "a"."ID" ) AS "Balance" FROM "Checkout" AS "a"
Set "Sort" for some fields.
"Sort" works with all fields, except the correlating subquery. It fails in the subquery with 
SELECT "ID", "Revenue", "Expense", ( SELECT SUM( "Revenue" ) - SUM( "Expense" ) FROM "Checkout" WHERE "ID" <= "a"."ID" ) AS "Balance" FROM "Checkout" AS "a" ORDER BY SELECT SUM( "Revenue" ) - SUM( "Expense" ) FROM "Checkout" WHERE "ID" <= "a"."ID" ASC
The brackets around the subquery were removed.
If you set two brackets before and after the subquery, only one bracket is removed and "Sort" works.
SELECT "ID", "Revenue", "Expense", (( SELECT SUM( "Revenue" ) - SUM( "Expense" ) FROM "Checkout" WHERE "ID" <= "a"."ID" )) AS "Balance" FROM "Checkout" AS "a"
works as expected for setting "Sort" to the subquery.

Seems to be the same behavior as https://bugs.freedesktop.org/show_bug.cgi?id=58644
Comment 1 Julien Nabet 2013-02-19 21:33:56 UTC
On pc Debian x86-64 with master sources updated today, I have no values for balance column in both queries.
I've got these kinds of logs too at the opening of queries:
warn:connectivity.parse:5289:1:workdir/unxlngx6/YaccTarget/connectivity/source/parse/sqlbison.cxx:9272: connectivity::OSQLParser::RuleIDToRule cannot reverse-lookup rule. Reverse mapping incomplete? _nRule='489' yytname[_nRule]='opt_window_clause'
warn:connectivity.parse:5289:1:workdir/unxlngx6/YaccTarget/connectivity/source/parse/sqlbison.cxx:9272: connectivity::OSQLParser::RuleIDToRule cannot reverse-lookup rule. Reverse mapping incomplete? _nRule='387' yytname[_nRule]='opt_limit_offset_clause'
warn:connectivity.parse:5289:1:workdir/unxlngx6/YaccTarget/connectivity/source/parse/sqlbison.cxx:9272: connectivity::OSQLParser::RuleIDToRule cannot reverse-lookup rule. Reverse mapping incomplete? _nRule='378' yytname[_nRule]='opt_result_offset_clause'
warn:connectivity.parse:5289:1:workdir/unxlngx6/YaccTarget/connectivity/source/parse/sqlbison.cxx:9272: connectivity::OSQLParser::RuleIDToRule cannot reverse-lookup rule. Reverse mapping incomplete? _nRule='383' yytname[_nRule]='opt_fetch_first_clause'
Comment 2 Robert Großkopf 2013-02-20 13:13:52 UTC
(In reply to comment #1)
> On pc Debian x86-64 with master sources updated today, I have no values for
> balance column in both queries.

You are right. The next bug in LO 4.0.*. I have tested https://bugs.freedesktop.org/attachment.cgi?id=71964 with LO up to 3.6.5.2; with all versions the queries of the attachment will work.
LO 4.0 doesn't show any value in the balance column.
Comment 3 Lionel Elie Mamane 2013-07-09 21:19:40 UTC
Cannot reproduce in recent master. Assuming fixed by:

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f1bde8cb0e00770e4f9a2e1cd2458906a7ec8a63 for LibreOffice 4.1

http://cgit.freedesktop.org/libreoffice/core/commit/?id=cfe27d4a86fdcaedcfc422382e3da09abc429603 for LibreOffice 4.0.1


Reopen if you can reproduce in newer versions.

(In reply to comment #1)
> On pc Debian x86-64 with master sources updated today, I have no values for
> balance column in both queries.

Also cannot reproduce that. Has someone opened another bug for that? I yes, please add me to CC. If not, make a new bug (and add me to CC) if you can reproduce with newer version.
Comment 4 Robert Großkopf 2013-07-11 05:58:01 UTC
Could be it isn't described in the way to reproduce.
Load down
https://bugs.freedesktop.org/attachment.cgi?id=71964
Open the database, query "Balance"
Start the dialog for "sorting" - not the quick sort up and down.
Chose the field "Balance".
Try to sort it - an error is shown. The brackets of the correlated subquery were removed in the sorting-order.
It also happens in LO 4.0.4.2 and, just tested, 4.1.0.2 under OpenSUSE 64bit rpm - don't think, this is a special bug of OpenSUSE.
Comment 5 Lionel Elie Mamane 2013-07-11 11:46:13 UTC
Still cannot reproduce on recent master, even with the new instructions. Assuming this has the same underlying reason, and was fixed by the same code change as bug 58644.

Reopen of you can reproduce in any version that has bug 58644 fixed.