Druid SQL Having clause?

Hi all,

I am experimenting with the SQL interface added in 0.10 and I would like to run a query that adds a having clause to a group by query. However I’m getting errors about the column name not being recognized.

For example this:

{

“query”:“SELECT FLOOR(__time TO DAY), location_id, product_family_id, CAST(COUNT() FILTER(WHERE event_status <> ‘success’) AS FLOAT)/COUNT() AS scan_error_rate FROM sor_business_events_all WHERE event_type=‘product_scan’ and __time > TIMESTAMP ‘2017-05-07 00:00:00’ AND __time < TIMESTAMP ‘2017-05-10 00:00:00’ GROUP BY FLOOR(__time TO DAY), location_id, product_family_id HAVING scan_error_rate = 1”

}

Results in:

{“error”:“Unknown exception”,“errorMessage”:“org.apache.calcite.runtime.CalciteContextException: From line 1, column 330 to line 1, column 344: Column ‘scan_error_rate’ not found in any table”,“errorClass”:“org.apache.calcite.tools.ValidationException”,“host”:null}

I also tried replacing scan_error_rate in the clause with the full statement and got the following error:

{“error”:“Unknown exception”,“errorMessage”:“Cannot build plan for query: SELECT FLOOR(__time TO DAY), location_id, product_family_id, CAST(COUNT() FILTER(WHERE event_status <> ‘success’) AS FLOAT)/COUNT() AS scan_error_rate FROM sor_business_events_all WHERE event_type=‘product_scan’ and __time > TIMESTAMP ‘2017-05-07 00:00:00’ AND __time < TIMESTAMP ‘2017-05-10 00:00:00’ GROUP BY FLOOR(__time TO DAY), location_id, product_family_id HAVING CAST(COUNT() FILTER(WHERE event_status <> ‘success’) AS FLOAT)/COUNT() = 1”,“errorClass”:“io.druid.java.util.common.ISE”,“host”:null}

Does that mean HAVING is not currently supported via SQL? Am I doing something wrong?

Thanks,

Hey Ben,

HAVING is supported but there are limitations. The first error is because Calcite doesn’t support aliases for HAVING: https://issues.apache.org/jira/browse/CALCITE-1306 (it’s resolved, but 1.13.0 hasn’t been released yet). The second error seems to be some combination of Calcite not pushing the division below the having for some reason, and Druid’s rules not being able to handle the case where it doesn’t. Could you please raise an issue on github about this query?