Doc: add example of type resolution in nested UNIONs.

tglsfdc · tglsfdc · commit 356f85f95cb0 · 2018-03-25T16:15:16.000-04:00
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
diff --git 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>
