Обсуждение: BUG #17101: Inconsistent behaviour when querying with anonymous composite types

The following bug has been logged on the website:

Bug reference:      17101
Logged by:          Andrew Kiellor
Email address:      akiellor@gmail.com
PostgreSQL version: 13.3
Operating system:   Debian (docker)
Description:

I've observed some inconsistent behaviour when querying with anonymous
composite types. The following queries illustrate the observations:

-- cleanup
DROP TABLE IF EXISTS table1;
DROP TYPE IF EXISTS type1;

-- scenario
CREATE TYPE type1 AS (x int);

CREATE TABLE table1 (column1 type1);

INSERT INTO table1 (column1) VALUES ('(0)');

-- passing scenario - equality with typed composite type
SELECT * FROM table1 WHERE column1 = '(0)'::type1;

-- failing scenario - equality with anonymous composite type
SELECT * FROM table1 WHERE column1 = '(0)';

-- passing scenario - IN query with multiple anonymous composite types
SELECT * FROM table1 WHERE column1 IN ('(0)', '(0)');

-- failing scenario - IN query with single anonymous composite type
SELECT * FROM table1 WHERE column1 IN ('(0)');

Andrew Kiellor <akiellor@gmail.com> writes:
> Sorry I omitted the output. It is as follows:

> psql:test.sql:14: ERROR:  input of anonymous composite types is not implemented
> LINE 1: SELECT * FROM table1 WHERE column1 = '(0)';
>                                              ^

I think this is operating as designed.  I agree it'd be slightly more
convenient if the parser would infer that the RHS must be of the same
type as the LHS, but shoehorning that into the existing system design
seems problematic.  The record_eq operator doesn't actually require
that its inputs be of identical composite types, only compatible ones.
To continue your example:

regression=# CREATE TYPE type2 AS (xyz int);
CREATE TYPE
regression=# SELECT * FROM table1 WHERE column1 = '(0)'::type2;
 column1
---------
 (0)
(1 row)

regression=# CREATE TYPE type3 AS (x float);
CREATE TYPE
regression=# SELECT * FROM table1 WHERE column1 = '(0)'::type3;
ERROR:  cannot compare dissimilar column types integer and double precision at record column 1

So if the parser assumed that the inputs must be of the same composite
type, it'd be exceeding its authority, and would likely cause queries
that work today to start failing.

The back story here is that type "record" isn't really a polymorphic
type, though it behaves similarly to those types in some ways.  If we
were designing in a green field it'd make sense to treat "record"
according to the polymorphism rules.  But we're not; "record" is way
older than the polymorphics so it has various unique idiosyncrasies.
The one that's relevant here is that an input argument that's declared
to be "record" isn't required to be the same composite type as another
argument also declared as "record".

            regards, tom lane

Andrew Kiellor <akiellor@gmail.com> writes:
> Sorry I omitted the output. It is as follows:

> psql:test.sql:14: ERROR:  input of anonymous composite types is not implemented
> LINE 1: SELECT * FROM table1 WHERE column1 = '(0)';
>                                              ^

I think this is operating as designed.  I agree it'd be slightly more
convenient if the parser would infer that the RHS must be of the same
type as the LHS, but shoehorning that into the existing system design
seems problematic. 

"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