Weird behaviour of lead() inside and outside CTE

I have a date series file below.

base_date_series.zip (57.1 KB)

It is a csv with the column as_of_date which goes from 1970-01-01 to 2050-01-01

It is continuous and does not contain any gap.

=======

When using the lead() window function inside a CTE like below

with cte as (
    SELECT *,
    lead(as_of_date, 1) over(order by as_of_date) as as_of_date__lead_by_1,
      lead(as_of_date, 2) over(order by as_of_date) as as_of_date__lead_by_2 ,
      lead(as_of_date, 3) over(order by as_of_date) as as_of_date__lead_by_3
 FROM Minio.finance.l1."base_date_series"

)


 select * from cte
 where as_of_date__lead_by_2 is null
 

I notice that when the offset is greater than 1, then I got null values.

==========

But interestingly, if the lead() over() is outside any CTE, there’s no gap.


    SELECT *,
    lead(as_of_date, 1) over(order by as_of_date) as as_of_date__lead_by_1,
      lead(as_of_date, 2) over(order by as_of_date) as as_of_date__lead_by_2
 FROM Minio.finance.l1."base_date_series"
 where as_of_date >= '1980-11-01'
order by as_of_date

—> Left: running the lead() over() with offset = 2 OUTSIDE of any CTE, and the gap is gone
—> Right: The previous screenshot showing the gap when runnning lead() over() with offset =2 INSIDE a CTE

===========
In short,

There’s a gap when

  • offset in lead() over() is greater than 1; and
  • window function lead() over() is used inside a CTE

====

I am using dremio/dremio-oss:25.0.0

It appears you have stumbled on the proper issue. Our engineers are investigating.
Thank you for bringing this up!

1 Like