Hi all,
When querying views, Couchbase supports universal minimum and maximum values for any value type (null and {}), which is very handy.
N1QL does not seem to support them. If you do not want to multiply the number of prepared statements, and would like to reuse a few general ones, it would be interesting to supply them with these minimum and maximum values. I do not see how to do that without support for null and {}?
Thanks!
I am not sure if I understood your question correctly cc @keshav_m.
But N1QL has the following constructs.
k1 IS NOT MISSING ( any type includes nulls)
k1 IS MISSING
k1 IS NOT NULL (any type doesn't include nulls)
k1 IS NULL
k1 IS NOT VALUED
k1 IS VALUED (any type doesn't include nulls)That doesn’t help me, none of these are universal minimum or maximum values, like null and {}, which are accepted by queries on views (outside of N1QL).
Let me take an example. You create an index over attribute a.
Then you prepare a select statement selecting on a range over these attributes:
select whatever from wherever where a>=$1 and a<=$2;
I use it with $1 and $2 values in a variety of places in the code.
If I want to reuse that prepared statement with a $1 value which is “foo” and a $2 value which is the “maximum possible value” for $2, I expect I should be able to. It should nicely use the index, and allow me to navigate through it, without having to define another select which would not have the second comparison.
The only problem is that there is no “maximum possible value” in N1QL. Unlike with views, I cannot supply {} as a valid value for $2. There is even no maximum possible value for a string anyway (only a minimum one, “”).
Views allow to do that very easily, for instance with the libcouchbase API (not using N1QL). You can reuse a view in different contexts, with a specified minimum value (say “bar”) and a maximum value (say “foo”). But you can also specify that you want to query the view between “bar” and {}, because {} is a universal maximum value (applicable to integers, strings, whatever needed) which is properly processed by Couchbase (and CouchDB btw). You can also query the view for values of a between null and “bar”, because null is a universal minimal value.
But neither null nor {} are accepted by N1QL.
Maybe I am missing something?
Things get much worse when several attributes are queried. If I have, say, 4 of them, a, b, c and d, and I prepare a statement selecting on a range on each of them (that’s $1-8 positional parameters), I do not see how I can reuse the - single - prepared statement with universal minimum and maximum values for a, b, c or d.
If I have to write and prepare additional select statements with all the variations, avoiding the use of universal minimum and maximum values, I have to write:
select whatever from wherever where a>=$1 and a<=$2 etc on b, c and d;
select whatever from wherever where a>=$1 etc;
select whatever from wherever where a<=$1 etc;
…
with all the combinations on b, c, and d. With asc and desc order by variations, that’s 486 different selects… That’s not very reasonable. And it’s not recommended to prepare all these statements either. I should be able to prepare only one select (carefully written, meant to perform well and use indexes properly). The only obstacle I see to that is that null and {} are not accepted by N1QL (well, also that asc and desc cannot be parameters, while they are for views).
But again, I am maybe missing something obvious.
N1QL works like SQL
You can’t use relational operators (,= ,!=, <=,<,>,>=) ON NULL,MISSING you need to use constructs one mentioned above.
N1QL comparison is not only on value it also includes type.
N1QL define an ordering when comparing values of different types. The following list describes the order by type (from lowest to highest): https://developer.couchbase.com/documentation/server/5.0/n1ql/n1ql-language-reference/datatypes.html
missing
null
false
true
number
string (string comparison is done using a raw byte collation of UTF8 encoded strings)
array (element by element comparison is performed until the end of the shorter array; if all the elements so far are equal, then the longer array sorts after)
object (larger objects sort after; for objects of equal length, name/value by name/value comparison is performed; names are examined in sorted order using the normal ordering for strings)
binary (raw byte-wise comparison)
select whatever from wherever where a>=$1 and a<=$2;
If you specify $1 = false and $2 = [] it includes all booleans,numbers,strings
If you specify $1 = false and $2 = {} it includes all booleans,numbers,strings,arrays
Oh wow, that’s perfect. I think that answers my question completely. Let me play with this!
Big thanks!
It works fine, for instance, using ‘false’ in a comparison in a where clause, like:
select * from test where i>false
works well, whatever type i is. The same goes with ‘{}’:
select * from test where i<{}
But for some reason (and it’s the reason why I thought nothing of this worked, as ‘null’ works well with views):
select * from test where i>null
does not work at all. It is syntactically correct, but it returns nothing. In the where clause, ‘null’ does not always seem to be less than false. I say “not always” because, funnily enough, ‘null’ works fine when manipulating an array:
select * from test where any v in a satisfies v>null end
Returns everything nicely.
NULL means Undefined , so i > NULL can’t be compared and always returns false. This is same for any relational SQL databases.
That is why IS NULL, IS NOT NULL, IS MISSING and IS NOT MISSING are there.
In N1QL i > NULL is equivalent of i >= false or i IS NOT NULL.