A custom column built from other columns: simple math operations

[Joanna]

@BetterRed: gonna check this! Right now I am downloading and installing English-US locale for the Win 8 computer .
The strangest thing is that sorting works just fine for any other columns.

// EDIT

@BetterRed: I've checked my Windows settings. Well, as expected they're a bit different: ',' as the decimal symbol, [blank] as the digit grouping symbol, ';' as the list separator. However, this has never seemed to be an issue for Calibre, which uses '.' as the decimal symbol and ',' as the digit grouping symbol (probably because I have Calibre in English).

[Joanna]

OK, it was the locale after all. Thanks to @BetterRed the exact culprit has just been identified.

If I change the Windows settings for numbers (as above), sorting works fine.

Now the question is:

what to do so that I can keep my Windows settings and sorting still works in Calibre?

Strangely enough, this affects exclusively sorting the composite columns: floating numbers custom columns work fine regardless of the settings in Windows.

// EDIT

Got the final solution for the sorting problem! Still kinda puzzled.

I changed the settings for numbers back. Despite that, sorting still works in Calibre.

[BetterRed]

@Joanna - longshot from left bunker - what happens if you change the calibre interface language to Polskie?

BR

[Joanna]

@BetterRed: It works just the same. I tried it before, too, and back then sorting wouldn't work in the Polish version of Calibre either.

But from what I see, in the Polish version of Calibre I still have points as decimal symbols, so such behavior is to be expected. I wonder if points are used like that in the Polish Calibre locale by default: generally, in Polish you would have to use commas there, and those points look really strange next to e.g. Polish dates.

[BetterRed]

I would have thought Polish would be like most other continental languages - comma to separate integer part from fractional part, and dot to mark thousands i.e. 37.178,53 - try French or German in calibre.

BR

[Joanna]

Well, Polish is indeed like most other continental languages: i.e. it should have commas to separate integer part from fractional part. We don't use dots that much to mark thousands (although some people do), usually we put spaces in between: 37 178,53.

Does the format of numbers change when you switch the language version in your Calibre?

[BetterRed]

Originally Posted by Joanna

Does the format of numbers change when you switch the language version in your Calibre?

No, it seems to be dependent on the value of the format in the column definition, the calibre language and OS region settings appear to be irrelevant. But I'm not 100% certain on that even.

I'm not very knowledgeable on python format strings, this {:,2f} put the comma before the decimal but I couldn't figure out how to mark the thousands with comma, dot, or space and some decimal parts got rounded!!

I had to add a column as I didn't have any floats. It would help if there were a some examples of formatting floats in the custom column window, the link to the python doco is definitely in the tl:dr class - could not see an example of formatting common monetary values even.

As I recall C's printf was a PITA when it came to applying commercial formats to floats. I used something that formatted floats using a mask, similar to date formatting. The python formatting appears to be little more than warmed up printf formatting.

I think I have exhausted my knowledge and ideas - chaley will no doubt be back to help after he's had a kip

BR

[chaley]

The two of you did indeed find the root of the problem. It is apparently caused by a bug in python related to locales, or at least how they are used by calibre.

Explanation: columns built from other columns (composite columns) store text, and only text. If a template does any arithmetic then the results are stored as strings representing the number using period as the decimal separator. You can use the format_number function to change the separators to match the locale. When you sort a composite column numerically, calibre must (attempt to) convert the text to a number. This is what is failing.

The bug: windows (at least) provides the locale's separator characters as a 256-bit ascii character instead of unicode. When I switch my locale to France, the thousands separator is reported as a non-breaking space character (0xA0). Unfortunately that character "looks" like it might be unicode but isn't. Unfortunately calibre treats it as unicode, with the result that any attempt to use that character throws an exception. The text-to-number conversion fails because of that exception, which means that the sort fails.

Note that numeric custom columns (float and integer) store the actual number, not the text version of it. This is why the problem doesn't arise with sorting those columns.

I am submitting a calibre change to Kovid to fix this problem. In the meantime (and assuming he accepts the fix), the only workarounds that I see are:

• Use a locale that does not use the non-breaking space as the thousands separator.
• Change the settings for your locale so that a simple space is used for the thousands separator instead of the non-breaking space. This could have side effects relating to numbers breaking across lines.
• Change the thousands separator to some other character such as a dot.

If and when the fix appears you can reset the locale to your default.

[chaley]

The fix is in calibre's source now and will be included in the next calibre release.

[Joanna]

@chaley, great to hear that!

And thanks for all the explanations how all the columns work.

[BetterRed]

@Joanna & @chaley

Somewhat surprising its taken so long to expose this problem.

Perhaps a side-effect of the proliferation of programming languages is that the compilers/interpreters don't get the same amount of field testing as a new language might have done 30 years ago.

BR

[chaley]

Originally Posted by BetterRed

Somewhat surprising its taken so long to expose this problem.

I am not convinced that the problem has always been there. At some point calibre changed to using unicode everywhere (I think during the qt upgrade), and I suspect that was the point that behavior changed.