N1QL query works even with missing predicate created using couchbase secondary index

SQL++
, , ,
1

I have created a secondary index in couchbase:

    CREATE INDEX `data` ON `bucket`(`field1`,`field2`,`field3`,
    lower(`field4`)) WHERE (`field5` = "CONSTANT")

When I execute this query on couchbase:

    select document.* from bucket AS document WHERE document.field5 = "CONSTANT" 
    AND document.field1 =  "docId"
    and (document.field3 like "%" or 
                lower(document.field4) like "%");

Even without giving the value for one of the leading predicates(field2), the query still seems to work properly. Can anyone explain why?

This is the query plan that is returned when adding “EXPLAIN” in front of the query:

        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "IndexScan3",
                "as": "document",
                "index": "data",
                "index_id": "6588bcds21b213",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "bucket",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"docId\"",
                        "inclusion": 3,
                        "low": "\"docId\""
                      }
                    ]
                  }
                ],
                "using": "gsi"
              },
              {
                "#operator": "Fetch",
                "as": "document",
                "keyspace": "bucket",
                "namespace": "default"
              },
              {
                "#operator": "Parallel",
                "~child": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "Filter",
                      "condition": "((((`document`.`field5`) = \"CONSTANT\") and ((`document`.`field1`) = \"docId\")) and (((`document`.`field3`) like \"%\"]) or (lower((`document`.`field4`)) like \"%\")))"
                    },
                    {
                      "#operator": "Project",
                      "result_terms": [
                        {
                          "expr": "`document`",
                          "star": true
                        }
                      ]
                    }
                  ]
                }
              }
            ]
          }
2

The Index Selection requirements are.
1. If index has where clause it must be superset of query where clause (i.e. index must have entry).
2. Query predicate must have leading index key every part of OR clause.
In your case field5 satisfied 1, field1 satisfied 2

If you look spans it asking indexer give all the docId
Rest of the predicates are applied post indexscan/post fetch