From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Andrew Kiellor <akiellor@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17101: Inconsistent behaviour when querying with anonymous
 composite types
In-reply-to: 
 <CAKFQuwY0QBhO_NrXs-Zf_Hy2Xy5mu8eMrYXN9_araRjjNSpK_w@mail.gmail.com>
References: <17101-a6faa1f74b35062d@postgresql.org>
 <CAJ_-DsS+NTXWz472c=jCJHNZ3u7LM=S+500Yi7XJAP1vcFs8KQ@mail.gmail.com>
 <2947259.1626099364@sss.pgh.pa.us>
 <CAKFQuwY0QBhO_NrXs-Zf_Hy2Xy5mu8eMrYXN9_araRjjNSpK_w@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Mon, 12 Jul 2021 07:26:43 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2949601.1626102444.1@sss.pgh.pa.us>
Date: Mon, 12 Jul 2021 11:07:24 -0400
Message-ID: <2949602.1626102444@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2949602.1626102444%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Why is the multi-valued IN expression special here?  I would not expect the
> more-than-one element IN clause to obey different rules than a one element
> IN clause
> -- passing scenario - IN query with multiple anonymous composite types
> SELECT * FROM table1 WHERE column1 IN ('(0)', '(0)');

Yeah, that's pretty weird and non-orthogonal.  With multiple non-Var
values on the RHS, transformAExprIn tries to make a ScalarArrayOpExpr,
for which it has to infer an array type for the array RHS, which it
does like this:

        /*
         * Try to select a common type for the array elements.  Note that
         * since the LHS' type is first in the list, it will be preferred when
         * there is doubt (eg, when all the RHS items are unknown literals).
         */
        allexprs = list_concat(list_make1(lexpr), rnonvars);
        scalar_type = select_common_type(pstate, allexprs, NULL, NULL);

This is fishy for various reasons, but the performance advantages of
ScalarArrayOpExpr are enough that we avert our eyes from the corner cases.
(In practice, people don't tend to write IN lists with intentionally
varying RHS types anyway.)

Meanwhile, the single-element IN is handled exactly like "x = y".

			regards, tom lane