Druid SQL - natively support mathematical functions ROUND(), RADIANS(), EXP(), LN(), POWER(), RAND()

[ntkawasaki]

Description

Currently these widely known mathematical operations are not officially implemented by Druid SQL:

Remote driver error: RuntimeException: org.apache.calcite.tools.ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 23: 
No match found for function signature RAND() -> ValidationException: org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 23: 
No match found for function signature RAND() -> CalciteContextException: From line 1, column 18 to line 1, column 23: No match found for function signature RAND() -> SqlValidatorException: No match found for function signature RAND()

Similar error for all.

These functions used to work in Druid version 0.10 when the useFallback option existed, calcite had a way to call these. However it looks like this parameter has been deprecated since 0.15: c648775 and #7567

The docs here claim that these functions do exist: https://druid.apache.org/docs/latest/querying/sql#numeric-functions

However I believe these are out of date, or had been written at an earlier time/version when useFallback was being used and they were callable.

Motivation

These would be useful for Business Intelligence applications connecting to Druid SQL clusters, and are very well known operations for any numeric data.

[gianm]

Hi @ntkawasaki, Druid does support all of those functions today except RAND. Most of them were added in version 0.15. (You can access old Druid docs at e.g. https://druid.apache.org/docs/0.14.0-incubating/querying/sql; note that the 0.14 docs don't include most of these functions.)

I raised an issue specifically for RAND: #8789, which is still missing today.

Btw, we deprecated and removed useFallback because it led to unscalable query plans in many cases. We found that often, queries with useFallback would pull all raw data into the Druid Broker and process it there, which was a bad approach for big datasets. This risk outweighed the benefits, and so we decided to implement more functionality in Druid's native query system instead of relying on SQL interpreter fallback.

[ntkawasaki]

Thanks @gianm

Made a Druid 0.15 database and its all good:

We can come up with a workaround for RAND() until thats native in Druid.

[stale]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 4 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the dev@druid.apache.org list. Thank you for your contributions.

[stale]

This issue has been closed due to lack of activity. If you think that is incorrect, or the issue requires additional review, you can revive the issue at any time.