[Bug]: Changing the display format of a column destroys its sort order

[midoragh]

What did you do?

I changed the display format of a column containing REAL values to printf('%.2f', "Price"). Clicking the column header to sort the table by the REAL values provides mixed ordering of values.

What did you expect to see?

I expected that changing the display format has no impact to the sort order of the column.

What did you see instead?

The sort order is unordered regarding to REAL values.

DB4S Version

3.13.0-rc1

What OS are you seeing the problem on?

Windows

OS version

No response

Relevant log output

No response

Prevention against duplicate issues

• I have searched for similar issues

[i-s-o]

My understanding is that once you apply a printf() as a Display Format to a column in DB4S, that column in the browser window is no longer REAL, but it is TEXT, hence the rules for text sorting apply. The following example illustrates this using a view:

CREATE TABLE t1 (price REAL);
INSERT INTO t1 (price) VALUES (102.1),(21.2),(1.3),(2.4);

SELECT printf('%.2f', "price") as price
FROM t1
ORDER BY price
;
-- |price |
-- |------|
-- |1.30  |
-- |102.10|
-- |2.40  |
-- |21.20 |

One solution to this problem is to write your own view, and order by the original column:

SELECT printf('%.2f', "price") as price
FROM t1
ORDER BY t1.price
;
-- |price |
-- |------|
-- |1.30  |
-- |2.40  |
-- |21.20 |
-- |102.10|

[midoragh]

But the type in the database is still REAL. And a view format should not change a type to TEXT. Clicking on the header of the column should still use REAL to sort the column cells. It makes not really sense that just adding a precision of 2 to a REAL value destroy sorting of the values.