Filter FieldName <-. [Nothing] not finding expected results

[rampion]

I'm running into some odd behaviour where the filter FieldName <-. [Just v, Nothing] finds rows where FieldName is either null or v, but FieldName <-. [Nothing] doesn't find any rows at all.

  _johnId <- insert $ Person "John Doe" $ Just 35
  _janeId <- insert $ Person "Jane Doe" Nothing

  selectList [] [] -- finds no one
  selectList [PersonAge <-. [Nothing, Just 35]] [] -- finds John and Jane
  selectList [PersonAge <-. [Just 35]] [] -- finds John
  selectList [PersonAge <-. [Nothing]] [] -- expected to find Jane, but finds no one

Here's a complete repro using sqlite, but I've also observed this in Postgres.

[Vlix]

I see this is because selectList uses selectSourceRes, which uses getFiltsValues, which uses filterClauseHelper that gets a OrNullNo, so it won't make an OR column IS NULL extension.

I'm not sure if anything would break if that would be adjusted internally, but I do know that PersonAge ==. Nothing would work as you'd expect. So that's at least a workaround.

[rampion]

Yeah currently my workaround is

selectList
  [ case ages of
      [] -> PersonAge ==. Nothing
      _:_ -> PersonAge <-. Nothing : map Just ages
  ]
  []

[Vlix]

If you always want the NULL values, I think you can just write it as:

selectList ([PersonAge ==. Nothing] ||. [PersonAge <-. map Just ages]) []

[rampion]

Yes, there are many workarounds to this surprising behaviour.

[parsonsmatt]

I agree that this should be fixed, fwiw

[Vlix]

I agree that this should be fixed, fwiw

Would changing the OrNullNo to OrNullYes in selectList break things? 🤔