BUG #18950: pgsql function that worked in Postgresql 16 does not return in Postgresql 17

The following bug has been logged on the website:

Bug reference:      18950
Logged by:          Lowell Hought
Email address:      lowell.hought@gmail.com
PostgreSQL version: 17.5
Operating system:   Red Hat Enterprise Linux release 8.10 (Ootpa)
Description:

A pgsql function that returns Table is relatively simple.  It accepts two
arguments; a date and an interval.  It uses these to arguments in a query
that returns a result set.  This function worked in Postgresql 16.1 and
Postgresql 16.9.  However, it does not work in versions 17.0 nor in 17.5
Here is my build sequence for version 17.5, the last version I tested.
./configure --prefix=/usr/local/pgsql/17/ --with-openssl --with-ldap
--with-systemd --with-libxml --with-libxslt
gmake world
gmake check : # All 222 tests passed.
sudo gmake install-world
su root
mkdir /usr/local/pgsql/17/data
chown postgres /usr/local/pgsql/17/data
su postgres
/usr/local/pgsql/17/bin/initdb -D /usr/local/pgsql/17/data
Success. You can now start the database server using:
    /usr/local/pgsql/17/bin/pg_ctl -D /usr/local/pgsql/17/data -l logfile
start
/usr/local/pgsql/17/bin/pg_ctl -D /usr/local/pgsql/17/data -l
/usr/local/pgsql/17/data/logfile start
/usr/local/pgsql/17/bin/createdb test
/usr/local/pgsql/17/bin/psql test
psql (17.5)
Type "help" for help.
Restore from backup:
/usr/local/pgsql/17/bin/psql  -d test -Upostgres -f /bak/db/db_backup.sql
Then I run psql and execute this command:
SELECT * FROM report.GetReportPoolTrainees(CURRENT_DATE, '1 year');
The result is that even after 12 hours the function does not return.  There
is no response whatsoever.  I tested this both on Redhat 8.10 and on Red Hat
Enterprise Linux release 8.10 (Ootpa) and on Rocky Linux release 8.7 (Green
Obsidian) with identical results.  On both systems the function call works
as expected in version 16, but in version 17 it never returns.
Another interesting fact: I run the query contained in within the function,
it executes as expected and returns the expected results.  It is only when
called via the function that it fails.
Here is the function definition:
CREATE OR REPLACE FUNCTION report.GetReportPoolTrainees(
venddate DATE, vtimespan INTERVAL
) RETURNS TABLE (
        facility_key INT,                       facility_code TEXT,
traineeid INT,
        ndc_emp_id INT,                         facility_eod DATE,
hire_status TEXT,
        trainee_start_date DATE,        devstatus_date DATE,    status TEXT,
        status_days INT
)AS $$
DECLARE
plenddate DATE;
plinterval INTERVAL;
BEGIN
        IF venddate IS NULL THEN
                plenddate = CURRENT_DATE;
        ELSE
                plenddate = venddate;
        END IF;
        IF vtimespan IS NULL THEN
                SELECT value::INTERVAL INTO plinterval FROM
ntd.site_settings WHERE variable = 'PPT_DATA_YEARS';
                IF NOT FOUND THEN
                        plinterval = '10 years';
                END IF;
        ELSE
                plinterval = vtimespan;
        END IF;
        RETURN QUERY
        WITH t AS (
                SELECT DISTINCT td.facility_key, td.traineeid,
td.ndc_emp_id, td.facility_eod,
                        td.tia_code AS hire_status, td.trainee_start_date,
td.devstatus_date, td.status, td.status_days
                  FROM report.vw_training_details td
        ),
        f AS (
                SELECT *
                  FROM ntd.facility_dim fd
                  JOIN (
                        SELECT hft.facility_key,
MAX(facility_type_start_date) AS facility_type_start_date
                          FROM ntd.history_facility_type hft
                          GROUP BY hft.facility_key
                        )DT1 USING(facility_key)
        )
        SELECT DT1.facility_key, DT1.facility_code, t.traineeid,
t.ndc_emp_id,
                t.facility_eod, t.hire_status, t.trainee_start_date,
t.devstatus_date, t.status, t.status_days
          FROM t
          JOIN (SELECT l.facility_key, l.facility_code,
                                   CASE
                                   WHEN facility_type_start_date IS NOT NULL
AND facility_type_start_date > l.last_devstatus_date - plinterval
                                                THEN
facility_type_start_date
                                   ELSE l.last_devstatus_date - plinterval
                                   END AS last_devstatus_date
                          FROM (SELECT t.facility_key, f.facility_code,
MAX(t.devstatus_date) AS last_devstatus_date
                                          FROM t
                                          LEFT JOIN f USING(facility_key)
                                         WHERE (t.status = 'Completed' OR
t.status = 'Did Not Complete')
                                           AND t.devstatus_date <= plenddate
                                         GROUP BY t.facility_key,
f.facility_code
                                )l
                          LEFT JOIN (SELECT pef.facility_key,
MAX(facility_type_start_date) AS facility_type_start_date
                                                   FROM
ntd.ppt_exception_facilities pef
                                                  WHERE
facility_type_start_date < CURRENT_DATE
                                                  GROUP BY pef.facility_key
                                ) e USING(facility_key)
                )DT1 USING(facility_key)
         WHERE t.devstatus_date <= plenddate
           AND t.devstatus_date >= DT1.last_devstatus_date
        ;
END;
$$ LANGUAGE plpgsql;
This is the first time I have ever encountered a bug and so I have no
experience with reporting it.  I understand I need to send the underlying
table structures but not sure if I do it here or some other way.  I do not
see an option to attach files.
I love Postgres!  It is a terrific database and has served me well for about
20 years.