LAST_VALUE Is Not the Last Row Until You Define the Frame

Account A has readings 10, 20 and 15 in the fixture’s approved event order. The report must retain all three event rows and attach reading 15, selected from event 3, to each one. Yet LAST_VALUE(reading) with an ordered default frame is expected to produce 10, 20 and 15 instead. The terminal row looks right while the earlier rows answer a different question. A successful SQL statement and an unchanged row count would not establish the requested report semantics.

Here, “last recorded reading” means the reading at the endpoint of each account’s retained population, ordered ascending by (event_order, event_id), inspected through a full-partition frame, with null readings respected. The population contains exactly seven synthetic events. Account B ends at a null reading; account C requires an explicitly approved tie rule. These details are requirements, not inconvenient exceptions to remove.

PostgreSQL’s window-function reference documents that LAST_VALUE reads a frame endpoint and warns about its default frame. This playbook turns that established distinction into a proposed qualification protocol: complete value vectors, selected event IDs, peer counts and nine literal checks.

No PostgreSQL execution is claimed here. The supplied SQL was statically reviewed, not run against a server. Every SQL case result below is an authored expectation, not an observation. The protocol explains how an operator can obtain actual evidence without confusing a diagnostic check, a success marker or a disposable rollback with approval of a report.

Define the last-reading report precisely

The application’s output grain is one row per retained event. It does not want a three-row account summary. Each output row must preserve its own event_id, account, order and original reading, then carry two additional fields: the selected account-endpoint reading and the identity of the event supplying it.

Before querying, approve this synthetic rule: within an account, ascending event_order determines precedence; ascending event_id resolves equal orders. The higher ID therefore follows the lower ID when orders tie. That is an application assumption for this fixture, not a general interpretation of generated identifiers. Null readings remain eligible, and the selection frame includes the account’s entire retained population.

The required endpoint pairs are (3, 15) for A, (5, NULL) for B and (7, 200) for C. Every A row carries A’s pair, every B row carries B’s pair, and every C row carries C’s pair. Keep the input event ID distinct from the selected endpoint event ID; they describe different roles.

The existing SQL foundations and window-function warnings article already identifies the LAST_VALUE frame pitfall. This qualification builds on that warning rather than offering another SQL roadmap. Its contribution is the fixed seven-event oracle and the operational decision attached to each control. There are no concurrent writers, changing populations or additional accounts in this laboratory. Any expanded population needs its own review rather than borrowing this fixture’s expected vectors.

Separate partition, ordering and frame

Treat the window specification as three independent parts of the report contract. Partitioning identifies the account-specific population available to a calculation. Ordering establishes precedence within that population. Framing identifies which of those ordered rows the current calculation may inspect. Writing one part correctly does not supply the other two by implication.

The PostgreSQL window-call syntax defines these clauses separately. For this fixture, PARTITION BY account_id separates A, B and C. ORDER BY event_order,event_id supplies the approved total order. A frame ending at UNBOUNDED FOLLOWING reaches the partition endpoint; a frame ending at CURRENT ROW does not generally do so.

For an ordered window without an explicit frame, the default is equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In that mode, the endpoint includes the current row’s peers. With the fixture’s unique order pair, each row has no other peer under that pair, so the endpoint is the current event. This is the contract-based explanation for Q1, not an observed query plan.

The output sort is not the window frame

The window-functions tutorial explains that window calculations preserve individual rows and that window order can differ from output order. The final ORDER BY account_id,event_order,event_id makes the report readable. It does not extend the frame used earlier to compute a value.

The sorting-rows documentation separately describes ordering the produced output. A reviewer should therefore inspect the OVER specification where the reading is selected, not infer it from how the displayed table happens to be sorted.

In practical review terms, ask two questions independently: “Which event supplied this attached reading?” and “Where is this output row displayed?” Reversing the presentation order can move an incorrect early-event annotation to another position on screen without correcting its source. Keep the original event identity visible so presentation cannot disguise that mismatch.

Prepare and run the nine-case PostgreSQL protocol

Use a PostgreSQL 18 psql client in a fresh session connected to a local, disposable PostgreSQL 18 database. The protocol creates temporary objects, inserts the seven declared events and evaluates every candidate against that same population. Use normal connection settings appropriate to the disposable database; the command below does not provision a server or choose a safe database on your behalf.

