The outer join became inner

# Self-authored for the EML case corpus (no external origin). The orders report
# joins every order to its shipment and the LEFT JOIN is written correctly. What
# a WHERE on the shipment column does to it is computed below.
#
# The join is careful. It is a LEFT JOIN, chosen on purpose so an order with no
# shipment still appears; it joins on the real foreign key; it runs over every
# order; and the report is meant to list all orders with their delivery region.
#
# A later WHERE clause filters on shipment.region, and shipment.region IS NULL
# for an order that has no shipment.

50000 => orders
47000 => orders_with_a_shipment
3000 => orders_never_shipped

orders_with_a_shipment => orders_the_where_kept
orders - orders_the_where_kept => orders_dropped_by_the_where
int(orders_dropped_by_the_where * 10000 / orders) => dropped_share_per_myriad

"orders                          : " + str(orders) ^0
"  with a shipment               : " + str(orders_with_a_shipment) ^0
"  never shipped                 : " + str(orders_never_shipped) ^0
"" ^0
"orders the LEFT JOIN keeps      : " + str(orders) ^0
"orders the WHERE then kept      : " + str(orders_the_where_kept) ^0
"  dropped by the WHERE          : " + str(orders_dropped_by_the_where) ^0
"dropped share                   : " + str(dropped_share_per_myriad) + " per ten thousand" ^0
"" ^0

# ---- what the join verified ----

"the LEFT JOIN" ^0
"  kind : LEFT, chosen so an unshipped order still appears" ^0
"  joins on : the real foreign key" ^0
"  over : every order" ^0
"  intent : all orders, with their delivery region" ^0
"  orders it keeps before the WHERE : " + str(orders) ^0
"  verdict : all orders present" ^0
"" ^0

# ---- what the WHERE does ----

"WHERE shipment.region = 'domestic'" ^0
"  what an unshipped order's shipment.region is : NULL" ^0
"  what NULL = 'domestic' yields : UNKNOWN" ^0
"  what WHERE keeps : rows where the predicate is TRUE" ^0
"  so the unshipped orders : are dropped by the WHERE" ^0
"  the LEFT JOIN, effectively : becomes an INNER JOIN" ^0
"" ^0

# ---- what the report lost ----

"the orders that never shipped" ^0
"  count : " + str(orders_never_shipped) ^0
"  present after the LEFT JOIN : yes, as NULL-shipment rows" ^0
"  present after the WHERE : no" ^0
"  is the join wrong : no; the LEFT JOIN is correct" ^0
"  is a WHERE on the right table's column safe on a LEFT" ^0
"    JOIN : no; it filters out the very rows the LEFT JOIN" ^0
"    exists to keep" ^0
"" ^0

# ---- null control ----

# The same query, with the region predicate moved into the JOIN's ON clause (or
# written region = 'domestic' OR region IS NULL) so unmatched rows survive.
47000 => nc_kept_with_where_on_right
50000 => nc_kept_with_predicate_in_on
3000 => nc_orders_it_recovers

"null control - predicate in ON, not WHERE" ^0
"  kept, predicate in WHERE : " + str(nc_kept_with_where_on_right) ^0
"  kept, predicate in ON : " + str(nc_kept_with_predicate_in_on) ^0
"  orders it recovers : " + str(nc_orders_it_recovers) ^0
"  no order and no shipment changed; the predicate stopped" ^0
"  running after the join and started running inside it" ^0
"" ^0

# ---- the rule ----

"what a LEFT JOIN guarantees" ^0
"  every left row appears, matched or not : exactly, on" ^0
"    the real key, over every order" ^0
"  every order appears in the report : not addressed; the" ^0
"    LEFT JOIN keeps unshipped orders as NULL rows, and a" ^0
"    WHERE on the shipment's column is UNKNOWN for NULL," ^0
"    turning the outer join into an inner one - " + str(orders_never_shipped) ^0
"    unshipped orders vanish" ^0
"" ^0

"a LEFT JOIN promises the unmatched rows with NULLs on the right, and a WHERE on" ^0
"a right-side column tests those NULLs and drops them; the outer join survives" ^0
"only until a later clause asks the missing side a question" ^0
"" ^0

"The LEFT JOIN keeps all " + str(orders) + " orders on the real key. A WHERE on shipment.region is" ^0
"UNKNOWN for the " + str(orders_never_shipped) + " unshipped orders, so it drops them and the outer join" ^0
"becomes inner - the report shows " + str(orders_the_where_kept) + " of " + str(orders) + ", " + str(dropped_share_per_myriad) + " per ten thousand lost." ^0

orders = 50000
orders_with_a_shipment = 47000
orders_never_shipped = 3000
orders_the_where_kept = orders_with_a_shipment
orders_dropped_by_the_where = orders - orders_the_where_kept
dropped_share_per_myriad = int(orders_dropped_by_the_where * 10000 / orders)
print("orders                          : " + str(orders))
print("  with a shipment               : " + str(orders_with_a_shipment))
print("  never shipped                 : " + str(orders_never_shipped))
print("")
print("orders the LEFT JOIN keeps      : " + str(orders))
print("orders the WHERE then kept      : " + str(orders_the_where_kept))
print("  dropped by the WHERE          : " + str(orders_dropped_by_the_where))
print("dropped share                   : " + str(dropped_share_per_myriad) + " per ten thousand")
print("")
print("the LEFT JOIN")
print("  kind : LEFT, chosen so an unshipped order still appears")
print("  joins on : the real foreign key")
print("  over : every order")
print("  intent : all orders, with their delivery region")
print("  orders it keeps before the WHERE : " + str(orders))
print("  verdict : all orders present")
print("")
print("WHERE shipment.region = 'domestic'")
print("  what an unshipped order's shipment.region is : NULL")
print("  what NULL = 'domestic' yields : UNKNOWN")
print("  what WHERE keeps : rows where the predicate is TRUE")
print("  so the unshipped orders : are dropped by the WHERE")
print("  the LEFT JOIN, effectively : becomes an INNER JOIN")
print("")
print("the orders that never shipped")
print("  count : " + str(orders_never_shipped))
print("  present after the LEFT JOIN : yes, as NULL-shipment rows")
print("  present after the WHERE : no")
print("  is the join wrong : no; the LEFT JOIN is correct")
print("  is a WHERE on the right table's column safe on a LEFT")
print("    JOIN : no; it filters out the very rows the LEFT JOIN")
print("    exists to keep")
print("")
nc_kept_with_where_on_right = 47000
nc_kept_with_predicate_in_on = 50000
nc_orders_it_recovers = 3000
print("null control - predicate in ON, not WHERE")
print("  kept, predicate in WHERE : " + str(nc_kept_with_where_on_right))
print("  kept, predicate in ON : " + str(nc_kept_with_predicate_in_on))
print("  orders it recovers : " + str(nc_orders_it_recovers))
print("  no order and no shipment changed; the predicate stopped")
print("  running after the join and started running inside it")
print("")
print("what a LEFT JOIN guarantees")
print("  every left row appears, matched or not : exactly, on")
print("    the real key, over every order")
print("  every order appears in the report : not addressed; the")
print("    LEFT JOIN keeps unshipped orders as NULL rows, and a")
print("    WHERE on the shipment's column is UNKNOWN for NULL,")
print("    turning the outer join into an inner one - " + str(orders_never_shipped))
print("    unshipped orders vanish")
print("")
print("a LEFT JOIN promises the unmatched rows with NULLs on the right, and a WHERE on")
print("a right-side column tests those NULLs and drops them; the outer join survives")
print("only until a later clause asks the missing side a question")
print("")
print("The LEFT JOIN keeps all " + str(orders) + " orders on the real key. A WHERE on shipment.region is")
print("UNKNOWN for the " + str(orders_never_shipped) + " unshipped orders, so it drops them and the outer join")
print("becomes inner - the report shows " + str(orders_the_where_kept) + " of " + str(orders) + ", " + str(dropped_share_per_myriad) + " per ten thousand lost.")

orders                          : 50000
  with a shipment               : 47000
  never shipped                 : 3000

orders the LEFT JOIN keeps      : 50000
orders the WHERE then kept      : 47000
  dropped by the WHERE          : 3000
dropped share                   : 600 per ten thousand

the LEFT JOIN
  kind : LEFT, chosen so an unshipped order still appears
  joins on : the real foreign key
  over : every order
  intent : all orders, with their delivery region
  orders it keeps before the WHERE : 50000
  verdict : all orders present

WHERE shipment.region = 'domestic'
  what an unshipped order's shipment.region is : NULL
  what NULL = 'domestic' yields : UNKNOWN
  what WHERE keeps : rows where the predicate is TRUE
  so the unshipped orders : are dropped by the WHERE
  the LEFT JOIN, effectively : becomes an INNER JOIN

the orders that never shipped
  count : 3000
  present after the LEFT JOIN : yes, as NULL-shipment rows
  present after the WHERE : no
  is the join wrong : no; the LEFT JOIN is correct
  is a WHERE on the right table's column safe on a LEFT
    JOIN : no; it filters out the very rows the LEFT JOIN
    exists to keep

null control - predicate in ON, not WHERE
  kept, predicate in WHERE : 47000
  kept, predicate in ON : 50000
  orders it recovers : 3000
  no order and no shipment changed; the predicate stopped
  running after the join and started running inside it

what a LEFT JOIN guarantees
  every left row appears, matched or not : exactly, on
    the real key, over every order
  every order appears in the report : not addressed; the
    LEFT JOIN keeps unshipped orders as NULL rows, and a
    WHERE on the shipment's column is UNKNOWN for NULL,
    turning the outer join into an inner one - 3000
    unshipped orders vanish

a LEFT JOIN promises the unmatched rows with NULLs on the right, and a WHERE on
a right-side column tests those NULLs and drops them; the outer join survives
only until a later clause asks the missing side a question

The LEFT JOIN keeps all 50000 orders on the real key. A WHERE on shipment.region is
UNKNOWN for the 3000 unshipped orders, so it drops them and the outer join
becomes inner - the report shows 47000 of 50000, 600 per ten thousand lost.

Trace event types