Re: Problems caused by type resolution for the iso-8859-1 type - Mailing list pgsql-general

From Tom Lane
Subject Re: Problems caused by type resolution for the iso-8859-1 type
Date
Msg-id 3575085.1711636356@sss.pgh.pa.us
Whole thread Raw
In response to [MASSMAIL]Problems caused by type resolution for the iso-8859-1 type  (rwxrw@posteo.net)
List pgsql-general
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%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



pgsql-general by date:

Previous
From: Daniel Gustafsson
Date:
Subject: Re: Inquiry on Participating in the Korean Translation Project for PostgreSQL Documentation
Next
From: Emond Papegaaij
Date:
Subject: [MASSMAIL]pg_rewind after promote