Table of contents
Open Table of contents
A price that ought to be a single value
This source walkthrough is for engineers learning TiDB’s execution engine. Suppose a query expects one price from a nested query:
SELECT (SELECT price FROM inventory WHERE sku = 'A-42') AS price;For one matching row, the inner query can provide the price. For no matching rows, the scalar result is NULL. But if two rows match, the database must not quietly choose one. One possible TiDB plan places a MaxOneRow guard over the inner query. The optimizer can sometimes rewrite or evaluate scalar subqueries differently, so this SQL is an illustration of the semantics, not a promise that every EXPLAIN shows this exact operator.
We follow four code units in pingcap/tidb at commit da76b94: LogicalMaxOneRow, PhysicalMaxOneRow, executorBuilder.buildMaxOneRow, and MaxOneRowExec. This is a snapshot of upstream master; it is not a claim about all released versions or TiKV’s storage implementation.
From a planning rule to a runtime check
scalar subquery's child plan
|
v
LogicalMaxOneRow describes at-most-one-row semantics
|
v
PhysicalMaxOneRow plans for detecting a second row
|
v
executorBuilder.buildMaxOneRow()
|
v
MaxOneRowExec asks child, rejects >1; makes NULL for 0
|
v
parent expressionLogicalMaxOneRow.Schema clones its child’s columns and clears their NOT NULL flags: even if inventory.price cannot be null, zero rows produce a null scalar result. Its PredicatePushDown keeps predicates above this boundary instead of pushing an outside predicate into the child, where it could hide an extra row before the guard counts it.
The conversion in ExhaustPhysicalPlans4LogicalMaxOneRow requests a child property with ExpectedCnt: 2. To disprove “at most one,” two rows suffice. The rule also declines physical properties with sort items or a TiFlash property; that is a planner constraint, not a setting you should toggle to make subqueries work.
executorBuilder.buildMaxOneRow first builds the child executor, then wraps it in MaxOneRowExec. It sets that executor’s initial chunk capacity and maximum chunk size to 2. These are local builder values, not a user-facing MaxOneRow tuning parameter. ExpectedCnt: 2 and the chunk settings express the same goal at different layers: detect a second row without asking the child to optimize for a huge result.
What Next does with each outcome
MaxOneRowExec.Open opens its child and resets evaluated for a fresh execution. Its Next clears the caller’s result chunk, marks itself evaluated, then pulls from the child. Follow the rows, not just the error name:
| Child’s first chunk | What the guard does | Result |
|---|---|---|
| 0 rows | Appends one NULL for each output column | One all-null row |
| 2 or more rows | Returns ErrSubqueryMoreThan1Row | Error |
| 1 row | Pulls one more child chunk | Keeps the first row only if the next chunk is empty; otherwise error |
The extra pull matters. A child might return the first row in one chunk and the second in another; checking only the first chunk would miss the duplicate. Once the guard has emitted its single result, another call to Next returns an empty chunk via evaluated rather than repeating the value. The first chunk’s size can depend on the caller and child: the code does not guarantee exactly two storage rows are read, nor that every child fetch fits into one chunk.
child chunks: [ price=10 ] [ price=11 ]
| |
first pull: keep price=10 |
second pull: +-- nonempty -> error
child chunks: [ price=10 ] [ empty ]
| |
first pull: keep price=10 |
second pull: +-- empty -> return price=10This is why the guard is neither LIMIT 1 nor a uniqueness constraint. LIMIT 1 would silently discard a second matching row; a unique index could prevent duplicate sku values in this particular example but cannot replace the general scalar-subquery rule. The guard enforces the contract of a scalar at runtime, across chunk boundaries, while the planner’s schema acknowledges the zero-row case. Other plans can enforce the same SQL semantics through different paths.