PipelineDB: continuous view output stream unexpectedly shows same (old) and (new) values

I am using PipelineDB 0.9.7u3

I played a little with continuous view output stream to find out if I could get a new continuous view just with some the updates.

This is my test case.

CREATE STREAM stream_test(ticketid text, val int, status text);

-- simple continuous view on stream_test
CREATE CONTINUOUS VIEW cv_test AS 
  SELECT 
    ticketid, 
    min(val) as v0, 
    keyed_min(val, status) as v0_status
  FROM stream_test 
  GROUP BY ticketid;

-- continuous view to keep cv_test's updates and insertions
CREATE CONTINUOUS VIEW cv_test_upin AS 
  SELECT 
    (new).ticketid, 
    (old).v0 as oldV0, 
    (old).v0_status as oldV0Status,
    (new).v0 as newV0, 
    (new).v0_status as newV0Status    
  FROM output_of('cv_test')

-- continuous view to keep just some cv_test's updates
CREATE CONTINUOUS VIEW cv_test_up AS 
  SELECT 
    (new).ticketid, 
    (old).v0 as oldV0, 
    (old).v0_status as oldV0Status,
    (new).v0 as newV0, 
    (new).v0_status as newV0Status    
  FROM output_of('cv_test')
  WHERE (old).v0 != (new).v0;

Let's put some data.

 INSERT INTO stream_test VALUES 
 ('t1', 124, 'open'),
 ('t2', 190, 'pending')

And as expected:

select * from cv_test;

"t2";190;"pending"

"t1";124;"open"

select * from cv_test_upin;

"t2";;"";190;"pending"

"t1";;"";124;"open"

select * from cv_test_up;

Then, some updates.

 INSERT INTO stream_test VALUES 
 ('t2', 160, 'waiting'),
 ('t1', 100, 'pending')

And as expected:

select * from cv_test;

"t2";160;"waiting"

"t1";100;"pending"

select * from cv_test_upin;

"t2";;"";190;"pending"

"t1";;"";124;"open"

"t2";190;"pending";160;"waiting"

"t1";124;"open";100;"pending"

select * from cv_test_up;

"t2";190;"pending";160;"waiting"

"t1";124;"open";100;"pending"

Now, some new data and some updates.

 INSERT INTO stream_test VALUES 
 ('t2', 90, 'spam'),
 ('t3', 140, 'open'),
 ('t1', 80, 'closed')

select * from cv_test; returned as expected, but select * from cv_test_upin;did not.

...

"t2";160;"waiting";90;"spam"

"t3";;"";140;"open"

"t1";80;"closed";80;"closed"

I expected last "t1" to be "t1";100;"pending";80;"closed"

Bug or expected behaviour?

Thanks.

Solution

After digging into this, it appears that you have indeed discovered some unexpected behavior, and most likely it's a bug. We are going to resolve it shortly, here is the issue:

https://github.com/pipelinedb/pipelinedb/issues/1797

After it's resolved we will publish an updated release.