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,