Use a database whose default text collation keeps A, B and C distinct and sorts them in that ascending order; C collation is suitable. Q9’s global-endpoint expectation depends on this prerequisite. Also ensure the connection allows NOTICE messages before running the unmodified driver.

Save the complete SQL as last_value_fixture.sql using UTF-8, LF line endings and exactly one trailing newline. Retain the file and compute its SHA-256 before running it. The supplied source digest is:

5ee703808f958c1954120b9a280d88d2633ca0bc8de01994619ffe97f6d203b4

That digest identifies the exact supplied source bytes, including line endings and the final newline. A changed file requires a new digest and a separately described execution. A digest confirms source identity; it is not evidence that PostgreSQL accepted the file. Preserve the full driver rather than copying only the candidate that appears most promising.

Run the supplied command and the two status-capture lines below consecutively in the same Bash session, with automatic exit-on-error disabled. Otherwise, a failed psql command could terminate the shell before its status is recorded:

psql -X -v ON_ERROR_STOP=1 -f last_value_fixture.sql > last-value-run.log 2>&1

Immediately retain the psql process status, before another command overwrites it. These two lines belong directly after the command above:

psql_status=$?
printf 'psql_exit_status=%s\n' "$psql_status" >> last-value-run.log

Also record the execution timestamp, psql client version, effective database collation and connection target without credentials. The psql command reference documents -X as disabling startup files and ON_ERROR_STOP as stopping script processing after an error. Normal completion returns zero; a script error with this setting returns three. Other client or connection failures can return other nonzero statuses. An automated wrapper must propagate the saved psql status rather than the status of the logging command.

The first statement records the server’s version string and numeric version. The subsequent guard admits only the PostgreSQL 18 version-number range. The transaction-local settings apply a five-second statement timeout and a one-second lock timeout. These are disposable-fixture statement and lock limits, not production recommendations or a timing experiment. The initial version query precedes those local timeout settings.

-- Proposed PostgreSQL 18 experiment. No execution is claimed in this brief.
\set ON_ERROR_STOP on
SELECT version(), current_setting('server_version_num') AS version_number;
BEGIN;
SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '1s';
DO $$ BEGIN
  IF current_setting('server_version_num')::integer NOT BETWEEN 180000 AND 189999 THEN
    RAISE EXCEPTION 'This fixture is qualified for a PostgreSQL 18 run';
  END IF;
END $$;
CREATE TEMP TABLE events (
  event_id integer PRIMARY KEY,
  account_id text NOT NULL,
  event_order integer NOT NULL,
  reading integer
) ON COMMIT DROP;
INSERT INTO events VALUES
  (1,'A',10,10), (2,'A',20,20), (3,'A',30,15),
  (4,'B',10,7), (5,'B',20,NULL),
  (6,'C',10,100), (7,'C',10,200);
-- The business contract in this synthetic fixture explicitly orders ties by event_id.
-- This is not an assertion that arbitrary generated IDs mean commit order.
CREATE TEMP TABLE results ON COMMIT DROP AS
SELECT ,
  last_value(reading) OVER w AS default_value,
  last_value(reading) OVER whole AS full_value,
  last_value(event_id) OVER whole AS full_event_id,
  last_value(reading) OVER prefix AS rows_prefix_value,
  count() OVER peers AS peer_frame_size,
  max(reading) OVER (PARTITION BY account_id) AS maximum_value,
  first_value(reading) OVER reverse_whole AS reverse_first,
  last_value(event_id) OVER global_whole AS unpartitioned_last_id
