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
Date: Thu, 28 Mar 2024 10:32:36 -0400
Message-ID: <3575085.1711636356@sss.pgh.pa.us> (raw)
In-Reply-To: <ccfa6e1b-4ed3-4829-898f-6992d8764aea@posteo.net>
References: <ccfa6e1b-4ed3-4829-898f-6992d8764aea@posteo.net>
rwxrw@posteo.net writes:
> I had initially made a false assumption that only UNION, CASE, and Related
> 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=# SELECT *
FROM (SELECT 2 AS a) AS t1
JOIN (SELECT NULL AS b) AS t2
ON t1.a = 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%3DhromW1Cvamkn7O-g8fPY2m%3D_7muJ0oA%4...> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, rwxrw@posteo.net, pgsql-general@lists.postgresql.org
Subject: Re: Problems caused by type resolution for the unknown type
In-Reply-To: <3575085.1711636356@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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