Date: 2011-02-21 22:41:47 +0100
From: @bartscheers
To: SQL devs <>
Version: 11.5.3 (Aug2011-SP1) [obsolete]
CC: @mlkersten, @njnes, @drstmane

Last updated: 2011-10-26 13:22:02 +0200

Comment 15479

Date: 2011-02-21 22:41:47 +0100
From: @bartscheers

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13
Build Identifier:

Double value, f.ex., 1.0e-310, can be import via the INSERT statement, but not from within file via the COPY INTO statement.

%> mclient -lsql -t -H

sql>create table t1 (i1 int,d1 double,i2 int);
operation successful
Timer 1.585 msec

sql>insert into t1 values (1,1.0e-310,1);
1 affected row (1.168ms)
Timer 1.199 msec
sql>insert into t1 values (1,1.0e-320,1);
1 affected row (0.833ms)
Timer 0.854 msec
sql>select * from t1;
+------+------------------------+------+
| i1 | d1 | i2 |
+======+========================+======+
| 1 | 9.9999999999999694e-31 | 1 |
: : 1 : :
| 1 | 9.9998886718268301e-32 | 1 |
: : 1 : :
+------+------------------------+------+
2 tuples (1.340ms)
Timer 1.511 msec 2 rows

Then I create a t1.csv file with two rows of content:
1;1.0e-310;1
1;1.0e-320;1
and run a copy into:

sql>copy 1 records into t1 from '/export/scratch1/bscheers/lsst/dbtests/tables/t1.csv' using delimiters ';','\n' null as '\N';
SQLException:importTable:value '1.0e-310' from line 1 field 2 not inserted, expecting type double
failed to import table
Timer 157.493 msec

Bart

Reproducible: Always

Steps to Reproduce:

See details above

Expected Results:

COPY INTO should have imported the double values.

Comment 15480

Date: 2011-02-21 23:20:01 +0100
From: @drstmane

some more checks reveal:

$ mclient -ls -s'create table f (f float);'
operation successful

$ mclient -ls -s'create table d (d double);'
operation successful

$ for ((i=320; i<=330; i++)) ; do echo $i ; mclient -ls -s "insert into f values (1e-$i);" ; mclient -ls -s "insert into d values (1e-$i);" ; done
320
1 affected row
1 affected row
321
1 affected row
1 affected row
322
1 affected row
1 affected row
323
1 affected row
1 affected row
324
1 affected row
1 affected row
325
1 affected row
1 affected row
326
1 affected row
1 affected row
327
1 affected row
1 affected row
328
1 affected row
1 affected row
329
1 affected row
1 affected row
330
1 affected row
1 affected row

$ mclient -ls -s'select * from f;'
+------------------------+
| f |
+========================+
| 9.9998886718268301e-32 |
: 1 :
| 9.9801260459931802e-32 |
: 2 :
| 9.8813129168249309e-32 |
: 3 :
| 9.8813129168249309e-32 |
: 4 :
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------------------+
11 tuples

$ mclient -ls -s'select * from d;'
+------------------------+
| d |
+========================+
| 9.9998886718268301e-32 |
: 1 :
| 9.9801260459931802e-32 |
: 2 :
| 9.8813129168249309e-32 |
: 3 :
| 9.8813129168249309e-32 |
: 4 :
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------------------+
11 tuples

$ for ((i=300; i<=310; i++)) ; do echo "1e-$i" ; done | tee /tmp/t.csv
1e-300
1e-301
1e-302
1e-303
1e-304
1e-305
1e-306
1e-307
1e-308
1e-309
1e-310

$ mclient -ls -s "copy into f from '/tmp/t.csv' using delimiters ',','\n';"
SQLException:importTable:value '1e-308' from line 9 field 1 not inserted, expecting type double
failed to import table

$ mclient -ls -s "copy into d from '/tmp/t.csv' using delimiters ',','\n';"
SQLException:importTable:value '1e-308' from line 9 field 1 not inserted, expecting type double
failed to import table

Comment 15481

Date: 2011-02-22 09:22:02 +0100
From: @mlkersten

This message comes from GDKatoms.mx dblFromStr() where the condition ERANGE is checked upon return of strtod(). Then the value is turned into dbl_nil,
which is stored in the result.

That check does not take place in the SQL lexical analyser.
As such the error is correct; the input is invalid.

Open issue, why is not the NULL value shown for doubles.

Comment 15482

Date: 2011-02-22 09:48:53 +0100
From: @grobian

(and why is the column width miscalculated)

Comment 15603

Date: 2011-03-18 14:23:40 +0100
From: @njnes

now we handle ERANGE errors equal within sql and gdk. Added a test script (bugTracker 2011/double_erange) for it. Please verify the results.

Comment 15608

Date: 2011-03-19 08:38:04 +0100
From: @njnes

Changeset ae7e80697674 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=ae7e80697674

Changeset description:

added  to debug output
check erange errors on strtod calls
added test for bug #2774

Comment 15961

Date: 2011-07-17 19:45:17 +0200
From: @drstmane

Changeset cc4337f7ecae made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=cc4337f7ecae

Changeset description:

bug #2774: extended test and approved (IMHO) expected output;

output and test behavior need to be double checked!

Comment 16359

Date: 2011-09-30 11:28:45 +0200
From: @sjoerdmullender

Considering fixed.

Comment 16367

Date: 2011-10-01 20:00:44 +0200
From: @drstmane

re-opened as test still fails; cf.,
http://monetdb.cwi.nl/testweb/tests/41918:3fc14f711088/GNU-Fedora-x86_64-propcheck/sql/mTests/test/BugTracker-2011/double_erange.Bug-2774.out.diff.html
http://monetdb.cwi.nl/testweb/tests/41918:3fc14f711088/GNU-Fedora-x86_64-propcheck/sql/mTests/test/BugTracker-2011/double_erange.Bug-2774.err.diff.html

Comment 16442

Date: 2011-10-19 14:37:04 +0200
From: @sjoerdmullender

Changeset dae3337378c2 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=dae3337378c2

Changeset description:

Floating point overflow is not allowed, but underflow *is* allowed.
According to the SQL spec, floating point ("approximate numeric
literal") overflow results in a syntax error, but underflow is treated
the same as overspecification (more digits than the implementation can
represent) and results in the value 0 or a value very close to 0.
On underflow, the strtof and strtod functions return either 0 or an
unnormalized number which is closer to zero than the smallest
normalized number.  In addition, the function may or may not set errno
to ERANGE.  On overflow, the functions return HUGE_VAL (or HUGE_VALF)
and set errno to ERANGE.  We now check for overflow by checking
whether errno is set to ERANGE *and* the value is not "close to zero".

This fixes bug #2774, the output of which is also now approved.