OneBug All write-ups

SQL

SQL bugs, explained.

SQL fails by returning a plausible result. A NULL poisons a predicate, a join multiplies or drops rows, and the report looks fine - just wrong. These write-ups follow the wrong number back to the clause that made it.

SQL integer division returns 0 instead of a decimal passed / total returned 0 for a student who passed three of four tests. SQL divides two integers as an integer and drops the remainder. Cast one side. SQL AVG ignores NULL: the average that reads too high AVG(discount) came back at 10 when the real average was 3.33. SQL aggregates skip NULLs, so no-coupon orders drop out of the math. Here is the fix. SQL != and <> exclude NULL rows (and how to fix it) A WHERE status <> 'shipped' filter quietly drops every row where status is NULL, so records vanish from the result. Here is why, and the fix. SQL COUNT(column) vs COUNT(*): why NULLs undercount COUNT(coupon_code) looked like it counted orders. It counts non-NULL coupons, so a customer with no coupons drops to zero. Here's the fix. LEFT JOIN with WHERE acts like INNER JOIN (missing rows) A WHERE filter on the right table's columns drops the NULL rows your LEFT JOIN just preserved. Why it happens and where the filter belongs. SQL join fan-out silently inflates SUM() totals An extra JOIN to a one-to-many table multiplies rows before SUM() runs, so revenue looks too high. The fix is dropping or pre-aggregating that join. SQL NOT IN returns nothing? Blame one NULL NOT IN (subquery) silently returns zero rows when the subquery contains a single NULL, because NULL comparisons evaluate to UNKNOWN. Here's the fix.