Thread: BUG #17916: Expression IN list translates to unqualified operator

BUG #17916: Expression IN list translates to unqualified operator

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      17916
Logged by:          RekGRpth
Email address:      rekgrpth@gmail.com
PostgreSQL version: 15.2
Operating system:   alpine in docker
Description:

```sql
create schema qwe;
create or replace function qwe.chartexteq(a char, b text) returns boolean
language plpgsql as $$begin perform 1/0;return true;end;$$;
create operator qwe.= (leftarg = char, rightarg = text, function =
qwe.chartexteq, commutator = operator(qwe.=), hashes, merges);
set search_path = qwe;
explain (costs off, verbose on) select i from generate_series(1, 10) i where
i::char in (2::text);
                  QUERY PLAN                   
-----------------------------------------------
 Function Scan on pg_catalog.generate_series i
   Output: i
   Function Call: generate_series(1, 10)
   Filter: ((i.i)::character(1) = '2'::text)
 Query Identifier: -7727870581584713193
(5 rows)
select i from generate_series(1, 10) i where i::char in (2::text);
ERROR:  division by zero
CONTEXT:  SQL statement "SELECT 1/0"
PL/pgSQL function chartexteq(character,text) line 1 at PERFORM
```
I expected, that IN list translates to pg_catalog.=


PG Bug reporting form <noreply@postgresql.org> writes:
> create operator qwe.= (leftarg = char, rightarg = text, function =
> qwe.chartexteq, commutator = operator(qwe.=), hashes, merges);
> set search_path = qwe;
> explain (costs off, verbose on) select i from generate_series(1, 10) i where
> i::char in (2::text);

> I expected, that IN list translates to pg_catalog.=

Why would you expect that?  It'd make it impossible to use IN
with user-defined data types.  In this case, you made an operator
that is a closer match to the given datatypes (ie, "char = text")
than the native "text = text" operator, so it used that one.

I've not checked the code, but my recollection is that X IN (Y) just
resolves to the same equality operator you'd get by writing X = Y.
There's been some discussion about allowing a schema qualifier to
be included in the syntax, but nothing's been done about that.

            regards, tom lane



Thank you for the clarification.

How can I safely use an expression IN list in extensions?

with bst regrds, RekGRpth

ср, 3 мая 2023 г. в 18:40, Tom Lane <tgl@sss.pgh.pa.us>:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > create operator qwe.= (leftarg = char, rightarg = text, function =
> > qwe.chartexteq, commutator = operator(qwe.=), hashes, merges);
> > set search_path = qwe;
> > explain (costs off, verbose on) select i from generate_series(1, 10) i where
> > i::char in (2::text);
>
> > I expected, that IN list translates to pg_catalog.=
>
> Why would you expect that?  It'd make it impossible to use IN
> with user-defined data types.  In this case, you made an operator
> that is a closer match to the given datatypes (ie, "char = text")
> than the native "text = text" operator, so it used that one.
>
> I've not checked the code, but my recollection is that X IN (Y) just
> resolves to the same equality operator you'd get by writing X = Y.
> There's been some discussion about allowing a schema qualifier to
> be included in the syntax, but nothing's been done about that.
>
>                         regards, tom lane



Re: BUG #17916: Expression IN list translates to unqualified operator

From
"David G. Johnston"
Date:
On Wed, May 3, 2023 at 6:43 AM RekGRpth <rekgrpth@gmail.com> wrote:
How can I safely use an expression IN list in extensions?


search_path control is your only knob if you want to use IN.  Otherwise, you can typically reformulate an IN expression into an "op ANY" expression and then "op" can be schema-qualified.

David J.