Let me walk you through the most common database bugs I have seen in real projects. For each one, I will show you the SQL to detect it. Bookmark this lesson. You will come back to it every time you start testing a new project.
A missing NOT NULL constraint lets blank data slip in. You end up with users who have no name, orders with no amount, or transactions with no timestamp.
-- Find users with missing critical data
SELECT user_id, full_name, email, phone
FROM users
WHERE full_name IS NULL
OR email IS NULL
OR phone IS NULL;
-- Find orders with no amount
SELECT order_id, user_id, total_amount, created_at
FROM orders
WHERE total_amount IS NULL OR total_amount = 0;Phone number stored as INTEGER instead of VARCHAR. What happens? Leading zeros get dropped. The phone number 09876543210 becomes 9876543210. Customer never receives OTP.
-- Check: Are phone numbers stored correctly?
SELECT user_id, phone, LENGTH(phone) AS phone_length
FROM users
WHERE LENGTH(phone) < 10;
-- If phone is stored as INT, leading zeros are gone
-- Check: Verify column data types
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users'
AND column_name = 'phone';
-- Should be VARCHAR(15), NOT INT or BIGINTServer is in UTC. Application shows IST. A transaction done at 11:30 PM IST shows as next day in the database (5:00 AM UTC). Monthly reports count it in the wrong month. Financial reconciliation fails.
-- Check database timezone
SHOW TIMEZONE; -- PostgreSQL
SELECT @@global.time_zone; -- MySQL
-- Compare: Transaction created "today" via UI
-- but stored with yesterday's date in DB
SELECT transaction_id, amount, created_at,
created_at AT TIME ZONE 'Asia/Kolkata' AS ist_time
FROM transactions
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 5;User deletes their account. The row in users table is gone. But orders, reviews, addresses, and payment methods still point to that user_id. Reports break. The application throws "User not found" errors when displaying old orders.
-- Find all orphan records across tables
-- Orphan orders
SELECT o.order_id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;
-- Orphan reviews
SELECT r.review_id, r.user_id
FROM reviews r
LEFT JOIN users u ON r.user_id = u.user_id
WHERE u.user_id IS NULL;
-- Orphan addresses
SELECT a.address_id, a.user_id
FROM addresses a
LEFT JOIN users u ON a.user_id = u.user_id
WHERE u.user_id IS NULL;Two users try to book the last seat on a flight at the exact same time. Both requests check availability, both see 1 seat available, both insert a booking. Now 2 people are booked for 1 seat. This happens when transactions lack proper isolation.
-- Detect double bookings
SELECT seat_number, flight_id, COUNT(*) AS bookings
FROM bookings
WHERE status = 'confirmed'
GROUP BY seat_number, flight_id
HAVING COUNT(*) > 1;
-- Detect oversold inventory
SELECT p.product_id, p.name, p.stock_quantity,
COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name, p.stock_quantity
HAVING p.stock_quantity < 0;User enters their name in Hindi: "रवि शर्मा". The database stores "??? ?????". This happens when the character set is Latin1 instead of UTF-8.
-- Check database and table character set
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'banking_app';
-- Should be utf8mb4 (not latin1 or utf8)
-- utf8mb4 supports emojis and all Indian languagesIf you are testing an Indian application (banking, government, e-commerce), ALWAYS test with Hindi, Tamil, and other regional language inputs. Character encoding bugs are extremely common and hard to fix after launch.
| Bug Type | Detection Query | Prevention |
|---|---|---|
| NULL in required field | WHERE column IS NULL | Add NOT NULL constraint |
| Duplicate records | GROUP BY + HAVING COUNT > 1 | Add UNIQUE constraint or index |
| Orphan records | LEFT JOIN + WHERE parent IS NULL | Add FOREIGN KEY with CASCADE |
| Data truncation | WHERE LENGTH(col) = MAX_LENGTH | Increase column size or validate input |
| Wrong data type | Check information_schema.columns | Use correct types during design |
| Timezone issues | Compare app time vs DB time | Use TIMESTAMP WITH TIME ZONE |
Key Point: The most common database bugs are NULL values, data truncation, orphan records, timezone mismatches, and race conditions. Learn the SQL to detect each one.