NULL value with embedded py
Hello,
I am creating a table using the result of an embedded py function. The result of the function is a dictionary composed of numpy arrays. The function and the CREATE TABLE work well. Inside the arrays of the dictionary, I have a few np.nan values, they seem to be recognized by monet during the creation of the table. In fact, when I query the whole table, I can see ‘null’ in the proper places.
But if I try to compare against these null values, I get nothing (SELECT * FROM table where field is null). After a few trials, it seems to me that monet understands that np.nan is a NULL value, but then when it inserts the value into the table the internal representation (?) is somewhat different form a ‘normal’ NULL value.
Any suggestion?
Thanks,
Stefano
Hi Stefano,
my guess is that whoever (i.e., whatever piece of code) creates/fills the MonetDB columns (BATs) from NumPy arrays might not correctly set the "nil" & "nonil" properties in the BAT header in case there are np.nan values inserted as "proper" MonetDB NULL (NIL) values.
Mark, our Python in MonetDB expert, should be able to tell more ...
Of course, it could also be that np.nan values are inserted "as-is" into MonetDB BATs; then, they are internally indeed different from, and hence not recognized as NULL (NIL) values, but as far as I can tell, our renderer for floating point numbers does recognize "invalid" floating point representations (INF, NaN), and render them as "NULL" ...
Best, Stefan
----- On Jun 20, 2016, at 2:28 PM, Stefano Fioravanzo fioravanzos@gmail.com wrote:
Hello,
I am creating a table using the result of an embedded py function. The result of the function is a dictionary composed of numpy arrays. The function and the CREATE TABLE work well. Inside the arrays of the dictionary, I have a few np.nan values, they seem to be recognized by monet during the creation of the table. In fact, when I query the whole table, I can see ‘null’ in the proper places.
But if I try to compare against these null values, I get nothing (SELECT * FROM table where field is null). After a few trials, it seems to me that monet understands that np.nan is a NULL value, but then when it inserts the value into the table the internal representation (?) is somewhat different form a ‘normal’ NULL value.
Any suggestion?
Thanks,
Stefano
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hey Stefano,
Stefan is indeed correct, when you pass a regular NumPy array back to MonetDB it (incorrectly) assumes that there are no null values in the array. I've pushed a change to default to fix it (http://dev.monetdb.org/hg/MonetDB/rev/c2487cd300be). Alternatively, you can convert your NumPy array to a masked array instead (using e.g. numpy.ma.masked_array(a, mask=numpy.isnan(a)), where a is your original array).
Regards,
Mark
----- Original Message ----- From: "Stefan Manegold" Stefan.Manegold@cwi.nl To: "users-list" users-list@monetdb.org Sent: Monday, June 20, 2016 3:06:47 PM Subject: Re: NULL value with embedded py
Hi Stefano,
my guess is that whoever (i.e., whatever piece of code) creates/fills the MonetDB columns (BATs) from NumPy arrays might not correctly set the "nil" & "nonil" properties in the BAT header in case there are np.nan values inserted as "proper" MonetDB NULL (NIL) values.
Mark, our Python in MonetDB expert, should be able to tell more ...
Of course, it could also be that np.nan values are inserted "as-is" into MonetDB BATs; then, they are internally indeed different from, and hence not recognized as NULL (NIL) values, but as far as I can tell, our renderer for floating point numbers does recognize "invalid" floating point representations (INF, NaN), and render them as "NULL" ...
Best, Stefan
----- On Jun 20, 2016, at 2:28 PM, Stefano Fioravanzo fioravanzos@gmail.com wrote:
Hello,
I am creating a table using the result of an embedded py function. The result of the function is a dictionary composed of numpy arrays. The function and the CREATE TABLE work well. Inside the arrays of the dictionary, I have a few np.nan values, they seem to be recognized by monet during the creation of the table. In fact, when I query the whole table, I can see ‘null’ in the proper places.
But if I try to compare against these null values, I get nothing (SELECT * FROM table where field is null). After a few trials, it seems to me that monet understands that np.nan is a NULL value, but then when it inserts the value into the table the internal representation (?) is somewhat different form a ‘normal’ NULL value.
Any suggestion?
Thanks,
Stefano
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Mark Raasveldt -
Stefan Manegold -
Stefano Fioravanzo