We are moving from legacy way of handling `null` t...
# general
j
We are moving from legacy way of handling
null
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.
g
hopefully, this answers some of your questions- if any remain after reading the doc, please let us know
j
Thanks @Gian Merlino, I have been following that guide my feel rewriting the query section is too vague - https://druid.apache.org/docs/latest/release-info/migr-ansi-sql-null/#rewrite-your-queries-to-be-sql-compliant I wanted to know generalized way of modifying my existing queries so that queries have proper checks for
null
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 context
For example: original filter:
Copy code
{
  "type": "selector",
  "dimension": "D",
  "value": "V"
}
should become:
Copy code
{
  "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?
and second variant would be: original filter:
Copy code
{
  "type": "not",
  "field": {
    "type": "selector",
    "dimension": "optional_field",
    "value": null
  }
}
updated filter:
Copy code
{
  "type": "or",
  "fields": [
    {
      "type": "not",
      "field": {
        "type": "null",
        "dimension": "optional_field"
      }
    },
    {
      "type": "not",
      "field": {
        "type": "selector",
        "dimension": "optional_field",
        "value": ""
      }
    }
  ]
}
is it safe to think we have to change only these two variants? is there anything else we should be careful about?
g
ahh, yes, the guide was written with SQL in mind
for native, the
null
filter (https://druid.apache.org/docs/latest/querying/filters/#null-filter) is the equivalent of SQL
IS NULL
equivalent of SQL
IS NOT NULL
is to wrap the
null
filter in
not
there are a few different aspects to SQL compliant null handling (described in the doc) - but the one that is most relevant to filters is that it uses 3 valued logic instead of 2 valued logic. the differences come out when you use
not
the simplest way to convert is use
istrue
, which wraps any filter and essentially converts 3 valued logic to 2 valued logic by mapping
unknown
to
false
i.e. instead of this:
Copy code
{
  "type": "not",
  "field": {
    "type": "selector",
    "dimension": "countryName",
    "value": "United States"
  }
}
use this:
Copy code
{
  "type": "not",
  "field": {
    "type": "istrue",
    "field": {
      "type": "selector",
      "dimension": "countryName",
      "value": "United States"
    }
  }
}
the first one will not match
countryName: null
. the second one will match
countryName: null
j
Thanks. I couldn't find any reference of
istrue
in https://druid.apache.org/docs/latest/querying/filters
also, how would this behave in case of nested filters inside
not
filter? example:
Copy code
{
  "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?
g
ah, it definitely exists and is used by the SQL planner. user-facing docs must be missing. @Clint Wylie do you remember if there is a reason it (
istrue
filter) is not documented?
if you want to mimic 2vl it's sufficient to wrap the innermost filters in
istrue
. in this case it's the four
selector
c
hmm, perhaps oversight 😅
yea, my mistake they are not documented
j
ahh, I think it would be super helpful for folks to know about this option, please plan to include it in the official docs 🙂 , do you have a link to any git reference or PR documentation that I can take a look meanwhile ?
c
Copy code
{ "type": <"istrue"|"isfalse">, "field": <wrapped filter> }
j
@Gian Merlino thanks a lot. so is it safe to wrap every
selector
filter in
istrue
if we want two value logic across the board ?
@Clint Wylie do you have a link to any PR or discussion about
istrue
/
isfalse
filters? it would be nice to know the details before we go with this approach to understand it better. Thanks.
g
when
value
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
UNKNOWN
j
Thank you, this is very helpful. I will give it a try.