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

[midoragh]

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

[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.