Re: Improve upcasting for INT range and multi range types - Mailing list pgsql-hackers

On Fri, Dec 8, 2023 at 4:21 AM Federico <cfederico87@gmail.com> wrote:
>
> Hi,
>
> Postgresql seems to be missing upcasting when doing INT range and
> multi-range operation, for example when checking if an int4 is inside
> an int8 range.
> Some non working example are the following
>
>     SELECT 2::INT4 <@ '[1, 4)'::INT8RANGE
>     -- ERROR: operator does not exist: integer <@ int8range

select  oprname,
        oprleft::regtype,
        oprright::regtype,
        oprcode
from    pg_operator
where   oprname = '<@';

look at the results, you can see related info is:
 oprname |  oprleft   |   oprright    |           oprcode
---------+------------+---------------+------------------------------
 <@      | anyelement | anyrange      | elem_contained_by_range
 <@      | anyelement | anymultirange | elem_contained_by_multirange

SELECT 2::INT4 <@ '[1, 4)'::INT8RANGE
It actually first does an operator sanity check, transforms
anyelement, anyrange to the detailed non-polymorphic data type.
 then calls the function elem_contained_by_range.
but it failed at the first step.

per doc https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC
Similarly, if there are positions declared anyrange and others
declared anyelement or anyarray, the actual range type in the anyrange
positions must be a range whose subtype is the same type appearing in
the anyelement positions and the same as the element type of the
anyarray positions. If there are positions declared anymultirange,
their actual multirange type must contain ranges matching parameters
declared anyrange and base elements matching parameters declared
anyelement and anyarray.

Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1,
4)'::INT8RANGE is doable.