[Bug]: Lateral Joins are unable to perform with even 1 record being queried

[KamalAman]

What type of bug is this?

Performance issue, Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

Lateral Joins on Materialized Views produce a temporary file size exceeds temp_file_limit error on a trivial example

TimescaleDB version affected

2.14.2

PostgreSQL version used

15.6

What operating system did you use?

OSX

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

Use a hypertable with > 50m records, create a cAgg, and then query the data

CREATE MATERIALIZED VIEW IF NOT EXISTS "test_cagg"
    WITH (timescaledb.continuous, timescaledb.materialized_only = false) as
    select
        time_bucket(INTERVAL '1 minute', timestamp) AS timestamp
    from timeseries ts
    group by
      time_bucket(INTERVAL '1 minute', timestamp)
    WITH DATA;

Go query the data

select * from ( VALUES 
        ('["2024-05-21 10:00:00-04","2024-05-21 10:01:00-04")'::tstzrange),
	('["2024-05-21 10:01:00-04","2024-05-21 10:02:00-04")'::tstzrange)
) AS "w" ("timerange")
JOIN LATERAL (
	select * from test_cagg ts
	where ts."timestamp" > lower(w.timerange)  and ts."timestamp" < upper(w.timerange) 
) AS "ts" ON 1 = 1;

There is only 1 record within this timeframe of 2 minute window and the query produces a temporary file size exceeds temp_file_limit or timeout.

This happens when there are 2 or more windows in w,

This might be related to 4783