CAST's Silent Truncation

1. Why this happens

Real tables end up with numeric-looking data stored as TEXT more often than anyone plans for — a CSV import that never set column types, a legacy system that dumped every field as a string, a form field nobody validated. The instinct is to CAST it to a number at query time and move on. In most programming languages, trying to convert "pending" or "2,500" into a number throws an exception you can't miss. SQLite doesn't work that way. CAST(x AS REAL) and CAST(x AS INTEGER) never raise an error for bad input — they read the string left to right, keep whatever looks like a valid number at the start, and silently stop the instant they hit a character that doesn't fit. Nothing breaks. The number is just wrong, and it looks exactly as legitimate as a correct one.

2. The dataset

Verified by actually running this against SQLite — not hand-computed. A freelance platform's payout_requests table, imported from a legacy CSV where amount_text was left as TEXT. Most rows are clean, but the export used a comma as a thousands separator in one of Priya's rows, and one of Rahul's payouts hadn't settled yet so someone typed the word pending into the amount column instead of leaving it NULL.

CREATE TABLE payout_requests(
  id INTEGER,
  freelancer_name TEXT,
  amount_text TEXT
);

INSERT INTO payout_requests VALUES
 (1,'Priya','15000'),
 (2,'Priya','2,500'),
 (3,'Rahul','8000'),
 (4,'Rahul','9500.50'),
 (5,'Rahul','pending'),
 (6,'Kavya','12000'),
 (7,'Kavya','6800');

Priya's real total across both payouts should be 15000 + 2500 = 17500. Rahul's should be 8000 + 9500.50 = 17500.50, with one payout still pending. Kavya has no dirty data at all — her two rows are clean numbers.

3. The bug

"Total payout per freelancer" reaches for the obvious cast:

SELECT freelancer_name, SUM(CAST(amount_text AS REAL)) AS total_payout_bug
FROM payout_requests
GROUP BY freelancer_name;

Rahul and Kavya happen to come out right — more on why in a moment. Priya doesn't. Her real total is 17500, and the query reports 15002, a shortfall of 2498 with zero errors, zero warnings, and a result that looks exactly as plausible as a correct one would. Nobody staring at this query would think to double-check it — SUM(CAST(...)) is about as ordinary as SQL gets.

4. Why it happens

SQLite's CAST doesn't validate — it scans. Given a string, it walks left to right consuming a leading run of digits, at most one decimal point, and an optional sign, and stops the moment it meets anything else. Whatever it consumed becomes the number; if it consumed nothing at all, the result is 0.

'2,500' parses the leading 2, then hits a comma — a character that isn't part of a number — and stops right there, discarding ,500 entirely. 'pending' doesn't start with anything numeric at all, so the scan consumes zero characters and CAST falls back to 0. That's also exactly why Rahul's total came out correct by accident: adding 0 for his pending payout doesn't change a sum, even though the row itself is meaningless. Priya's row is the dangerous one, because the corrupted value isn't 0 — it's a plausible-looking number that's just wrong.

This leniency is SQLite-specific, not a SQL standard guarantee. PostgreSQL's CAST('2,500' AS NUMERIC) raises a hard invalid input syntax for type numeric error and aborts the statement instead of guessing. Writing a query against SQLite and assuming it will behave the same way in Postgres — or the reverse — is its own trap.

5. The fix

Don't let a value that fails to actually be a number silently become 0. Normalize the formatting SQLite can't parse through on its own, validate what's left with GLOB, and only cast rows that pass — while still counting the ones that didn't, instead of letting them vanish.

SELECT freelancer_name,
       SUM(CASE WHEN REPLACE(amount_text, ',', '') GLOB '[0-9]*'
                 AND REPLACE(amount_text, ',', '') NOT GLOB '*[^0-9.]*'
            THEN CAST(REPLACE(amount_text, ',', '') AS REAL) END) AS total_payout,
       SUM(CASE WHEN REPLACE(amount_text, ',', '') GLOB '[0-9]*'
                 AND REPLACE(amount_text, ',', '') NOT GLOB '*[^0-9.]*'
            THEN 1 ELSE 0 END) AS settled_count,
       SUM(CASE WHEN NOT (REPLACE(amount_text, ',', '') GLOB '[0-9]*'
                 AND REPLACE(amount_text, ',', '') NOT GLOB '*[^0-9.]*')
            THEN 1 ELSE 0 END) AS unparseable_count
FROM payout_requests
GROUP BY freelancer_name;

REPLACE(amount_text, ',', '') strips the thousands separator SQLite's scanner would otherwise choke on, turning '2,500' into '2500' before CAST ever sees it. The GLOB pair checks that what's left actually starts with a digit and contains nothing but digits and a decimal point — 'pending' fails that check outright, so it's excluded from total_payout rather than silently averaged in as a zero. settled_count and unparseable_count reuse the exact same condition to make the gap visible: Rahul now clearly shows 1 unparseable payout instead of that row disappearing into a correct-looking total.

6. CAST's other trap: it truncates, it doesn't round

Even with clean, fully numeric input, CAST(x AS INTEGER) has a second gotcha worth knowing: it truncates toward zero instead of rounding to the nearest integer.

SELECT CAST(9.9 AS INTEGER) AS a,
       CAST(9.4 AS INTEGER) AS b,
       CAST(-9.9 AS INTEGER) AS c;

9.9 becomes 9, not 10 — CAST simply chops off everything after the decimal point rather than asking which integer is closer. -9.9 becomes -9 for the same reason: truncating toward zero, not rounding down (a true floor would give -10). Anywhere the intent is "nearest whole number," ROUND(x) is the right call — CAST(x AS INTEGER) is for discarding the fractional part on purpose, not for rounding.

7. Cheat sheet

8. Try it yourself

This bug is dangerous precisely because it never announces itself. A malformed row doesn't crash the query or show up as an obvious NULL — it just quietly contributes a smaller (or larger) number than it should, and the total still looks like a real number. The only real defense is treating every TEXT-typed "number" column as guilty until validated: check what CAST actually does to your edge cases before trusting it inside a SUM.

Practice type coercion live on TableNotFound →