You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by GitBox <gi...@apache.org> on 2020/06/03 06:42:22 UTC
[GitHub] [druid] suneet-s opened a new issue #9977: SQL SELECT with IN filter containing null value throws ISE
suneet-s opened a new issue #9977:
URL: https://github.com/apache/druid/issues/9977
### Affected Version
0.19
### Description
The following query
```
select * from druid.foo where dim2 in ('a', null)
```
fails to plan with this exception
```
Unknown exception / Cannot build plan for query: select * from druid.foo where dim2 in ('a', null) / org.apache.druid.java.util.common.ISE
```
This query should be able to use an in dim filter with a null value, but the Calcite planning phase fails with this error
```
Missing conversion is LogicalSort[convention: NONE -> DRUID]
There is 1 empty subset: rel#4087:Subset#5.DRUID.[], the relevant part of the original plan is as follows
4085:LogicalSort(fetch=[100:BIGINT])
4083:LogicalProject(subset=[rel#4084:Subset#4.NONE.[]], __time=[$0], dim1=[$1], dim2=[$2], dim3=[$3], m1=[$4], m2=[$5])
4081:LogicalJoin(subset=[rel#4082:Subset#3.NONE.[]], condition=[=($2, $6)], joinType=[inner])
4050:LogicalTableScan(subset=[rel#4077:Subset#0.NONE.[]], table=[[druid, foo]])
4051:LogicalValues(subset=[rel#4098:Subset#1.NONE.[]], tuples=[[{ 'a' }, { null }]])
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] suneet-s edited a comment on issue #9977: SQL SELECT with IN filter containing null value throws ISE
Posted by GitBox <gi...@apache.org>.
suneet-s edited a comment on issue #9977:
URL: https://github.com/apache/druid/issues/9977#issuecomment-639828756
Druid does not support joins against constant conditions - see #9942
Calcite translates any in clause with a null to a join expression. If the in filter does not have null, Calcite will translate the query to an IN filter. So Druid can not support this type of query til #9942 is fixed.
If you want this query to work, re-write it to
```
select * from druid.foo where dim2 in ('a')
```
This will produce the same results as the query filed in this issue.
If you want the query to match nulls, then the above query should be re-written to
```
select * from druid.foo where dim2 in ('a') or dim2 is NULL
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] maytasm closed issue #9977: SQL SELECT with IN filter containing null value throws ISE
Posted by GitBox <gi...@apache.org>.
maytasm closed issue #9977:
URL: https://github.com/apache/druid/issues/9977
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] maytasm commented on issue #9977: SQL SELECT with IN filter containing null value throws ISE
Posted by GitBox <gi...@apache.org>.
maytasm commented on issue #9977:
URL: https://github.com/apache/druid/issues/9977#issuecomment-640137458
FYI: Regarding Calcite translates any in clause with a null to a join expression -> this is not optimal on Calcite planning. I believe Calcite should not forbids null in the IN clause. See: https://issues.apache.org/jira/browse/CALCITE-4048 for related discussion.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] suneet-s commented on issue #9977: SQL SELECT with IN filter containing null value throws ISE
Posted by GitBox <gi...@apache.org>.
suneet-s commented on issue #9977:
URL: https://github.com/apache/druid/issues/9977#issuecomment-639828756
Druid does not support joins against constant conditions - see #9994
Calcite translates any in clause with a null to a join expression. If the in filter does not have null, Calcite will translate the query to an IN filter. So Druid can not support this type of query til #9994 is fixed.
If you want this query to work, re-write it to
```
select * from druid.foo where dim2 in ('a')
```
This will produce the same results as the query filed in this issue.
If you want the query to match nulls, then the above query should be re-written to
```
select * from druid.foo where dim2 in ('a') or dim2 is NULL
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] maytasm edited a comment on issue #9977: SQL SELECT with IN filter containing null value throws ISE
Posted by GitBox <gi...@apache.org>.
maytasm edited a comment on issue #9977:
URL: https://github.com/apache/druid/issues/9977#issuecomment-640137458
FYI: Regarding Calcite translates any in clause with a null to a join expression -> this is not optimal on Calcite conversion from SqlNode to RelNode. I believe Calcite should not forbids null in the IN clause. See: https://issues.apache.org/jira/browse/CALCITE-4048 for related discussion.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org