Allow null in secondary index

[neumino]

I expect

r.db("foo").table("bar").filter( r.row("field").eq( value ) )

to return exactly the same results as

r.db("foo").table("bar").getAll( value, { index: "field" })

Whatever the value of value is, including null. I could understand that we don't want to index array/object, but we should index null.

[coffeemug]

A good way to think of this is r.row("field").eq( null ) being equivalent to r.row.has_fields("field"). The fact that you say eq is an artifact of treatment of nulls which unfortunately we can't do much about (other than undefining eq(null), which worse). We've in general established that we should move towards null being equivalent to non-existence, and while we can't do it in every case and we'll always have inconsistencies like these, it's in general better than the alternative.

You can still index on nulls the same way you'd index on non-existant values (e.g. createIndex('foo', r.row.has_fields('foo').not()) and I think we should keep it this way because the alternative is worse.

I move that we close this issue and treat null as non-existence whenever possible.

[mlucy]

So, another option worth thinking about would be to make get_all(null) return all the rows that were excluded from the index. That would fix the unintuitive behavior michel highlighted, allow an easy way to get a useful set of rows, and is consistent with "null means non-existence".

[neumino]

Since when are we moving toward null being equivalent to non existence?
Are we going to have

r.table("foo").update( { field: null} )

equivalent to

r.table("foo").replace( r.row.without("field") )

?

Having .get(null) returns all rows excluded from the index would mean returning more than just null values since we don't index arrays and objects.

So if I want to store if someone answer yes/no/don't know/didn't answer, I will have to use an integer or a string, which is not really cool.

I feel like we are going to add more and more inconsistencies in RQL by having null meaning absence of value... which means making RQL just more and more complicated.

[coffeemug]

So, another option worth thinking about would be to make get_all(null) return all the rows that were excluded from the index.

How would we implement that?

Are we going to have r.table("foo").update( { field: null} ) equivalent to r.table("foo").replace( r.row.without("field") )? [...]

No -- we agreed to treat that differently, but when getting documents we'll try to treat these as equivalent whenever possible. We're trying to do what's useful at any given time and use the idea of nulls being equivalent to non-existence as an overarching principle whenever possible.

I feel like we are going to add more and more inconsistencies in RQL by having null meaning absence of value... which means making RQL just more and more complicated.

Treatment of non-existence is complicated. Look at SQL -- it's an established standard designed by very smart people, and the treatment of non-existence there is a mess. ReQL is way simpler in comparison, but there will be messy parts. It's a messy problem, and as much as I'd like to find a simple and elegant solution, we've searched and searched and there doesn't seem to be one.

[mlucy]

@neumino -- We allow arrays as secondary index values, and I think we allow objects as well. The only things that aren't inserted into an index are things that return null, a non-datum type, or an error. (I think.)

We've been moving toward treating null as non-existence for a few releases now, because users seem to be using it that way. If you look at JSON datasets, some have a variable schema, and some have a consistent schema where non-existent values map to null. This means that in practice about half our users have one thing, and half have the other, and we want to make the queries those two groups need to write as similar as possible. Sometimes we can't do that because the ugliness of doing so is overwhelming, or because we settled on another way of doing it back before we knew what was up and it's too hard to change, but in general we're trying to move in that direction.

There's the additional fact that SQL uses null to mean non-existence, so a lot of people in the DB world expect that.

[mlucy]

@coffeemug -- I don't know much about the sindex implementation, but it doesn't sound that hard. (Famous words, I know.)

[neumino]

I just talked to @coffeemug and we end up agreeing that having null in the secondary indexes are better.

I think what lead to the conclusions are the following reasons:

• null is different than undefined (in RQL), doing selection.update( { field: null } ) doesn't remove the field, but set the value null
• table.filter( r.row("field").eq(null) ) would be the same as table.getAll(null, { index: "field" } )
• When creating a secondary index that is the result of a regex matching, people may still want to retrieve results that didn't match
• A common use case of using null is to store a boolean about our users (like "is_adult"), and use true/false/null/undefined: null when the user doesn't want to answer and undefined when he hasn't been asked yet. So null is a valid value in this case
• In the case of filter( r.row("age").gt(5) ), if the field age to null, we should drop the field not because the field is missing but because we try to compare null to a number.

[mlucy]

