✏️ Explanatory Question
Level: Hard — Type coercion bugs cause wrong calculations and silently ignored WHERE filters in production.
discount = 0 for everyone, and a security query WHERE api_key = 0 unexpectedly returns every row in the table. Both bugs come from MySQL's silent type conversion rules. Can you spot why?
users table| id | name | api_key (VARCHAR) |
|---|---|---|
| 1 | Rumman | 'abc123' |
| 2 | Krushna | 'xyz789' |
| 3 | Swetha | '0secret' |
Many assume / always gives a decimal. But mixing it with DIV or integer context surprises people:
| Expression | Result | Why |
|---|---|---|
SELECT 5 / 2; | 2.5000 | / always returns decimal in MySQL |
SELECT 5 DIV 2; | 2 | DIV = integer division (truncates) |
SELECT 10 / 3 * 3; | 9.9999 | Rounding of the decimal division |
SELECT 7 % 3; | 1 | Modulo (remainder) |
(20/100) * price gives 0 because 20/100 is integer 0. In MySQL / is safe (0.2000), but if a developer uses DIV or casts to INT, 20 DIV 100 = 0 → the whole discount becomes 0.
When you compare a string column to a number, MySQL converts the string to a number — and a string that doesn't start with a digit becomes 0:
SELECT * FROM users WHERE api_key = 0;| String | Converts To |
|---|---|
'123abc' | 123 (reads leading digits) |
'abc123' | 0 (no leading digit) |
'25.5kg' | 25.5 |
'0secret' | 0 |
-- THE BUG: string column compared to a number -> all strings become 0
SELECT * FROM users WHERE api_key = 0; -- returns EVERY row!
-- THE FIX: always compare strings to strings (quote the literal)
SELECT * FROM users WHERE api_key = '0'; -- returns 0 rows (correct)
-- Integer division safety: use / for decimals, be explicit with DIV
SELECT price * (20 / 100) AS correct_discount FROM products; -- 0.2 * price
SELECT price * (20 DIV 100) AS broken_discount FROM products; -- 0 !
-- Force exact decimal math for money
SELECT price * CAST(20 AS DECIMAL(5,2)) / 100 AS safe_discount FROM products;'0' not 0), use / (not DIV) for fractional math, and CAST to DECIMAL for money. Implicit conversion also kills indexes on the column.
api_key = 0 break?" → It prevents the index on api_key from being used (the column is implicitly converted per row), forcing a full table scan — a performance AND correctness bug in one.