ORDER BY out of order. Is this a bug or expected?
create table t(n float); insert into t values(-574.41),(500.00); select printf('%8.2f',n) as n from t order by n desc;
+----------+ | n | +----------+ | -574.41 | | 500.00 | +----------+
which indicates n is treated as string (the result of printf) instead of float (the column's affinity).
So, the question is:
ORDER BY see the alias of the printf that masks the original column name on the
SELECT part, or the actual column name which would produce the correct result as indicated by changing the alias?
select printf('%8.2f',n) as nn from t order by n desc;
+----------+ | nn | +----------+ | 500.00 | | -574.41 | +----------+
BTW, using a cast over printf to convert to float for
ORDER BY's sake ruins the formatting so it's no-go.
You have correctly surmised why the first result set is ordered as you show.
Answering your question: Your alias refers to the expression whose type is text. This is all as it should be and is documented.
Is there any way then to get the correct order but without having to use a different name for alias as I want the result to use the original column name?
(3.1) By David Raymond (dvdraymond) on 2021-05-19 15:11:29 edited from 3.0 in reply to 1 [link] [source]
I believe order by is supposed to look at the column aliases first, yes. I can't point you to anything specific that says it though.
Don't forget you can order by something that's not in the result columns. So you can explicitly say the column you want to order by.
sqlite> select printf('%8.2f', n) as n from t order by t.n desc; ┌──────────┐ │ n │ ├──────────┤ │ 500.00 │ │ -574.41 │ └──────────┘ sqlite>