DynamoDB - Query returning fewer results than Scan

Question

Really hoping someone can help me here because I'm out of ideas.

I've filled a database table with 4974 items, all of which are deliberately the exact same record i.e. all of type "car" and subtype "suv"

When I do a scan for all records, I get the full number of items back. When I query for all the records (basically by saying startTime = 1, and endTime is a time after the last post) - I'm short on the results back (when they should be the same).

I have the following code:

database.ts:

public async fetchAllMessages(): Promise<
    DocumentClient.ItemList | undefined>
{
  let scanOutput: DocumentClient.ScanOutput;
  let scanResults: DocumentClient.ItemList = [];

  this.logger.log('Database fetchAllMessages');
  try {
    const query: DocumentClient.QueryInput = {
      TableName: this.tableName(),
      ReturnConsumedCapacity: 'INDEXES',
    };
    do {
      scanOutput = await this.documentClient.**scan**(query).promise();
      if (scanOutput.Items) {
        scanResults = scanResults.concat(scanOutput.Items);
        this.logRetrievedResults(scanOutput);
        query.ExclusiveStartKey = scanOutput.LastEvaluatedKey;
      }
    } while (typeof scanOutput.LastEvaluatedKey !== 'undefined');
  
    this.logger.log(
      `Database fetchAllMessages scanResults ${JSON.stringify(scanResults)}`,
    );
    return scanResults;
  } catch (exp) {
    this.logger.error(exp);
    throw exp;
  }
}

and:

 public async searchMessages(
    type?: string,
    subType?: string,
    startTime?: number,
    endTime?: number,
  ): Promise<DocumentClient.ItemList | undefined> {
    let queryOutput: DocumentClient.QueryOutput;
    let queryResults: DocumentClient.ItemList = [];
    
    this.logger.log(
      'Database searchMessages using params - type: ' +
        JSON.stringify(type) +
        ', subType: ' +
        JSON.stringify(subType) +
        ', startTime: ' +
        JSON.stringify(startTime) +
        ', endTime: ' +
        JSON.stringify(endTime),
    );
    try {
      const keyConditionExpression = generateKeyConditionExpression(
        type,
        subType,
        startTime,
        endTime,
      );
      const expressionAttributeNames = generateExpressionAttributeNamesMap(
        type,
        subType,
        startTime,
        endTime,
      );
      const expressionAttributeValues = generateExpressionAttributeValueMap(
        type,
        subType,
        startTime,
        endTime,
      );
      const filterExpression = generateFilterExpression(startTime, endTime);
      const query: DocumentClient.QueryInput = {
        TableName: this.tableName(),
        IndexName: this.TYPE_IDX,
        ReturnConsumedCapacity: 'INDEXES',
      };
      if (keyConditionExpression.length > 0) {
        query.KeyConditionExpression = keyConditionExpression;
      }
      if (Object.keys(expressionAttributeNames).length !== 0) {
        query.ExpressionAttributeNames = expressionAttributeNames;
      }
      if (Object.keys(expressionAttributeValues).length !== 0) {
        query.ExpressionAttributeValues = expressionAttributeValues;
      }
      if ((type || subType) && filterExpression.length > 0) {
        query.FilterExpression = filterExpression;
      }
      
      do {
        queryOutput = await this.documentClient.**query**(query).promise();
        if (queryOutput.Items) {
          queryResults = queryResults.concat(queryOutput.Items);
          this.logRetrievedResults(queryOutput);
          query.ExclusiveStartKey = queryOutput.LastEvaluatedKey;
        }
      } while (typeof queryOutput.LastEvaluatedKey !== 'undefined');
      return queryResults;
    } catch (exp) {
      this.logger.error(exp);
      throw exp;
    }
  }

message-helper.ts:

edit: forgot this code block for messageType

export function messageType(type?: string, subType?: string): string {
  let typeValue = '';
  if (type && subType) {
    typeValue = type + '_' + subType;
  } else if (type && !subType) {
    typeValue = type;
  } else if (!type && subType) {
    typeValue = subType;
  }

  return typeValue;
}

