Is it bug of interpolate previous value?

Dmitry_Dremin

I have Left Join of 2 tables with a ON clause included equality of ids and interpolate previous value of date columns.
I expect if the right table doesn't have any ids and the left table has then right table's columns are NULL in result , BUT they aren't.

For example:
Tables with different single ids:

create table public.interpolate_left
as select 781818000266::int as id, current_date::date as event_date
order by id, event_date
segmented by hash(id) all nodes;

create table public.interpolate_right
as select 781745000057::int as id, (current_date-1)::date as event_date
order by id, event_date
segmented by hash(id) all nodes;

I make join:

with
sq_left as (
select id, event_date from public.interpolate_left where id = 781818000266
),
sq_right as (
select id, event_date from public.interpolate_right where id = 781745000057
)
select l.id l_id, r.id r_id, l.event_date l_date, r.event_date r_date
from sq_left l
left join sq_right r
on l.id = r.id and l.event_date interpolate previous value r.event_date;

I get NOT NULL result for the right table, but it works how I expected with hint ENABLE_WITH_CLAUSE_MATERIALIZATION.

---

Is it bug of interpolate previous value?

It replayed on Vertica Analytic Database v9.2.1-5 (1 and more nodes) and v10.1.0-0 (1 node).

Vertica_Curtis

That's an interesting result. It looks like hint is causing the query order of operations to adjust. But I think this is your real culprit here:
on l.id = r.id

What appears to be happening is that the hint is changing the order in which the optimizer does things. Which is kind of what hints do.

It is an interesting result, but I think the bigger issue is that l.id = r.id is kind of incompatible with interpolate previous value in this case. Which one do you want? A left outer join on ID, or for the query to pick up the previous record based on the date? You're asking for two things, and getting two different answers as a result of the hint. If you're expecting interpolate_right's columns to be null, don't use interpolate. It's like a fuzzy join - it's going to find a record. But if you're trying to ensure that the join keys match, it's going to override that, so it's probably not what you want in this case.

Dmitry_Dremin

For example:
On the one hand I have table with key columns ID and event_date (public.interpolate_left), which contains state of ID's day by day.
On the other hand historical table of new attribute exists (public.interpolate_right).
I want to combine these tables taking actual attribute from the last table by the moment of event_date from the first table.
That's why I used left join with equality of ID's with interpolate previous value of date columns. And it works fine(!!!), if I don't filter certain ID's.
"Interesting result" appears only with filters in subquery. Why?