You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "Brimborion (via GitHub)" <gi...@apache.org> on 2023/05/02 10:41:45 UTC
[GitHub] [druid] Brimborion opened a new issue, #14199: SQL AND clause with lookup are not well translated to native queries
Brimborion opened a new issue, #14199:
URL: https://github.com/apache/druid/issues/14199
### Affected Version
25.0.0
### Description
Sometimes, when I create an SQL query with a AND clause referencing a lookup on the same column, I get an error or the native query cannot be created.
This request is processed correctly:
```sql
select isCompletedCasesLookup.k
from lookup."30ef4a57-3647-4785-b7ca-9cbd568ae7c1-1_cases_is_completed" "isCompletedCasesLookup"
where isCompletedCasesLookup.v = 'false' and isCompletedCasesLookup.v = 'true'
```
to this native query:
```json
{
"queryType": "scan",
"dataSource": {
"type": "inline",
"columnNames": [
"k",
"v"
],
"columnTypes": [
"STRING",
"STRING"
],
"rows": []
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
]
},
"resultFormat": "compactedList",
"columns": [
"k"
],
"legacy": false,
"context": {
"populateCache": false,
"queryId": "e46be9e6-0686-4663-8fee-fd5c9b805708",
"sqlQueryId": "e46be9e6-0686-4663-8fee-fd5c9b805708",
"useApproximateCountDistinct": false,
"useApproximateTopN": false,
"useCache": false,
"useNativeQueryExplain": true
},
"granularity": {
"type": "all"
}
}
```
But, when I try to run this query:
```sql
select isCompletedCasesLookup.k
from lookup."30ef4a57-3647-4785-b7ca-9cbd568ae7c1-1_cases_is_completed" "isCompletedCasesLookup"
where lower(isCompletedCasesLookup.v) = 'false' and lower(isCompletedCasesLookup.v) = 'true'
```
I get `java.lang.IllegalArgumentException` with message *Can only handle a single interval, got[[]]*. This is the native query generated:
```json
{
"queryType": "scan",
"dataSource": {
"type": "lookup",
"lookup": "30ef4a57-3647-4785-b7ca-9cbd568ae7c1-1_cases_is_completed"
},
"intervals": {
"type": "intervals",
"intervals": []
},
"virtualColumns": [
{
"type": "expression",
"name": "v0",
"expression": "lower(\"v\")",
"outputType": "STRING"
}
],
"resultFormat": "compactedList",
"columns": [
"k"
],
"legacy": false,
"context": {
"populateCache": false,
"queryId": "af155c04-9132-4058-b912-e73a0a6fbd9c",
"sqlQueryId": "af155c04-9132-4058-b912-e73a0a6fbd9c",
"useApproximateCountDistinct": false,
"useApproximateTopN": false,
"useCache": false,
"useNativeQueryExplain": true
},
"granularity": {
"type": "all"
}
}
```
And if run the following request, I can’t even retrieve the native query:
```sql
select caseid, __time
from "30ef4a57-3647-4785-b7ca-9cbd568ae7c1-1"
join (
select isCompletedCasesLookup.k "filter"
from lookup."30ef4a57-3647-4785-b7ca-9cbd568ae7c1-1_cases_is_completed" "isCompletedCasesLookup"
where isCompletedCasesLookup.v = 'FALSE' and isCompletedCasesLookup.v = 'TRUE'
) on caseid = "filter"
group by
caseid,
__time
order by
__time desc,
caseid
```
--
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@druid.apache.org.apache.org
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
Re: [I] SQL AND clause with lookup are not well translated to native queries (druid)
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #14199: SQL AND clause with lookup are not well translated to native queries
URL: https://github.com/apache/druid/issues/14199
--
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@druid.apache.org
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
Re: [I] SQL AND clause with lookup are not well translated to native queries (druid)
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #14199:
URL: https://github.com/apache/druid/issues/14199#issuecomment-1989680208
This issue has been closed due to lack of activity. If you think that
is incorrect, or the issue requires additional review, you can revive the issue at
any time.
--
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@druid.apache.org
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
Re: [I] SQL AND clause with lookup are not well translated to native queries (druid)
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #14199:
URL: https://github.com/apache/druid/issues/14199#issuecomment-1937927769
This issue has been marked as stale due to 280 days of inactivity.
It will be closed in 4 weeks if no further activity occurs. If this issue is still
relevant, please simply write any comment. Even if closed, you can still revive the
issue at any time or discuss it on the dev@druid.apache.org list.
Thank you for your contributions.
--
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@druid.apache.org
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