Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc515ff8

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

File tree

1 file changed

+30
-0
lines changed

1 file changed

+30
-0
lines changed

‎doc/src/sgml/typeconv.sgml

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1077,6 +1077,36 @@ but <type>integer</type> can be implicitly cast to <type>real</type>, the union
10771077
result type is resolved as <type>real</type>.
10781078
</para>
10791079
</example>
1080+
1081+
<example>
1082+
<title>Type Resolution in a Nested Union</title>
1083+
1084+
<para>
1085+
<screen>
1086+
SELECT NULL UNION SELECT NULL UNION SELECT 1;
1087+
1088+
ERROR: UNION types text and integer cannot be matched
1089+
</screen>
1090+
This failure occurs because <productname>PostgreSQL</productname> treats
1091+
multiple <literal>UNION</literal>s as a nest of pairwise operations;
1092+
that is, this input is the same as
1093+
<screen>
1094+
(SELECT NULL UNION SELECT NULL) UNION SELECT 1;
1095+
</screen>
1096+
The inner <literal>UNION</literal> is resolved as emitting
1097+
type <type>text</type>, according to the rules given above. Then the
1098+
outer <literal>UNION</literal> has inputs of types <type>text</type>
1099+
and <type>integer</type>, leading to the observed error. The problem
1100+
can be fixed by ensuring that the leftmost <literal>UNION</literal>
1101+
has at least one input of the desired result type.
1102+
</para>
1103+
1104+
<para>
1105+
<literal>INTERSECT</literal> and <literal>EXCEPT</literal> operations are
1106+
likewise resolved pairwise. However, the other constructs described in this
1107+
section consider all of their inputs in one resolution step.
1108+
</para>
1109+
</example>
10801110
</sect1>
10811111

10821112
<sect1 id="typeconv-select">

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp