BUG #17916: Expression IN list translates to unqualified operator

The following bug has been logged on the website:

Bug reference: 17916
Logged by: RekGRpth
Email address: rekgrpth(at)gmail(dot)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(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
>
> PG Bug reporting form <noreply(at)postgresql(dot)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

On Wed, May 3, 2023 at 6:43 AM RekGRpth <rekgrpth(at)gmail(dot)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.