Bug 47370 - EDITING: queries mangle sorting-order when open for reediting
Summary: EDITING: queries mangle sorting-order when open for reediting
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Database (show other bugs)
Version: 3.4.4 release
Hardware: All All
: medium critical
Assignee: Lionel Elie Mamane
QA Contact:
URL:
Whiteboard: target:3.6.0 target:3.5.2
Keywords:
Depends on: 46843
Blocks: mab3.5 47560
  Show dependency treegraph
 
Reported: 2012-03-15 11:07 UTC by Lionel Elie Mamane
Modified: 2012-03-20 04:50 UTC (History)
1 user (show)

See Also:
i915 platform:
i915 features:


Attachments

Description Lionel Elie Mamane 2012-03-15 11:07:21 UTC
Found in versions:
 * OpenOffice.org 3.3
 * 3.4.4
 * branch libreoffice-3-5 with fix for bug 46843 applied.

Reprodcution case: attachment 57874 [details] from bug 46843

Open query order_job_name in SQL mode. Note that it orders first by job, then by name. Open in design mode (graphical editor). Order goes to "first by name then by job", because name is before job as a  result column.

The graphical editor should duplicate the "name" column, as in:

column:   name      job    name
table:    tbl1      tbl1   tbl1
sort:     (none)    asc    asc
display:   yes      yes    no
Comment 1 Julien Nabet 2012-03-15 14:08:30 UTC
On Debian pc x86-64, master (updated today), I reproduce this behaviour.

Now just some ideas :
1) Since what you said here http://cgit.freedesktop.org/libreoffice/core/commit/?id=80c235510aeb19d4df6a07be7499e70122313bbf, it could be useful we avoid this kind of things :
if ( pTableExp->getChild(6)->count() > 0 || pTableExp->getChild(7)->count() > 0 || pTableExp->getChild(8)->count()

Now I know it's easy to say, less easy to do :-)
egrep "getChild[0-9]" may be a good start

2) 
Context :
i runned on gdb to better understand how it works. I understood that fields are inserted in designquery by :
"InstallFields" located in "dbaccess/source/ui/querydesign/QueryDesignView.cxx" (all the functions quoted are in this same file)

then order of columns is added by "GetOrderCriteria"

Both functions are called by "InitFromParseNodeImpl"

Here are the interesting lines :
   2351                         OTableFields::iterator aIter = aList.begin();
   2352                         OTableFields::iterator aEnd = aList.end();
   2353                         for(;aIter != aEnd;++aIter)
   2354                         {
   2355                             OTableFieldDescRef pEntry = *aIter;
   2356                             if(pEntry.is() && pEntry->GetFieldAlias() == aColumnName)
   2357                                 pEntry->SetOrderDir( eOrderDir );
   2358                         }
   2359                     }

So we search the column with the iter loop and add the sort once the column is found. (it explains the pb we have) 


What about doing that ?
if pNode->count() = 1 (ie there's only 1 field in ORDER BY), we let the current behaviour, it's sufficient and correct.

else : (order by list contains more than 1 element)
we search the column with the iter and once found :
- we clone it at the end of aList
- add order one the cloned element
- remove visible attribute of the cloned element

So we would have the columns with visibility first, then columns with order ("in good order") with no visibility.
 
Hope I was clear :-)
Comment 2 Lionel Elie Mamane 2012-03-15 15:00:18 UTC
(In reply to comment #1)

> What about doing that ?
> if pNode->count() = 1 (ie there's only 1 field in ORDER BY), we let the current
> behaviour, it's sufficient and correct.
> 
> else : (order by list contains more than 1 element)
> we search the column with the iter and once found :
> - we clone it at the end of aList
> - add order one the cloned element
> - remove visible attribute of the cloned element

I'm implementing the following:

Sorting columns s=(s_1, s_2, s_3, s_4, ..., s_n)
Visible columns v=(v_1, v_2, v_3, ... v_p)

Let p = (s_1, s_2, ..., s_i) be the longest prefix of s that is a subsequence of v, i.e.:

 s_1 = v_j1
 s_2 = v_j2 && j2 > j1
 s_3 = v_j3 && j3 > j2
 etc


We put the sorting order directly on columns s_1, ... , s_i. We duplicate at end in invisible mode columns s_{i+1} to s_n.
Comment 3 Not Assigned 2012-03-20 03:10:10 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=470feb03f11e015b69fc5df11bd141f536eb8c91

fdo#47370 properly duplicate (invisible) out-of-order sort columns
Comment 4 Not Assigned 2012-03-20 04:42:21 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7e0be5ebd688a5e8c2faa8f097f104c89139efeb&g=libreoffice-3-5

fdo#47370 properly duplicate (invisible) out-of-order sort columns


It will be available in LibreOffice 3.5.2.


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.