ISNULL does not support operand types

I am getting this error when trying to execute query with isnull() function against MS SQL server.
For example, In case of isnull(r.mentions ,0) mentions I am getting ISNULL does not support operand types (DOUBLE,INTEGER) In case of VARCHAR type ISNULL does not support operand types (VARCHAR,VARCHAR). I was wondering if is there any equivalent function present in Dremio or do we have any workaround for that.


What’s your query? Would you be able to share the profile?

How To Share A Query Profile

I’m getting a similar error:

" ISNULL does not support operand types (TIMESTAMP,TIMESTAMP)"

I can share the profile privately if required.

The function you are looking for is named COALESCE:

That works as part of a SELECT e.g.

SELECT COALESCE(end_dttm, start_dttm) AS end_dttm

but it won’t work in a where clause, which is where I was using it:

SELECT refno
WHERE COALESCE(end_dttm, start_dttm) >= ‘2019-01-01’

even though the above is perfectly valid when run against SQL Server natively I get the following error when I try it via Dremio: Incorrect syntax near ‘>’.

As this is SQL Server 2008 and I recently learned it is not supported I didn’t bother raising it as an issue.

Would it be possible to share the query profile with us?

Sure, sent privately.

Thanks, it looks like the SQL generated for SQL Server is invalid. I reported the issue to our engineering team.

Great, thanks for that.