DATEDIFF is not supported in Unit Test

1

The SQL below could run correctly in Dremio, but report error in unit test. Do you have any idea how could make it run in unit test case?

SQL

SELECT DATEDIFF(CURRENT_DATE, to_date(substring(TP_System_Capture_Timestamp, 0, 10), 'YYYY-MM-DD')) as Age FROM "EDGE"."data"

ERROR:

  Caused By (org.apache.calcite.sql.validate.SqlValidatorException) No match found for function signature DATEDIFF(<DATE>, <DATE>)
    sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
    sun.reflect.NativeConstructorAccessorImpl.newInstance():62
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
    java.lang.reflect.Constructor.newInstance():422
    org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
    org.apache.calcite.runtime.Resources$ExInst.ex():572
2

@popejune

Dremio does not support DATEDIFF, Can you please use TIMESTAMPDIFF as an alternative?

http://docs.dremio.com/sql-reference/sql-functions/datetime-functions.html

3

But the DATEDIFF is working dremio portal? I am wonderring if there is any config to enable this.

4

@popejune

Where are you seeing DATEDIFF?

5

Hi @balaji.ramaswamy
Our user is using this function, but it could execute correctly on dremio portal.

image
image847×354 23.8 KB

6

Found a new one nvl, which is not supported in unit test case, but support in Dremio protal. Is there any way to add it in unit test framework?

Caused By (org.apache.calcite.sql.validate.SqlValidatorException) No match found for function signature nvl(, )

7

@popejune For NVL, you should use coalesce instead.