export function generateFilterExpression(
  startTime?: number,
  endTime?: number,
): string {
  logger.log(
    `Message-Helper startTime: ${toString(startTime)} 
      endTime: ${toString(endTime)}`,
  );
  let filterExpression = '';
  const BOTH_EXP = '#time BETWEEN :startTimeValue AND :endTimeValue';
  const START_EXP = '#time >= :startTimeValue';
  const END_EXP = '#time <= :endTimeValue';
  
  if ((startTime || startTime == 0) && (endTime || endTime == 0)) {
    filterExpression = BOTH_EXP;
  } else if ((startTime || startTime == 0) && !(endTime || endTime == 0)) {
    filterExpression = START_EXP;
  } else if (!(startTime || startTime == 0) && (endTime || endTime == 0)) {
    filterExpression = END_EXP;
  }
  
  logger.log('Message-Helper filterExpression', filterExpression);
  return filterExpression;
}

export function generateKeyConditionExpression(
  type?: string,
  subType?: string,
  startTime?: number,
  endTime?: number,
): string {
  logger.log(
    `Message-Helper startTime: ${toString(startTime)} 
      endTime: ${toString(endTime)}`,
  );
  let keyConditionExpression = '';
  const TYPE_EXP = '#columnName = :typeValue';
  const BOTH_EXP = '#time BETWEEN :startTimeValue AND :endTimeValue';
  const START_EXP = '#time >= :startTimeValue';
  const END_EXP = '#time <= :endTimeValue';
  
  if (type || subType) {
    keyConditionExpression = TYPE_EXP;
  } else if ((startTime || startTime == 0) && (endTime || endTime == 0)) {
    keyConditionExpression = BOTH_EXP;
  } else if ((startTime || startTime == 0) && !(endTime || endTime == 0)) {
    keyConditionExpression = START_EXP;
  } else if (!(startTime || startTime == 0) && (endTime || endTime == 0)) {
    keyConditionExpression = END_EXP;
  }
  
  logger.log('Message-Helper keyConditionExpression', keyConditionExpression);
  return keyConditionExpression;
}

export function generateExpressionAttributeValueMap(
  type?: string,
  subType?: string,
  startTime?: number,
  endTime?: number,
): DocumentClient.ExpressionAttributeValueMap {
  const expressionAttributeValueMap: DocumentClient.ExpressionAttributeValueMap = {};
  
  if (type || subType) {
    expressionAttributeValueMap[':typeValue'] = messageType(type, subType);
    if ((startTime || startTime == 0) && (endTime || endTime == 0)) {
      logger.log(
        'Message-Helper generateExpressionAttributeValueMap all values',
      );
      expressionAttributeValueMap[':startTimeValue'] = startTime;
      expressionAttributeValueMap[':endTimeValue'] = endTime;
    } else if ((startTime || startTime == 0) && !(endTime || endTime == 0)) {
      logger.log('Message-Helper generateExpressionAttributeValueMap no end');
      expressionAttributeValueMap[':startTimeValue'] = startTime;
    } else if (!(startTime || startTime == 0) && (endTime || endTime == 0)) {
      logger.log('Message-Helper generateExpressionAttributeValueMap no start');
      expressionAttributeValueMap[':endTimeValue'] = endTime;
    }
  } else {
    if ((startTime || startTime == 0) && (endTime || endTime == 0)) {
      logger.log('Message-Helper generateExpressionAttributeValueMap no type');
      expressionAttributeValueMap[':startTimeValue'] = startTime;
      expressionAttributeValueMap[':endTimeValue'] = endTime;
    } else if ((startTime || startTime == 0) && !(endTime || endTime == 0)) {
      logger.log(
        'Message-Helper generateExpressionAttributeValueMap only start',
      );
      expressionAttributeValueMap[':startTimeValue'] = startTime;
    } else if (!(startTime || startTime == 0) && (endTime || endTime == 0)) {
      logger.log('Message-Helper generateExpressionAttributeValueMap only end');
      expressionAttributeValueMap[':endTimeValue'] = endTime;
    }
  }
  
  return expressionAttributeValueMap;
}

