The column was not null and the value was an empty string

# Self-authored for the EML case corpus (no external origin). The column is NOT
# NULL, the constraint has never been violated, and the database enforces it.
# How many rows carry a value is computed below.
#
# The constraint is doing real work. It was added after a release where a
# nullable column reached a report as the word "None" in front of a customer, it
# has rejected fourteen thousand malformed inserts since, and the migration that
# added it took a maintenance window because the back-fill was careful. Zero
# violations is a true and hard-won number.
#
# NOT NULL constrains the ABSENCE MARKER, and there is more than one way to
# write nothing down. The loader receives an empty field from the upstream feed
# and writes an empty string, which satisfies the constraint completely.
#
# Every report that counts rows where the column is not null counts them.

14200000 => rows
0 => not_null_violations
4860000 => rows_holding_an_empty_string
14000 => malformed_inserts_rejected

rows - rows_holding_an_empty_string => rows_holding_a_value

"rows                        : " + str(rows) ^0
"holding an empty string     : " + str(rows_holding_an_empty_string) ^0
"holding a value             : " + str(rows_holding_a_value) ^0
"not null violations         : " + str(not_null_violations) ^0
"" ^0

# ---- what the constraint verified ----

"the NOT NULL constraint" ^0
"  enforced by            : the database" ^0
"  violations ever        : " + str(not_null_violations) ^0
"  malformed inserts rejected since : " + str(malformed_inserts_rejected) ^0
"  added after            : a null reaching a customer report" ^0
"  verdict                : ENFORCED" ^0
"" ^0
"  it was added for a real reason and it prevents that" ^0
"  reason from recurring" ^0
"" ^0

# ---- the two ways to write nothing ----

"what the loader does with a missing upstream field" ^0
"  writes null          : rejected by the constraint" ^0
"  writes empty string  : accepted" ^0
"  which it does        : the second, since the constraint" ^0
"    was added, because the first stopped working" ^0
"" ^0
"  the constraint did not remove the absent values; it" ^0
"  selected the spelling they are written in" ^0
"" ^0

int(rows_holding_an_empty_string * 10000 / rows) => empty_per_myriad
"share of rows with nothing in them : " + str(empty_per_myriad) + " per ten thousand" ^0
"" ^0

# ---- what the reports count ----

"three counts of the same column" ^0
"  rows where it is not null  : " + str(rows) ^0
"  rows where it is non-empty : " + str(rows_holding_a_value) ^0
"  rows a person would call filled : " + str(rows_holding_a_value) ^0
"" ^0
rows - rows_holding_a_value => overcount
"  the first exceeds the third by : " + str(overcount) ^0
"  which query the dashboards use : the first" ^0
"" ^0

# ---- why the fix made it worse ----

# Before the constraint the same rows held null, and every count of "not null"
# excluded them correctly. Adding the constraint moved them from a value the
# reports understood into one they do not.
rows_holding_an_empty_string => rows_that_used_to_be_excluded

"before the constraint" ^0
"  these rows held        : null" ^0
"  counted as filled      : no" ^0
"  reports were           : correct about them" ^0
"after the constraint" ^0
"  these rows hold        : an empty string" ^0
"  counted as filled      : yes, " + str(rows_that_used_to_be_excluded) + " of them" ^0
"" ^0

# ---- null control ----

# The same constraint, plus a check that the value has length.
0 => nc_rows_holding_an_empty_string
rows_holding_a_value => nc_rows_accepted

"null control - NOT NULL plus a length check" ^0
"  violations of NOT NULL : " + str(not_null_violations) + ", unchanged" ^0
"  rows holding an empty string : " + str(nc_rows_holding_an_empty_string) ^0
"  rows accepted          : " + str(nc_rows_accepted) ^0
"  the constraint did not get stricter about nulls; a" ^0
"  second spelling of nothing stopped being accepted" ^0
"" ^0

# ---- the rule ----

"what NOT NULL guarantees" ^0
"  no row holds the null marker : exactly" ^0
"  every row holds information  : not addressed; the" ^0
"    constraint names one representation of absence and" ^0
"    every type has others" ^0
"" ^0
"constraining a representation redirects the values it" ^0
"forbids; the ones that used to be visibly missing become" ^0
"invisibly missing, and the reports get worse" ^0
"" ^0

"The constraint is enforced and its zero is real: " + str(not_null_violations) + " violations ever and" ^0
str(malformed_inserts_rejected) + " malformed inserts rejected since it was added, after a null reached a" ^0
"customer report. " + str(rows_holding_an_empty_string) + " of " + str(rows) + " rows hold an empty string - " + str(empty_per_myriad) + " per ten" ^0
"thousand - so every dashboard counting not-null rows overcounts by " + str(overcount) + ", and" ^0
"before the constraint existed those same rows were counted correctly." ^0

