Re: [sqlite] SQLite and integer division 1/2=0

2013-05-13 Thread Nico Williams
On May 12, 2013 11:36 PM, "James K. Lowden" wrote: I'd add also that syntactically the key need is to distinguish "use floating point arithmetic" from "use integer arithmetic" where no other type information is available, specifically in numeric constant literals.

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-13 Thread Paul van Helden
Tim, Simon & Darren, if you read my whole OP you will see that I've discovered this: use REAL instead. My point is that the behaviour of a NUMERIC column is not intuitive and gives mixed results which wouldn't be a problem if the division operator could be modified. My suggestion cannot be too

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-13 Thread Darren Duncan
On 2013.05.12 11:42 AM, Simon Slavin wrote: I think your problem is just that you have columns declared as NUMERIC. You can have REAL behaviour if you want: just declare your columns as REAL instead: I agree with this. In principle, the behavior of addition should be tied to the data type

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread James K. Lowden
On Sun, 12 May 2013 10:59:39 -0400 Stephen Chrzanowski wrote: > Correct me if I'm wrong, but the reason behind doing the int div > first is due to integer division is faster than float division? In the abstract, it's actually impossible to say that one or the other type of

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Simon Slavin
On 12 May 2013, at 7:15pm, Paul van Helden wrote: > On Sun, May 12, 2013 at 1:54 PM, Michael Black wrote: > >> PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you >> would not have known to turn it on (default would have to be

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Tim Streater
On 12 May 2013 at 19:15, Paul van Helden wrote: > I can live with SELECT 1/2 vs SELECT 1.0/2. The problem is that there is no > way to specify a float when you insert into a NUMERIC. 1.0 turns into an > integer. Then you do a division on all rows with an SQL select and you

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Paul van Helden
On Sun, May 12, 2013 at 1:54 PM, Michael Black wrote: > PRAGMA INTEGER_DIVISION would probably not have saved you this bug as you > would not have known to turn it on (default would have to be OFF for > backwards compatibility). I will use it on every connection I make in

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Stephen Chrzanowski
I remember something about this kinda thing a LONG time ago. Correct me if I'm wrong, but the reason behind doing the int div first is due to integer division is faster than float division? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Yan Seiner
Simon Slavin wrote: On 12 May 2013, at 11:07am, Paul van Helden wrote: SELECT 1/2 returns 0 which to me is a little odd, but I see from this page: http://www.sqlite.org/sqllogictest/wiki?name=Differences+Between+Engines that most other engines do the same. A

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Simon Slavin
On 12 May 2013, at 11:07am, Paul van Helden wrote: > SELECT 1/2 returns 0 which to me is a little odd, but I see from this page: > http://www.sqlite.org/sqllogictest/wiki?name=Differences+Between+Engines > that most other engines do the same. A few year ago I was

Re: [sqlite] SQLite and integer division 1/2=0

2013-05-12 Thread Michael Black
Discussion of SQLite Database Subject: [sqlite] SQLite and integer division 1/2=0 Hi All, I will probably get little support on this here, but I think it is worthwhile documenting my complaint. SELECT 1/2 returns 0 which to me is a little odd, but I see from this page: http://www.sqlite.org