postgres.git / summary / log / commit / refs

commit    356f85f95cb0b0e99ce51932c2a9dbd292f0b292
Author:   Tom Lane <tgl@sss.pgh.pa.us>
Date:     Sun Mar 25 20:15:16 2018 +0000
First released in: 11.0, 9.3.23, 9.4.18, 9.5.13, 9.6.9, 10.4

    Doc: add example of type resolution in nested UNIONs.
    
    Section 10.5 didn't say explicitly that multiple UNIONs are resolved
    pairwise.  Since the resolution algorithm is described as taking any
    number of inputs, readers might well think that a query like
    "select x union select y union select z" would be resolved by
    considering x, y, and z in one resolution step.  But that's not what
    happens (and I think that behavior is per SQL spec).  Add an example
    clarifying this point.
    
    Per bug #15129 from Philippe Beaudoin.
    
    Discussion: https://postgr.es/m/152196085023.32649.9916472370480121694@wrigleys.postgresql.org


 doc/src/sgml/typeconv.sgml | 29 +++++++++++++++++++++++++++++
 1 file changed, 29 insertions(+)

diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml
index c031c01ed35..aa367bb0eb0 100644
--- a/doc/src/sgml/typeconv.sgml
+++ b/doc/src/sgml/typeconv.sgml
@@ -1077,5 +1077,34 @@ result type is resolved as <type>real</>.
 </para>
 </example>
 
+<example>
+<title>Type Resolution in a Nested Union</title>
+
+<para>
+<screen>
+SELECT NULL UNION SELECT NULL UNION SELECT 1;
+
+ERROR:  UNION types text and integer cannot be matched
+</screen>
+This failure occurs because <productname>PostgreSQL</productname> treats
+multiple <literal>UNION</literal>s as a nest of pairwise operations;
+that is, this input is the same as
+<screen>
+(SELECT NULL UNION SELECT NULL) UNION SELECT 1;
+</screen>
+The inner <literal>UNION</literal> is resolved as emitting
+type <type>text</type>, according to the rules given above.  Then the
+outer <literal>UNION</literal> has inputs of types <type>text</type>
+and <type>integer</type>, leading to the observed error.  The problem
+can be fixed by ensuring that the leftmost <literal>UNION</literal>
+has at least one input of the desired result type.
+</para>
+
+<para>
+<literal>INTERSECT</literal> and <literal>EXCEPT</literal> operations are
+likewise resolved pairwise.  However, the other constructs described in this
+section consider all of their inputs in one resolution step.
+</para>
+</example>
 </sect1>
 </chapter>


[parent: 7b55a3b16751]