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).
