Why does totalResults in the SuiteQL REST response not match SELECT COUNT(*)?

Because it is not a count. totalResults reports min(rows the query returns, limit * 1000), so on any result set bigger than that cap it returns the cap and calls it the total. HTTP 200, no flag. Run SELECT COUNT(*) separately for a real number.

Minimal reproduction

Same query, same table, two different limit values:

POST /services/rest/query/v1/suiteql?limit=1&offset=0
body: {"q":"SELECT id FROM transaction"}

Envelope: count: 1, hasMore: true, offset: 0, totalResults: 1000.

POST /services/rest/query/v1/suiteql?limit=1000&offset=0
body: {"q":"SELECT id FROM transaction"}

Envelope: count: 1000, hasMore: true, offset: 0, and totalResults now matching SELECT COUNT(*) on that table exactly. The table did not change between the two calls. The reported total moved because the cap moved.

A third reading pins the arithmetic: the same table at ?limit=5, with no row-limiting clause in the SQL, reported totalResults: 5000 in each of the two calls that used it. Read the three together and the rule is limit * 1000, truncated to the size of the result set when the result set is smaller.

That second half of the rule is easy to trip over from the other direction. If the SQL itself caps the result set, totalResults reports that cap instead: the same table at ?limit=5 with FETCH NEXT 5 ROWS ONLY in the SQL reports totalResults: 5 and hasMore: false, again in each of two calls. The field is about the result set the query defines, not about the table it reads from.

The fix

Ask for the count with a query that returns a count:

SELECT COUNT(*) AS n FROM transaction

Through N/query.runSuiteQL that returns a single row whose value is the real row count of the table, uncapped, on both a mid-sized and a large table. A count returns exactly one row whatever the page size, so there is nothing for a row cap to truncate. Do not derive dataset size, page counts or completeness checks from the envelope.

One engine difference to plan for: the REST endpoint returns every value in items as a JSON string, a COUNT(*) result included, where N/query.runSuiteQL returns native types. Parse before you compare or add. This run did not send a standalone SELECT COUNT(*) through the REST endpoint, so this page does not claim what the REST engine does with one.

Why this happens

The cap tracks the page size, which is what an estimate produced by a bounded scan looks like: the engine appears to count only as far as roughly limit * 1000 rows and reports where it stopped. On the two small tables tested, both comfortably under 1000 rows, even limit=1 reported the true count, because the scan exhausted the result set before it hit the cap.

That the mechanism is a bounded scan is inference from the arithmetic. What the run establishes is the arithmetic itself, across three limit values that produced a reading (1, 5 and 1000) and three tables, and that nothing in the envelope distinguishes a capped value from a true one. Above 1000 the limit parameter is rejected outright with HTTP 400 rather than producing a larger cap, so the arithmetic was never exercised further up.

The practical shape of the trap, with illustrative round numbers rather than measured ones: a table of 40,000 rows queried at limit=10 reports totalResults: 10000. A progress bar built on it shows 100 percent at a quarter of the data, and it is wrong by a factor that changes when someone tunes the page size.

How to tell if you are affected

Send the same query twice, once at limit=1 and once at limit=1000, and compare totalResults. This is a template; substitute a table of your own that you expect to hold more than 1000 rows, and keep the SQL free of any FETCH clause so that the SQL is not what bounds the result set.

POST /services/rest/query/v1/suiteql?limit=1&offset=0
body: {"q":"SELECT id FROM YOUR_TABLE"}

POST /services/rest/query/v1/suiteql?limit=1000&offset=0
body: {"q":"SELECT id FROM YOUR_TABLE"}

If the two agree, that result set is under the cap and totalResults is the true count. If they differ, the smaller one was capped, and any code reading totalResults at that page size is under-reporting. A single reading of exactly 1000, 5000 or another round limit * 1000 value is the same signal on its own.

Scope: one production account on NetSuite 2026.1, Administrator role, three tables. The envelope arithmetic is REST only. The SELECT COUNT(*) fix was run through N/query.runSuiteQL. N/query.runSuiteQL does not return this envelope at all, so it has no totalResults to be wrong about.