SQL traps · Chapter 6 of 16

Money in a float

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Everyday query traps
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

Why you need this

The error is far below a paisa, so anything that rounds hides it. The wallet page rounds to two decimals and shows a clean amount. No error is raised, either: the sum is a valid float, just not the one you meant. It bites at a comparison:

SELECT 0.7::float8 + 0.1::float8 AS balance,
       0.7::float8 + 0.1::float8 >= 0.8 AS enough;
      balance       │ enough
────────────────────┼────────
 0.7999999999999999 │ f

Rounding only inside the comparison is a trap of its own. It says yes, but the error stays in what is stored:

SELECT round((0.7::float8 + 0.1::float8)::numeric, 2) >= 0.8 AS looks_enough,
       (0.7::float8 + 0.1::float8) - 0.8 AS left_over;
 looks_enough │        left_over
──────────────┼─────────────────────────
 t            │ -1.1102230246251565e-16

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: 10.80 that cannot be withdrawn

    Write repro.sql. It runs in a fresh, empty database where wallets.sql has just made the table wallets and the functions add_tip and withdraw. Make one wallet, tip it 10.70 and then 0.10 with add_tip, and end with a SELECT that gives one value: what withdraw gives when that wallet withdraws 10.80.

    The check runs your file twice, each time in a fresh database: beside the original wallets.sql it must give 'insufficient' (the withdrawal is refused), and beside a fixed wallets.sql it must give 'paid'. So it has to really call the functions: a file that only says SELECT 'insufficient' fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    wallets.sql: a withdrawal that adds up

    Fix wallets.sql so a wallet holds exact rupees and paise. Tips of 10.70 and 0.10 make a balance of exactly 10.80, and withdrawing 10.80 then gives 'paid' and leaves exactly 0. Ten tips of 0.10 make exactly 1.00. Withdrawing more than the balance gives 'insufficient' and leaves the balance as it was. Tips of 0.10 and 0.20 then a withdrawal of 0.30 leave exactly 0.

    The fix may change the table: wallets.balance must be a numeric column (numeric(12,2), for example), still in rupees. Keep the table's name and its columns (id, owner, balance), and keep add_tip(wallet, amount) and withdraw(wallet, amount) with their names, arguments and results. The check uses its own wallets, and a fix that only rounds inside the comparison lets 10.80 through but leaves a balance that is not exactly 0.

  3. Task 3

    wallets.test.sql: a test that would have caught it

    Write a SQL test file, wallets.test.sql, that would have caught this bug before the tips launch. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of wallets.sql has just made the table and the functions: beside the original wallets.sql at least one test must fail; beside the fixed wallets.sql every test must pass and nothing may raise an error; beside a hotfix that keeps the float and rounds only inside the comparison at least one test must fail.

    So make a wallet, tip it 10.70 and 0.10, and test both parts: withdrawing the whole 10.80 is 'paid', and the balance left afterwards is exactly 0. Run it with node run.mjs wallets.test.sql.

What you will learn

  • repro.sql: 10.80 that cannot be withdrawn
  • wallets.sql: a withdrawal that adds up
  • wallets.test.sql: a test that would have caught it

More in Everyday query traps

Build "Money in a float" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.