Replace function does not include the last characters

1

I am executing this query: SELECT REPLACE(‘A SPIDER WITH AN ANT’, ‘AN’, ‘THIN’) FROM data.“mine”.doc LIMIT 1

This is the current output of the query: A SPIDER WITH THIN T

This is the expected output: A SPIDER WITH THIN THINT

As you can see the current output doesn’t include the last characters.

I am not sure what is the problem.

Thanks and regards.

2

Hi @Jeff23

Yes, I see that, will look into this and get back to you

Thanks
@balaji.ramaswamy

3

Hi,

I was able to handle it using CAST(“myString” as VARCHAR(8000)).

For example,
SELECT REPLACE(CAST(‘A SPIDER WITH AN ANT’ AS VARCHAR(8000)), ‘AN’, ‘THIN’) FROM data.“mine”.doc LIMIT 1

The issue was that Dremio keeps the original input’s length, even if the replace function generates a longer string. When the output is longer than the original string, it doesn’t have enough space to be displayed or returned completely.

Maybe, this is a bug that has to be reported.

Thank you very much for the help.

Regards,
Jeff.

4

I believe this only happens when the replace function is applied to a literal string in the query. If applied to a column in a table, it will treat the column as varchar, and behave as expected.