Recent

Author Topic: Problems sorting a DataSet  (Read 3138 times)

jciberta

  • Newbie
  • Posts: 6
Problems sorting a DataSet
« on: May 11, 2021, 05:35:38 pm »
I have problems sorting a DataSet with IndexFieldNames. I don't know if it's a bug, or I'm doing something wrong.

The code is:
Code: Pascal  [Select][+][-]
  1.   //SQLQuery1.IndexFieldNames:='Title DESC'; // Works fine!
  2.   //SQLQuery1.IndexFieldNames:='Title; LastName'; // Works fine!
  3.   SQLQuery1.IndexFieldNames:='Title; LastName DESC'; // WRONG: Order Title by DESC!    
  4.  

I'm using Ubuntu 18.04 with Lazarus 2.0.12 (the newest at this moment). The database engine is SQLite with Chinook database (https://github.com/lerocha/chinook-database).

A sample program is attached.

I need to know if IndexFieldNames works properly or if there is a bug. Change SQL is not an option.

Thanks in advance


jciberta

  • Newbie
  • Posts: 6
Re: Problems sorting a DataSet
« Reply #1 on: May 12, 2021, 05:29:41 pm »
I just find out if you set the value of IndexFieldNames twice, it works.

That is:

Code: Pascal  [Select][+][-]
  1.   //SQLQuery1.IndexFieldNames:='Title DESC'; // Works fine!
  2.   //SQLQuery1.IndexFieldNames:='Title; LastName'; // Works fine!
  3.   SQLQuery1.IndexFieldNames:='Title; LastName DESC'; // WRONG: Order Title by DESC!
  4.   SQLQuery1.IndexFieldNames:='Title; LastName DESC'; // Do it twice -> Works properly!
  5.  

So, it seems to me a bug. I do not know how to report it, any help would be appreciated

Lutz Mändle

  • Jr. Member
  • **
  • Posts: 65
Re: Problems sorting a DataSet
« Reply #2 on: May 12, 2021, 07:17:54 pm »
The string for IndexFieldNames should not contain any other spaces then before DESC, like this:

Code: Pascal  [Select][+][-]
  1. SQLQuery1.IndexFieldNames:='Title;LastName DESC';
  2.  

jciberta

  • Newbie
  • Posts: 6
Re: Problems sorting a DataSet
« Reply #3 on: May 12, 2021, 08:20:50 pm »
Still not working. It persists in order Title by DESC.

I tried now on a Windows 10, Lazarus 2.0.10. The same behaviour

MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
Re: Problems sorting a DataSet
« Reply #4 on: May 12, 2021, 08:29:58 pm »
So, it seems to me a bug. I do not know how to report it, any help would be appreciated

You'll find https://bugs.freepascal.org/bug_report_page.php fairly painless. Sorry I've not been able to help any more than that.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

GAN

  • Sr. Member
  • ****
  • Posts: 370
Re: Problems sorting a DataSet
« Reply #5 on: May 12, 2021, 09:51:02 pm »
Did you try something like this:

Code: Pascal  [Select][+][-]
  1.  SQLQuery1.IndexFieldNames:='Title ASC ; LastName DESC';
  2.  
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: Problems sorting a DataSet
« Reply #6 on: May 12, 2021, 10:31:50 pm »
I just find out if you set the value of IndexFieldNames twice, it works.

That is:

Code: Pascal  [Select][+][-]
  1.   //SQLQuery1.IndexFieldNames:='Title DESC'; // Works fine!
  2.   //SQLQuery1.IndexFieldNames:='Title; LastName'; // Works fine!
  3.   SQLQuery1.IndexFieldNames:='Title; LastName DESC'; // WRONG: Order Title by DESC!
  4.   SQLQuery1.IndexFieldNames:='Title; LastName DESC'; // Do it twice -> Works properly!
  5.  

So, it seems to me a bug. I do not know how to report it, any help would be appreciated

I think it's a bug too. I tried with ZeosDBO components and it seems to work fine there.

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: Problems sorting a DataSet
« Reply #7 on: May 12, 2021, 10:33:20 pm »
Did you try something like this:

Code: Pascal  [Select][+][-]
  1.  SQLQuery1.IndexFieldNames:='Title ASC ; LastName DESC';
  2.  

There is an error if you use ASC ('Field not found: "Title ASC"').

jciberta

  • Newbie
  • Posts: 6
Re: Problems sorting a DataSet
« Reply #8 on: May 13, 2021, 01:44:51 pm »
Thank you to you all.

I'm gonna report the bug. Temporary I fixed it doing it twice.

I tried to debug unit BufDataset, but I don't know how to do it. My guess is something is not right in function SetIndexFieldNames and BuildCustomIndex is not called the first time

