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). Changing to float math everywhere might hurt some embedded systems. MYSQL was never designed to work on embedded systems. So yeah...I doubt you'll get much support as there's not a good solution which stops somebody from making this mistake.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul van Helden Sent: Sunday, May 12, 2013 5:07 AM To: General 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/sqllogictest/wiki?name=Differences+Between+Engines that most other engines do the same. (In my opinion MySQL does it right: 1/2=0.5; 1 div 2=0). So be it, but it can really trip you up in SQLite if you have a table create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC); insert into numtypes values (1, 2, 25.23); insert into numtypes values (1.0, 2, 27.17); insert into numtypes values (1.1, 2, 22.92); select A, typeof(A), B, typeof(B), A/B*C from numtypes; A,typeof(A),B,typeof(B),A/B*C 1,integer,2,integer,0,0 1,integer,2,integer,0,0 1.1,real,2,integer,0.12.606 Yes, I have discovered create table numtypes (A REAL, B REAL, C REAL) solves the problem. It just seems illogical to me that numbers are silently converted to integer and then integer division is done on those. A client of mine just got very bad answers from a simple calculation because some rows had integers and others real. It is not intuitive that a NUMERIC column would mix integer and float division. If you don't know this, as I'm sure most regular users don't, it can really burn you. I don't mind the conversion to integer, but then 1/2 should be 0.5. On my wishlist: PRAGMA INTEGER_DIVISION = off; I would use it all the time. Yes "feature creep" I can hear you type. :-) Best regards, Paul. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
