QuantumLeap API Query doesn't find records

[pinkerltm]

We have Timeseries data of NGSI Devices from Dec. 2019 to now stored in CrateDB. So when I query Crate

select * from etdevice 
where entity_id = 'urn:ngsi-ld:Device:evn-joulie:FFFF644214006784:64002D0204BC'
  and time_index between '2020-04-04T12:00:00' and '2020-04-04T13:00:00' 
order by time_index desc;

I get 13 records (5 Minute steps) for the given timespan. But when I query QuantumLeap with the same parameters

/v2/entities/urn:ngsi-ld:Device:evn-joulie:FFFF644214006784:64002D0204BC/attrs/value?fromDate=2020-04-04T12:00:00&toDate=2020-04-04T13:00:00

I get

{
"description": "No records were found for such query.",
"error": "Not Found"
}

When I just change the dates to 2019-12-xx, I get 13 records either on CrateDB directly but also from QuantumLeap. The datasets certainly only differ in time_index and value, other fields have the same content in every row.

Versions: CrateDB: 4.1.7 QL: 0.8.0

P.S. Although we use 'ngsi-ld' in our ID fields, this data isn't a NGSI-LD model...we are just preparing to fit the standard as much as possible.

[c0c0n3]

@pinkerltm thanks for reporting this, we'll look into it.

use 'ngsi-ld' in our ID fields

it should make no difference to QL how you choose your entity ID values, any string should do. so it's most likely a genuine bug we've bumped into :-)

[chicco785]

@pinkerltm can you try without from and to? i suspect it may be related to that and last changes we made

[pinkerltm]

@pinkerltm can you try without from and to? i suspect it may be related to that and last changes we made

That works, but it only delivers values until a certain limit is reached (which is all in 2019, which also works with from and to anyway).

[c0c0n3]

until a certain limit is reached

results sets get capped at 10,000 records unless you explicitly specify a limit query param, in which case the least of the two is used. #319/#330 made the result set cap configurable to something else than 10,000.

[chicco785]

@pinkerltm we are going to check generated queries, something is clearly broken.

[pinkerltm]

until a certain limit is reached

results sets get capped at 10,000 records unless you explicitly specify a limit query param, in which case the least of the two is used. #319/#330 made the result set cap configurable to something else than 10,000.

Sorry, I wasn't clear... it returns only 534 rows although the database table holds 62.524 for this specific device.

[chicco785]

@pinkerltm can you export data and ship them to us?

[pinkerltm]

@chicco785 mhm...only via another channel as I am not allowed to publish it here. I'll send you a message where to get the timeseries of this device as CSV File, ok?

[pinkerltm]

@chicco785 Did you receive my testdata file and were you able to import it?

[chicco785]

@pinkerltm we got the data, but we hadn't time to work on it yet

[mclucky]

@chicco785 are there any news on this issue? We (@pinkerltm) using QL API for a big data energy project and rely on that functionality to work. Can we somehow help?

[chicco785]

@mclucky @pinkerltm it's a bad timing, with holidays and projects for customers, not sure when we will have time to luck into it

[pinkerltm]

@chicco785 I´ve spinned up the code locally, debugging the generated SQL Statement to sort things out. What I didn't know yet: QL inserts and fiware_servicepath = '' to the WHERE clause, so you expect the fiware_servicepath to be an empty string.

Our data rows which weren't returned have all fiware_servicepath set to NULL, as we don't use the servicepath and data got restored from a CSV File, so fiware-servicepath was never set.

To understand this and prevent future issues: Is this an NGSI requirement that fiware_servicepath is NOT NULL? (and therefore have to be set to an empty string)?

If we know how to prevent this, I think we are able to close this issue immediately

[chicco785]

ql should use the fiware_servicepath of the orginal entity in orion, is any path set in orion? and do you use any header? it could be that the query is not correct when fiware_servicepath is not set in the header

[pinkerltm]

Ok, thanks for clarifying this... as said, these rows were restored from CSV manually and therefore fiware_servicepath was not set. So it was my fault and I was able to solve this now by setting fiware_servicepath via an UPDATE Statement.

[chicco785]

@pinkerltm great!