select_common_type()'s behavior doesn't match the documentation
In our fine manual, at
http://www.postgresql.org/docs/devel/static/typeconv-union-case.html
it's claimed that the nontrivial parts of UNION type resolution
work like this:
4. Choose the first non-unknown input type which is a preferred type in
that category, if there is one.
5. Otherwise, choose the last non-unknown input type that allows all the
preceding non-unknown inputs to be implicitly converted to it. (There
always is such a type, since at least the first type in the list must
satisfy this condition.)
This appears to have only the vaguest of resemblances to what
select_common_type() actually does, which is to make a single
pass over the inputs in which it does this:
/*
* take new type if can coerce to it implicitly but not the
* other way; but if we have a preferred type, stay on it.
*/
Thus for example there's a surprising inconsistency between
these cases:
regression=# select pg_typeof(t) from (select 'a'::text union select 'b'::char(1)) s(t);
pg_typeof
-----------
text
text
(2 rows)
regression=# select pg_typeof(t) from (select 'a'::char(1) union select 'b'::text) s(t);
pg_typeof
-----------
character
character
(2 rows)
I think that at the very least, we ought to prefer preferred types,
the way the manual claims. I'm less certain about whether step 5
is ideal as written.
This came up because some of my Salesforce colleagues were griping about
the fact that UNION isn't commutative. They argue that the type
resolution behavior ought not be sensitive at all to the ordering of the
inputs. I'm not sure we can achieve that in general, but the current
approach certainly seems more order-sensitive than it oughta be.
Some trolling in the git history says that the last actual change in
this area was in my commit b26dfb95222fddd25322bdddf3a5a58d3392d8b1 of
2002-09-18, though it appears the documentation has been rewritten more
recently. It's a bit scary to be proposing to change behavior that's been
stable for eleven years, but ...
Thoughts?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Tom Lane <tgl@sss.pgh.pa.us> wrote:
This came up because some of my Salesforce colleagues were griping about
the fact that UNION isn't commutative. They argue that the type
resolution behavior ought not be sensitive at all to the ordering of the
inputs. I'm not sure we can achieve that in general, but the current
approach certainly seems more order-sensitive than it oughta be.Some trolling in the git history says that the last actual change in
this area was in my commit b26dfb95222fddd25322bdddf3a5a58d3392d8b1 of
2002-09-18, though it appears the documentation has been rewritten more
recently. It's a bit scary to be proposing to change behavior that's
been stable for eleven years, but ...Thoughts?
The current behavior is bad enough to merit changing it. Not for
back-patch, of course.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Any progress on this?
---------------------------------------------------------------------------
On Sat, Nov 30, 2013 at 12:43:39PM -0500, Tom Lane wrote:
In our fine manual, at
http://www.postgresql.org/docs/devel/static/typeconv-union-case.html
it's claimed that the nontrivial parts of UNION type resolution
work like this:4. Choose the first non-unknown input type which is a preferred type in
that category, if there is one.5. Otherwise, choose the last non-unknown input type that allows all the
preceding non-unknown inputs to be implicitly converted to it. (There
always is such a type, since at least the first type in the list must
satisfy this condition.)This appears to have only the vaguest of resemblances to what
select_common_type() actually does, which is to make a single
pass over the inputs in which it does this:/*
* take new type if can coerce to it implicitly but not the
* other way; but if we have a preferred type, stay on it.
*/Thus for example there's a surprising inconsistency between
these cases:regression=# select pg_typeof(t) from (select 'a'::text union select 'b'::char(1)) s(t);
pg_typeof
-----------
text
text
(2 rows)regression=# select pg_typeof(t) from (select 'a'::char(1) union select 'b'::text) s(t);
pg_typeof
-----------
character
character
(2 rows)I think that at the very least, we ought to prefer preferred types,
the way the manual claims. I'm less certain about whether step 5
is ideal as written.This came up because some of my Salesforce colleagues were griping about
the fact that UNION isn't commutative. They argue that the type
resolution behavior ought not be sensitive at all to the ordering of the
inputs. I'm not sure we can achieve that in general, but the current
approach certainly seems more order-sensitive than it oughta be.Some trolling in the git history says that the last actual change in
this area was in my commit b26dfb95222fddd25322bdddf3a5a58d3392d8b1 of
2002-09-18, though it appears the documentation has been rewritten more
recently. It's a bit scary to be proposing to change behavior that's been
stable for eleven years, but ...Thoughts?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Tom Lane-2 wrote
In our fine manual, at
http://www.postgresql.org/docs/devel/static/typeconv-union-case.html
it's claimed that the nontrivial parts of UNION type resolution
work like this:4. Choose the first non-unknown input type which is a preferred type in
that category, if there is one.5. Otherwise, choose the last non-unknown input type that allows all the
preceding non-unknown inputs to be implicitly converted to it. (There
always is such a type, since at least the first type in the list must
satisfy this condition.)This came up because some of my Salesforce colleagues were griping about
the fact that UNION isn't commutative. They argue that the type
resolution behavior ought not be sensitive at all to the ordering of the
inputs. I'm not sure we can achieve that in general, but the current
approach certainly seems more order-sensitive than it oughta be.
4. Use the preferred type for whatever category all inputs share (per 3).
Per 1 this is only used if at least one input does not agree.
5. No longer needed
6. Stays the same
It is possible for a result type to not match any of the input types but if
you want to be commutative this would have to be allowed.
You could add a "majority rules" condition rules before 4 and punt if there
is no one dominate type.
Should #1 repeat after flattening domains to their base types?
I would probably logically place 2 before 1 since if everything is unknown
nothing else matters.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/select-common-type-s-behavior-doesn-t-match-the-documentation-tp5780985p5813963.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers