skip to content

A column of numbers now sorts with 10 before 9. What does that tell you, and why did no step raise?

level: juniorimportance: must knowfreq 74%

answer

  1. the sort order is only the symptom
  2. one representation for the whole column
  3. ordered character by character
  4. '1' comes before '9'

basics

~20 s

A column ordered character by character is being held as text rather than as numbers: '10' comes before '9' because '1' comes before '9'. Nothing raised because putting text in order is a legal operation.

solid answer

~40 s

A column carries one representation for all of its rows, and some earlier step rebuilt this one out of text. Once it is text, ordering compares character by character rather than by magnitude, so `10` sorts ahead of `9` and `100` ahead of both. No error appeared because ordering text is perfectly legal — and so are comparing it, grouping it and matching on it, which is why the same column also skews every threshold comparison downstream without saying so. Whether anything ever raises depends on the design: tools that type columns strictly refuse the first step that mixes text with a number, while tools that fall back to a catch-all representation keep returning plausible answers. Read the column's declared representation rather than the rendered values.

code

pseudocode · 6 lines
pseudocode
sort(["9", "10", "100"])   ->  ["10", "100", "9"]
sort([9, 10, 100])         ->  [9, 10, 100]

same threshold, two representations:
  "9" > "100"   ->  true    (first characters compared: 9 after 1)
   9  >  100    ->  false

go deeper

for a junior

Recall that a column is held one way for all of its rows, and that text is ordered character by character rather than by size. Seeing 10 before 9 should point at the representation, not at the sort.

for a middle

Explain why the intermediate steps still succeeded: ordering, comparing, grouping and matching are all defined on text, so each one returns something plausible instead of an error.

for a senior

Show that you read the declared representation rather than the rendered values, and that whether a wrong result ever surfaces depends on whether the tool types columns strictly or falls back to a catch-all.

for a principal

The tradeoff is where this class of defect should cost: a cheap representation claim at each boundary in the file, against a report that ships a wrong number and is believed.

## One representation for the whole column A table column is not a bag of independently typed cells. Each column carries a single **representation** — one decision, made once, about how every value in that column is physically held, and therefore about how every operation on that column behaves. "This column is numeric" is a statement about the column, not about the values you happen to see in it. Text is a different representation from a number. If a step in the file rebuilt this column out of text — a placeholder written out in characters, a value joined to a label, a conversion aimed at the wrong column, a reassembly from parts — the column stops being numeric for **every row**, including all the rows that still look like clean numbers. One value that would not fit the numeric form at the moment the column was built is enough to change the behaviour of all the others. ## Why 10 lands before 9 Ordering numbers compares magnitude. Ordering text compares character by character, left to right, settling at the first position where the two values differ: - `10` and `9` differ at the very first character. - `1` comes before `9` in the character ordering. - So `10` sorts ahead of `9`, and `100` sorts ahead of both. Nothing here is a bug. The ordering is exactly right **for text**. The defect is that the column is text at all; the sort is merely the first place where the difference becomes visible to a human, because a human knows 10 is bigger than 9 and the character ordering has no opinion about that. ## What still runs, and what it gives back The ordering is the symptom you noticed. It is not the only one, and most of the others are quieter. | step | on the numeric column | on the same column held as text | |---|---|---| | putting rows in order | by magnitude | character by character | | a threshold comparison | keeps the rows above the value | keeps the rows whose characters sort after the threshold's characters | | grouping | one group per number | one group per spelling, so a bare five, a five written with a fractional part, and a padded five are three groups | | smallest and largest | the smallest and largest values | the first and last spellings in character order — real values out of the data, which is why they look right | | adding the values up | a total | in some designs every value runs together into one long run of characters; in others the step refuses | | matching on the column | pairs by value | pairs only where the spellings agree exactly | The threshold comparison is the dangerous row. It raises nothing, returns a subset of roughly the right size, and is wrong about which rows belong in it. ## Why nothing raised, and where designs differ This is the part worth saying out loud, because the answer is not the same everywhere. - Where a tool types its columns **strictly**, a later step that mixes text with a number is refused outright. The wrong answer never ships; a run fails instead, and the distance between cause and error is short. - Where a tool keeps a **catch-all representation** — the form it falls back to when a column's values do not all fit one narrow type, holding arbitrary values of the host language — ordering, comparing, grouping and matching are all defined on text. Every step returns something. Nothing refuses until some step has a hard requirement for a numeric column, and there may be no such step in the file at all. The second case is the one that produces a wrong number in a report rather than a failed run, and it is why this column is worth checking rather than waiting on. ## Confirming it 1. Read the column's **declared representation** — the tool's own record of how the whole column is held. It is bookkeeping the tool already has, so it costs nothing, and it answers the question directly. 2. Do not settle it from the rendering. Some renderings hint at text through alignment, quotation marks, or a type line under the header; others show a number and the same number written as characters identically. A glance at the first rows can suggest an answer but cannot establish one. 3. Do not settle it from convertibility either. "Every value here reads as a number" is true of this column and will stay true; it is a different claim from "this column is held as a number", and only the second one is false right now. ## The repair, and where it goes Narrowing the column with an explicit conversion fixes the symptom, and you choose up front what happens to a value that will not convert — raise, or become absent — because that choice decides whether the awkward values are seen or buried. Then go back for the step that produced text in the first place, because it runs again tomorrow. And leave a claim behind, written beside that step, that the column is held numerically at this point, so the next occurrence stops the run where it started rather than surfacing as an ordering somebody happened to notice.

  • The column prints exactly as it always did. What do you look at instead of the values?
    The column's declared representation — the tool's own record of how the whole column is held, which is bookkeeping rather than a scan and costs nothing to read. Renderings vary: some hint at text through alignment, quotation marks or a type line under the header, and some show nothing at all, so the first few rows are not evidence either way.
  • A total over the same column comes back as one very long run of digits. What happened?
    In designs where the addition operator on text joins values end to end, folding the column concatenated every value instead of summing them, so the total is every row's characters in a row. Other designs refuse the operation outright. Either way the cause is the representation, not the fold.
  • Does the defect affect only the rows that were malformed?
    No. The representation belongs to the whole column, so every row is text, including the ones that still look like clean numbers. A single value that would not fit the numeric form when the column was built is enough to change how all the others sort, compare and group.

Room numbers written on cardboard tags and filed as words: Room 10 lands in front of Room 9 because the clerk is reading letters, not counting. The clerk is filing correctly; the mistake was writing the numbers as words.

saying these in an interview costs you the question

  • Says the sort is broken or the tool has a bug
  • Treats the rendering of the rows as settling how the column is held
  • Assumes a wrong type always raises straight away
  • Treats it as a few bad rows rather than the whole column
  • Thinks changing the sort's options fixes it rather than the representation