limit()

[Alex0007]

Sample code

  Address
    .scan()
    .where('active').ne(false)
    .where('user').eq(req.user.get('id'))
    .where('type').eq('pickup')
    .limit(1)

Items corresponding to this condition exist in db but doesn't returned because of .limit(1) call. Is this correct?

[clarkie]

Are you saying that you're not getting a single item back? Are you calling .exec()? Can you share your schema?

[Alex0007]

Schema

import { location } from './common'

var schema = {
  id: vogels.types.uuid(),
  active: Joi.boolean().default(true),
  user: Joi.string().guid().required(),
  type: Joi.string().valid('billing', 'pickup'),
  address: location
}

Are you saying that you're not getting a single item back?

Correct. I'm calling exec((err, data) => {}) and receive no items back. When i remove .limit(1) call i get correct items

[clarkie]

Hmm, that's interesting. Can you put together a failing test case and I can start to look into it.

Thanks

[clarkie]

I've created a couple of passing and breaking tests: https://github.com/clarkie/dynogels/compare/limit-1-bug

The results (interspersed with the console.logs of the array lengths)

91
      ✓ should return all users with age between 18 and 22
1
      ✓ should return 1 user with age between 18 and 22
24
      ✓ should return all users with email beginning with test1
0
      1) should return 1 user with email beginning with test1
3
      ✓ should return all users with age between 18 and 22 and with email beginning with test1
0
      2) should return 1 user with age between 18 and 22 and with email beginning with test1

So it looks like it's an issue with a subset of the where clauses when combined with limit

[clarkie]

In fact, in the travis logs it shows as them all failing (https://travis-ci.org/clarkie/dynogels/jobs/147401027):

9
  ✓ should return all users with age between 18 and 22
0
  1) should return 1 user with age between 18 and 22
7
  ✓ should return all users with email beginning with test1
0
  2) should return 1 user with email beginning with test1
3
  ✓ should return all users with age between 18 and 22 and with email beginning with test1
0
  3) should return 1 user with age between 18 and 22 and with email beginning with test1

[clarkie]

I've found some time to have another look at this. It looks like the scan is limiting the number of items scanned before evaluating the filter expression rather than the other way round. The AWS docs state:

In a response, DynamoDB returns all the matching results within the scope of the Limit value. For example, if you issue a Query or a Scan request with a Limit value of 6 and without a filter expression, DynamoDB returns the first six items in the table that match the specified key conditions in the request (or just the first six items in the case of a Scan with no filter). If you also supply a FilterExpression value, DynamoDB will return the items in the first six that also match the filter requirements (the number of results returned will be less than or equal to 6).

From my tests above I've logged the params sent to dynamodb and the response data:

params: { ExpressionAttributeNames: { '#email': 'email', '#age': 'age' },
  ExpressionAttributeValues: { ':email': 'test1', ':age': 18, ':age_2': 22 },
  FilterExpression: '(#age BETWEEN :age AND :age_2) AND (begins_with(#email, :email))',
  TableName: 'vogels-int-test-users' }
data: { Items:
   [ { name: 'Test 0',
       id: 'userid-12',
       acceptedTerms: false,
       email: 'test12@example.com',
       age: 22,
       roles: [Object] },
     { name: 'Test 2',
       id: 'userid-11',
       acceptedTerms: false,
       email: 'test11@example.com',
       age: 21,
       roles: [Object] },
     { name: 'Test 1',
       id: 'userid-10',
       acceptedTerms: false,
       email: 'test10@example.com',
       age: 20,
       roles: [Object] } ],
  Count: 3,
  ScannedCount: 227 }
      ✓ should return all users with age between 18 and 22 and with email beginning with test1

params: { ExpressionAttributeNames: { '#email': 'email', '#age': 'age' },
  ExpressionAttributeValues: { ':email': 'test1', ':age': 18, ':age_2': 22 },
  FilterExpression: '(#age BETWEEN :age AND :age_2) AND (begins_with(#email, :email))',
  Limit: 1,
  TableName: 'vogels-int-test-users' }
data: { Items: [],
  Count: 0,
  ScannedCount: 1,
  LastEvaluatedKey: { id: '458f4273-28b8-4a9f-80ee-7535cfa5877e' } }
      3) should return 1 user with age between 18 and 22 and with email beginning with test1

You can see in the second test that the ScannedCount is 1 which is the limit value.

Another example, this time with .limit(10):

{ ExpressionAttributeNames: { '#email': 'email', '#age': 'age' },
  ExpressionAttributeValues: { ':email': 'test1', ':age': 18, ':age_2': 22 },
  FilterExpression: '(#age BETWEEN :age AND :age_2) AND (begins_with(#email, :email))',
  Limit: 10,
  TableName: 'vogels-int-test-users' }
{ Items: [],
  Count: 0,
  ScannedCount: 10,
  LastEvaluatedKey: { id: '0dfdc4be-1289-45e9-a67f-10e82bfcd98e' } }

@Alex0007 can you check my logic and understanding of the docs before I raise an issue on https://github.com/aws/aws-sdk-js Thanks

[clarkie]

Actually. It looks like there's some confusion over what the docs say and this may be expected behaviour: http://stackoverflow.com/questions/38778506/why-dynamodb-scan-with-limit-and-filterexpression-not-return-the-items-that-matc

[Alex0007]

So the behavior is expected and nothing to fix here? C:

[clarkie]

Yeah, I think so. I'll update the docs to make it a little clearer.