If people are creating a secondary index on a function like match that can return null, they almost certainly aren't getting the behavior that they want even with this change, because they'll be able to find all things that didn't match but not all things that did match. I don't think we need to worry about supporting that use case because people almost always want to tack a .ne(null) on the end so that both halves are in the index.

I think the number of people using null and non-existence side-by-side in their datasets to mean different things is pretty small. In general I don't think we should try very hard to support that usage (although we should always provide some way to do it), because by doing so we end up making interfaces that treat those two things differently, when most people seem to be using them to indicate the same thing in different datasets rather than different things in the same dataset. We've decided in several other places -- has_fields, for example -- to try to treat these more similarly, and I think all of those arguments still stand.

(As an aside, we don't currently throw or anything when people compare null to a number. This is arguably another place where we're treating it too much like a user value.)

[coffeemug]

I talked to @neumino today and I changed my mind on this -- I agree with him, I think we should actually index nulls, and I think I came up with a somewhat consistent attempt at formalizing this.

When we talk about null equivalency with non-existence, I think we need to differentiate between two cases -- storing data, and querying data. When storing data I think we shouldn't treat null as equivalent to non-existence, and when querying data I think we should.

We already do this to some degree -- when we save a document, {} isn't equivalent to {a: null}, but when we query it mostly is (for instance, if I try to add a number to a). I think that doing it this way is actually the most useful, and we should continue moving our treatment of nulls and non-existence in this direction. In this context, if an index expression evaluates to null on a row, I think we should still index it and make it available via getAll(null). This is useful because, as @neumino pointed out, if I index on people's age, I expect to be able to say "give me all the users that didn't fill out the form", and the easiest way to express that is with nulls.

As an aside, we don't currently throw or anything when people compare null to a number

I think that in case of filter this is an oversight and we should skip the row, but perhaps we should wait and see what users say when they (inevitably) run into this edge case.

[neumino]

👍 @coffeemug

[mlucy]

@coffeemug -- let's say you have a table of people who filled out a survery. User A writes {:name => "Slava", :age => nil} to mean "didn't provide age", while user B writes {:name => "Slava"} to indicate the same thing. Now let's say you create a secondary index on age.

Currently, we don't offer any way for either user to get the people who didn't enter their age. If we index null, we solve user A's problem but not user B's. If we say that get_all(null) means "get everything not in the index", we solve both user's problems.

This only doesn't work for users who are using null and non-existence in the same dataset to mean different things, which seems like a rare case to me, and even they would have a workaround (they could just tack a filter on the end).

[coffeemug]

I see what you mean. I have to think about it a bit more, but a few things immediately jump out at me.

1. I don't see how we could efficiently implement get_all(null) as "everything not in the index" without actually indexing nulls (and missing attributes). I know we're talking semantics, but when there is no clear implementation path, I think it's important to discuss this too.
2. I think it would be very confusing for users to say get_all(null) and actually get non-existent rows. I think it's ok to filter out nulls and missing attrs in the same way (e.g. via has_fields), but not ok to select them in the same way. The former seems desirable, the latter seems confusing.
3. Intuitively, I would very much prefer RethinkDB to solve user A's problem but not user B's in this case. I'm not sure why, but that feels righter to me. I'll try to think of a way to explain/formalize it.

[coffeemug]

I think I seem to have a really strong intuitive sense for when nulls and missing attrs should be treated similarly and when they should be treated differently, but I can't find an underlying principle for this intuition. (Not to say that this sense is necessarily correct, just that it's strong.)

[coffeemug]

Here's a very rough set of rules that seem to adequately express my intuition (probably inconsistent and incomplete):

1. When storing things, treat nulls and missing attrs differently.
2. When selecting for existence (e.g. age == 20, age != 20), treat nulls and missing attrs in the same way (as non-existence; e.g. filtering for age != 20 when age is null should skip the row)
3. When selecting for non-existence (e.g. age == null, getAll(null), etc.), treat nulls and missing attrs differently.
4. When filtering out non-existence (e.g. has_fields('age')), treat nulls and missing attrs in the same way.

A system that adheres to these rules would feel extremely intuitive to me -- I think it would do exactly what I would expect most of the time, would allow me to do most everything I want, and wouldn't feel forced or icky. This is the kind of DB I'd like to actually use (wrt nulls), I think.

[mlucy]

Actually, another argument in favor of this is that it would make compound indexes more useful if one of the entries could be null.