BUG #18271: Re: Postgres policy exists bug

The following bug has been logged on the website:

Bug reference:      18271
Logged by:          Wladimir Trubizin
Email address:      vost_800@gmx.de
PostgreSQL version: 16.1
Operating system:   debian:bookworm-slim docker postgres:latest
Description:

Hi,

After submitting my initial report, I attempted to find a workaround for the
issue. However, during this process, I discovered the same behavior as with
the EXISTS operation, specifically when dealing with subqueries.

The common factor among all cases was that they were based on subqueries,
and the inconsistencies surfaced when either selecting a column with the
boolean type or using a WHERE clause against boolean. To illustrate,
consider the following example:

FALSE IN (
    SELECT is_private
    FROM public.profiles AS p
    WHERE p.user_id = user_id
)

I also investigated a subquery within a function called in a policy:

CREATE OR REPLACE FUNCTION public.is_user_private(_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
        SELECT is_private
        FROM public.profiles
        WHERE user_id = _user_id
    );
END;
$$ LANGUAGE plpgsql;

The same behavior was observed in this scenario as well.

The workaround I found was to store the value in a variable and then return
the variable instead of the value from the SELECT statement directly:

CREATE OR REPLACE FUNCTION public.is_user_private(_user_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    is_user_private BOOLEAN;
BEGIN
    SELECT is_private
    INTO is_user_private
    FROM public.profiles
    WHERE user_id = _user_id;

    RETURN is_user_private;
END;
$$ LANGUAGE plpgsql;

I hope this clarifies the issue. Let me know if you have any questions or if
further clarification is needed.

Best regards,

Wladimir