IS NOT VALUED does not seem to work.

Maybe I am not understanding this correctly, but I expected IS NOT VALUED to be the same as (IS MISSING OR IS NULL). This does not seem to be the case.

SELECT * FROM gamesim-sample WHERE loggedIn IS NOT VALUED
// returns no results
 
// whereas
SELECT * FROM gamesim-sample WHERE loggedIn IS MISSING
// does return results

1 Answer

« Back to question.

From the DP1 spec:

Comparing NULL and MISSING values

The normal comparison operators cannot be used to check for NULL or MISSING values because they do not contain type information. Instead the following operators are designed specifically to work for these values.

IS NULL - returns rows where the value of a property is explicitly set to NULL (not missing).
IS NOT NULL - returns rows which contain a value (not NULL or missing).
IS MISSING - returns rows where the value of a property is missing (not explicitly set to null).
IS NOT MISSING - returns rows which contain a value or null.
IS VALUED - synonym for IS NOT NULL
IS NOT VALUED - synonym for IS NULL
NOTE: IS NULL/IS NOT NULL and IS MISSING/IS NOT MISSING are NOT inverse operators. See table below:

Value
Operator JSON value NULL MISSING
IS NULL FALSE TRUE FALSE
IS NOT NULL TRUE FALSE FALSE
IS MISSING FALSE FALSE TRUE
IS NOT MISSING TRUE TRUE FALSE
IS VALUED TRUE FALSE FALSE
IS NOT VALUED FALSE TRUE FALSE

The spec you posted is not inline with the spec linked below. In the spec linked, IS NULL/IS NOT NULL are inverse operators.
http://docs.couchbase.com/couchbase-query-language-0.1/#comparison-terms

Your table does seem to be inline with how DP2 is actually working (I think...). With that said, this table is logically counter intuitive. IMO...
MISSING should mean typeof field === "undefined"
VALUED should mean typeof field !== "undefined" && field !== null
NULL should mean field === null

What's the point of having IS NULL and IS NOT VALUED being synonymous? This behavior does not make sense.

We went back and forth on this, and are still weighing this. You are right that if IS [ NOT ] VALUED is a synonym, we should remove it.

We wanted a formalism for MISSING, and we defined it as "we don't know if the value is undefined, null, or valued". So IS NOT VALUED for missing was defined as "we cannot affirmatively say true". The approach you suggest was the original approach, and may be more practical and useful. There's a good chance we'll go back to that after DP3.

For now, you can use NOT(IS VALUED ...).

Thanks.