Hi N1QL team,
I was trying out a SUBQUERY where the UPDATE as the outer query and a SELECT as the inner query.
Both of these queries make use of the same Index and both queries work separately fine but when I combine them into a Subquery it doesn’t work and it says “No index available on keyspace shadows that matches your query.”. What am I doing wrong here?
UPDATE shadows AS sha
SET sha.`waspd` = (CASE WHEN sha.`cmid` == " !!" AND MILLIS(sha.`due`) < NOW_MILLIS() THEN TRUE ELSE FALSE END),
sha.`isp` = (CASE WHEN sha.`strt` IS VALUED AND MILLIS(sha.`strt`) >= NOW_MILLIS() THEN TRUE ELSE FALSE END)
WHERE (NOT IFMISSINGORNULL(sha.`softdel`, FALSE))
AND (sha.`type` = "task")
AND (sha.`isp` IS NOT VALUED
OR sha.`strtloc` IS NOT VALUED
OR sha.`strt` IS NOT VALUED
OR sha.`waspd` IS NOT VALUED)
AND (sha.`tid` IN (SELECT RAW innerSha.`tid`
FROM shadows AS innerSha
WHERE (NOT IFMISSINGORNULL(innerSha.`softdel`, FALSE))
AND (innerSha.`type` = "task")
AND (innerSha.`isp` IS VALUED
OR innerSha.`strtloc` IS VALUED
OR innerSha.`strt` IS VALUED
OR innerSha.`waspd` IS VALUED)
AND innerSha.`tid` IS VALUED
LIMIT 2))