rwxrw@xxxxxxxxxx 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%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
