getRecordCount builds an invalid count query when the SOQL contains a subquery

[bleaf-GH]

Hi, just wanted to call out a potential issue I've noticed with string parsing when a query contains a subquery

Summary

AsyncProcessor.getRecordCount(String query) finds the FROM and ORDER BY clauses with lastIndexOf. That returns the last match anywhere in the string, including matches inside parenthesized subqueries and string literals. When the query has either, the generated SELECT count() query is invalid and kickoff() throws before any batch or queueable starts.

Current code (core/classes/AsyncProcessor.cls)

private Integer getRecordCount(String query) {
  String lowerQuery = query.toLowerCase();
  Integer fromIndex = lowerQuery.lastIndexOf(' from ');
  Integer orderByIndex = lowerQuery.lastIndexOf('order by');

  String fromClause = (orderByIndex > -1)
      ? query.substring(fromIndex, orderByIndex)
      : query.substring(fromIndex);

  String countQuery = 'SELECT count()' + fromClause;
  return Database.countquery(countQuery);
}

Repro 1: anti-join / semi-join in WHERE

.get('SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Contact)')

lastIndexOf(' from ') matches the subquery's FROM, which produces this count query:

SELECT count() FROM Contact)
System.QueryException: unexpected token: ')'

This affects any IN (SELECT ...) / NOT IN (SELECT ...).

Repro 2: child relationship subquery with ORDER BY

.get('SELECT Id, (SELECT Id FROM Contacts ORDER BY LastName) FROM Account')

orderByIndex points at the child subquery's ORDER BY, which comes before the outer FROM. So substring(fromIndex, orderByIndex) gets start > end:

System.StringException: Ending position out of bounds: 36

Repro 3: order by inside a string literal

.get('SELECT Id FROM Account WHERE Name = \'sort order by date\'')

'order by' has no leading space and literals aren't skipped, so the clause gets cut inside the literal:

SELECT count() FROM Account WHERE Name = 'sort
System.QueryException: unexpected token: '<EOF>'

All three were reproduced with the method body above copied into anonymous Apex, so this is independent of any subclass or org data.

Expected

The count query should be built from the top-level FROM and ORDER BY only, ignoring anything inside parentheses or quoted string literals.

Suggested fix

Replace the two lastIndexOf calls with a scanner that tracks parenthesis depth and single-quoted literals (including \' escapes). It records a keyword match only at depth 0 and outside a literal. Here's some sample code:

private static final Integer SINGLE_QUOTE = '\''.charAt(0);
private static final Integer BACKSLASH = '\\'.charAt(0);
private static final Integer OPEN_PARENTHESIS = '('.charAt(0);
private static final Integer CLOSE_PARENTHESIS = ')'.charAt(0);
private static final Integer SPACE = ' '.charAt(0);

private Integer getRecordCount(String query) {
  String lowerQuery = query.toLowerCase();
  Integer fromIndex = this.lastTopLevelIndexOf(lowerQuery, ' from ');
  Integer orderByIndex = this.lastTopLevelIndexOf(lowerQuery, ' order by');

  String fromClause = (orderByIndex > -1)
      ? query.substring(fromIndex, orderByIndex)
      : query.substring(fromIndex);

  return Database.countQuery('SELECT count()' + fromClause);
}

private Integer lastTopLevelIndexOf(String lowerQuery, String keyword) {
  Integer lastIndex = -1;
  Integer depth = 0;
  Boolean isInsideLiteral = false;
  Integer queryLength = lowerQuery.length();

  for (Integer i = 0; i < queryLength; i++) {
    Integer currentCharacter = lowerQuery.charAt(i);

    if (isInsideLiteral) {
      if (currentCharacter == BACKSLASH) {
        i++; // skip escaped character, e.g. 'O\'Hare'
      } else if (currentCharacter == SINGLE_QUOTE) {
        isInsideLiteral = false;
      }
      continue;
    }

    if (currentCharacter == SINGLE_QUOTE) {
      isInsideLiteral = true;
    } else if (currentCharacter == OPEN_PARENTHESIS) {
      depth++;
    } else if (currentCharacter == CLOSE_PARENTHESIS) {
      depth--;
    } else if (
      depth == 0 &&
      currentCharacter == SPACE &&
      lowerQuery.substring(i, Math.min(i + keyword.length(), queryLength)) == keyword
    ) {
      lastIndex = i;
    }
  }
  return lastIndex;
}

We've been running essentially the above block with tests covering an anti-join in WHERE, a child subquery with ORDER BY, and a literal containing ) from ( x order by y with an escaped quote. Happy to open a PR if you're able to verify these findings + if that would be helpful. Thanks!

[jamessimone]

Feel free to open a PR - sounds like that would be a good fix. Thanks!