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
