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

[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.
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

[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 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

[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 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

[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 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

[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 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

[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 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

[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 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

[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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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

[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 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

[Michael Black]

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