CSV 去重流程在兩台主機產生不同結果：48 小時排查筆記

Have you ever diffed the same script on two hosts, found zero differences, and still watched the output change? I did, for two full days, on a small CSV-to-report pipeline that deduped contact rows and picked one representative per group. The script was byte-identical on my laptop and on a throwaway box, the input checksum matched, and the join count still disagreed. This was not concurrency flakiness; each host was deterministic on its own, and the two hosts simply disagreed with each other.

I drafted the first version of that dedupe step with MonkeyCode's free model access and replayed it on the free server option, which is how the two environments met at all.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The product is not the bug in this story, and I want to be explicit about that, because the real cause sat in my own comparison rules. The free server was just the second environment that made my assumptions visible.

The 48-hour shape of the bug

Hour 0-6: I blamed everything except collation

My first hypothesis was caching, so I cleared every cache layer I could name and re-ran the pipeline from scratch. When that changed nothing, I blamed model nondeterminism and regenerated the same helper three times, which produced three equivalent scripts and felt like proof of nothing. Then I blamed threads, serialized the join, and watched the counts stay exactly where they were.

Here is the full list of things I suspected before I ever looked at locale settings:

1. Stale build artifacts and an incremental cache keyed on the wrong field.
2. Nondeterministic generation of the dedupe helper itself.
3. A race in the thread pool that only showed up under load.
4. Truncated or partially written input on the second host.

That last item gave me the check that actually mattered, because sha256sum on both hosts returned the same digest. Once the data was provably identical, only behaviour was left, and behaviour is where the environment hides.

Hour 6-22: I stopped diffing output and started diffing order

The first useful command was embarrassingly small, and it printed different values on the two machines:

locale | sed -n '1,3p'
# laptop:  LANG=en_US.UTF-8   LC_COLLATE=en_US.UTF-8
# server:  LANG=              LC_COLLATE=C

Then I compared what Python thinks ordering means against what sort thinks ordering means:

python3 -c "print(sorted(['zebra', 'Angstrom', 'apple']))"
# ['Angstrom', 'apple', 'zebra']  <- code point order, locale ignored

printf 'zebra\nAngstrom\napple\n' | LC_ALL=C sort
printf 'zebra\nAngstrom\napple\n' | LC_ALL=en_US.UTF-8 sort

Run those two printf lines on your own hosts, because the ordering your libc ships for non-ASCII text is not something I can promise on your machine. The important part is structural: Python always compares by code point, while sort compares by whatever collation the environment hands it.

My pipeline did the grouping in Python and then picked one representative per group with sort | head -1 in a shell stage. Both halves were internally consistent, and together they selected different representatives, which is how a join count moves without a single line of code changing.

Hour 22-36: the reproduction that fits on one screen

I rebuilt the failure with five rows, because a bug I cannot reproduce in five rows is a bug I do not understand yet.

python3 - <<'PY'
rows = ['Taylor', 'taylor', 'TAYLOR', 'caf\u00e9', 'cafe\u0301']
open('keys.txt', 'w', encoding='utf-8').write('\n'.join(rows) + '\n')
PY

# the shell stage: raw bytes, no normalization
LC_ALL=C sort -u keys.txt | wc -l

# the Python stage: NFC normalisation plus casefold
python3 -c "
import unicodedata
keys = [line.strip() for line in open('keys.txt', encoding='utf-8')]
print(len({unicodedata.normalize('NFC', k).casefold() for k in keys}))
"

The shell stage reports five, and the Python stage reports two, and neither of them is wrong on its own terms. Two of those five rows are the same word written with different Unicode forms, and another three differ only by case. That gap is the entire bug, and it is the same class of gap that moved my join count on the real corpus.

The three rules I pin now

1. Normalize once, in one function everybody calls

# dedupe_key.py
import unicodedata

def group_key(raw: str) -> str:
    text = unicodedata.normalize('NFC', raw.strip())
    return text.casefold()

If two stages of one pipeline compute a key differently, you have two pipelines and one shared filename. I keep exactly one implementation and import it everywhere, including in the test harness below.

2. Pin the collation for every machine-facing step

export LC_ALL=C
# better, scope it to the single command that needs it:
LC_ALL=C sort -u keys.txt > keys.sorted.txt

This is not about making the output pretty, because byte order is genuinely unpleasant for humans to read. It is about making comparison rules explicit instead of inherited from whatever LANG happens to be set on the box.

3. Assert parity between the Python path and the shell path

The reason LC_ALL=C sort and Python's sorted can agree at all is that UTF-8 byte order matches code point order. That property is the whole trick, and it deserves a test rather than my memory.

# test_group_key_parity.py
import os, subprocess
from dedupe_key import group_key

SAMPLES = ['Taylor', 'taylor', 'TAYLOR', 'caf\u00e9', 'cafe\u0301']

python_order = sorted({group_key(s) for s in SAMPLES})

proc = subprocess.run(
    ['sort', '-u'],
    input='\n'.join(group_key(s) for s in SAMPLES),
    text=True, capture_output=True, env={**os.environ, 'LC_ALL': 'C'},
)
shell_order = proc.stdout.splitlines()

assert python_order == shell_order, (python_order, shell_order)

That assertion failed for me the first time, which is how I learned that my shell stage was feeding raw columns instead of group_key output. A test that never fails is a test you have not aimed at the right seam.

What this does not fix

• Semantic duplicates, such as plus-addresses, typos, and renamed accounts, will survive casefold because they are different strings. Normalization merges spellings, not meanings.
• casefold is actively wrong for identifiers where case carries information: base64 blobs, quoted SQL identifiers, and paths on a case-sensitive filesystem.
• NFC is a comparison-key choice, not a file-naming policy, since some filesystems will normalize names on their own and undo your rename.
• Byte order is unreadable in reports, so keep LC_ALL=C for keys and format human-facing output in a separate step.
• My sample has five rows, and your corpus will have more, so run the parity check at real size before you trust it.

Decision table

Who should not use this approach

If you dedupe on a UUID or another canonical primary key, most of this is irrelevant to you, and adding normalization would only slow the pipeline down. If your product genuinely needs locale-aware ordering for readers of Swedish or German phonebook conventions, do not force byte order into the user-visible layer, because correctness for machines and correctness for people are different requirements. And if your write boundary already normalizes once, adding a second normalization step here just gives you two places to keep in sync.

Where the free server option fits, for me, is replay: it gave me a second environment to run the same harness against, which is exactly what this bug needed. Treat any remote scratch box as a replay surface rather than a guarantee, and pin the locale inside your own code before you compare results. If you already keep a second host around for two-environment checks, that is a reasonable place to start, and you can try the free model access and free server option with the harness above without changing your production setup.

What I would repeat

I would keep the five-row reproduction, the parity assertion, and the habit of printing locale on every host before I trust a diff. I would drop the six hours I spent regenerating helpers, because nondeterminism was never on the table once the checksums matched. The cheap question I now ask first is not whether the code is the same, but whether ordering, normalization, and collation are the same on both sides.

Which of your pipeline stages compares strings without ever saying which collation it is comparing them in?