FROM events
WINDOW
  w AS (PARTITION BY account_id ORDER BY event_order,event_id),
  whole AS (PARTITION BY account_id ORDER BY event_order,event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  prefix AS (PARTITION BY account_id ORDER BY event_order,event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  peers AS (PARTITION BY account_id ORDER BY event_order),
  reverse_whole AS (PARTITION BY account_id ORDER BY event_order DESC,event_id DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
  global_whole AS (ORDER BY account_id,event_order,event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
CREATE TEMP TABLE ledger (
  case_id text PRIMARY KEY, observed jsonb, expected jsonb, decision text, passed boolean
) ON COMMIT DROP;
CREATE FUNCTION pg_temp.check_case(
  identity text, actual jsonb, wanted jsonb, verdict text
) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE '% observed=% expected=%', identity, actual, wanted;
  IF actual IS DISTINCT FROM wanted THEN
    RAISE EXCEPTION 'Case % failed; retain this session log', identity;
  END IF;
  INSERT INTO ledger VALUES (identity,actual,wanted,verdict,true);
END $$;
SELECT pg_temp.check_case('Q1',jsonb_agg(default_value ORDER BY event_id),
  '[10,20,15,7,null,100,200]','REPAIR_DEFAULT_FRAME') FROM results;
SELECT pg_temp.check_case('Q2',jsonb_agg(full_value ORDER BY event_id),
  '[15,15,15,null,null,200,200]','ACCEPT_FULL_FRAME_VALUES') FROM results;
SELECT pg_temp.check_case('Q3',jsonb_agg(full_event_id ORDER BY event_id),
  '[3,3,3,5,5,7,7]','ACCEPT_ROW_IDENTITY') FROM results;
SELECT pg_temp.check_case('Q4',jsonb_agg(peer_frame_size ORDER BY event_id),
  '[1,2,3,1,2,2,2]','PEER_GROUP_CONTROL') FROM results;
SELECT pg_temp.check_case('Q5',jsonb_agg(rows_prefix_value ORDER BY event_id),
  '[10,20,15,7,null,100,200]','ROWS_ALONE_IS_NOT_THE_REPAIR') FROM results;
SELECT pg_temp.check_case('Q6',jsonb_agg(maximum_value ORDER BY event_id),
  '[20,20,20,7,7,200,200]','MAX_IS_A_DIFFERENT_QUESTION') FROM results;
SELECT pg_temp.check_case('Q7',jsonb_agg(reverse_first ORDER BY event_id),
  '[15,15,15,null,null,200,200]','REVERSE_ORDER_COMPARISON') FROM results;
SELECT pg_temp.check_case('Q8',jsonb_agg(jsonb_build_array(account_id,event_order,n)
  ORDER BY account_id,event_order),'[["C",10,2]]','HOLD_WITHOUT_APPROVED_TIE_ORDER')
FROM (SELECT account_id,event_order,count(*) AS n FROM events
  GROUP BY account_id,event_order HAVING count(*)>1) AS ambiguous;
SELECT pg_temp.check_case('Q9',jsonb_agg(unpartitioned_last_id ORDER BY event_id),
  '[7,7,7,7,7,7,7]','REJECT_CROSS_ACCOUNT_RESULT') FROM results;
TABLE ledger;
SELECT event_id,account_id,event_order,reading,default_value,full_value,full_event_id
FROM results ORDER BY account_id,event_order,event_id;
DO $$ BEGIN
  IF (SELECT count(*) FROM ledger) <> 9 OR EXISTS(SELECT 1 FROM ledger WHERE NOT passed)
     OR (SELECT count(*) FROM results) <> 7 THEN
    RAISE EXCEPTION 'Incomplete case or event inventory';
  END IF;
END $$;
SELECT 'PASS_9_CASES' AS result;
ROLLBACK;

The named windows keep the comparison readable: w uses the default frame, whole includes the partition, and prefix deliberately stops at the current event. peers removes the ID tie-breaker only for counting frame membership. The reverse-order and unpartitioned windows are controls, not additional source populations.

Each call to check_case compares an actual JSON value with a literal expectation. The routine issues a notice before checking equality, raises on mismatch, and inserts a ledger row only after a matching comparison. Its decision argument is an authored label; the routine does not independently discover or approve a business policy. In particular, a matching Q9 records a successfully detected wrong-account result, not an accepted report.

A proposed script is not a recorded database run

This article’s execution status is PROPOSED / NOT EXECUTED ON POSTGRESQL. The source contains nine checks and the string PASS_9_CASES, but no server log or observed result accompanies it. Static inspection, source hashing and manual derivation of vectors do not establish database behavior.

The cited PostgreSQL pages use documentation version 18 and were accessed on October 16, 2026. That access date is neither a page-publication claim nor an execution timestamp. An operator’s evidence must separately identify the actual server, file digest, run time, retained output and process status. Populate observations only from an identified run’s retained output. A failed run can support partial observations, but not a nine-case success claim. Even a successful run covers this fixed laboratory, not universal production correctness.

Read the default-frame result by event identity

Q1’s authored expected vector is [10,20,15,7,null,100,200], serialized in ascending input event_id. Position one describes the annotation on event 1, position two describes event 2, and so on. It is not a list of one selected reading per account. That distinction keeps the test aligned with the seven-row output grain.

For A, the default-frame endpoint advances through events 1, 2 and 3. The expected annotations are therefore 10, 20 and 15. Only event 3 agrees with the required account-endpoint reading. Checking that terminal row alone would miss the disagreement on events 1 and 2.

For B, the expected annotations are 7 and null. The required full-partition answer is null on both rows because event 5 supplies B’s endpoint reading. For C, the approved ID tie order makes event 6 precede event 7. Q1 therefore expects 100 on event 6 and 200 on event 7, although the required attached reading is 200 on both.

This reasoning depends on the complete order pair. The default frame does not universally mean “only through the current physical row”; peers can extend its endpoint. Here, adding the unique event ID removes that peer ambiguity while leaving the frame too short for earlier events. Deterministic ordering and a correct endpoint are different accomplishments.

Record REPAIR_DEFAULT_FRAME for this candidate under the declared account-report contract. If Q1 matches during an actual run, its diagnostic assertion has passed: the fixture has reproduced the expected default-frame behavior. The flawed implementation has not been approved. The acceptance question remains whether every retained event carries its account’s approved endpoint pair.

The useful comparison is event by event, with source columns still attached. An export showing only the distinct annotations would discard their positions and obscure which rows need repair. Likewise, a screenshot centered on the final event of each account would present only the places where the default candidate happens to agree. Preserve the whole vector and its ID order together.

Extend the frame to the partition endpoint

Q2 changes the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING while retaining account partitioning and the same approved ascending order. The authored expected vector is [15,15,15,null,null,200,200]. Earlier rows now inspect the same account endpoint as the terminal row, rather than stopping at their own positions.

Under this fixed contract, every A row selects event 3, every B row selects event 5, and every C row selects event 7. The frame expansion changes neither the seven-event source population nor the required seven-row output grain. It changes the set of account rows available to the endpoint calculation at each output position.

The repair is the explicit combination, not a magic property of the word ROWS: account partition, approved ordering, both unbounded boundaries and respected nulls. The frame-boundary definitions establish what the unbounded endpoints mean. Q2 is the supplied fixture’s literal consequence of choosing them.

Verify the selected row as well as its value

Q3 independently expects selected IDs [3,3,3,5,5,7,7]. The query applies the same named whole window to reading and event_id. This makes the intended relationship inspectable: both annotations describe the endpoint of the same ordered account frame.

Reading equality alone cannot establish row identity. Two eligible events can carry an equal reading while representing different source records. This fixture does not add duplicate-valued rows to demonstrate that separate scenario; it instead requires endpoint identity as part of the existing seven-row contract. Preserve that distinction rather than expanding the experiment silently.

Use ACCEPT_FULL_FRAME_VALUES only together with matching Q3 identity evidence and the approved tie/null policy. The label is conditional until an actual run exists. Do not reduce acceptance to “Q2 equals Q7” or “each account has one repeated value.” Those observations could still agree on an incorrect definition. The literal endpoint pairs, not agreement between candidate expressions, remain the oracle.

Show why adding ROWS alone is not enough

Q5 deliberately writes an explicit frame but chooses the wrong endpoint for this report: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Its authored expected vector is [10,20,15,7,null,100,200], matching Q1 under the fixture’s complete order. The presence of a frame clause does not itself demonstrate that the account endpoint is available.

Separate the mode from the boundaries when reviewing a patch. ROWS tells PostgreSQL how to interpret the frame in row terms. UNBOUNDED PRECEDING selects its starting boundary. CURRENT ROW selects its ending boundary. Reading the first keyword and overlooking the ending phrase would approve a syntactic change without repairing the requirement.

The window-frame syntax reference distinguishes the current row in ROWS mode from peer-aware boundaries in RANGE and GROUPS. For this total order, the distinction does not rescue Q5: each current row is also its default-frame peer endpoint. Q1 and Q5 therefore share a seven-position expectation for a stated reason, not by an asserted universal equivalence between frame modes.

The contrast with time-bin boundaries versus labels is useful only at the level of declaring membership precisely. That article qualifies different time-bin definitions. Here there is no resampling or time arithmetic: all seven SQL events are already selected, and the frame determines which account events an endpoint calculation can inspect.

When reviewing a proposed repair, read the complete named window aloud as a requirement: “Within this account, in this approved order, from the partition’s beginning through its endpoint, retaining null readings.” Q5 fails that sentence at its ending boundary. Its ledger label, ROWS_ALONE_IS_NOT_THE_REPAIR, is a diagnostic explanation, not an alternative acceptance state. Keep it in the test inventory so a future edit cannot pass merely because it introduces the preferred-looking frame keyword.

Inspect peer groups without inventing a tie winner

Q4 intentionally orders its counting window by event_order alone. Its authored expected frame-size vector is [1,2,3,1,2,2,2]. The two C events are peers under this window because both have order 10. The default frame reaches through that peer group, so both C positions are expected to count two rows.

The column name peer_frame_size deserves care: it counts the entire default frame, not just the current peer group. A’s second event is expected to count two because its frame includes A’s first two events, even though those events have different orders. A’s third event counts three for the same cumulative reason. C’s frame and peer group coincide only because C contains one tied order group.

This is a membership control, not another candidate reading selector. It removes event_id from one named window to expose the effect of peer equality, while the other windows retain their separately declared definitions. Do not infer that every calculation in the SELECT shares the same peers simply because it reads the same temporary table.

Peer membership is deterministic without a chosen winner

For the declared C population, membership under equal event_order values is clear: events 6 and 7 belong together. The count does not need to decide which one is later within that equality. A reading selector over an incompletely ordered endpoint would need a separate tie policy.

The tutorial’s tied-order discussion explicitly illustrates that tied rows can have an unspecified relative order. Repeated local appearances of the same peer ordering would not turn an incomplete ordering contract into a guaranteed tie rule.

Consequently, Q4 must not be rewritten as an assertion that C’s LAST_VALUE under ORDER BY event_order always chooses 100 or always chooses 200. Its authored assertion is the frame-size vector only. Preserve that limited claim: the diagnostic can expose shared frame membership without inventing an application-approved winning event.

Require an approved order for tied events

Q8 examines the source population for repeated (account_id, event_order) groups. Its authored expected JSON is [["C",10,2]]: account C has two events at order 10. The query does not select a winner, inspect readings or discard either event. It identifies exactly where the primary ordering field is insufficient to distinguish precedence.

The supplied contract resolves that issue by approving ascending event_id as the secondary key. With that approval, C’s full-frame endpoint is event 7. Without it, record HOLD_WITHOUT_APPROVED_TIE_ORDER. A technically available unique identifier is not automatically a meaningful business precedence rule.

Ask the domain owner to approve the direction as well as the field. “Use event ID for ties” still leaves a material choice between ascending and descending precedence. For this fixture, the approved choice is ascending, so ID 7 follows ID 6. The qualification does not infer chronology from the identifier or derive a different ordering from the readings.

Q8’s ledger label records the condition that would require a hold when that approval is absent. It does not mean the SQL dynamically reads an approval register. Nor does a passing Q8 mean the fixture is unresolved: the fixture explicitly supplies the approval in prose and source comments. The query supplies the tie inventory; the application contract supplies the decision about it.

The related discussion of source-row identity in PostgreSQL writes concerns a different kind of ambiguity. This report performs no write repair or multi-match selection. Its connection is narrower: a result should identify the source row that the requirement authorizes, not merely an available row that produces a plausible number.

Do not edit the expected vector after seeing an inconvenient tied outcome. Preserve the approved ordering and investigate the candidate definition. If the application changes its tie policy, that creates a revised contract, source revision and qualification record. It does not retroactively validate the earlier query under the original seven-event oracle.

Keep the terminal null as part of the answer

Account B consists of event 4 with reading 7 and event 5 with reading NULL. Its population is retained intact; its partition is B; its order is ascending (event_order, event_id); its selection frame is the whole partition. Under respected-null behavior, the endpoint pair is therefore (5, NULL) and both B output rows must carry that pair.

The PostgreSQL null-behavior documentation states that these window functions behave as RESPECT NULLS; PostgreSQL 18 does not implement the standard’s IGNORE NULLS option. Treat that as documented behavior, not permission to insert unsupported syntax or assume that an endpoint null will be skipped.

A null selected reading does not imply that no endpoint event exists. Q3 identifies event 5 explicitly. The value and identity checks together distinguish “the selected event has a null payload” from an unproven source-selection failure. A report reviewer needs both fields to interpret B correctly.

The JSON expectations retain null positions. PostgreSQL’s aggregate-function reference documents that jsonb_agg includes null inputs. In Q2, the fourth and fifth array entries are JSON null because both B annotations must preserve that answer. Do not remove those entries when preparing an export or comparing vector lengths. They correspond to retained event rows, not absent records.

Latest non-null is a separate requirement

A product request for the most recent non-null observation changes eligibility: it asks for a different selected population within each account. It must define its own ordering, frame, behavior when no eligible reading exists, and mapping back to retained output events. Those choices require a separate query and acceptance policy.

This playbook does not supply that replacement. Filtering event 5 away or substituting 7 would alter the current requirement, which deliberately treats a terminal null as the answer. In the present fixture, B is the control that prevents a reassuring non-null value from silently replacing the authorized endpoint. Preserve the null and its source ID even when the report’s display layer needs a human-readable explanation of what that null means.

Compare latest, largest and reverse-first questions

Q6 expects maximum readings [20,20,20,7,7,200,200]. That is a different question from selecting each account’s endpoint under the approved event order and full frame. A’s largest reading is 20, supplied before its terminal reading 15. B’s maximum is 7 although its terminal event carries NULL. C’s maximum happens to equal its endpoint reading, which cannot validate the substitution for A and B.

The MAX aggregate definition specifies the maximum non-null input value. It does not identify the event at the end of a separate application ordering. Q6 therefore protects against replacing an endpoint query with an aggregate that answers a numerically different requirement.

Q7 expects [15,15,15,null,null,200,200] from FIRST_VALUE over the full partition with both ordering keys reversed. Its first event under descending (event_order, event_id) is the endpoint under the approved ascending pair. The window-value function definitions support this endpoint comparison. Respected nulls remain part of it, so B still yields null.

This is a bounded comparison inside the same seven-row experiment. It is not authorization to reverse only one key, choose a new business order, shorten the frame or remove null readings. Q7 also checks readings, not a separately computed reverse-first event ID. Q3 remains the direct identity assertion for the accepted whole window.

Agreement between Q2 and Q7 is useful corroboration only after both are compared with the literal oracle. Two expressions can share the same mistaken assumption. Here the account-specific endpoint pairs were declared before either candidate, allowing the reviewer to distinguish an equivalent expression from a conveniently matching number. Keep Q6 and Q7 as controls with different purposes rather than treating every matching assertion as a recommended implementation.

Keep account boundaries visible in the result

Q9 deliberately omits PARTITION BY account_id. Its window orders the complete seven-event population by (account_id, event_order, event_id) and uses a full frame. The authored expected selected-ID vector is [7,7,7,7,7,7,7]. This is a global endpoint calculation, not the required account-specific one; the reading null policy has not repaired the missing partition boundary.

The partition-clause definition states that without partitioning, all query rows form one partition. Q9 makes that difference visible through source identity. With the stated ordering and collation prerequisite, event 7 is the expected global endpoint. Attaching its ID to A and B violates the report contract regardless of whether a reading somewhere happens to match.

Record REJECT_CROSS_ACCOUNT_RESULT for this candidate. A passing Q9 assertion would mean the negative control produced the authored wrong-account vector. It would not mean account isolation has been demonstrated by the candidate. The decision concerns the calculation, not whether the SELECT ran without an error.

For the accepted candidate, preserve input IDs 1 through 7 exactly once and inspect the printed source columns alongside full_value and full_event_id. The source primary key prevents duplicate input IDs, and the supplied results query selects directly from the seven-row temporary table without a filtering or grouping step. Its final inventory assertion requires seven result rows.

That inventory count is necessary evidence within this driver, not a universal proof of identity preservation. A different query could retain seven rows while repeating one source ID and losing another. When qualifying a revised report, compare actual event membership and multiplicity with the retained source manifest, not just the total.

Keep both account labels and endpoint IDs in the review export. An ID-only vector is compact and testable because this fixture declares the ID-to-account mapping; an operational reviewer should not need to reconstruct that mapping from memory. The desired annotations are A to event 3, B to event 5 and C to event 7, repeated across the appropriate source rows. No account may borrow another account’s endpoint simply because the query uses a full frame.

Reconcile the nine expected cases with an actual run

The inventory below records authored expectations, not observations. Vectors use ascending input event ID except Q8, whose tuple describes a grouped ambiguity. “Not executed” is an evidence state, not a failed database assertion. Populate observations only from the retained output of the identified source revision.

During execution, the routine issues each actual/expected pair before its comparison. PostgreSQL’s message and exception documentation explains that client settings control notice delivery and an unhandled exception normally aborts the transaction. The client message threshold defaults to NOTICE, but the supplied SQL does not set client_min_messages. Retain a connection configuration that delivers NOTICE messages. Missing notice output is missing diagnostic evidence, not a passed comparison.

Retain failure logs before rolling back the fixture

An exception aborts this disposable transaction, and ON_ERROR_STOP stops the script. Subsequent ledger output and the explicit final ROLLBACK may never be reached. Earlier temporary ledger inserts are not a durable failure record. On normal completion, the final rollback discards the transaction’s changes, including the temporary objects created inside it. Retained client output is the evidence that remains outside it.

Inspect the exit status, all nine cases, the seven-row report and completion through rollback. A PASS_9_CASES line cannot establish success if the command subsequently fails. Keep failed and successful attempts separately identified rather than overwriting the only failure log.

For communicating analytical definitions, explain the approved endpoint rule alongside the evidence status. A reader should be able to distinguish a proposed oracle from an actual run without interpreting SQL comments or guessing what a PASS label proves.

Rebuild affected reports and assign ownership

When a report is found to use the default-frame candidate for this account-endpoint requirement, preserve its query revision and the exact retained source population before replacing its output. Keep the approved ordering and null policy with that evidence. The correction is not “make every value look like the final displayed row”; it is to recompute the declared endpoint from authorized source events.

Build a new report revision from the retained source snapshot rather than inferring replacement values from old annotations. An old value column might describe current-frame endpoints, maxima or a global endpoint. Without source identities, it may not contain enough information to recover the requested selection reliably. Do not overwrite that ambiguity with a plausible repeated number.

Assign the query owner responsibility for the account partition, complete order, full frame and endpoint-ID projection. Assign the domain owner responsibility for approving what equal event orders mean and whether a terminal null is a valid answer. Assign the report owner responsibility for preserving the seven-row evidence, clearly labeling its execution status and explaining the annotation to consumers.

For this fixture, approval requires the authored Q2 and Q3 vectors together, the declared C tie rule, B’s retained terminal null and unchanged event membership. The diagnostic controls explain why the rejected candidates are rejected. They are not alternative routes to acceptance merely because their assertions can pass.

For a real report revision, retain the source manifest, old and new query text, approved contract, actual qualification output and reviewer decision as one review record. Identify any differences between that report’s population and this seven-row laboratory. A successful run here would demonstrate this protocol’s checks on this population; it would not establish complete coverage of another application.

Only after result correctness is established should a separate discussion of correctness before query optimization enter the review. This experiment makes no speed claim, and none of its values justify a performance recommendation.

Keep unresolved policy separate from a known query defect. REPAIR_DEFAULT_FRAME names the endpoint correction. HOLD_WITHOUT_APPROVED_TIE_ORDER names missing authorization. REJECT_CROSS_ACCOUNT_RESULT names a boundary violation. ACCEPT_FULL_FRAME_VALUES requires matching row identity and actual evidence. Those decisions let each owner address the specific gap without treating a clean SQL exit as a substitute for a defined report. No production rollback or report rebuild has been performed by presenting this proposed protocol.

Strengthen practical SQL review skills

A useful review exercise is to explain the selected endpoint for every retained event without starting from a function name. State the population, account partition, approved order, complete frame and null rule, then identify the supplying event. Compare that reasoning with the literal vectors before consulting any actual output. This keeps the requirement independent of the implementation being tested.

For structured study of relevant foundations, consider Database Administrator Essentials. Its published coverage includes database design, advanced SQL techniques and SQL query optimization. These are relevant foundations for reviewing analytical queries.

The practical skill is knowing what the evidence permits you to say. A defined oracle permits an expected result. An inspected database run permits a bounded observation. An approved report additionally requires the correct source identities, account boundaries and application policy. Keep those claims separate when reviewing or publishing an analytical result.