REAL data type issue with Pandas

[acont95]

When creating a table with REAL or FLOAT(24), a float32 data type Pandas dataframe inserted with cursor.insert() becomes corrupted when retrieved with fetchdf(). The code below will reproduce what I am experiencing.

import monetdbe
import pandas as pd
import numpy as np

conn = monetdbe.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE "test"("a" REAL);')

df = pd.DataFrame({'a':[1,2,3,4]}, dtype=np.float32)
print(df)
cursor.insert('test', df)

cursor.execute('SELECT * FROM "test"')
dfn = cursor.fetchdf()
print(dfn)

This is the output of the above code:

0 1.0
1 2.0
2 3.0
3 4.0

0 2.000000e+00
1 5.120001e+02
2 2.105353e-314
3 -5.345402e-309

• OS and version: Fedora 32
• Python version: 3.8.1
• MonetDBe-Python version: 0.8.4

[gijzelaerr]

@njnes how should I deal with this? It works fine with a normal float, but not with a REAL. MonetDB returns type monetdbe_float for the REAL column. it does work when the results are fetched normally (fetchall), so its probably an error in my numpy building logic. I thought maybe the way floats are represented by monetdb is different from numpy, but it does seem to work for normal floats....

[njnes]

but a REAL != FLOAT (sql one).

The real == c-float, double == c-double, float(<=24) == c-float, float(>24) == double == c-double

[njnes]

float in sql will be rewritten into a real or double, ie writting float(24) or real, should be the same.

[njnes]

diff --git a/monetdbe/_cffi.py b/monetdbe/_cffi.py
index f4962e6..2b8cb91 100644
--- a/monetdbe/_cffi.py
+++ b/monetdbe/_cffi.py
@@ -90,7 +90,7 @@ type_map: Dict[Any, Tuple[str, Optional[Callable], np.dtype, Optional[Any]]] = {
lib.monetdbe_int32_t: ("int32_t", None, np.dtype(np.int32), np.iinfo(np.int32).min),
lib.monetdbe_int64_t: ("int64_t", None, np.dtype(np.int64), np.iinfo(np.int64).min),
lib.monetdbe_size_t: ("size_t", None, np.dtype(np.uint), None),

• lib.monetdbe_float: ("float", py_float, np.dtype(np.float), np.finfo(np.float).min),

• lib.monetdbe_float: ("float", py_float, np.dtype(np.float32), np.finfo(np.float32).min),
  lib.monetdbe_double: ("double", py_float, np.dtype(np.float), np.finfo(np.float).min),
  lib.monetdbe_str: ("str", make_string, np.dtype('=O'), None),
  lib.monetdbe_blob: ("blob", make_blob, np.dtype('=O'), None),

[gijzelaerr]

this issue has been resolved and wil be included in the 0.9 release