Item8685: Table sorting incorrect for numbers with decimal places

pencil
Priority: Normal
Current State: Closed
Released In: 1.0.10
Target Release: patch
Applies To: Extension
Component: TablePlugin
Branches:
Reported By: VickiBrown
Waiting For:
Last Change By: KennethLavrsen
According to the TablePlugin docs:

The type of data in the cell is determined automatically:

  • date if format is:
    • dd MMM YYYY - hh:mm (MMM is Jan, Feb, etc
    • dd-MMM-YY or dd-MMM-YYYY (can be / or space in place of -) (MMM is Jan, Feb, etc)
  • number is digits, with optional decimal point
  • otherwise treated as text

However, the following data is not sorted correctly.

%TABLE{sort="on" initsort="1"}%
| *number* |
| 1 |
| 10.1 |
| 9.99 |
| 2 |
| 2.0 |
| 20 |

numberSorted ascending
1
2
2.0
9.99
10.1
20

-- VickiBrown - 08 Mar 2010

Works correctly on trunk, that is, the unreleased TablePlugin.

You can test version 1.121, it seems to work correctly on Foswiki 1.0.9. Attached: TablePlugin.zip.

-- ArthurClemens - 08 Mar 2010

The attached v1.121 changes handling of mixed-type columns in ways I don't think you intended:

%TABLE{sort="on" initsort="1"}%
| *mostly numbers* |
| -1 |
| 0 |
| 1 |
| string |
| 3 |

mostly numbersSorted ascending
-1
0
1
3
string

I get (-1, 0, string, 3, 1) using your attached version. It's sorting numerically, but doesn't have a numeric value for "string" or "3" so it sorts them with 0.

I think _guessColumnType should unset $isNum and $isDate when it sees the string, leading to string sorting. Or if you want to tolerate some strings in otherwise-numeric columns, keep processing cells so $row->[$col]->{number} can be set for the rest of the column.

-- MegCrocker - 10 Mar 2010 (edited to fix markup munged by comment form)

Also, I think the real problem for the integers in Vicki's example is in the number-followed-by-string clause in _convertToNumberAndDate:
        elsif ( $text =~ /^\s*(-?[0-9]+)(\.[0-9]+)?/ ) {
             my $num1 = $1 || 0;
             my $num2 = $2 || 0;  # <-- this multiplies ints by 10
             $num = scalar("$num1$num2");

Your version fixes the problem for standalone ints by skipping that clause, but integers followed by strings will still fail:

number of thingsSorted ascending
1 thingy
2 thingies
2.0 thingies
9.99 thingies
10.1 thingies
20 thingies

-- MegCrocker - 10 Mar 2010

I see that this module is not correct. And unit test are lacking. I've got something to work on.

-- ArthurClemens - 10 Mar 2010

I have fixed this in Release branch. But the fix does not yet cover the sorting of different date formats where one or more dates are a year:

mixed datesSorted ascending
2001-12-23T23:59Z
20 Mar 2010
2008
2009

The fix for trunk needs a bigger refactoring: Item8700.

-- ArthurClemens - 13 Mar 2010

 

ItemTemplate edit

Summary Table sorting incorrect for numbers with decimal places
ReportedBy VickiBrown
Codebase 1.0.9, trunk
SVN Range
AppliesTo Extension
Component TablePlugin
Priority Normal
CurrentState Closed
WaitingFor
Checkins distro:839d7630d450
TargetRelease patch
ReleasedIn 1.0.10
Topic revision: r8 - 08 Sep 2010, KennethLavrsen
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy