Issue with nested SQL Query (Percentage)

Hi,
I would like to extract a percentage of HTTP response code by response code obtained from data that is stored into Druid. My SQL query looks like:

SELECT
respCode, round(100.0*count(respCode)/(SELECT count(respCode) from table),4) from table
GROUP BY respCode

When running this query, I have the following error message:

druid error: Unknown exception (org.apache.druid.java.util.common.ISE): Cannot build plan for query: SELECT
respCode, round(100.0*count(respCode)/(SELECT count(respCode) from table),4) from table
GROUP BY respCode

However, when I use the Druid Wikipedia sample data, I’m able to run a similar query:

SELECT
cityName, round(100.0*count(cityName)/(SELECT count(cityName) from wikipedia where countryIsoCode=‘FR’),4) from wikipedia
where countryIsoCode=‘FR’
GROUP BY cityName

Any suggestion of what could be wrong?
Thanks - Christian

What is the data type for respCode? Also, maybe simplify the query down to the individual parts to see where it is failing… and if there is nothing obvious, might be worth posting in Github as a possible issue.

Thanks Rachel for your quick reply and suggestions.

  • respCode and cityName are of different types - I tried using another field than respCode which is of same type as cityName and have the same error.
  • for the data that is failing, I did a simple POST query (through the druid api: http://server:8082/druid/v2/sql?pretty with a body that is

{“query” : “SELECT * from table”}

and copied the data to load it into a local quickstart Druid server. I could then query it and the following SQL is working: > SELECT

(SELECT count(respCode) from table) from table
GROUP BY respCode

{“query” : “SELECT (select count(respCode) from table) from table group by respCode”}

, it fails with:

{
“error”: “Unknown exception”,
“errorMessage”: “Cannot build plan for query: SELECT (select count(respCode) from apim_metrics_hour) from apim_metrics_hour group by respCode”,
“errorClass”: “org.apache.druid.java.util.common.ISE”,
“host”: null
}

You suggested posting the issue to github - can you let me where?

Thanks again for your help.

I think I found where the issue is: the Druid version where it fails is 0.16 whilst the local Druid version where it works is 0.21.