Falsely derived type bigint

[juliangrube1988]

Describe the bug
I have a problem with the tables created for the entities:
The first data record determines the type of the table. If the first data record is 23.0, for example, a column with the type Bigint is created, but the next data type can be 23.1 and now only zero is written into the database for the property.

To Reproduce
Steps to reproduce the behavior:

1. Send Value for attribute "temp" with Type: "Property" and value 0
2. Check type of new created Column for attribute "temp": bigint
3. Send Value for attribute "temp" with Type: "Property" and value 0.1
4. Check content of added entry for attribute "temp": null

Expected behavior
Quantumleap should derive Number/Float instead of Integer since the data are transmitted from IOTAgent to Context Broker to Quantumleap as JSON Number.

Environment (please complete the following information):

• Quantumleap 1.0.0

[c0c0n3]

@juliangrube1988 thanks for reporting this. Sadly, I don't think there's any easy fix. In the case of Property, QL has to guess what the DB type should be, which happens here:

• https://github.com/orchestracities/ngsi-timeseries-api/blob/master/src/translators/sql_translator.py#L1707C18-L1710

If this is the first time QL sees that property for an entity, it will have to determine the DB column type and after that point, the column type can't be changed. (Unfortunately this is a limitation with QL which would be very hard to work around given the current implementation.)

Now if you'd like to have a float type in the DB, the first value you send should be a float. Sending 0 as first value, will result in an integer DB type, because to Python 0 is an int:

>>>  isinstance(0, int)
True

and this check happens in the code I linked above before the float type check---we can't change this for backward-compatibility reasons.

So if you want float, the first value you send should be 0.0 or 23.0 since in that case the resulting DB type should be float too:

>>> isinstance(0.0, int)
False
>>> isinstance(0.0, float)
True

Can you try testing again but this time sending 0.0 in the first record instead of 0?

Thanks alot!

[juliangrube1988]

Thanks for the quick reply!

Unfortunately this won't solve our Problem since we use ngsi-ld and data are transferred as json between services.

I am currently using a workaround by changing the sql_translator.py to always use float for numbers.

[c0c0n3]

this won't solve our Problem since we use ngsi-ld and data are transferred as json between services

Ha! I see what your problem is now. Then what I suggested won’t help since technically JSON makes no distinction between integer and floating-point:

• https://www.json.org/json-en.html

So a service is free to output 0 or 0.0 and the consumer should treat them as equivalent. Indeed, this is a problem with the current QL implementation of NGSI-LD which is tangled up with NGSI-v2. If you use LD, then sending 0 or 0.0 should result in QL interpreting both as float values, but that doesn’t happen because the NGSI-v2 code interferes with the LD flow. Like I said, we can’t easily change this now, but we’ll fix this when we drop support for NGSI-v2.

changing the sql_translator.py to always use float for numbers.

yes, you can do that—as long as you stick with NGSI-LD and never handle v2 data :-)