pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17101: Inconsistent behaviour when querying with anonymous composite types
5+ messages / 4 participants
[nested] [flat]
* BUG #17101: Inconsistent behaviour when querying with anonymous composite types
@ 2021-07-12 08:37 PG Bug reporting form <noreply@postgresql.org>
2021-07-12 08:44 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Andrew Kiellor <akiellor@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2021-07-12 08:37 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: akiellor@gmail.com
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)');
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types
2021-07-12 08:37 BUG #17101: Inconsistent behaviour when querying with anonymous composite types PG Bug reporting form <noreply@postgresql.org>
@ 2021-07-12 08:44 ` Andrew Kiellor <akiellor@gmail.com>
2021-07-12 14:16 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew Kiellor @ 2021-07-12 08:44 UTC (permalink / raw)
To: Andrew Kiellor <akiellor@gmail.com>; pgsql-bugs@lists.postgresql.org
Sorry I omitted the output. It is as follows:
DROP TABLE
DROP TYPE
CREATE TYPE
CREATE TABLE
INSERT 0 1
column1
---------
(0)
(1 row)
psql:test.sql:14: ERROR: input of anonymous composite types is not
implemented
LINE 1: SELECT * FROM table1 WHERE column1 = '(0)';
^
column1
---------
(0)
(1 row)
psql:test.sql:20: ERROR: input of anonymous composite types is not
implemented
LINE 1: SELECT * FROM table1 WHERE column1 IN ('(0)');
On Mon, Jul 12, 2021 at 1:38 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> 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)');
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types
2021-07-12 08:37 BUG #17101: Inconsistent behaviour when querying with anonymous composite types PG Bug reporting form <noreply@postgresql.org>
2021-07-12 08:44 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Andrew Kiellor <akiellor@gmail.com>
@ 2021-07-12 14:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-07-12 14:26 ` BUG #17101: Inconsistent behaviour when querying with anonymous composite types David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-07-12 14:16 UTC (permalink / raw)
To: Andrew Kiellor <akiellor@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* BUG #17101: Inconsistent behaviour when querying with anonymous composite types
2021-07-12 08:37 BUG #17101: Inconsistent behaviour when querying with anonymous composite types PG Bug reporting form <noreply@postgresql.org>
2021-07-12 08:44 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Andrew Kiellor <akiellor@gmail.com>
2021-07-12 14:16 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-07-12 14:26 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-12 15:07 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-07-12 14:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Kiellor <akiellor@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Monday, July 12, 2021, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
>
>
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)');
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types
2021-07-12 08:37 BUG #17101: Inconsistent behaviour when querying with anonymous composite types PG Bug reporting form <noreply@postgresql.org>
2021-07-12 08:44 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Andrew Kiellor <akiellor@gmail.com>
2021-07-12 14:16 ` Re: BUG #17101: Inconsistent behaviour when querying with anonymous composite types Tom Lane <tgl@sss.pgh.pa.us>
2021-07-12 14:26 ` BUG #17101: Inconsistent behaviour when querying with anonymous composite types David G. Johnston <david.g.johnston@gmail.com>
@ 2021-07-12 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2021-07-12 15:07 UTC (permalink / raw)
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>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-07-12 15:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-12 08:37 BUG #17101: Inconsistent behaviour when querying with anonymous composite types PG Bug reporting form <noreply@postgresql.org>
2021-07-12 08:44 ` Andrew Kiellor <akiellor@gmail.com>
2021-07-12 14:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-07-12 14:26 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-12 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox