The number was right and the label came from another query

# Self-authored for the EML case corpus (no external origin). A report shows
# four regions and their totals. The names come from one query and the totals
# from another, joined by row position. What each region is shown as is
# computed below.
#
# Splitting the two queries was reasonable and it was done for a reason. The
# names live in a reference table that changes once a quarter and is cached;
# the totals come from a warehouse scan that takes eleven seconds. Running them
# separately lets the page render the labels immediately and fill the numbers
# when they arrive, which is a real improvement a user can feel.
#
# Both queries are correct. Each returns exactly the rows it should, with
# exactly the right values, and each has an ORDER BY - which is more than most
# queries have. They order by different columns, because each was written to be
# read on its own: the names alphabetically, the totals largest first.
#
# Joining by position is joining on a column neither query returns.

# [region, true total]
[["north", 960], ["south", 175], ["east", 412], ["west", 388]] => truth

# query A: names, ORDER BY name
["east", "north", "south", "west"] => names_by_name

# query B: totals, ORDER BY total DESC
[960, 412, 388, 175] => totals_by_value

"query A returns names ordered by name  : east, north, south, west" ^0
"query B returns totals ordered by value: 960, 412, 388, 175" ^0
"the report pairs them by row position" ^0
"" ^0

"region   shown   true    correct" ^0
0 => mislabelled
0 => shown_sum
0 => true_sum
0 => idx
for n in names_by_name:
    totals_by_value[idx] => shown
    shown_sum + shown => shown_sum
    0 => actual
    for t in truth:
        if t[0] == n:
            t[1] => actual
    true_sum + actual => true_sum
    if shown == actual:
        "  " + n + "     " + str(shown) + "     " + str(actual) + "     yes" ^0
    else:
        mislabelled + 1 => mislabelled
        "  " + n + "     " + str(shown) + "     " + str(actual) + "     NO" ^0
    idx + 1 => idx
"" ^0

"  rows with the wrong label : " + str(mislabelled) + " of " + str(idx) ^0
"" ^0

# ---- what reconciles anyway ----
#
# Position-joining is a permutation. A permutation preserves every quantity
# that does not depend on which value sits beside which name.

"quantities that survive a permutation" ^0
"  sum of the totals, as shown : " + str(shown_sum) ^0
"  sum of the totals, true     : " + str(true_sum) ^0
"  difference                  : " + str(shown_sum - true_sum) ^0
"  row count, as shown         : " + str(idx) ^0
"  largest value on the page   : " + str(totals_by_value[0]) ^0
"  largest value in truth      : 960" ^0
"" ^0
"  the monthly reconciliation compares the total and the row count" ^0
"  both match exactly, and they would match under any permutation" ^0
"" ^0

# ---- what does not survive ----

"questions the report is now wrong about" ^0
"  what is the total across regions   right" ^0
"  how many regions are there         right" ^0
"  what is the largest regional total right" ^0
"  WHICH region is largest            wrong" ^0
"  is north above target              wrong" ^0
"  every question naming a region is wrong and every aggregate is right" ^0
"" ^0

# ---- when the two orderings coincide ----
#
# If the alphabetical order happens to match the descending-value order, the
# position join is correct by accident. That is one arrangement out of many,
# and a small test fixture is unusually likely to land on it.

"a fixture where the two orderings agree" ^0
[["alpha", 900], ["bravo", 600], ["charlie", 300], ["delta", 100]] => lucky
0 => lucky_wrong
0 => lidx
for lk in lucky:
    lucky[lidx][1] => shown
    if shown == lk[1]:
        "  " + lk[0] + " : shown " + str(shown) + ", true " + str(lk[1]) + ", correct" ^0
    else:
        lucky_wrong + 1 => lucky_wrong
        "  " + lk[0] + " : shown " + str(shown) + ", true " + str(lk[1]) + ", WRONG" ^0
    lidx + 1 => lidx
"  mislabelled rows : " + str(lucky_wrong) + " of " + str(lidx) ^0
"  names ascending and values descending happen to agree here" ^0
"  a fixture written alphabetically with decreasing values passes" ^0
"" ^0

# ---- the control ----
#
# Each query on its own. Both return the right rows with the right values in a
# defined order, and running either one by hand reproduces the report's data
# exactly.

"control - each query judged alone" ^0
"  query A: returns 4 region names, ordered by name   : correct" ^0
"  query B: returns 4 totals, ordered by value desc   : correct" ^0
"  incorrect queries : 0 of 2" ^0
"  and each has an explicit ORDER BY, which is more than most queries have" ^0
"" ^0
"  the defect is in the join, and the join is a line of presentation code" ^0
"  that reads neither query's ORDER BY" ^0
"" ^0

# ---- the null control ----
#
# The same two queries, both ordered by the same column. Position becomes a
# valid join key because both sides are the same permutation. Nothing else
# changes.

["north", "east", "west", "south"] => names_by_value

"null control - the same position join with both sides ordered by value" ^0
0 => nc_wrong
0 => nidx
for n in names_by_value:
    totals_by_value[nidx] => shown
    0 => actual
    for t in truth:
        if t[0] == n:
            t[1] => actual
    if shown == actual:
        "  " + n + " : " + str(shown) + ", correct" ^0
    else:
        nc_wrong + 1 => nc_wrong
        "  " + n + " : " + str(shown) + ", wrong, true is " + str(actual) ^0
    nidx + 1 => nidx
"  mislabelled rows : " + str(nc_wrong) + " of " + str(nidx) ^0
"  same join, same code, same two queries" ^0
"  position is a valid key exactly when both sides share an ordering" ^0
"" ^0

# ---- the rule ----

"joining two result sets by position" ^0
"  each query correct on its own       necessary, not sufficient" ^0
"  each query has an ORDER BY          necessary, not sufficient" ^0
"  the two ORDER BY clauses agree      this is the condition" ^0
"  and it is not stated in either query, or in the join" ^0
"" ^0
"the aggregate checks cannot find it, because a permutation preserves them" ^0
"the check that finds it is joining on a key, which removes the question" ^0
"" ^0

"Splitting the queries lets the page render labels in milliseconds instead of" ^0
"eleven seconds, and both queries are correct with an explicit ORDER BY each." ^0
"They order by different columns because each was written to be read alone." ^0
"Pairing them by row position mislabels " + str(mislabelled) + " of " + str(idx) + " rows while the total, " + str(shown_sum) + "," ^0
"and the row count both reconcile exactly - as they would under any" ^0
"permutation." ^0

truth = [["north", 960], ["south", 175], ["east", 412], ["west", 388]]
names_by_name = ["east", "north", "south", "west"]
totals_by_value = [960, 412, 388, 175]
print("query A returns names ordered by name  : east, north, south, west")
print("query B returns totals ordered by value: 960, 412, 388, 175")
print("the report pairs them by row position")
print("")
print("region   shown   true    correct")
mislabelled = 0
shown_sum = 0
true_sum = 0
idx = 0
for n in names_by_name:
    shown = totals_by_value[idx]
    shown_sum = shown_sum + shown
    actual = 0
    for t in truth:
        if t[0] == n:
            actual = t[1]
    true_sum = true_sum + actual
    if shown == actual:
        print("  " + n + "     " + str(shown) + "     " + str(actual) + "     yes")
    else:
        mislabelled = mislabelled + 1
        print("  " + n + "     " + str(shown) + "     " + str(actual) + "     NO")
    idx = idx + 1
print("")
print("  rows with the wrong label : " + str(mislabelled) + " of " + str(idx))
print("")
print("quantities that survive a permutation")
print("  sum of the totals, as shown : " + str(shown_sum))
print("  sum of the totals, true     : " + str(true_sum))
print("  difference                  : " + str(shown_sum - true_sum))
print("  row count, as shown         : " + str(idx))
print("  largest value on the page   : " + str(totals_by_value[0]))
print("  largest value in truth      : 960")
print("")
print("  the monthly reconciliation compares the total and the row count")
print("  both match exactly, and they would match under any permutation")
print("")
print("questions the report is now wrong about")
print("  what is the total across regions   right")
print("  how many regions are there         right")
print("  what is the largest regional total right")
print("  WHICH region is largest            wrong")
print("  is north above target              wrong")
print("  every question naming a region is wrong and every aggregate is right")
print("")
print("a fixture where the two orderings agree")
lucky = [["alpha", 900], ["bravo", 600], ["charlie", 300], ["delta", 100]]
lucky_wrong = 0
lidx = 0
for lk in lucky:
    shown = lucky[lidx][1]
    if shown == lk[1]:
        print("  " + lk[0] + " : shown " + str(shown) + ", true " + str(lk[1]) + ", correct")
    else:
        lucky_wrong = lucky_wrong + 1
        print("  " + lk[0] + " : shown " + str(shown) + ", true " + str(lk[1]) + ", WRONG")
    lidx = lidx + 1
print("  mislabelled rows : " + str(lucky_wrong) + " of " + str(lidx))
print("  names ascending and values descending happen to agree here")
print("  a fixture written alphabetically with decreasing values passes")
print("")
print("control - each query judged alone")
print("  query A: returns 4 region names, ordered by name   : correct")
print("  query B: returns 4 totals, ordered by value desc   : correct")
print("  incorrect queries : 0 of 2")
print("  and each has an explicit ORDER BY, which is more than most queries have")
print("")
print("  the defect is in the join, and the join is a line of presentation code")
print("  that reads neither query's ORDER BY")
print("")
names_by_value = ["north", "east", "west", "south"]
print("null control - the same position join with both sides ordered by value")
nc_wrong = 0
nidx = 0
for n in names_by_value:
    shown = totals_by_value[nidx]
    actual = 0
    for t in truth:
        if t[0] == n:
            actual = t[1]
    if shown == actual:
        print("  " + n + " : " + str(shown) + ", correct")
    else:
        nc_wrong = nc_wrong + 1
        print("  " + n + " : " + str(shown) + ", wrong, true is " + str(actual))
    nidx = nidx + 1
print("  mislabelled rows : " + str(nc_wrong) + " of " + str(nidx))
print("  same join, same code, same two queries")
print("  position is a valid key exactly when both sides share an ordering")
print("")
print("joining two result sets by position")
print("  each query correct on its own       necessary, not sufficient")
print("  each query has an ORDER BY          necessary, not sufficient")
print("  the two ORDER BY clauses agree      this is the condition")
print("  and it is not stated in either query, or in the join")
print("")
print("the aggregate checks cannot find it, because a permutation preserves them")
print("the check that finds it is joining on a key, which removes the question")
print("")
print("Splitting the queries lets the page render labels in milliseconds instead of")
print("eleven seconds, and both queries are correct with an explicit ORDER BY each.")
print("They order by different columns because each was written to be read alone.")
print("Pairing them by row position mislabels " + str(mislabelled) + " of " + str(idx) + " rows while the total, " + str(shown_sum) + ",")
print("and the row count both reconcile exactly - as they would under any")
print("permutation.")

query A returns names ordered by name  : east, north, south, west
query B returns totals ordered by value: 960, 412, 388, 175
the report pairs them by row position

region   shown   true    correct
  east     960     412     NO
  north     412     960     NO
  south     388     175     NO
  west     175     388     NO

  rows with the wrong label : 4 of 4

quantities that survive a permutation
  sum of the totals, as shown : 1935
  sum of the totals, true     : 1935
  difference                  : 0
  row count, as shown         : 4
  largest value on the page   : 960
  largest value in truth      : 960

  the monthly reconciliation compares the total and the row count
  both match exactly, and they would match under any permutation

questions the report is now wrong about
  what is the total across regions   right
  how many regions are there         right
  what is the largest regional total right
  WHICH region is largest            wrong
  is north above target              wrong
  every question naming a region is wrong and every aggregate is right

a fixture where the two orderings agree
  alpha : shown 900, true 900, correct
  bravo : shown 600, true 600, correct
  charlie : shown 300, true 300, correct
  delta : shown 100, true 100, correct
  mislabelled rows : 0 of 4
  names ascending and values descending happen to agree here
  a fixture written alphabetically with decreasing values passes

control - each query judged alone
  query A: returns 4 region names, ordered by name   : correct
  query B: returns 4 totals, ordered by value desc   : correct
  incorrect queries : 0 of 2
  and each has an explicit ORDER BY, which is more than most queries have

  the defect is in the join, and the join is a line of presentation code
  that reads neither query's ORDER BY

null control - the same position join with both sides ordered by value
  north : 960, correct
  east : 412, correct
  west : 388, correct
  south : 175, correct
  mislabelled rows : 0 of 4
  same join, same code, same two queries
  position is a valid key exactly when both sides share an ordering

joining two result sets by position
  each query correct on its own       necessary, not sufficient
  each query has an ORDER BY          necessary, not sufficient
  the two ORDER BY clauses agree      this is the condition
  and it is not stated in either query, or in the join

the aggregate checks cannot find it, because a permutation preserves them
the check that finds it is joining on a key, which removes the question

Splitting the queries lets the page render labels in milliseconds instead of
eleven seconds, and both queries are correct with an explicit ORDER BY each.
They order by different columns because each was written to be read alone.
Pairing them by row position mislabels 4 of 4 rows while the total, 1935,
and the row count both reconcile exactly - as they would under any
permutation.

Trace event types