Nine SQL Pitfalls That Break Silently During Database Migration
A migration that passes every syntax check can still corrupt business data silently. These nine failure modes are deterministic and reproducible across Oracle-to-KES and MySQL-to-KES moves, and the three-layer detection method catches them before production.
Database migrations to platforms like KingbaseES often pass syntax checks but produce wrong row counts, missing data, or shifted values. The root cause falls into three categories: optimizer behavior differences (outer join elimination, predicate pushdown boundaries, subquery unnesting), SQL semantic standard differences (NULL vs. empty string equivalence, three-valued logic short-circuiting, NULL sort position), and function/expression behavior differences (side-effect ordering in WHERE clauses, implicit string-number conversion, time zone parsing).
Each pitfall is silent — no error, no warning, just a successful execution with subtly wrong results. An outer join gets rewritten to an inner join when a WHERE condition rejects NULLs on the nullable side. A NOT IN subquery returns an empty set the moment a NULL appears in production. An ORDER BY clause places NULLs differently across Oracle, MySQL, and KES, breaking pagination.
The fix is a three-layer methodology: static code scanning with regex or SQL parsers to flag suspicious patterns, execution plan and result-set comparison between old and new systems using EXPLAIN and EXCEPT, and production canary releases with continuous monitoring of slow queries, reconciliation metrics, and UDF call patterns. Rewrites favor explicit CTEs, NOT EXISTS over NOT IN, manual NULLS FIRST/LAST declarations, and removing stateful UDFs from WHERE clauses.
High syntax compatibility percentages are misleading: the real migration cost lives in the 5% of SQL that runs without error but produces wrong results, and that 5% is concentrated in optimizer decisions and semantic edge cases, not syntax.
The optimizer is not a neutral executor — it actively rewrites query semantics when it believes an optimization is safe, and the definition of 'safe' differs across database engines. This makes EXPLAIN comparison the single highest-signal diagnostic during migration.
CTEs with MATERIALIZED are an underused defense against optimizer-induced semantic drift; they act as optimization fences that preserve the original intent of subquery boundaries.
NULL handling is the most cross-cutting source of silent corruption, appearing in joins, subqueries, sorting, string equivalence, and three-valued logic — and no two databases agree on all of them.
UDF side effects in WHERE clauses are a time bomb in any database, but migration surfaces the bomb because the optimizer's reordering freedom changes; the fix is architectural, not syntactic — move state changes outside SQL.