Re: [sqlite] SQLite and integer division 1/2=0
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. Having a decimal part on numeric constant literals (even though 1.0 and so on are still integers, mathematically-speaking) traditionally serves this role. It's difficult to imagine a more compact and recognizable syntax than that! In some languages this is achieved by having a multiplicity of different operators for the same arithmetic operations, one set for integer arithmetic, another for dousing point, but this requires remembering more things. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 outlandish if MySQL does it "my way". Simon says: "The PRAGMAs allow SQLite to switch between different behaviours when the standard doesn't say what should happen". I would venture to say perhaps the standard wasn't too clear on this, or at the very least the fact that MySQL does it differently means there is a bit of a smudge on this part. Darren says: "declaring NUMERIC types is saying you don't care about the behavior". I do care about behaviour, so I'll change my management system to exclude NUMERIC as an option since I have no use for it then! I cannot expect my clients to know little quirks to this level of detail. I agree with what Darren says about the option of having 2 operators, / and div, that's what MySQL does and it is also a feature of Pascal and other languages. Please don't get me wrong. I haven't used MySQL for new projects in years, so I'm not promoting it in any way. Also, if NUMERIC wasn't so ubiquitous in the SQL world, I wouldn't even have raised the issue. If I am correct in taking away from this discussion "don't use NUMERIC column definitions if you want to do any calculations [with divisions] on them", then we can let it rest now. I'll dream of seeing NUMERIC(p,s) one day that enforces (p,s) (and doesn't do integer division unless s=0 !) :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 or to the operator or both. If you want integer behavior, declare INTEGER types, if you want real behavior, declare REAL types; declaring NUMERIC types is saying you don't care about the behavior. That's the proper way to do this. (Or have 2 operators, say "/" and "div", where the former can produce a fractional result while the latter guarantees a whole number result.) The pragma is a bad idea. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 division is faster. C doesn't define bit patterns or sizes of datatypes, only minima. It's more accurate to say that integer division is an operation defined on integers, and floating point division is defined on floats. They are different, as different from each other as both are from string concatenation. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 OFF for >> backwards compatibility). > > I will use it on every connection I make in future to avoid future pain. > [snip] The PRAGMAs allow SQLite to switch between different behaviours when the standard doesn't say what should happen. If there was a PRAGMA like that it would allow SQLite to stop behaving as the SQL standard says it should. Doing that would let SQLite violate the standard, and lots of people around here thinks that would be bad. > On Sun, May 12, 2013 at 2:59 PM, Yan Seiner wrote: > >> If you want floats, you have to specify floats. If you want integers, you >> have to specify integers. > > 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. 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: SQLite version 3.7.12 2012-04-03 19:43:07 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE test (a REAL, b REAL); sqlite> INSERT INTO test VALUES (1,2); sqlite> INSERT INTO test VALUES (1.0,2.0); sqlite> SELECT a,b,a/b FROM test; 1.0|2.0|0.5 1.0|2.0|0.5 Works fine: the values inserted were 1 and 2, but the column affinity was REAL, so the values were understood as REAL when they were inserted into the table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 get > mixed results because some rows have floats and some rows have integers. In > C, 1/2=0. In Pascal 1/2=0.5. Oracle/MSSQL/others act like C, MySQL acts > like Pascal. This is not my main issue. Consistency throughout a table, is. You need to read the documentation, seems to me. Here: http://www.sqlite.org/datatype3.html in particular, where it seems quite clear to me that what you need is to define your columns as REAL rather than NUMERIC. Then your data will always be of type REAL. Note that there is no NUMERIC type, only a NUMERIC affinity. Your data will by default have NUMERIC affinity if you try to give it type NUMERIC, but that's just a coincidence, far as I can tell. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 future to avoid future pain. (I have a SQLite management system where my clients can create their own tables and enter their own SQL for custom reports) On Sun, May 12, 2013 at 2:35 PM, Simon Slavin wrote: > > I think it's endemic to computers, the same as the difficulty with > counting in units of 0.1 until you get 1. > > On Sun, May 12, 2013 at 2:59 PM, Yan Seiner wrote: > If you want floats, you have to specify floats. If you want integers, you > have to specify integers. The compiler has no way to know which you want. > > Just get in the habit of always adding a .0 if you want float constants. > > 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 get mixed results because some rows have floats and some rows have integers. In C, 1/2=0. In Pascal 1/2=0.5. Oracle/MSSQL/others act like C, MySQL acts like Pascal. This is not my main issue. Consistency throughout a table, is. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 few year ago I was debugging ridiculous behaviour in a huge app and found to my dismay the same problem in C. Evaluate 1/9 and you get zero. You have to use 1.0/9 or 1/9.0 for a floating point result. And hey presto: That is the correct behavior. 1/9 is integer division. No remainder and no automatic conversion to float. It is expected that you would assign the result to an integer variable. float x = 1/9; first we do the integer division: 1/9 =0 then we convert to float and do the assignment Not a bug. Most modern compilers would actually do the 1/9 division prior to compiling your program. If you want floats, you have to specify floats. If you want integers, you have to specify integers. The compiler has no way to know which you want. Just get in the habit of always adding a .0 if you want float constants. -- Engineer for hire Contract management, administration, training http://www.seiner.com/engineer/resume.pdf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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 debugging ridiculous behaviour in a huge app and found to my dismay the same problem in C. Evaluate 1/9 and you get zero. You have to use 1.0/9 or 1/9.0 for a floating point result. And hey presto: SQLite version 3.7.12 2012-04-03 19:43:07 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT 1/2; 0 sqlite> SELECT 1/2.0; 0.5 sqlite> SELECT 1.0/2; 0.5 sqlite> I think it's endemic to computers, the same as the difficulty with counting in units of 0.1 until you get 1. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
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
[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