Why does N1QL use Collation Order for WHERE filters?

SQL++
1

If I write a query SELECT * where x < 99, I expect documents where x=1 to be returned, but not documents where x="foo".

N1QL Collation Order treats booleans as less than integers, followed by strings and then JsonArray. I understand that this is valuable for ORDER BY: If there is a document with x=99 and another document with x="foo", then the engine needs to return them in some order and it is best that that order be deterministic.

But for WHERE clauses, clearly "foo" < 9 and "foo" > 99 should both be false and not include such values in the filter. (And also, it seems that in Javascript, both these expressions are false , as they should be.)

Why does Couchbase do it this way?

(Based on this StackOverflow question.)

2

In JSON there is no type. you can have one document x as boolean, another x as number, some other as string.
All comparison operators follows N1QL collation order.

3

Certainly, different documents can have different-typed values for a given key.

And I understand the collation order. By why do comparison operators in WHERE clauses follow N1QL collation order?

The Javascript approach, where "foo" < 9 is false and also "foo" > 99 is false makes more sense for WHERE clauses.

(Minor correction: JSON does have types, even though it does not have static typing, but it . For example, true is a boolean while "true" is a string. And in fact Couchbase recognizes this with getString() functions and the like.)

4

Values have type but not doesn’t have field names.

SELECT * where x < "z";

When one end of relation operator is not specified, If you want return the documents only x has strings (not number, boolean values) You must further filter with type functions https://docs.couchbase.com/server/6.0/analytics/8_builtin.html#TypeFunctions

Example:

SELECT * where x  < "z" AND ISSTR(x);
SELECT * where x  < "z" AND TYPE(x) IN ["string"];
5

Thank you. That type-filter is useful. But I would like to understand the reasoning behind this design.

Why did they build it so that a filter would return a string being less than a string is returned when I ask for x>99? There seem to be no use cases for that.

6

If you design data model and if you don’t overload the value type you are good. If some one overloads if they don’t specify the both ends it goes lowest/highest type of the collation order.

7

Please clarify:

By “overload the value type” do you mean “mix data of different types under the same key in different objects”?

What do you mean by “don’t specify the both ends”?

it goes lowest/highest type of the collation order

Certainly, that is my question: Why did the designers of Couchbase apply collation order to WHERE clauses?

8

Yes, you are right.

If you are using less than or less than equal you are not giving minimum value. grater than or grater than equal you are not giving maximum value.

9

This is new to me. I thought that it doesn’t make a difference if I use < or <=.

If I query WHERE x<99 , I will get the document where x="foo" (which doesn’t make sense).

But if I query y WHERE x<=99, will it be any different?

10 
SELECT y FROM default WHERE x < 99;

    Will get any documents that have x value as 
                  boolean (true, false)   or 
                  x value < 99 . 
     Any documents that 
                 x >= 99 or 
                   string or
                  array or
                  object or 
                   binary  or 
                  null or 
                 MISSING field 
      will not get. same is true  <= except equal value.

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/orderby.html#return-values

The value of y is irrelevant.

x < 99 means 99 value is not included, x <= 99 means 99 value included.