rows = 14200000
not_null_violations = 0
rows_holding_an_empty_string = 4860000
malformed_inserts_rejected = 14000
rows_holding_a_value = rows - rows_holding_an_empty_string
print("rows                        : " + str(rows))
print("holding an empty string     : " + str(rows_holding_an_empty_string))
print("holding a value             : " + str(rows_holding_a_value))
print("not null violations         : " + str(not_null_violations))
print("")
print("the NOT NULL constraint")
print("  enforced by            : the database")
print("  violations ever        : " + str(not_null_violations))
print("  malformed inserts rejected since : " + str(malformed_inserts_rejected))
print("  added after            : a null reaching a customer report")
print("  verdict                : ENFORCED")
print("")
print("  it was added for a real reason and it prevents that")
print("  reason from recurring")
print("")
print("what the loader does with a missing upstream field")
print("  writes null          : rejected by the constraint")
print("  writes empty string  : accepted")
print("  which it does        : the second, since the constraint")
print("    was added, because the first stopped working")
print("")
print("  the constraint did not remove the absent values; it")
print("  selected the spelling they are written in")
print("")
empty_per_myriad = int(rows_holding_an_empty_string * 10000 / rows)
print("share of rows with nothing in them : " + str(empty_per_myriad) + " per ten thousand")
print("")
print("three counts of the same column")
print("  rows where it is not null  : " + str(rows))
print("  rows where it is non-empty : " + str(rows_holding_a_value))
print("  rows a person would call filled : " + str(rows_holding_a_value))
print("")
overcount = rows - rows_holding_a_value
print("  the first exceeds the third by : " + str(overcount))
print("  which query the dashboards use : the first")
print("")
rows_that_used_to_be_excluded = rows_holding_an_empty_string
print("before the constraint")
print("  these rows held        : null")
print("  counted as filled      : no")
print("  reports were           : correct about them")
print("after the constraint")
print("  these rows hold        : an empty string")
print("  counted as filled      : yes, " + str(rows_that_used_to_be_excluded) + " of them")
print("")
nc_rows_holding_an_empty_string = 0
nc_rows_accepted = rows_holding_a_value
print("null control - NOT NULL plus a length check")
print("  violations of NOT NULL : " + str(not_null_violations) + ", unchanged")
print("  rows holding an empty string : " + str(nc_rows_holding_an_empty_string))
print("  rows accepted          : " + str(nc_rows_accepted))
print("  the constraint did not get stricter about nulls; a")
print("  second spelling of nothing stopped being accepted")
print("")
print("what NOT NULL guarantees")
print("  no row holds the null marker : exactly")
print("  every row holds information  : not addressed; the")
print("    constraint names one representation of absence and")
print("    every type has others")
print("")
print("constraining a representation redirects the values it")
print("forbids; the ones that used to be visibly missing become")
print("invisibly missing, and the reports get worse")
print("")
print("The constraint is enforced and its zero is real: " + str(not_null_violations) + " violations ever and")
print(str(malformed_inserts_rejected) + " malformed inserts rejected since it was added, after a null reached a")
print("customer report. " + str(rows_holding_an_empty_string) + " of " + str(rows) + " rows hold an empty string - " + str(empty_per_myriad) + " per ten")
print("thousand - so every dashboard counting not-null rows overcounts by " + str(overcount) + ", and")
print("before the constraint existed those same rows were counted correctly.")

rows                        : 14200000
holding an empty string     : 4860000
holding a value             : 9340000
not null violations         : 0

the NOT NULL constraint
  enforced by            : the database
  violations ever        : 0
  malformed inserts rejected since : 14000
  added after            : a null reaching a customer report
  verdict                : ENFORCED

  it was added for a real reason and it prevents that
  reason from recurring

what the loader does with a missing upstream field
  writes null          : rejected by the constraint
  writes empty string  : accepted
  which it does        : the second, since the constraint
    was added, because the first stopped working

  the constraint did not remove the absent values; it
  selected the spelling they are written in

share of rows with nothing in them : 3422 per ten thousand

three counts of the same column
  rows where it is not null  : 14200000
  rows where it is non-empty : 9340000
  rows a person would call filled : 9340000

  the first exceeds the third by : 4860000
  which query the dashboards use : the first

before the constraint
  these rows held        : null
  counted as filled      : no
  reports were           : correct about them
after the constraint
  these rows hold        : an empty string
  counted as filled      : yes, 4860000 of them

null control - NOT NULL plus a length check
  violations of NOT NULL : 0, unchanged
  rows holding an empty string : 0
  rows accepted          : 9340000
  the constraint did not get stricter about nulls; a
  second spelling of nothing stopped being accepted

what NOT NULL guarantees
  no row holds the null marker : exactly
  every row holds information  : not addressed; the
    constraint names one representation of absence and
    every type has others

constraining a representation redirects the values it
forbids; the ones that used to be visibly missing become
invisibly missing, and the reports get worse

The constraint is enforced and its zero is real: 0 violations ever and
14000 malformed inserts rejected since it was added, after a null reached a
customer report. 4860000 of 14200000 rows hold an empty string - 3422 per ten
thousand - so every dashboard counting not-null rows overcounts by 4860000, and
before the constraint existed those same rows were counted correctly.

Trace event types