Filter using != misses records where value does not exist

We are trying to retrieve all records where a certain numeric field does not match a particular value. However using != on a field that is not always populated produces strange results:

c3Count(PhysicalMeasurementSeries)
45047546
c3Count(PhysicalMeasurementSeries, "count == 999999999")
1463
c3Count(PhysicalMeasurementSeries, "count != 999999999")
33026061

We expected the last query to return 45047546 - 1463 = 45046083 but that is not happening. The following works:

c3Count(PhysicalMeasurementSeries, "!exists(count) || count != 999999999")
45046083

Can someone help us understand this behavior?

@yaroslav Welcome to the world of relational databases. Null doesn’t work as you might expect. In particular, both of the following are false statements in sql:

‘anything’ = null
‘anything’ <> null

So you see, null doesn’t equal anything but also doesn’t not equal anything!

We have also encountered this same behavior with relational types.

To me this looks like a “problem” that we can “fix”.

Users don’t query the relational database, they query the typesystem. An argument could be made that we convert a filter: "field != value" to a sql query as field <> value OR !exists field

I’m interested to know what our customers would think about this change, and also if @trothwein you have any thoughts positive or negative about it?

1 Like

Sometimes using filters with !exists take a while to execute on large tables, so I could see a bit of downside, but maybe there is a good way to specify using !== rather than != or something clever.