Rather than get caught in a mess of integers and floating point numbers
and the consequent unpredictable results, how about having some typing,
such as integers and floats and also a decimal fixed point type which
looks like a text field and displays like one but which is much more
compatible with the flexible typing of Sqlite. Arithmetic on display
format fields like that is not fast, but overall it is not bad when you
take into account the absence of radix changes when you are inputting
and outputting such numbers, often the most frequent activity on numbers
in a general purpose database.
Users love such a number system because it is natural and works like the
Arithmetic they learned in Grade School. As I read the SQL spec it is
not excluded.
I find the idea of dividing two integers stored in binary form and
getting a gratuitous conversion to floating point ugly and potentially
very annoying.
JS
Fred Williams wrote:
Ah the sticky wicket that is "Type less" :-) We now have issues
evolving as a direct result of that feature in our cute little database.
We now seem to have by backing into it: Really Restricted Integer, Real,
DateTime (sort of), and Text. BLOB and CLOB away at your own risk!
Is it time to officially declare/fully support some Types and clear the
air?
Fred
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 01, 2005 9:43 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
"Preston Zaugg" <[EMAIL PROTECTED]> wrote:
As was discussed in the original
post this would be NON-STANDARD behavior.
The SQL-99 spec says that integer
math remains an integer.
The change I propose (and have now checked into CVS, btw,
though I might still back it out) does not violate this
specification.
Think of it this way: SQLite supports only a single
numeric type which is REAL. We call that type "numeric".
But the type represents what we normally think of as real
numbers.
We permit integer values to be read from and written to
the database as a convenience to the user. And internally,
some values are sometimes kept as machine integers for
computational and storage efficiency. But that is only
an optimization. At the end of the day, there is only a
single numeric data type and that type is real.
An INTEGER PRIMARY KEY column seems like an exception to
this rule. But perhaps not. Think of an INTEGER PRIMARY
KEY column as holding a numeric value with restrictions.
It is as if we added to every INTEGER PRIMARY KEY named
"x" the following check constraint:
CHECK( x >= -9223372036854775808
AND x <= 923372036854775807
AND x == round(x) )
So the values going in and out of an integer primary key
are still real values. It just happens that their range
is restricted somewhat and they do not have a fractional
part.
If you look at things from this point of view, SQLite
does not support integer values. And so we never have
to worry about integer division.
For complete consistency, I suppose we might want to
modify the built-in typeof() function to always return
"numeric" instead of "integer". I wonder....
--
D. Richard Hipp <[EMAIL PROTECTED]>
