Jvalant Patel
05/14/2025, 10:45 PMnull to the latest Druid version where legacy mode is not supported, just wanted to get some help from here to know what should be the best strategy to upgrade druid if we have null and "" strings in the datasources and our queries rely on the legacy behavior. If we want to rewrite queries to handle three valued logic for null comparisons, what should be the strategy ? is there any generalized way to modify the queries ? we are still using native Druid query language.Gian Merlino
05/14/2025, 11:02 PMGian Merlino
05/14/2025, 11:02 PMJvalant Patel
05/14/2025, 11:12 PMnull values to avoid unknown results.
Also, all the references are in SQL but we use native Druid queries.
1. What is the equivalent of IS NULL (SQL) in native Druid query? same how about IS NOT NULL check?
2. Does this mean, we have to only change:
a. explicit checks for NOT NULL or NOT Empty string - need to include both NULL and empty string checks for given dimension.
b. recursive null checks for all dimension in given NOT filter contextJvalant Patel
05/14/2025, 11:14 PM{
"type": "selector",
"dimension": "D",
"value": "V"
}
should become:
{
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "D",
"value": "V"
},
{
"type": "not",
"field": {
"type": "selector",
"dimension": "D",
"value": null
}
}
]
}
if it is inside the parent NOT filter?Jvalant Patel
05/14/2025, 11:16 PM{
"type": "not",
"field": {
"type": "selector",
"dimension": "optional_field",
"value": null
}
}
updated filter:
{
"type": "or",
"fields": [
{
"type": "not",
"field": {
"type": "null",
"dimension": "optional_field"
}
},
{
"type": "not",
"field": {
"type": "selector",
"dimension": "optional_field",
"value": ""
}
}
]
}Jvalant Patel
05/14/2025, 11:16 PMGian Merlino
05/14/2025, 11:18 PMGian Merlino
05/14/2025, 11:19 PMnull filter (https://druid.apache.org/docs/latest/querying/filters/#null-filter) is the equivalent of SQL IS NULLGian Merlino
05/14/2025, 11:20 PMIS NOT NULL is to wrap the null filter in notGian Merlino
05/14/2025, 11:28 PMnotGian Merlino
05/14/2025, 11:30 PMistrue, which wraps any filter and essentially converts 3 valued logic to 2 valued logic by mapping unknown to falseGian Merlino
05/14/2025, 11:31 PM{
"type": "not",
"field": {
"type": "selector",
"dimension": "countryName",
"value": "United States"
}
}
use this:
{
"type": "not",
"field": {
"type": "istrue",
"field": {
"type": "selector",
"dimension": "countryName",
"value": "United States"
}
}
}Gian Merlino
05/14/2025, 11:31 PMcountryName: null. the second one will match countryName: nullJvalant Patel
05/14/2025, 11:34 PMistrue in https://druid.apache.org/docs/latest/querying/filtersJvalant Patel
05/14/2025, 11:37 PMnot filter?
example:
{
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "clientid",
"value": "300"
},
{
"type": "not",
"field": {
"type": "or",
"fields": [
{
"type": "and",
"fields": [
{
"type": "selector",
"dimension": "category",
"value": "A"
},
{
"type": "selector",
"dimension": "source",
"value": "B"
}
]
},
{
"type": "selector",
"dimension": "source",
"value": "B"
}
]
}
}
]
}
wouldn't have to check for null values for source and category dimensions here?Gian Merlino
05/14/2025, 11:38 PMistrue filter) is not documented?Gian Merlino
05/14/2025, 11:39 PMistrue. in this case it's the four selectorClint Wylie
05/14/2025, 11:40 PMClint Wylie
05/14/2025, 11:42 PMJvalant Patel
05/14/2025, 11:43 PMClint Wylie
05/14/2025, 11:43 PM{ "type": <"istrue"|"isfalse">, "field": <wrapped filter> }Jvalant Patel
05/14/2025, 11:44 PMselector filter in istrue if we want two value logic across the board ?Jvalant Patel
05/14/2025, 11:51 PMistrue / isfalse filters? it would be nice to know the details before we go with this approach to understand it better. Thanks.Gian Merlino
05/14/2025, 11:55 PMvalue is not null, i believe wrapping in istrue is all you need. when value is null, i would replace the selector with a type: null filter. there is no need to wrap the type: null filter in istrue since it never returns UNKNOWNJvalant Patel
05/15/2025, 12:01 AM