Re: [sqlite] Bug in division?

[Jay Kreibich]

On May 6, 2014, at 5:26 PM, Gene Connor <neothreeei...@hotmail.com> wrote:

> Subject: Re: [sqlite] Bug in division?
> From: j...@kreibi.ch
> Date: Tue, 6 May 2014 17:02:02 -0500
> CC: neothreeei...@hotmail.com
> To: sqlite-users@sqlite.org
> 
> > The system does not return 0 any time you divide two integers, but it does 
> > return zero for 2 / 4.  After all, how many *whole* times does four go into 
> > two?
> 
>  
> 
> Right.  I discovered this 'problem' when I divided a real big integer by an 
> even bigger integer.  The actual result was 0.004572 or something like that.
> 
> MS Access did it right, but SQLite returned 0.  That prompted my bug? report
> 
> I also found out SQLite might change the column affinity to INTEGER if your 
> data can be converted "losslessly”.


You’re mixing terms.  “Affinity” is the “default type” of the column.  It is 
defined by the CREATE TABLE statement and is part of the table definition, 
meaning it cannot change on a row-by-row basis.  In SQLite, a column “affinity" 
is different from the “type" of a value in a given column, which (in SQLite, 
anyways) can change from row to row.  In fact, SQLite “types” and “affinities” 
do not even share the same set of classifications… Value types are limited to 
{NULL, INTEGER, REAL, TEXT, BLOB}, while affinities are {TEXT, NUMERIC, 
INTEGER, REAL, NONE}.

In your example below, you define the columns at “NUMERIC," which results in a 
NUMERIC affinity for the two columns (although not because the names match, see 
section 2.1 of http://www.sqlite.org/datatype3.html#affinity)

NUMERIC affinities have a preference to store things as an integer, followed by 
a floating point, followed by a string or other native type.  As you’ve shown, 
if SQLite can “up convert” a value, it will.  It isn’t just floating point 
numbers that get converted.  "INSERT INTO tbl1 VALUES ( ‘2’, ‘4’ )" will also 
result in two integers, even though you’ve passed in two strings.

For what it’s worth, all this is pretty clearly documented: 
http://www.sqlite.org/datatype3.html#affinity

It is a bit weird, but it comes from the fact that SQLite allows different 
types on a row by row basis.


> sqlite> CREATE TABLE TBL1 (N1 NUMERIC, N2 NUMERIC);
> 
> Same thing happens with type DECIMAL.
> 
> This means your best bet is to store /all/ numbers in FLOAT or DOUBLE 
> columns.   Or use CAST  (ugh).

Well, not really.  Large integer values cannot be accurately stored in 
floating-point values.  If you really want floating-point values, and you’re 
using a number domain that is acceptable to use floating-point values, then 
sure.  But if you want integers, they store larger precise numbers, and are 
usually much smaller on disk.

In short, the same as any other engineering decision: use the right tool for 
the job.

  -j


--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Jay Kreibich]

On May 6, 2014, at 4:29 PM, John Drescher  wrote:

>> Interesting.  It makes NO sense to return 0 when dividing two integers.
>> 
> 
> Never took a C/C++ class?



The system does not return 0 any time you divide two integers, but it does 
return zero for 2 / 4.  After all, how many *whole* times does four go into two?

 -j

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Simon Slavin]

On 6 May 2014, at 1:52pm, RSmith  wrote:

> I think the OP might be seeing the list via one of those connected sites and 
> not getting the feedback. Maybe send a direct mail to him.

I'll send a personal email to him.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Simon Slavin]

On 6 May 2014, at 2:06am, Gene Connor  wrote:

> SELECT DISTINCT 2/4 AS RESULT FROM TABLE;
> returns 0

Not a bug.  By providing two integer operands you have asked for integer 
arithmetic, and will get an integer answer.

It's something that happens in several different languages, even C !  Worse 
still, in some languages you get an integer answer if /either/ of the operands 
are integers.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Hick Gunter]

BTW: The FROM clause is optional in SQLite. It is required only for expressions 
involving fields.

SELECT 2/4 AS RESULT;

Will also work, without the overhead of accessing every row of TABLE and 
performing DISTINCT processing and maybe even without obtaining a lock on the 
database.

-Ursprüngliche Nachricht-
Von: Gene Connor [mailto:neothreeei...@hotmail.com]
Gesendet: Mittwoch, 30. April 2014 06:21
An: sqlite-users@sqlite.org
Betreff: [sqlite] Bug in division?


SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0 SELECT DISTINCT 2/4.0 AS 
RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4 AS RESULT FROM 
TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4.0 AS RESULT FROM 
TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4.01 AS RESULT FROM 
TABLE;returns 0.49875 (correct) As long as one or both numbers has at least one 
decimal place, it calcs correctly.
Verified that it also happens in queries using tables and real data.
Windows SQLite version 3.8.4.3 2014-04-03 16:53:12

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Clemens Ladisch]

