Обсуждение: Improve upcasting for INT range and multi range types

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 1::INT4 <@ '{[1, 4),[6,19)}'::INT8MULTIRANGE
    -- ERROR: operator does not exist: integer <@ int8multirange

    SELECT 1::INT2 <@ '{[1, 4),[6,19)}'::INT4MULTIRANGE
    -- ERROR: operator does not exist: smallint <@ int4multirange

    SELECT '[2, 3]'::INT4RANGE <@ '[1, 42)'::INT8RANGE
    -- ERROR: operator does not exist: int4range <@ int8range

    SELECT 2::INT8 <@ '[1, 4)'::INT4RANGE
    -- ERROR: operator does not exist: bigint <@ int4range

etc.

In all these cases the smaller integer type can be upcasted to the
larger integer type.

Posted here since it doesn't seem like a bug, just a missing feature.

Thanks for reading
    Federico

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.

jian he <jian.universality@gmail.com> writes:
> Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1,
> 4)'::INT8RANGE is doable.

Yeah, it would require a considerable expansion of the scope of
what can be matched by a polymorphic operator.  I'm afraid that
the negative consequences (mainly, "ambiguous operator" failures
because more than one thing can be matched) would outweigh the
benefits.  It is kind of annoying though that the system can't
do the "obvious" right thing here.

            regards, tom lane

Hi,

Thanks for the reply. I suspected that there were technical reasons
that prevented the obvious right thing to be done.

Would adding overloads to the functions and operators be something
that could be considered as an acceptable solution?
I've tried a very naive solution and it seems to work (there are for
sure better options to declare the function overloads):

    begin;

    create function elem_contained_by_range(int4, int8range) returns
boolean as $$ select elem_contained_by_range($1::int8, $2) $$ LANGUAGE
SQL;
    create function elem_contained_by_range(int8, int4range) returns
boolean as $$ select elem_contained_by_range($1, $2::text::int8range)
$$ LANGUAGE SQL;

    create operator <@(
        LEFTARG = int4,
        RIGHTARG = int8range,
        FUNCTION = elem_contained_by_range,
        RESTRICT = rangesel,
        JOIN = contjoinsel,
        HASHES, MERGES
    );
    create operator <@(
        LEFTARG = int8,
        RIGHTARG = int4range,
        FUNCTION = elem_contained_by_range,
        RESTRICT = rangesel,
        JOIN = contjoinsel,
        HASHES, MERGES
    );

    select 2::int4 <@ '[1,9)'::int8range;
    select 2::int8 <@ '[1,9)'::int4range;

    rollback;

The major drawback is that every combination operator - type would
need its own overload creating a large number of them.

As a side note it seems that int4range cannot be casted automatically
to int8range.

Best regards,
    Federico

On Wed, 13 Dec 2023 at 05:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> jian he <jian.universality@gmail.com> writes:
> > Based on my interpretation, I don't think SELECT 2::INT4 <@ '[1,
> > 4)'::INT8RANGE is doable.
>
> Yeah, it would require a considerable expansion of the scope of
> what can be matched by a polymorphic operator.  I'm afraid that
> the negative consequences (mainly, "ambiguous operator" failures
> because more than one thing can be matched) would outweigh the
> benefits.  It is kind of annoying though that the system can't
> do the "obvious" right thing here.
>
>                         regards, tom lane