Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

COPY INTO of double (~1.0e-310) cannot be imported. #2774

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

COPY INTO of double (~1.0e-310) cannot be imported. #2774

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant