Following error occurs while creating a view as UNION of two views with
UNKNOWN columns.
create table test_tb1(i int, j varchar);
create table test_tb2(j int, k varchar);
create or replace view v_test1(i,j,lit) AS SELECT a.j "i" ,a.k "j", ''
"lit" from test_tb2 a;
create or replace view v_test(i,j,lit) AS SELECT a.i "i" ,a.j "j", '' "lit"
from test_tb1 a;
create or replace view v_test_merge(i,j,lit) AS SELECT i,j,lit from v_test
UNION ALL SELECT i,j,lit from v_test1;
ERROR: could not determine which collation to use for view column "lit"
HINT: Use the COLLATE clause to set the collation explicitly.
The error occurs when a target list with column type as TEXT and collation
as InvalidOid is used to create a view.
In transformSetOperationTree, when a targetlist is formed from UNION of two
SELECTs,
the resultant column type is coerced to TEXT when both the columns are
UNKNOWN. But the resultant column collation of such UNKNOWN columns remain
InvalidOid
This is because the original columns types are not coerced to TEXT only
resultant type is. Hence original column types remain UNKNOWN while
determining the resultant collation.
When such target list with a column of type TEXT and collation as
InvalidOid is used while creating a view
in DefineVirtualRelation it throws error because the column type is
collatable but it does not have a collation assigned.
Shouldn=E2=80=99t the resultant collation be assigned value corresponding t=
o TEXT
datatype and not UNKNOWN datatype in this case? i.e some valid value and
not InvalidOid.
Thank you,
Rahila Syed