Code: Pascal  [Select][+][-]
  1. procedure TCustomBufDataset.SetIndexFieldNames(const AValue: String);
  2. begin
  3.   FIndexFieldNames:=AValue;
  4.   if (AValue='') then
  5.     begin
  6.     FCurrentIndexDef:=FIndexes.FindIndex(SDefaultIndex);
  7.     Exit;
  8.     end;
  9.   if Active then
  10.     BuildCustomIndex;
  11. end;
  12.  


MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
Re: Problems sorting a DataSet
« Reply #9 on: May 13, 2021, 02:32:47 pm »
Temporary I fixed it doing it twice.

I tried to debug unit BufDataset, but I don't know how to do it. My guess is something is not right in function SetIndexFieldNames and BuildCustomIndex is not called the first time

I'm surprised that IndexFieldNames is cumulative... it's not documented as such at https://www.freepascal.org/docs-html/fcl/bufdataset/tcustombufdataset.indexfieldnames.html and the only hint is that '' is suggested as removing all file names not just one. I suggest that you point that out in your bug report.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

jciberta

  • Newbie
  • Posts: 6
Re: Problems sorting a DataSet
« Reply #10 on: May 13, 2021, 04:32:19 pm »
Thanks MarkMLl. I took a look deeper and I think the first guess was wrong because it does some order with the data the fisrt time (wrong order though), so BuildCustomIndex is called as well


MarkMLl

  • Hero Member
  • *****
  • Posts: 6676
Re: Problems sorting a DataSet
« Reply #11 on: May 13, 2021, 05:12:48 pm »
Just report it. You've done exactly the right thing raising it here first so that if you were doing something obviously wrong people could correct you, but the maintainer of that part might not use the forum... there's also mailing lists etc.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

Lutz Mändle

  • Jr. Member
  • **
  • Posts: 65
Re: Problems sorting a DataSet
« Reply #12 on: May 13, 2021, 05:26:22 pm »
The problem is in TCustomBufDataset.BuildCustomIndex in the unit .../fpcsrc/3.2.0/packages/fcl-db/src/base/bufdataset.pas

Changing the following sequence
Code: Pascal  [Select][+][-]
  1. ..
  2.   F.Fields:=SortFields;
  3.   F.Options:=[];
  4.   F.DescFields:=DescFields;
  5. ..
  6.  

to

Code: Pascal  [Select][+][-]
  1. ..
  2.   F.Fields:=SortFields;
  3.   F.DescFields:=DescFields;
  4.   F.Options:=[];
  5. ..
  6.  

solves the problem, but maybe arises some other inconsistencies.

That a second assignment to the IndexFieldNames seems to solve the problem is a side effect of how TIndexDef.SetDescFields in the unit .../fpcsrc/3.2.0/packages/fcl-db/src/base/db.pas works. It adds the value ixDescending to the index options, but only if the new assigned value is different from the former value and not empty.

Edit:
It is possible to debug bufdataset.pas by including the path in project options->compiler options->paths>other unit files (in my case /usr/share/fpcsrc/3.2.0/packages/fcl-db/src/base/).
« Last Edit: May 13, 2021, 06:09:11 pm by Lutz Mändle »

jciberta

  • Newbie
  • Posts: 6
Re: Problems sorting a DataSet
« Reply #13 on: May 13, 2021, 06:10:15 pm »
Just report it. You've done exactly the right thing raising it here first so that if you were doing something obviously wrong people could correct you, but the maintainer of that part might not use the forum... there's also mailing lists etc.

MarkMLl


I reported earlier. https://bugs.freepascal.org/view.php?id=38884

The problem is in TCustomBufDataset.BuildCustomIndex in the unit .../fpcsrc/3.2.0/packages/fcl-db/src/base/bufdataset.pas

Changing the following sequence
Code: Pascal  [Select][+][-]
  1. ..
  2.   F.Fields:=SortFields;
  3.   F.Options:=[];
  4.   F.DescFields:=DescFields;
  5. ..
  6.  

to

Code: Pascal  [Select][+][-]
  1. ..
  2.   F.Fields:=SortFields;
  3.   F.DescFields:=DescFields;
  4.   F.Options:=[];
  5. ..
  6.  

solves the problem, but maybe arises some other inconsistencies.

That a second assignment to the IndexFieldNames seems to solve the problem is a side effect of how TIndexDef.SetDescFields in the unit .../fpcsrc/3.2.0/packages/fcl-db/src/base/db.pas works. It adds the value ixDescending to the index options, but only if the new assigned value is different from the former value and not empty.




Great! I'm gonna add the issue this additional information to help the mantainer




 

TinyPortal © 2005-2018