You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by GitBox <gi...@apache.org> on 2021/12/17 17:26:22 UTC
[GitHub] [pinot] kbastani opened a new issue #7922: SQL MIN/MAX functions produce error when using HAVING without NULL check
kbastani opened a new issue #7922:
URL: https://github.com/apache/pinot/issues/7922
The following SQL query will cause an error when using `MIN` or `MAX`.
```SQL
SELECT MAX(calcs.int0) AS TEMP_Test__645427419__0_
FROM calcs
HAVING (COUNT(1) > 0)
```
The error produced is as follows:
```JSON
[
{
"message": "QueryExecutionError:
java.lang.IndexOutOfBoundsException: Index: 0
\tat java.base/java.util.Collections$EmptyList.get(Collections.java:4553)
\tat org.apache.pinot.core.plan.DictionaryBasedAggregationPlanNode.<init>(DictionaryBasedAggregationPlanNode.java:52)
\tat org.apache.pinot.core.plan.maker.InstancePlanMakerImplV2.makeSegmentPlanNode(InstancePlanMakerImplV2.java:231)
\tat org.apache.pinot.core.plan.maker.InstancePlanMakerImplV2.makeInstancePlan(InstancePlanMakerImplV2.java:187)",
"errorCode": 200
}
]
```
There are multiple ways to work around the exception.
This workaround checks for a NULL value for the `calcs.int0` field.
```SQL
SELECT MAX(calcs.int0) AS TEMP_Test__645427419__0_
FROM calcs
WHERE calcs.int0 IS NOT NULL
HAVING (COUNT(1) > 0)
```
This workaround adds a `COUNT(1)` as a field in the `SELECT` clause.
```SQL
SELECT MAX(calcs.int0) AS TEMP_Test__645427419__0_, COUNT(1)
FROM calcs
HAVING (COUNT(1) > 0)
```
Another relevant condition for reproducing this issue is to set a `defaultNullValue` in the schema configuration for the table. Also, `nullHandlingEnabled` has been set to `true` in the table configuration.
Table configuration for reproducing the issue:
```JSON
{
"tableName":"calcs",
"segmentsConfig":{
"segmentAssignmentStrategy":"BalanceNumSegmentAssignmentStrategy",
"segmentPushType":"APPEND",
"schemaName":"calcs",
"replication":1
},
"tableIndexConfig":{
"invertedIndexColumns":[
],
"loadMode":"MMAP",
"nullHandlingEnabled": true
},
"tenants":{
"broker":"DefaultTenant",
"server":"DefaultTenant"
},
"tableType":"OFFLINE",
"metadata":{
"customConfigs":{}
}
}
```
Schema for reproducing the issue:
```JSON
{
"schemaName":"calcs",
"dimensionFieldSpecs":[
{
"name":"int0",
"dataType":"INT",
"defaultNullValue": 0
}
]
}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] kbastani commented on issue #7922: SQL MIN/MAX functions produce error when using HAVING without NULL check
Posted by GitBox <gi...@apache.org>.
kbastani commented on issue #7922:
URL: https://github.com/apache/pinot/issues/7922#issuecomment-997141826
The query is a part of the Tableau testing suite of SQL queries used to test SQL compliance. This is a kind of unit test that failed repeatedly for a variety of scenarios when using MIN/MAX aggregation. I've reviewed with @xiang @kishoreg and @mayankshriv who can provide more details on a fix.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] kbastani closed issue #7922: SQL MIN/MAX functions produce error when using HAVING without NULL check
Posted by GitBox <gi...@apache.org>.
kbastani closed issue #7922:
URL: https://github.com/apache/pinot/issues/7922
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] kbastani commented on issue #7922: SQL MIN/MAX functions produce error when using HAVING without NULL check
Posted by GitBox <gi...@apache.org>.
kbastani commented on issue #7922:
URL: https://github.com/apache/pinot/issues/7922#issuecomment-998997990
This issue is fixed as of the latest SNAPSHOT build. Closing issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] Jackie-Jiang commented on issue #7922: SQL MIN/MAX functions produce error when using HAVING without NULL check
Posted by GitBox <gi...@apache.org>.
Jackie-Jiang commented on issue #7922:
URL: https://github.com/apache/pinot/issues/7922#issuecomment-997107718
What is the semantic of `HAVING` without `GROUP BY`? The error message is definitely confusing.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [pinot] siddharthteotia commented on issue #7922: SQL MIN/MAX functions produce error when using HAVING without NULL check
Posted by GitBox <gi...@apache.org>.
siddharthteotia commented on issue #7922:
URL: https://github.com/apache/pinot/issues/7922#issuecomment-998431366
The query is not correct. Standard SQL does not support use of HAVING without GROUP BY.
Try the examples here for MySQL - https://www.w3schools.com/sql/sql_having.asp
Error message should still be fixed and should indicate query/syntax is invalid .
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org