Hello.
We use ms sql server 12 and have GUID(16 byte) type columns there that are obviously treated as binary columns by dremio. We want to use string representatin of those columns in dremio.
The problem is that Dremio can not convert it to proper string using CAST(GUID as varchar) BUT only when CAST function is pushed down to sql server query. In this case it outputs proper guid:
select CAST(GUID as varchar)
FROM MsSqlTable WHERE ID = 8336754
Outputs: 56B15BAF-CF49-4708-81CF-F6EF5B9B437C
Dremio also has its own convertion function that is executed on dremio side CONVERT_FROM(GUID , 'UTF8'). It outputs such text as it has no clue about guid binary format:
select CONVERT_FROM(GUID , 'UTF8')
FROM MsSqlTable WHERE ID = 8336754
Outputs: �[�VI�G����[�C|
My problem is that unfortunatelly Dremio does not push down CAST function to sql always, but quite often decides to pull GUID as binary column and try to convert it locally as an optimization. For example such query will output unexpectedly two broken text values not just second one.
select CAST(GUID as varchar), CONVERT_FROM(GUID , 'UTF8')
FROM MsSqlTable WHERE ID = 8336754
Outputs �[�VI�G����[�C| ; �[�VI�G����[�C|
How can we safely cast guid to proper text representation? Is there dremio native workaround or a way to force CAST function propogate down always to sql?
Build
25.0.7-202407181421320869-2632b04f
Edition
AWS Edition (activated)