# Query.jl with filtering by missing values doesn't seem to work?

**URL:** <https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507>\
**Category:** General Usage\
**Created:** [January 21, 2018, 6:08pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507 "2018-01-21T18:08:46Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![essenciary](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/essenciary/32/210469_2.png) [@essenciary](https://discourse.julialang.org/u/essenciary)\
**Post date:** [January 21, 2018, 6:08pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/1 "2018-01-21T18:08:46Z")

</div>

I’m probably missing something but I can’t figure out why this doesn’t work.

I have a `DataFrame` and I want to use `Query.jl` to filter it by various criteria, including keeping/removing rows where certain columns are missing.

Now, let’s take just one row as an example:

```julia
julia> clean_df = @from b in df begin
       @where b.Location_Id == "0474285-05-001"
       @select {b.DBA_Name, b.Business_End_Date}
       @collect DataFrame
       end

1×2 DataFrames.DataFrame
│ Row │ DBA_Name │ Business_End_Date │
├─────┼─────────────────┼───────────────────┤
│ 1 │ Pressed Juicery │ missing │

```

Sure enough, `Business_End_Date` is missing:

```julia
julia> clean_df[1, :Business_End_Date] |> ismissing
true

```

However, attempting to filter by the values where `Business_End_Date` fails:

```julia
julia> @from b in df begin
       @where b.Location_Id == "0474285-05-001" && ismissing(b.Business_End_Date)
       @select {b.DBA_Name, b.Business_End_Date}
       @collect DataFrame
       end

0×2 DataFrames.DataFrame

```

🤨

---

<div class="post-metadata">

**Author:** ![essenciary](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/essenciary/32/210469_2.png) [@essenciary](https://discourse.julialang.org/u/essenciary)\
**Post date:** [January 21, 2018, 6:12pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/2 "2018-01-21T18:12:09Z")

</div>

Hmmm, for some reason, within the query statements block, `ismissing` is `false`:

```julia
julia> @from b in df begin
       @where b.Location_Id == "0474285-05-001"
       @select {b.DBA_Name, b.Business_End_Date, ismissing(b.Business_End_Date)}
       @collect DataFrame
       end

1×3 DataFrames.DataFrame
│ Row │ DBA_Name │ Business_End_Date │ _3_ │
├─────┼─────────────────┼───────────────────┼───────┤
│ 1 │ Pressed Juicery │ missing │ false │

```

---

<div class="post-metadata">

**Author:** ![essenciary](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/essenciary/32/210469_2.png) [@essenciary](https://discourse.julialang.org/u/essenciary)\
**Post date:** [January 21, 2018, 7:20pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/3 "2018-01-21T19:20:58Z")

</div>

Digging in it turned out that `Query.jl` does not have support for `missing`, instead using a “proprietary” `DataValues.DataValue`. The check should be performed using `isnull`.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [January 21, 2018, 8:03pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/4 "2018-01-21T20:03:45Z")

</div>

That is right. The current design of `Missing` is simple, but can’t be used for something like [Query.jl](https://github.com/davidanthoff/Query.jl). Various folks have been trying to find a solution to this, but no workable plan has emerged so far, so we’ll have to see how this plays out.

---

<div class="post-metadata">

**Author:** ![essenciary](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/essenciary/32/210469_2.png) [@essenciary](https://discourse.julialang.org/u/essenciary)\
**Post date:** [January 22, 2018, 6:37am UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/5 "2018-01-22T06:37:29Z")

</div>

As a quick workaround, do you think there would be any value in having the API extended so that:

1. an `ismissing` method is defined for `DataValues.DataValue` (or as an alias for `isnull`).
2. optionally define a `DataValues.Missing` as an alias to `DataValues.DataValue`.

Like a simple form of duck-typing so that the minimalistic `Missings` API “would work”.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [January 22, 2018, 4:22pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/6 "2018-01-22T16:22:24Z")

</div>

So I could certainly add a method to `ismissing` so that it also works with `DataValue`. I’m a bit on the fence whether it is a good idea: the semantics of `Missing` and `DataValue` are different in a number of instances, and I’m just not sure whether it might be better to keep the nomenclatur of the two separate to make it clear to users that they actually have different semantics, or not. I think if we do any of this I’d do it in the release that goes along with julia 0.7.

I think defining `DataValues.Missing` is probably not a good idea, I think that would just lead to a very confusing situation where the same term has different meanings in different contexts and no one would know anymore what is going on.

---

<div class="post-metadata">

**Author:** ![piever](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/piever/32/1815_2.png) [@piever](https://discourse.julialang.org/u/piever)\
**Post date:** [January 22, 2018, 5:16pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/7 "2018-01-22T17:16:12Z")

</div>

Slightly off-topic, but I was wondering whether a first step could be to replace the content of a `DataValue` with something like:

```julia
struct DataValue{T}
    value::Union{T, Missing}
end

```

which hopefully in 0.7 should be as performant as the previous implementation. I don’t have a strong intuition on these things but I believe this could also have the correct layout in an Array without having to use a DataValueArray.

Then one would simply argue that Query needs a wrapper around Missing for technical reasons (as a temporary solution, before the Missing story becomes compatible with the Query design) and it would make sense to try and make this wrapper as invisible as possible to the user.

---

<div class="post-metadata">

**Author:** ![davidanthoff](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/davidanthoff/32/223493_2.png) [@davidanthoff](https://discourse.julialang.org/u/davidanthoff)\
**Post date:** [January 22, 2018, 7:50pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/8 "2018-01-22T19:50:20Z")

</div>

It probably would have to be `value::Union{Some{T}, Missing}`. I tried that a few weeks ago, and it segfaulted julia 0.7 😉 I think that is fixed now on `master`, though.

I would be surprised if this would give the correct array layout, though. I think one would end up with an efficient representation of the union in the `struct`, but that would probably be all.

I think I still need a different sentinal `NA` value for `DataValue`, no matter what. Right now that is defined as `const NA = DataValue{Union{}}()`, and I don’t think that could easily be replaced with the existing `missing` definition.

I’ve also been thinking briefly about a design where we had

```julia
struct Missing{T}
    value::Union{Some{T}, Void}
end

const missing = Missing{Union{}}()

```

and then one could have `Union{T, Missing{Union{}}` as the simple representation of missingness. It would be some sort of hybrid between the current `Missing` design and the `DataValue` design. I haven’t thought it through and it might well be a silly idea. But it is probably kind of pointless to even consider it at this point, given that the `Missing` design is essentially baked into stone until juila 2.0 at this point, as far as I can tell.

> [@piever](#):
>
> as a temporary solution, before the Missing story becomes compatible with the Query design

Well, that is still the question, whether that can happen. I’ll need to follow up on the other thread, but I still think there is just a really deep design mismatch. Query’s design is all inspired by the kind of monadic composability that folks like Erik Meijer and his MS friends have pushed in the early 2000s, and the `Union` approach just doesn’t seem to have those properties. But, sorry for even bringing this up here, lets keep that discussion in the other thread we have 🙂

---

<div class="post-metadata">

**Author:** ![nalimilan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nalimilan/32/147_2.png) [@nalimilan](https://discourse.julialang.org/u/nalimilan)\
**Post date:** [January 23, 2018, 3:05pm UTC](https://discourse.julialang.org/t/query-jl-with-filtering-by-missing-values-doesnt-seem-to-work/8507/9 "2018-01-23T15:05:56Z")

</div>

> [@davidanthoff](#):
>
> So I could certainly add a method to ismissing so that it also works with DataValue. I’m a bit on the fence whether it is a good idea: the semantics of Missing and DataValue are different in a number of instances, and I’m just not sure whether it might be better to keep the nomenclatur of the two separate to make it clear to users that they actually have different semantics, or not. I think if we do any of this I’d do it in the release that goes along with julia 0.7.

Yeah, using `ismissing` for `DataValue` would kind of make sense, given that it’s supposed to represent missing values and that `isnull` doesn’t exist anymore in Base – especially since Query converts `missing` to an empty `DataValue` automatically. It doesn’t seem very useful to bug users with this difference in function names, which AFAICT is really the main significant difference in behavior between `DaatValue` and `missing`.