# LEFT JOIN and inflated totals / LEFT JOIN 与金额重复 / LEFT JOIN と金額の重複

Experiment date: 2026-10-18.

## Run / 运行 / 実行

Extract the ZIP, then run from its directory:

```sh
python3 sql-join-cardinality.py > reproduced.json
```

The script uses only the Python standard library and an in-memory database.
It requires Python 3.9 or later syntax; the recorded run used CPython 3.12.14,
SQLite 3.53.1, macOS 27.0.1 arm64. Older Python environments were not tested.
No business database, network connection, dependency installation, or existing
database file is used. The shell redirection creates the output JSON file.

中文：解压后运行上面的命令。所有数据均为教学构造，不代表真实交易。
每笔订单的金额与每条支付的金额是两个不同层级的指标。脚本在内存中建库，
输出包含建表语句、十三条命名查询、预期结果检查及三个边界场景的 JSON。

日本語：展開後に上のコマンドを実行してください。データは説明用に作成した
もので、実際の取引ではありません。注文金額と決済記録の金額は集計単位が
異なります。メモリ内で作成したデータベースに対し、十三個の名前付きクエリ、
期待する結果の照合、三つの境界ケースを実行し、JSON を出力します。

## Files

- `sql-join-cardinality.py`: complete fixture, queries, checks, and JSON output.
- `sql-join-cardinality-results.json`: output from the recorded environment.
- `sql-join-cardinality-README.md`: this file.

The ZIP contains these three files, without the ZIP itself. Result environment
fields can differ when rerun elsewhere. SQL NULL is represented as JSON null.
The SQL schema explicitly enables foreign-key checks; order_id is not unique
in payments or order_items. All sample amounts use small integers in cents
(Chinese 分). The program does not model refunds, currency conversion, fees,
unknown amounts, or assignment of a payment to a particular item.

## Recorded results

- Four orders total 30000 cents; three settled payment records total 16000.
- Joining settled payments produces five rows. Summing the order amount
  gives 40000; summing distinct amount values instead gives 20000.
- COUNT(*) / COUNT(payment id) / COUNT(DISTINCT order id) give 5 / 3 / 4.
- Grouping by order id and summing order amounts still gives 20000 for order 1.
- Preaggregating payments per order gives four output rows and totals
  of 30000 order cents and 16000 payment cents.
- Filtering settled status in WHERE leaves orders 1 and 3. Adding OR id IS NULL
  restores order 4 but still drops order 2, which has only a pending payment.
- EXISTS returns orders 1 and 3 once each, with order amounts totaling 16000.
- Two settled payments and three items, independently joined by order id for
  order 1, give six pairs. Summed order cents: 60000; summed payment cents: 30000.
- Preaggregating both children gives order 1, order cents 10000,
  payment cents 10000, item count 3.

## Additional boundary runs

Each boundary starts with a fresh copy of the fixture:

1. Delete payment 12: at most one settled payment per order; the joined
   order total remains 30000. Settled payment total becomes 12000.
2. Delete all payments: all four orders remain; COUNT(payment id) is zero;
   preaggregated payment total is explicitly displayed as zero.
3. Delete all orders and children: raw SUM is NULL; the report's explicit
   outer COALESCE policy displays zero totals.

All thirteen baseline query results and the boundary results discussed above
are checked by the script. It raises RuntimeError if a checked result changes.
The experiment establishes results for this synthetic fixture and the recorded
SQLite runtime. It does not benchmark performance, test concurrent updates,
exercise production services, or validate other SQL engines. Logical query
steps should not be interpreted as a measured physical execution plan.

## Primary references checked on 2026-10-18

- SQLite SELECT, including LEFT JOIN and WHERE:
  https://www.sqlite.org/lang_select.html
- SQLite aggregate functions, COUNT, DISTINCT, and SUM:
  https://www.sqlite.org/lang_aggfunc.html
- SQLite EXISTS:
  https://www.sqlite.org/lang_expr.html#the_exists_operator
- SQLite foreign keys:
  https://www.sqlite.org/foreignkeys.html
- PostgreSQL table expressions, for the documented ON/WHERE comparison only;
  no PostgreSQL runtime was used:
  https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN
- Python 3.12 sqlite3 API:
  https://docs.python.org/3.12/library/sqlite3.html

## Articles

- 中文：https://gumi.ink/posts/2026-10-18-left-join-and-inflated-totals/
- 日本語：https://gumi.ink/ja/posts/2026-10-18-left-join-and-inflated-totals/
