Query 500 on `select col, count(*) from table` missing `group by` (query mistake)

[lgo]

While writing some adhoc queries, I found the controller query console was throwing a 500 and I was scratching my head about the error until I realized the query was incorrect. I'm not sure if Pinot often 500s on invalid queries, or this is a corner case, but certainly making sure a helpful error is returned is better that informs the user their query is invalid.

For example, I was trying

select $segmentName, count(*)
from table
where $segmentName = 'bar'

Mind, the same error happened when I used actual columns rather than the virtual column.

And it was raising the following exception

java.io.IOException: Failed : HTTP error code : 500
    at org.apache.pinot.controller.api.resources.PinotQueryResource.sendPostRaw(PinotQueryResource.java:305)
    at org.apache.pinot.controller.api.resources.PinotQueryResource.sendRequestRaw(PinotQueryResource.java:343)
    at org.apache.pinot.controller.api.resources.PinotQueryResource.getQueryResponse(PinotQueryResource.java:225)
    at org.apache.pinot.controller.api.resources.PinotQueryResource.handlePostSql(PinotQueryResource.java:137))

[mayankshriv]

Hello Joey, Thanks for finding this issue. Do you mind creating a GitHub issue with details for both the problems you encountered? Also, please feel free to join the Apache Pinot slack community for more interactive discussions.

Thanks Mayank

On Dec 4, 2021, at 11:07 AM, Joey Pereira @.***> wrote:

 While writing some adhoc queries, I found the controller query console was throwing a 500 and I was scratching my head about the error until I realized the query was incorrect. I'm not sure if Pinot often 500s on invalid queries, or this is a corner case, but certainly making sure a helpful error is returned is better that informs the user their query is invalid.

For example, I was trying

select $segmentName, count(*) from table where $segmentName = 'bar' Mind, the same error happened when I used actual columns rather than the virtual column.

And it was raising the following exception

java.io.IOException: Failed : HTTP error code : 500 at org.apache.pinot.controller.api.resources.PinotQueryResource.sendPostRaw(PinotQueryResource.java:305) at org.apache.pinot.controller.api.resources.PinotQueryResource.sendRequestRaw(PinotQueryResource.java:343) at org.apache.pinot.controller.api.resources.PinotQueryResource.getQueryResponse(PinotQueryResource.java:225) at org.apache.pinot.controller.api.resources.PinotQueryResource.handlePostSql(PinotQueryResource.java:137)) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

[Jackie-Jiang]

We should validate this query on the broker side and directly reject it if the query is invalid

@xiangfu0 For the example query above, should we attach the group-by clause if it is missing or just fail it?

[PrachiPrakash]

https://github.com/apache/pinot/pull/7972 this tries to solve this problem by validating it after the query compilation. it would be really good if someone can review this change. Thanks, Prachi