Query with select causing issue - Precondition Failed

[susanlinsfu]

I have a kind called 'm'. All the entities inside this kind have the same structure. All, but one attribute are indexed.

I run the following query:

var query = datastore.createQuery('m').select(['b','c','d','e', 'f']).filter('a', '1464235500').limit(10);

  datastore.runQuery(query, function(err, entities, info) {
    if (err) {
      return (callback('error : ' + err)); // This prints out 'error : Precondition Failed'
    }
       //do stuff with entities
});

It never gets the entities and instead prints out the error message : Precondition Failed. Without select, the query return fine, but with select it does not. I can't find the problem since all the projected attributes are indexed and none are the filtering is not on a projected attribute.

0.34.0-gcloud-node and real cloud datastore (non-emulator)

[stephenplusplus]

@pcostell any ideas?

[pcostell]

When you perform a projection, it indicates to Cloud Datastore to perform an index-only query -- the full entity is never looked up in the Entities table. This means you need all data to be included in the index scan. In the case of your query, this would require a composite index:

indexes:
- kind: m
  properties:
  - name: a
  - name: b
  - name: c
  - name: d
  - name: e
  - name: f

(Note the 'a' must come first, but the order of the rest of the properties doesn't matter).

[susanlinsfu]

@pcostell Ahh I was not aware you had to create a composite index on every filter attribute you want to use. I created the com piste iindex and it works, but I did notice if I had an 'order' filter say on attribute c it no longer works? Is there something I am missing?

[pcostell]

By default, Cloud Datastore creates indexes on each individual property. However, Cloud Datastore requires that all queries can be answered with a single scan of an index, or via a merge join of a series of scans. So for the example of adding the order c, you have to guarantee that the order of properties in the index you need actually matches the order of the properties in your query.

So for the query SELECT b, c, d, e, f FROM m WHERE a = 10 ORDER BY c, you need an index that first hasa(to answer the filter), thenc` for the order. Thus, you need the index:

indexes:
- kind: m
  properties:
  - name: a
  - name: c
  - name: b
  - name: d
  - name: e
  - name: f

Note that all queries don't necessarily require new indexes because of the merge join. For example, if you issue the query SELECT * FROM m WHERE a = 1 AND b = 2 AND c = 3, this can use an index on (a, b, c), or any order of those. However, it can also do a merge join query on the built-in indexes since after the equality filters each index has the same ordering.

With another example, if you want to issue the query SELECT * FROM m WHERE a = 1 and b =2 ORDER BY C, you would either need:

indexes:
- kind: m
  properties:
  - name: a
  - name: b
  - name: c

Or Cloud Datastore could perform a merge join with the indexes:

indexes:
- kind: m
  properties:
  - name: a
  - name: c
- kind: m
  properties:
  - name: b
  - name: c

This would work because both indexes share the same ordering after the equality filters are applied (on c).

Figuring out which indexes a query would use is fairly complicated which is why we recommend that you use the local Cloud Datastore emulator to test out your application. When this runs, it will automatically generate an index.yaml file for any queries you run. You can run the emulator with the gcloud beta emulators datastore start command. After running your queries against the emulator, you can upload the generated index.yaml file to Cloud Datastore with the gcloud preview datastore create-indexes /path/to/index.yaml.

[pcostell]

Actually @stephenplusplus -- Our error message returned from Cloud Datastore looks like this:

{
 "error": {
  "code": 400,
  "message": "no matching index found. recommended index is:\n- kind: m\n  properties:\n  - name: a\n  - name: b\n  - name: c\n  - name: d\n",
  "status": "FAILED_PRECONDITION",
}
}

I'm going to leave this bug open -- can we get the message output to the user so that it's clear what index is needed without having to run the emulator?

[stephenplusplus]

grpc returns to us:

{ [Error] code: 9, metadata: Metadata { _internal_repr: {} } }

We have a grpc error -> http status map to get the 412 - Precondition Failed from the 9. Where did you find that error object?

[stephenplusplus]

@pcostell tried again to confirm I wasn't missing anything. I'm logging the error response object directly from the call to gRPC here and I just get the code 9 and nothing else.

[pcostell]

Hmm I'll look into why it doesn't get propagated, I'm not sure if it is a gRPC issue or a public/internal visibility issue.

[stephenplusplus]

@pcostell going to assign to you just to make filtering our issues a bit easier.

[stephenplusplus]

#1378 will track the error message improvements.