[EMAIL PROTECTED] wrote:
Ticket #1662 (http://www.sqlite.org/cvstrac/tktview?tn=1662)
complains that SQLite is not converting strings into numbers
if the string contains leading spaces. This happens because
SQLite just hands the string to strtod() and strtod() does not
recognize numbers with leading spaces. (Actually, strtod is
not used - our own internal implementation gets called, but
it works about the same.)
So the question is: should this be changed. Should SQLite
ignore leading space in strings when trying to determine if
the string looks like a number.
This has far-reaching implications. Right now if you say:
CREATE TABLE t1(a REAL);
INSERT INTO t1 VALUES(' 12.34');
The t1.a column will be filled with a string because the
leading spaces on the string make it not look like a number,
as far as SQLite is concerned. If you had said:
INSERT INTO t1 VALUES('23.45');
Then the string does look like a number and gets converted.
If the change requested by ticket #1662 is implemented then
both INSERTs above would convert their arguments to numbers.
As it currently stands, only the second INSERT converts its
argument.
Which is the correct behavior? Is this important enough to
change (and possible cause problems in legacy code)?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Richard,
I think SQLite should be changed to trim this leading space
automatically when storing a string representation of a number into a
column that is explicitly declared REAL or INTEGER.
As Igor has already pointed out, strtod() and strtol() are defined to
skip leading whitespace. So there should not be any implementation
difficulty.
This change will give correct and expected behavior for columns that are
declared to hold numeric data. It will have no effect for columns that
are declared to hold textual data, or columns that with no type declared
(which are common for SQLite because of its typeless history) since they
are treated as text columns.
Furthermore, I'm fairly sure that anyone who really required these
values to be stored as strings would have (or at least should have)
declared the column type as textual and not REAL or INTEGER. This is
really the same kind of distinction that needs to be made to store
numeric strings with leading zeros. If they are inserted into a numeric
column the leading zero are removed, if they are stored into a text
column the leading zeros are retained. If you need to keep the leading
zeros or spaces you should be using a text column and not a numeric one.
Finally, if the change isn't made there is no way for the user to get
the correct numeric interpretation of the value back out of SQLite.
Casting (i.e. cast(a as real)) won't work since it returns the same 0.0
value. Therefore the user will not be able to use any of the numeric
functions (i.e. sum(), avg(), min() etc.) to get correct (or at least
the expected) results using this column.
Dennis Cote
