Incorrect cs_limit behavior when used with window functions - issues when using from_pos parameter

[pavitrakumar78]

Hi,

I have another issue with using shifts and window functions. I think this might be related to another issue (#55 ) I opened yesterday.

When using cs_limit on a plain timeseries, all the results are as expected. But when using it on a timeseries and then wrapping it inside a window function gives output that is not consistent with the documentation.

Example:

select cs_project( cs_window_avg((cs_limit(cs_parse('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,
18,19,20,21,22,23,24,25,26,27,28,29,30}', 'int4'),till_pos:=5)), 4)  ) as ts;

I've used till_pos as 5 which means cs_window_avg must operate on the subset [1,2,3,4,5,6] and it does - the above query returns 6 rows which is as expected.

Similarly, I use the inverse with cs_window_avg

select cs_project( cs_window_avg((cs_limit(cs_parse('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}', 'int4'),till_pos:=-5)), 4)  ) as ts;

Gives output of 26 rows - both the examples above are consistent with the docs.

But the issue is with from_pos

select cs_project( cs_window_avg((cs_limit(cs_parse('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}', 'int4'),from_pos:=5)), 4)  ) as ts;

The above query returns 30 rows which is wrong. The same query above without cs_window_avg returns 25 row - which is correct but if I use a window function, the shift is ignored (same issue I observed when using << as reported in #55 )

Moreover, the below query returns no rows at all! Which is very surprising! But the normal query without the window function returns 15 rows correctly.

select cs_project( cs_window_avg((cs_limit(cs_parse('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,
16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}', 'int4'),from_pos:=-15)), 4)  ) as ts;

I'm not 100% sure if this and issue #55 are related so I created a new issue for this. What could be the reason for this? Is the imcs_limit_next function not handling a specific case? I'm checking it right now but not sure if I'm looking at the right place.

[pavitrakumar78]

Hi,

I just saw the fix. I tried a fresh install and this solves the shifting issue for both #56 (this issue) and #55. Can I close this or are you still working on any other cases related to this?

Thank you for the fix!

[knizhnik]

Yes, #55 is caused by the same problem as #56. This is why I have linked this commit only to #56. Please close the tickets - I didn't close it myselfm because from my point of view only the person opened the ticket should close it if he agree that issue is fixed.

[pavitrakumar78]

Thank you for confirming. I'll close the issues!