Petite Abeille wrote:
> I don’t thing the various ANSI standards have anything normative to
> say about what the result of a division should be, merely that there
> is a division operator.

Indeed:
| 
|
| Syntax Rules
|
| 1) If the declared type of both operands of a dyadic arithmetic
|operator is exact numeric, then the declared type of the result is
|an implementation-defined exact numeric type, [...]
|
|d) The precision and scale of the result of division are
|   implementation-defined.
|
| 2) If the declared type of either operand of a dyadic arithmetic
|operator is approximate numeric, then the declared type of the
|result is an implementation-defined approximate numeric type.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Charles J. Daniels]

This is a very common thing. Many programming languages, like C++, do the
same thing. So it's not just a SQL thing.

--charlie

On Wed, Apr 30, 2014 at 7:11 AM, Marc L. Allen
wrote:

> Not an error. Int/Int uses integer division and results in an integer
> number. When one number is a float, the result becomes a float.
>
> I don't know about all SQL varieties, but MSSQL is the same.
>
> > On Apr 30, 2014, at 8:04 AM, "Gene Connor" 
> wrote:
> >
> >
> > SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0
> > SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> > SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;returns 0.5 (correct)
> > SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> > SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;returns 0.49875 (correct)
> > As long as one or both numbers has at least one decimal place, it calcs
> correctly.
> > Verified that it also happens in queries using tables and real data.
> > Windows SQLite version 3.8.4.3 2014-04-03 16:53:12
> >
> > Thanks
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received
this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> 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

Re: [sqlite] Bug in division?

[Petite Abeille]

On Apr 30, 2014, at 8:50 PM, Jay Kreibich  wrote:

> Given Oracle’s legacy, it might be that “2” defaults to a “numeric” type, 
> rather than an integer.

Indeed, there are no ‘integer’ type per se in Oracle. At least not at the SQL 
level. But more to the point, I don’t thing the various ANSI standards have 
anything normative to say about  what the result of a division should be, 
merely that there is a division operator. So it’s rather a free for all. Sigh.

FWIW, I personally find Oracle’s behavior more intuitive in that respect. But 
then again, nothing really wrong with how SQLite handles it.

In other news:

http://stilldrinking.org/programming-sucks

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Rob Richardson]

I don't know if it's in the SQL standard or not, but the C, C++ and C# 
languages all act this way.  The result of mathematical operations on integers 
is always an integer.  If you want the result to be a floating-point number, 
you have to force at least one of the operands to be a floating-point number.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Jay Kreibich]

On Apr 30, 2014, at 1:00 PM, Petite Abeille  wrote:
> 
> On Apr 30, 2014, at 2:22 PM, John McKown  wrote:
> 
>> PostgreSQL likewise returns 0 for 2/4 and .5 for 2/4.0 . This is likely a
>> part of the SQL standard.
> 
> Just to be contrarian, Oracle doesn’t and returns 0.5. Ah!
> 


Given Oracle’s legacy, it might be that “2” defaults to a “numeric” type, 
rather than an integer.
 
 -j


--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Petite Abeille]

On Apr 30, 2014, at 2:22 PM, John McKown  wrote:

> PostgreSQL likewise returns 0 for 2/4 and .5 for 2/4.0 . This is likely a
> part of the SQL standard.

Just to be contrarian, Oracle doesn’t and returns 0.5. Ah!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[John McKown]

PostgreSQL likewise returns 0 for 2/4 and .5 for 2/4.0 . This is likely a
part of the SQL standard.


On Wed, Apr 30, 2014 at 7:11 AM, Marc L. Allen
wrote:

> Not an error. Int/Int uses integer division and results in an integer
> number. When one number is a float, the result becomes a float.
>
> I don't know about all SQL varieties, but MSSQL is the same.
>
> > On Apr 30, 2014, at 8:04 AM, "Gene Connor" 
> wrote:
> >
> >
> > SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0
> > SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> > SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;returns 0.5 (correct)
> > SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> > SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;returns 0.49875 (correct)
> > As long as one or both numbers has at least one decimal place, it calcs
> correctly.
> > Verified that it also happens in queries using tables and real data.
> > Windows SQLite version 3.8.4.3 2014-04-03 16:53:12
> >
> > Thanks
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Marc L. Allen]

Not an error. Int/Int uses integer division and results in an integer number. 
When one number is a float, the result becomes a float. 

I don't know about all SQL varieties, but MSSQL is the same. 

> On Apr 30, 2014, at 8:04 AM, "Gene Connor"  wrote:
> 
> 
> SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0
> SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;returns 0.5 (correct)
> SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;returns 0.49875 (correct)
> As long as one or both numbers has at least one decimal place, it calcs 
> correctly.
> Verified that it also happens in queries using tables and real data.
> Windows SQLite version 3.8.4.3 2014-04-03 16:53:12
> 
> Thanks 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users