CONNECT BY Operators CONNECT_BY_ROOT and PRIOR in the select list are not supported for HASHTYPE

Detail name	Value
Changelog Number	11458
Type	Bug
Status	Resolved
Affected Versions
Fix Versions	Exasol 7.0.10
Resolution Date	2021-06-05

Description

CONNECT_BY_ROOT and PRIOR in the select list do not work for HASHTYPE

preparation:

create schema test;
create or replace table c(c1 hashtype, c2 hashtype);

insert into c values ('a7b38db6-d985-42ca-a3ba-cc78c3d5bc2f', 'a7b38db6-d985-42ca-a3ba-cc78c3d5bc2e');
insert into c values ('a7b38db6-d985-42ca-a3ba-cc78c3d5bc2e', NULL);

example:

--> error: Feature not supported
select prior c1 from c connect by c1=prior c2;

--> error: Feature not supported
select connect_by_root(c1) from c connect by c1=prior c2;

Workaround

Cast the arguments of PRIOR and CONNECT_BY_ROOT from HASHTYPE to their corresponding CHAR type. So for the above example (HASHTYPE(16 BYTE) maps to CHAR(32 BYTE)), the workaround looks as follows:

select prior cast(c1 as char(32)) from c connect by c1=prior c2;

select connect_by_root(cast(c1 as char(32))) from c connect by c1=prior c2;

Fix

CONNECY_BY_ROOT and PRIOR will also work for HASHTYPE data types.