Hi,
May i add to that that 'order by' also seems involved, see below. This
can lead to really unexpected errors... "order by 1.0 * b / c" as
workaround solves it btw.
Differating between floating point operator '/' and integer operator
'DIV' like some program languages do would lead to predictable output.
regards,
rene
=======================
#select *, b/c, 1.0 * b/c from test2 order by b/c
a b c b/c 1.0 * b/c
1 3 4 0 0.75
2 5 6 0 0.833333333333333
5 6 7 0 0.857142857142857
6 6 8 0 0.75
7 7 8 0 0.875
3 7 6 1 1.16666666666667
4 6 6 1 1.0
8 8 8 1 1.0
9 9 8 1 1.125
10 10 8 1 1.25
11 9 8 1 1.125
12 8 8 1 1.0
13 11 8 1 1.375
14 13 8 1 1.625
15 12 8 1 1.5
#select *, b/c, 1.0 * b/c from test2 order by 1.0*b/c
a b c b/c 1.0 * b/c
1 3 4 0 0.75
6 6 8 0 0.75
2 5 6 0 0.833333333333333
5 6 7 0 0.857142857142857
7 7 8 0 0.875
4 6 6 1 1.0
8 8 8 1 1.0
12 8 8 1 1.0
9 9 8 1 1.125
11 9 8 1 1.125
3 7 6 1 1.16666666666667
10 10 8 1 1.25
13 11 8 1 1.375
15 12 8 1 1.5
14 13 8 1 1.625
Ralf Junker wrote:
Hello DRH,
3. If the division of INTEGERs can not be stored as an INTEGER
(i.e. if a % b != 0), the result should be returned as a REAL.
create table t1( a integer, b integer);
insert into t1 values(5,2);
update t1 set a=a/b;
If your rule above was in force, this would leave
T1.A holding 2.5, which is incompatible with the
way other database engines work.
Well, understandable. But suppose that's exactly what one wants to do? How to
achieve this using the current implementation of sqlite3?
On the other hand, I think that the following, currently implemented behaviour
of sqlite3 is also incompatible with the way other database engines work. Where
they return 2.5 for real type columns, sqlite3 does not:
create table t1 (a real, b real);
insert into t1 values (5,2);
select a / b from t1;
2
So the final question biols down to: How can I reliably guarantee a real type
result for divisions on real typed columns even if they happen to contain
integers? I cant't believe I always have to use a workaround like this:
select 1.0 * a / b from t1;
I am sure there must be a better possibility without the extra multiplication!
Or is there not?
Regards,
Ralf