export function generateExpressionAttributeNamesMap(
  type?: string,
  subType?: string,
  startTime?: number,
  endTime?: number,
): DocumentClient.ExpressionAttributeNameMap {
  const expressionAttributeNameMap: DocumentClient.ExpressionAttributeNameMap = {};
  if (type || subType) {
    logger.log(
      'Message-Helper generateExpressionAttributeNamesMap #columnName set',
    );
    expressionAttributeNameMap['#columnName'] = 'type';
  }
  if (startTime || startTime == 0 || endTime || endTime == 0) {
    logger.log('Message-Helper generateExpressionAttributeNamesMap time set');
    expressionAttributeNameMap['#time'] = 'CreationTime';
  }
  return expressionAttributeNameMap;
}

When I do fetchAllMessages, I get back the items, across 3 indexes, as logged on CloudWatch:

[Nest][Database] {"ScannedCount":1932,"Count":1932,"ItemsLength":1932,"LastEvaluatedKey":{"id":"fe13d459-329f-44bb-b456-ab0de9a515ee"... "ConsumedCapacity": {"TableName": "analytics","CapacityUnits": 128,"Table": {"CapacityUnits": 128}}}

[Nest][Database] {"ScannedCount":1932,"Count":1932,"ItemsLength":1932,"LastEvaluatedKey":{"id":"22f1c6f0-9e52-4836-a92e-6ad3f87f7281"... "ConsumedCapacity": {"TableName": "analytics","CapacityUnits": 128,"Table": {"CapacityUnits": 128}}}

[Nest][Database] {"ScannedCount":1110,"Count":1110,"ItemsLength":1110," "ConsumedCapacity": {"TableName": "analytics","CapacityUnits": 73.5,"Table": {"CapacityUnits": 73.5}}}

Which is good!

But when I do searchMessages, searching for the type, subtype, a startTime of 1 (the beginning of time), endTime (now) - e.g. my-api-/search?type=car&subtype=suv&startTime=1&endTime=1652285721230&x-api-key=-mykey- I get the following back:

[Nest][Database] {"ScannedCount":1802,"Count":1802,"ItemsLength":1802,"LastEvaluatedKey":{"id":"5d0e2da1-f00f-4af6-a824-0684855d0a41"..."ConsumedCapacity":{"TableName":"analytics","CapacityUnits":121,"Table":{"CapacityUnits":0},"GlobalSecondaryIndexes":{"typeIdx":{"CapacityUnits":121}}}}

[Nest][Database] {"ScannedCount":1802,"Count":1802,"ItemsLength":1802,"LastEvaluatedKey":{"id":"c32ca6e6-b06b-4393-8a8d-83f6a19e7813",..."ConsumedCapacity":{"TableName":"analytics","CapacityUnits":121,"Table":{"CapacityUnits":0},"GlobalSecondaryIndexes":{"typeIdx":{"CapacityUnits":121}}}}

[Nest][Database] {"ScannedCount":1103,"Count":1103,"ItemsLength":1103,"ConsumedCapacity":{"TableName":"analytics","CapacityUnits":74,"Table":{"CapacityUnits":0},"GlobalSecondaryIndexes":{"typeIdx":{"CapacityUnits":74}}}}

The count metrics from searchMessages are adding up to ~200 less than for the fetchAllMessages version.

I don't understand why this is.

I'd really appreciate some help because not all of this is my code, so I'm having trouble understanding a lot of it - but I do know that at the time, certain factors weren't considered (for example it was me who added the do-while for the pagination edge-case)

Your help would be greatly appreciated - I'm really lost!

Edit:for yourself, jarmod,