Don't Assume Your SQL WHERE Conditions Work — Run Them First
For any developer migrating SQL between databases — MySQL to KingbaseES, PostgreSQL, or any other system — the most dangerous bugs hide in the simplest WHERE conditions. This systematic test shows exactly where assumptions fail: case sensitivity, NULL handling, and aggregate behavior. Running these checks before production deployment saves hours of debugging.
When migrating to a new database, the biggest risks aren't complex JOINs or window functions — they're the everyday WHERE conditions that everyone assumes "just work." One developer systematically tested equality, inequality, LIKE, IN, BETWEEN, NULL checks, sorting, LIMIT, GROUP BY, and HAVING on a small 12-row table in KingbaseES, and the results highlight exactly where assumptions break down.
Case sensitivity is the first trap. With `--enable-ci` enabled, `LIKE 'MYSQL%'` and `LIKE 'mysql%'` return identical results — but that behavior is environment-specific and can't be generalized. NULL handling is another: `WHERE remark = NULL` returns zero rows, while `WHERE remark IS NULL` correctly finds four. And aggregate functions silently skip NULLs, so `count(*)` and `count(score)` differ when scores are missing.
The real lesson: before migrating any SQL, run the actual queries on actual data. Don't rely on memory or "it should be the same." Test equality, test LIKE patterns, test NULL boundaries, and always use parentheses with AND/OR combinations. The cost of a wrong assumption is a production bug that's hard to trace.
The most dangerous SQL bugs aren't in complex JOINs or subqueries — they're in the everyday WHERE conditions that everyone assumes 'just work.'
Case sensitivity in LIKE is a silent killer: it works differently across environments, character sets, and collations, yet most developers never test it explicitly.
NULL handling reveals a deeper truth: SQL is not intuitive. `= NULL` feels natural to many programmers, but the correct `IS NULL` syntax is a fundamental SQL concept that must be learned, not guessed.
The difference between `count(*)` and `count(column)` is a classic reporting bug — it's easy to miss until someone notices the numbers don't add up.
Adding a tiebreaker column to ORDER BY is a cheap insurance policy against pagination bugs that only appear at scale.
Testing basic conditions on a small table before migration is a practice that saves more time than any optimization trick.
The fact that `IN` doesn't preserve list order surprises many developers who come from ORMs or frameworks that hide SQL details.
Parentheses in AND/OR conditions aren't just for correctness — they're for readability. A future developer (or your future self) will thank you.