From: Tom Lane <tgl@sss.pgh.pa.us>
To: rwxrw@posteo.net
cc: pgsql-general@lists.postgresql.org
Subject: Re: Problems caused by type resolution for the unknown type
In-reply-to: <ccfa6e1b-4ed3-4829-898f-6992d8764aea@posteo.net>
References: <ccfa6e1b-4ed3-4829-898f-6992d8764aea@posteo.net>
Comments: In-reply-to rwxrw@posteo.net
	message dated "Thu, 28 Mar 2024 12:03:24 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3575084.1711636356.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 28 Mar 2024 10:32:36 -0400
Message-ID: <3575085.1711636356@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3575085.1711636356%40sss.pgh.pa.us>
Precedence: bulk

rwxrw@posteo.net writes:
> I had initially made a false assumption that only UNION, CASE, and Relat=
ed
> Constructs (listed in the documentation) are affected by this.

The examples you show aren't particularly about those things, they
are about use of sub-SELECTs, and specifically about the fact that
once we've finished parsing a sub-SELECT it's too late to revisit
decisions about what its output column types are.  The recursive
invocation of the parser will end by resolving the undecorated
NULL as "text", since there isn't any better choice.  Then the
outer invocation fails with a type mismatch.

In trivial cases such as your examples, we could imagine simply
leaving the sub-SELECT's output column type unresolved, but there
are problems with that:

1. It's inconsistent with what has to happen in other cases:
the sub-SELECT might contain constructs that force resolution
of the output column type, for instance SELECT DISTINCT.

2. We actually used to do it that way.  It was not better.
If you try this pre-v10 you get something like

regression=3D# SELECT *
FROM (SELECT 2    AS a) AS t1
JOIN (SELECT NULL AS b) AS t2
ON t1.a =3D t2.b;
ERROR:  failed to find conversion function from unknown to integer

Avoiding that would have required giving up a lot of type-safety.
You might find this thread of interest:

https://www.postgresql.org/message-id/flat/CAH2L28uwwbL9HUM-WR%3DhromW1Cva=
mkn7O-g8fPY2m%3D_7muJ0oA%40mail.gmail.com

> Note that all queries above succeed on Microsoft SQL Server.

SQL Server doesn't need to worry about an extensible type system.

			regards, tom lane