Prepared Statements / Covered Index / No results

We’ve got the following index:

CREATE INDEX `my_idx` 
ON `data1` ((split((meta().`id`),"::")[0]), `id`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6`) 
WHERE (((split((meta().`id`), "::")[1]) = "message") 
AND ((split((meta().`id`), "::")[2]) = "type"))

We have the following query:

SELECT `id`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6`
FROM `data1` WHERE split(meta().id, "::")[0] = "prefix"
AND split(meta().id, "::")[1] = "message"
AND split(meta().id, "::")[2] = "type"

When we make the query from console, it works fine (returns results)

When we make the query from code, adhoc = true, works fine (return results)

When we make the query from code, adhoc = false, does not work (NO RESULTS)

When making a similar query, but with SELECT *, it works with adhoc = false

We, btw, do not permit primary indexes.

What is Couchbase version. Can you post the explain. Did you tried with curl prepare statements? @marcog

Also try this.

CREATE INDEX `my_idx`  ON `data1` (META().id, `id`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6`) ;
SELECT `id`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6` FROM `data1` WHERE META().id LIKE "prefix::message::type%";

In addition, how many N1QL nodes do you have?

We are on Couchbase 4.5.1.

Not using curl, using the Java SDK.

We cannot do the query like you suggested (where LIKE “…%”) because of how we break up the keys for our application instances by environment… So, prefix may not always be prefix.

We have 3 data nodes, 2 index nodes and 2 query nodes.

Please post the explain. Did you tried with latest version.

In your Select try putting ,in your WHERE , indexed_item IS NOT MISSING
This way your query catches the INDEX you created.

EXPLAIN SELECT `id`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6`
FROM `data1` WHERE split(meta().id, "::")[0] = "prefix"
AND split(meta().id, "::")[1] = "message"
AND split(meta().id, "::")[2] = "type"
AND `id` IS NOT MISSING
AND `col1` IS NOT MISSING
AND `col2` IS NOT MISSING
AND `col3` IS NOT MISSING
AND `col4` IS NOT MISSING
AND `col5` IS NOT MISSING
AND `col6` IS NOT MISSING