Hi all,
We found a strange case this week when testing some insert.
We tried to insert -1.7976931348623157e308 in a double value and we get a null in the column.
Data to reproduce the case :
create table test.testdouble ( id int, testdouble double, primary key (id) )
insert into test.testdouble (id,testdouble) values (1, -1.7976931348623157e308)
select * from test.testdouble
Regards
Mathieu
Hey Mathieu,
This is not actually a bug, but a feature :)
MonetDB encodes null values of numeric columns by a specific value. This is typically the lowest possible value in that column, or close to the lowest possible value for that column. For doubles, the null value is encoded as -1.7976931348623157e308, thus if you insert this value into the database it will be interpreted as null.
Is there any specific reason you need this value in the database?
Regards, Mark
On Fri, Nov 27, 2015 at 4:37 PM, Mathieu Raillard mraillard@data-mat.fr wrote:
Hi all,
We found a strange case this week when testing some insert.
We tried to insert -1.7976931348623157e308 in a double value and we get a null in the column.
Data to reproduce the case :
create table test.testdouble ( id int, testdouble double, primary key (id) )
insert into test.testdouble (id,testdouble) values (1, -1.7976931348623157e308)
select * from test.testdouble
Regards
Mathieu
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Dear Mark,
We are doing some integrity checks over the data loaded in MonetDB, we would like to know how the values can be correctly loaded. We have tested many cases (like min/max) for the different types, that why we have found this behavior. In practice, we will not need to insert the -1.7976931348623157e308 value! But it should be great if the documentation can stat which values are reserved for NULL storage.
Best regards, Sebastien
From: users-list [mailto:users-list-bounces+sebastien.raillard=passman.fr@monetdb.org] On Behalf Of Mark Raasveldt Sent: vendredi 27 novembre 2015 17:02 To: Communication channel for MonetDB users Subject: Re: Double insert
Hey Mathieu, This is not actually a bug, but a feature :) MonetDB encodes null values of numeric columns by a specific value. This is typically the lowest possible value in that column, or close to the lowest possible value for that column. For doubles, the null value is encoded as -1.7976931348623157e308, thus if you insert this value into the database it will be interpreted as null.
Is there any specific reason you need this value in the database? Regards, Mark
On Fri, Nov 27, 2015 at 4:37 PM, Mathieu Raillard mraillard@data-mat.fr wrote: Hi all,
We found a strange case this week when testing some insert.
We tried to insert -1.7976931348623157e308 in a double value and we get a null in the column.
Data to reproduce the case :
create table test.testdouble ( id int, testdouble double, primary key (id) )
insert into test.testdouble (id,testdouble) values (1, -1.7976931348623157e308)
select * from test.testdouble
Regards
Mathieu
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Dear Sébastien & Mathieu,
in MonetDB, for numerical types, the NULL value is the smallest value of the type's domain; see also https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes
Best, Stefan
----- On Nov 30, 2015, at 3:13 PM, Sébastien RAILLARD (PASSMAN) sebastien.raillard@passman.fr wrote:
Dear Mark,
We are doing some integrity checks over the data loaded in MonetDB, we would like to know how the values can be correctly loaded. We have tested many cases (like min/max) for the different types, that why we have found this behavior. In practice, we will not need to insert the -1.7976931348623157e308 value! But it should be great if the documentation can stat which values are reserved for NULL storage.
Best regards, Sebastien
From: users-list [mailto:users-list-bounces+sebastien.raillard=passman.fr@monetdb.org] On Behalf Of Mark Raasveldt Sent: vendredi 27 novembre 2015 17:02 To: Communication channel for MonetDB users Subject: Re: Double insert
Hey Mathieu, This is not actually a bug, but a feature :) MonetDB encodes null values of numeric columns by a specific value. This is typically the lowest possible value in that column, or close to the lowest possible value for that column. For doubles, the null value is encoded as -1.7976931348623157e308, thus if you insert this value into the database it will be interpreted as null.
Is there any specific reason you need this value in the database? Regards, Mark
On Fri, Nov 27, 2015 at 4:37 PM, Mathieu Raillard mraillard@data-mat.fr wrote: Hi all,
We found a strange case this week when testing some insert.
We tried to insert -1.7976931348623157e308 in a double value and we get a null in the column.
Data to reproduce the case :
create table test.testdouble ( id int, testdouble double, primary key (id) )
insert into test.testdouble (id,testdouble) values (1, -1.7976931348623157e308)
select * from test.testdouble
Regards
Mathieu
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
--
Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17
www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com https://www.facebook.com/PASSMAN-187787814053/ _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Mark Raasveldt -
Mathieu Raillard -
Stefan Manegold -
Sébastien RAILLARD (PASSMAN)