Why doesn't maxItems (the parameter of QueryPropertyHistory) work properly?

[rwiseman]

Intuitively, if you set maxItems to 10, then you only expect up to 10 rows back from the service.  In my experience, the number of rows returned is about four times whatever I set maxItems to.  In a simple empirical test, I tried a variety of values of maxItems for the same timeframe, and here's what I got back:

maxItems	Rows returned
1	4
2	8
3	12
4	16
5	19
6	22
7	25

Furthermore, I would expect as I increase maxItems, I would simply get additional rows with a later time; this too is not what happens.  I always get new rows with each increase of maxItems, but they aren't all later in time!  I've summarised this below:

timestamp	First value of maxItems where timestamp is seen
2016-11-23 01:00:00.224	1
2016-11-23 01:00:00.226	1
2016-11-23 01:00:00.231	1
2016-11-23 01:01:00.180	2
2016-11-23 01:01:00.182	2
2016-11-23 01:01:00.184	1
2016-11-23 01:01:00.186	2
2016-11-23 01:02:00.224	3
2016-11-23 01:02:00.227	3
2016-11-23 01:02:00.230	3
2016-11-23 01:03:00.161	4
2016-11-23 01:03:00.164	4
2016-11-23 01:03:00.168	4
2016-11-23 01:04:00.173	5
2016-11-23 01:04:00.175	5
2016-11-23 01:04:00.179	5
2016-11-23 01:05:00.132	6
2016-11-23 01:05:00.135	6
2016-11-23 01:05:00.138	6
2016-11-23 01:06:00.130	7
2016-11-23 01:06:00.132	7
2016-11-23 01:06:00.135	7
2016-11-23 01:09:00.136	2
2016-11-23 01:16:00.220	3
2016-11-23 01:24:00.157	4

Unless I'm missing something fundamental, maxItems really doesn't work properly!

PS - I've seen a post by Pat Nash related to this, but it doesn't address exactly what's going on with maxItems!

[ttielebein]

What version of ThingWorx are you in?

[rwiseman]

I'm using ThingWorx 7.0.4-b44.

[ttielebein]

I just tested this in 7.2.4, and there were no issues with either streams or value streams. Perhaps if this was a bug, it is already fixed in newer versions.

[rwiseman]

I just tested this in 7.2.5 and the problem persists.  I clicked the "Test" button for QueryPropertyHistory on one of my Things, set maxItems to 10 and clicked "Execute Service".  I got back new fewer than 50 items!  I tried again, setting maxItems to 1 and still got back 5 items.  maxItems=2 => 10 items.

[ttielebein]

Can you tell me specifically what type of stream you are using? Where is the data coming from? Are you in Neo or Postgres?

[rwiseman]

It's a Value Stream attached to a Thing that has a number of logged properties (the one I just checked has 28).  I don't know whether it's significant, but in the case of the one I just checked, the Value Stream is attached to the Thing Template.  There's nothing special about the Value Stream as far as I can tell: it has minimal settings and uses the ThingworxPersistenceProvider.  I'm using Postgres.

By the way, I've upgraded to ThingWorx 7.3 and this issue is still evident.  When I tried QueryPropertyHistory just now (via the "Test" button) I requested a maximum of 5 items and got back 58!

[jkaczynski]

Hello Richard Wiseman,

Just tested on Thingworx 7.2.1-b71 with H2. Logged a lot of Number properties to Value Stream.

QueryPropertyHistory is working fine, returning [maxItems] rows. If I need to get oldest data first, this boolean param is also working.

Could you provide a more detailed info regarding your environment, as Tori Tielebein asked.

What version of Thingworx and persistence provider do you use? What type of property do you log? From the service you use I can guess that you log to the Value Stream. Do you invoke QueryPropertyHistory on the Thing, not on the Stream?

Regards,

Jakub.

[rwiseman]

My previous comment is now online; does it tell you what you need to know?

[jkaczynski]

Hello Richard,

Now I'm not able to test and replicate it on Postgre-backed Thingworx, I'll be able to do it later, so after that I'll come back to you with the results.

[jkaczynski]

Hello Richard Wiseman​,

I tried to reproduce your issue on Thingworx 7.2.3 with PostgreSQL and couldn't.

I have a Template-set ValueStream and 3 Things that pushed data to this VS.

Have you checked if there is anything related in Thingworx logs?

Regards,

J.

[rwiseman]

Thanks Jakub (sorry, typo before!).  I think the difference could be something I mentioned in a previous post: "It's a Value Stream attached to a Thing that has a number of logged properties (the one I just checked has 28)."  You've only got one logged property; I've got multiple.  I think it could be the combination of multiple logged properties changing at different times that's causing the problems.

I don't recall whether I checked the logs for this issue, but I will do so next time I'm logged on.

[jkaczynski]

Hello Richard,

Sorry, I didn't notice that post of you before - and yes, that could cause this behavior. But not consistently. This behavior happens only, when in the requested number of items there are not all properties. Notice this:

\

In comparison with:

In the second, it returned maxItems and - because property b and c was missing - a missing row for b and missing property for c.

If I change maxItems to 2, then it would return 4 rows. That's normal behavior of VS (don't know if intended).

In this case, if you'd like to make it more stable and certain, I recommend using Query<Type>PropertyHistory, e.g. QueryNumberPropertyHistory for each property  separately. Then it would return only maxItems, without unexpected results.

Regards,

J.

[rwiseman]

I've just tried it and yes, if I tick "oldestFirst" the number of rows with all properties populated does indeed correspond to the value of "maxItems" that I specified -- but I still get a whole load of other rows first.  Clearly there's a bug in that service!

Also, perhaps I don't understand what "oldestFirst" means: I would expect that to affect the order in which the rows are returned, but the actual data would be the same (i.e. if you don't tick "oldestFirst", you get the same data back but with the rows in the opposite order).  I get completely different data, however.

And something else I've just noticed (now that I see there's a pattern) is that if I don't tick "oldestFirst" I get lots of extra data back and then the last exactly N rows (where N is the value of "maxItems") have only one property populated.  (So this is the opposite of where I have "oldestFirst" ticked: in that case, it's exactly N rows of all properties having data; whereas in this case it's exactly N rows of only one property having data.)  So also a bug, presumably!

I guess I should report it as a bug now that we've confirmed it doesn't do what it should!

Thanks for the suggestion about using alternative services.  It's a useful workaround although my reason for using QueryPropertyHistory was that I wanted to display multiple values on a line chart, so I would then have to generate the Infotable for that chart manually is I used the alternative services.

Regards,

Richard

[jkaczynski]

Notice, that Streams are not table-based data storage options, so it's hard to define "n rows". If you request for n items and there is no value for specific property, query returns more rows to assure that result has at least n items for each property. That's my understanding.

And - 'oldestFirst' concerns the query, not result sorting. If you set it to true, the query would return n oldest values that are persisted in this Value Stream and matches other criterias (start date, end date, query).

If you report it as a bug, it would be great if you can share the response - it's kinda interesting.

[rwiseman]

Will do, and thanks for all the help.