LET clause and sub select processing

n1ql

#1

If we have document types (one is referring other):

{“id”:“parent_1”,
“type”:“Parent”,
“child”:“child_1”,
“name”:“John”
}

{“id”:“child_1”,
“type”:“Child”,
“name”:“Mike”}

having M-1 one relationship and index on child field in Parent type. Let’s say Parent has very big cardinality and do the following query:

SELECT meta().id FROM bucket parent
WHERE type = “Parent” AND child IN [“Child_1”]

it will, offcourse, do the index scan and finish this in no time. If you the alias this through LET and get this:

SELECT meta().id FROM bucket parent
LET cc = [“Child_1”]
WHERE type = “Parent” AND child IN cc

it will take eternity, since it will not allow index to work with calculated cc. Same goes for this:

SELECT meta().id FROM bucket parent
WHERE type = “Parent” AND child IN (SELECT[“Child_1”] ids)[0].ids)

It will also take forever although semantically should be the same as first and second query.

Offcourse, aliasing static arrays like this is stupid and should never be done, but in cases you would like to have some dynamic array here you would have to use some of the last two variants and getting performances very poor.

Anyone knows if this can be overridden somehow?

Thanks,
Vladimir.


#2

Do you know the size of the dynamic array in advance?