Perhaps not a solution, but a workaround: Try coercing the data entering the table into a value recognizable as a real:
Create trigger t_t1i after insert on t1 Begin Update t1 set a = 1.0 * a, b = 1.0 * b Where rowid = new.rowid; End; Create trigger t_t1u after update on t1 Begin Update t1 set a = 1.0 * a, b = 1.0 * b Where rowid = new.rowid; End; The table will then hold reals in all cases. Expressions then work without change: Select a/b from t1; Update a set a = a / b; Doug -----Original Message----- From: Ralf Junker [mailto:[EMAIL PROTECTED] Sent: Monday, October 03, 2005 7:12 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ? 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
