Using cache in findAndCount and getManyAndCount returns 0 as count

[Tallyrald]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[x] @next
[x] 0.2.18

Steps to reproduce or a small repository showing the problem:

Consider the following code (the function is inside an abstract class):

static async GetBasicList(userRepo: Repository<User>, limit: number, page: number): Promise<[User[], number]> {
	return userRepo.findAndCount({
		cache: {
			id: 'userBasicList',
			milliseconds: 100000,
		},
		order: {
			id: 'DESC',
		},
		relations: ['profile'],
		skip: page * limit,
		take: limit,
	});
}

Without the cache, the result is fully correct (returns the correct amount of users in the right order, loads the profile relation, correctly paginates and gives back the number of total hits). However when the above is run with caching (as it is specified above) the full result count returns 0.
For example if I have 10 users in my database and I run the query with limit set to 5 and page set to 0, I get 5 users and a count of 0 instead of 10.

The User entity is fairly complex with 11 relations and some other fields, however nothing is eagerly loaded. Some relations have cascade set to true (like the profile the query uses), but removing cascades does not help.

Caching is enabled in ormconfig.js and this issue is present with database cache and ioredis as well.

Also interesting to observe that in the cache, there are 2 queries inserted. One with the 'userBasicList' identifier and one with a NULL identifier. The latter contains a query that starts like this:

SELECT DISTINCT `distinctAlias`.`User_id` as "ids_User_id", `distinctAlias`.`User_id` FROM (SELECT...

Tried using @next, but the same thing happened.

EDIT:
Forgot to mention but the exact same pattern applies when a queryBuilder.getManyAndCount() is used.

[Tallyrald]

It seems the problem originates from somewhere in SelectQueryBuilder's getManyAndCount() method.
Looking at the logs whenever the cache option is omitted (that is cache is disabled on the query) 3 queries are run.

1. The one mentioned above which fetches the required user ids (I believe it's this part here)
   

   typeorm/src/query-builder/SelectQueryBuilder.ts

   Lines 1794 to 1804 in 0d47ccd

   	rawResults = await new SelectQueryBuilder(this.connection, queryRunner)
   	.select(`DISTINCT ${querySelects.join(", ")}`)
   	.addSelect(selects)
   	.from(`(${this.clone().orderBy().getQuery()})`, "distinctAlias")
   	.offset(this.expressionMap.skip)
   	.limit(this.expressionMap.take)
   	.orderBy(orderBys)
   	.cache(this.expressionMap.cache ? this.expressionMap.cache : this.expressionMap.cacheId, this.expressionMap.cacheDuration)
   	.setParameters(this.getParameters())
   	.setNativeParameters(this.expressionMap.nativeParameters)
   	.getRawMany();

2. The data fetching query generated by the same method using the loadRawResults method
   

   typeorm/src/query-builder/SelectQueryBuilder.ts

   Lines 1806 to 1835 in 0d47ccd

   	if (rawResults.length > 0) {
   	let condition = "";
   	const parameters: ObjectLiteral = {};
   	if (metadata.hasMultiplePrimaryKeys) {
   	condition = rawResults.map((result, index) => {
   	return metadata.primaryColumns.map(primaryColumn => {
   	parameters[`ids_${index}_${primaryColumn.databaseName}`] = result[`ids_${mainAliasName}_${primaryColumn.databaseName}`];
   	return `${mainAliasName}.${primaryColumn.propertyPath}=:ids_${index}_${primaryColumn.databaseName}`;
   	}).join(" AND ");
   	}).join(" OR ");
   	} else {
   	const ids = rawResults.map(result => result["ids_" + DriverUtils.buildColumnAlias(this.connection.driver, mainAliasName, metadata.primaryColumns[0].databaseName)]);
   	const areAllNumbers = ids.every((id: any) => typeof id === "number");
   	if (areAllNumbers) {
   	// fixes #190. if all numbers then its safe to perform query without parameter
   	condition = `${mainAliasName}.${metadata.primaryColumns[0].propertyPath} IN (${ids.join(", ")})`;
   	} else {
   	parameters["ids"] = ids;
   	condition = mainAliasName + "." + metadata.primaryColumns[0].propertyPath + " IN (:...ids)";
   	}
   	}
   	rawResults = await this.clone()
   	.mergeExpressionMap({ extraAppendedAndWhereCondition: condition })
   	.setParameters(parameters)
   	.loadRawResults(queryRunner);
   	}
   	} else {
   	rawResults = await this.loadRawResults(queryRunner);
   	}

3. The entity counting query
   

   typeorm/src/query-builder/SelectQueryBuilder.ts

   Lines 1731 to 1743 in 0d47ccd

   	const results = await this.clone()
   	.orderBy()
   	.groupBy()
   	.offset(undefined)
   	.limit(undefined)
   	.skip(undefined)
   	.take(undefined)
   	.select(countSql)
   	.setOption("disable-global-order")
   	.loadRawResults(queryRunner);
   	if (!results || !results[0] || !results[0]["cnt"])
   	return 0;

However, with caching enabled on the query, the counting query is missing from the logs so I assume it does not get executed for some reason and therefore !results[0]["cnt"] (see 3rd linked code line 1742) returns true since it doesn't exist. Even better in my debugging session the result variable contained raw text results from the second query instead of the counting query. I haven't been able to narrow it down any further yet.

[mikelduffy]

Faced a similar issue. This is happening in the getFromCache function when being used with a cache id (userBasicList in your case). The cache stores the JSON string of a single query/result set using the cache id as the key. So when used with findAndCount the cache will always return the results for the first query (raw elements) rather than the second query (total element count).

[Ponjimon]

Any update on this?

[xmflsct]

Same, using postgres and redis.