Summary/Reported Issue
Below Query convert_From a json field fails, but if we run multiple times it succeeds , is this a bug in Dremio if so is there a workaround for this..
Query:
select CONVERT_FROM("value", 'JSON') FROM your.table.name where "column_name1" = 'value1' and "column_name1" = 'value2' limit 1
Error:
New field in the schema found. Please reattempt the query. Multiple attempts may be necessary to fully learn the schema.
Relevant Versions
Dremio 25.1.x
Cause
We were able to replicate the problem internally. The issue has been identified to be a bug that comes specifically in Nessie sources.
Steps to Resolve
As a workaround you can use the function `TRY_CONVERT_FROM` with following syntax to get the same output:
SELECT TRY_CONVERT_FROM(json_data AS ARRAY(ROW(city VARCHAR, state VARCHAR))) FROM your.table.name;
Please be aware that the function `TRY_CONVERT_FROM` was introduced in 25.1.6 and 25.2.2. In order to have access to the workaround, you would need to upgrade to 25.1.6, 25.2.2 or a later version.