Why does my transactionAccountingLine join return far more rows than expected?

You joined on transaction alone. transactionAccountingLine has to match transactionLine on both the transaction and the line id. With only the transaction predicate, every accounting line of a document matches every line of the same document, and nothing warns you.

Minimal reproduction

Two counts over the same two tables, differing by one predicate. One statement per call:

-- wrong: transaction only
SELECT COUNT(*) AS n
FROM transactionAccountingLine tal
JOIN transactionLine tl ON tl.transaction = tal.transaction

-- right: transaction plus line id
SELECT COUNT(*) AS n
FROM transactionAccountingLine tal
JOIN transactionLine tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline

The first returned many times as many rows as the second. The multiplier is not a constant you can look up: the loose join emits one row per accounting line per line of the same document, so it is roughly the average number of lines on your documents, and it grows with them. Both statements succeeded. Neither warned about anything.

The fix

SELECT SUM(tal.amount) AS posted
FROM transactionAccountingLine tal
JOIN transactionLine tl ON tl.transaction = tal.transaction
                       AND tl.id = tal.transactionline

The join key is a compound one. tal.transactionline holds the line id and pairs with tl.id; tal.transaction pairs with tl.transaction. Both transactionline and transaction are on transactionAccountingLine, and both id and transaction are on transactionLine.

One more predicate may be needed. transactionAccountingLine also carries an accountingbook column. In the account tested, the compound-key join returned exactly the transactionLine row count, one accounting line per line, so a single book was in play. Where multi-book accounting is enabled the compound key is no longer unique and a book predicate is needed as well. That case was not measured here.

Why this happens

A document with N lines and N accounting lines produces N by N rows instead of N. That is a cartesian product inside each document, and it is why the ratio you measure is not the typical document's line count: squaring makes the largest documents dominate. The measured multiplier sits at or above the average lines per document, and further above it the more the account's volume is concentrated in large documents.

The multiplier is a property of your data, not of NetSuite. An account of mostly single-line documents would barely fan out at all, which is exactly what makes this dangerous to test on small sample data. That reading is inference from the mechanism, since only one account was measured.

COUNT(*) is the visible symptom. SUM(amount) is the expensive one: each accounting line is counted once per line on its document, so any total built on the loose join is wrong while still looking plausible. The row ratio is not the money ratio, and no sum was measured on the loose join in this run, so do not use the row ratio to correct a total. Rerun the total on the compound key instead.

Scope: one production account on NetSuite 2026.1. Every probe behind this page ran entirely through N/query.runSuiteQL; the finding was not tested on the REST endpoint.

How to tell if you are affected

Run the two counts as two separate calls and compare them. Do not combine them into one statement: the loose join is the heavy half, the compound-key join on its own already hit the runner's transport timeout on a first attempt, and a timeout reads as "test inconclusive".

SELECT COUNT(*) AS n
FROM transactionAccountingLine tal
JOIN transactionLine tl ON tl.transaction = tal.transaction

SELECT COUNT(*) AS n
FROM transactionAccountingLine tal
JOIN transactionLine tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline

Equal counts mean the loose join happens to be harmless on your data. Any ratio above 1 means it is duplicating rows, and every query using that join is counting the same accounting line more than once.