Bug 60504

Summary:	EDITING: Sort removes brackets in Queries - Sorting doesn't work in correlated Subqueries
Product:	LibreOffice	Reporter:	Robert Großkopf <robert>
Component:	Base	Assignee:	Lionel Elie Mamane <lionel>
Status:	RESOLVED FIXED
Severity:	normal	CC:	serval2412
Priority:	medium
Version:	3.3.4 release
Hardware:	Other
OS:	All
See Also:	https://bugs.freedesktop.org/show_bug.cgi?id=58644
Whiteboard:	target:4.2.0 target:4.1.0 target:4.0.5
Crash report or crash signature:		Regression By:
Bug Depends on:	61166
Bug Blocks:

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.