Following UNION of two queries with constant literals runs successfully.
CASE 1:
postgres=# SELECT 'abc' UNION SELECT 'bcd' ;
?column?
----------
abc
bcd
(2 rows)
whereas when these literals are part of a view, the UNION fails.
CASE 2:
postgres=# create view v as select 'abc' a;
2016-11-16 15:28:48 IST WARNING: column "a" has type "unknown"
2016-11-16 15:28:48 IST DETAIL: Proceeding with relation creation anyway.
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE VIEW
postgres=# create view v1 as select 'bcd' a;
2016-11-16 15:28:56 IST WARNING: column "a" has type "unknown"
2016-11-16 15:28:56 IST DETAIL: Proceeding with relation creation anyway.
WARNING: column "a" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE VIEW
postgres=# select a from v UNION select a from v1;
2016-11-16 15:25:28 IST ERROR: could not determine which collation to use for string comparison
2016-11-16 15:25:28 IST HINT: Use the COLLATE clause to set the collation explicitly.
2016-11-16 15:25:28 IST STATEMENT: select a from v UNION select a from v1;
ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.
When UNION of queries with constant literals as in CASE 1 is allowed shouldn't a UNION of queries with literals in a view as in CASE 2 be allowed?
In transformSetOperationTree, while determining the result type of the merged
output columns, if the left and right column types are UNKNOWNs the result type
is resolved to TEXT.
The difference of behaviour in above two cases arises because the result collation
assigned is not valid in CASE 2.
When the left and the right inputs are literal constants i.e UNKNOWN as in Case 1
the collation of result column is correctly assigned to a valid value.
Whereas when the left and the right inputs are columns of UNKNOWN type as in Case 2,
the result collation is InvalidOid.
So if we ensure assignment of a valid collation when the left and the right columns/inputs
are UNKNOWN, the above can be resolved.
Attached WIP patch does that. Kindly let me know your opinion.
Thank you,
Rahila Syed