'interpolate previous value' expression order does not matter?

ftobin

When doing an X interpolate previous value Y, the order of X and Y doesn't seem to matter. Is this intentional?

I'd expect that if I did X interpolate previous value Y I'd get the 'next Y after X'.
And if I did Y interpolate previous value X I'd get the 'next X after Y.
But it seems only dependent on the join order, not the expression order.

With the below code, I'd expect the result set to have the 'aTicks' row at 12:03, but it get the 12:00 line, which happens before the hTicks row at 12:01.

CREATE TABLE bizint.hTicks (
   stock VARCHAR(20), 
   time TIME, 
   price NUMERIC(8,2)
);
CREATE TABLE bizint.aTicks (
   stock VARCHAR(20), 
   time TIME, 
   price NUMERIC(8,2)
);

INSERT INTO bizint.hTicks VALUES ('HPQ', '12:01', 51.00);
INSERT INTO bizint.aTicks VALUES ('ACME', '12:00', 340.00);
INSERT INTO bizint.aTicks VALUES ('ACME', '12:03', 340.10);

SELECT h.*, a.* FROM bizint.hTicks h
LEFT OUTER JOIN bizint.aTicks a
   -- This is what code would normally look like:
    -- ON h.time INTERPOLATE PREVIOUS VALUE a.time
   -- But we reverse the expression order here:
    ON a.time INTERPOLATE PREVIOUS VALUE h.time;

Result:

|stock|time |price|stock|time |price|
|-----|---------------|-----|-----|---------------|-----|
|HPQ |12:01:00.000000|51 |ACME |12:00:00.000000|340 |

Bryan_H

This syntax may be confusing, but it's what I would expect from the documentation: "Interpolated values come from the table that contains the null, not from the other table." Please see https://docs.vertica.com/23.3.x/en/sql-reference/language-elements/predicates/interpolate/

ftobin

Thank you! Certainly extremely confusing!