Database

A LEFT JOIN That Silently Dropped 40% of Report Data During a Database Migration

By 倔强的石头_ · Jul 9, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Database migrations from MySQL or PostgreSQL to a new system often surface silent data corruption that has existed for years. A LEFT JOIN that silently becomes an INNER JOIN drops rows without errors, and the only reliable way to catch it is by inspecting the execution plan—not by trusting that the SQL runs without exceptions.

Summary

A production report lost 40% of its data after migrating from MySQL to KingbaseES (KES), not because of a database bug, but because a long-standing SQL error was finally exposed. The culprit was a LEFT JOIN with a WHERE clause filtering on a column from the right table, which the optimizer correctly rewrites as an INNER JOIN under SQL standard rules. The old MySQL system had been running the same incorrect logic for years, but no one had ever reconciled the numbers closely enough to notice.

KES, PostgreSQL, MySQL, and all SQL:2008-compliant databases perform this outer join elimination when they detect a null-rejecting condition on the nullable side. The fix is straightforward: move the filter condition from the WHERE clause into the ON clause, or use COALESCE to create a null-preserving comparison. The execution plan reveals the truth instantly—a plain `Hash Join` instead of a `Hash Left Join` confirms the outer join has been eliminated.

Six common trap patterns extend beyond the basic case: non-equality operators, strict functions wrapping right-table columns, IN/BETWEEN clauses, mixing IS NULL with other right-table conditions, transitive elimination across nested joins, and views whose outer joins are reversely eliminated by outer WHERE clauses. A practical checklist combining static regex scanning, EXPLAIN sampling, and EXCEPT-based regression testing catches over 90% of these semantic traps during migration.

Takeaways

— Any LEFT JOIN with a WHERE clause filtering a right-table column using =, >, <, !=, LIKE, IN, or BETWEEN triggers outer join elimination and silently becomes an INNER JOIN.

— KES, MySQL, PostgreSQL, Oracle, and SQL Server all perform this optimization identically because it is mandated by the SQL:2008 standard.

— The execution plan is the only reliable diagnostic: a plain `Hash Join` or `Nested Loop` instead of `Hash Left Join` or `Nested Loop Left Join` confirms elimination.

— Moving the filter condition from the WHERE clause into the ON clause preserves the outer join semantics and keeps all left-table rows.

— Wrapping a right-table column in COALESCE, such as `COALESCE(t2.status, 'A') = 'A'`, creates a null-preserving condition that prevents elimination.

— Static regex scanning for LEFT JOIN patterns with WHERE clauses referencing nullable-side columns catches most suspicious SQL before testing begins.

— Running EXCEPT difference queries between old and rewritten SQL on identical test data verifies that no semantic change was introduced.

Conclusions

A migration project's real value is often not the new platform itself, but the forced audit of legacy SQL that exposes years-old semantic bugs no one had ever reconciled.

Optimizer behavior is consistent across databases; what differs is the tooling and community knowledge available to diagnose it quickly, which is where KES's EXPLAIN visibility and community knowledge base provide an edge.

The hardest traps to spot are cross-layer ones where a view's internal LEFT JOIN looks correct, but an outer WHERE clause on the calling query reversely eliminates it after the optimizer inlines the view.

Transitive elimination across nested joins can cascade: one null-rejecting condition on t3 can eliminate outer joins on both t2→t3 and t1→t2, multiplying the data loss.

Concepts & terms

Outer Join Elimination

An optimizer rewrite that converts a LEFT JOIN or RIGHT JOIN into an INNER JOIN when a WHERE clause contains a null-rejecting condition on the nullable side, because the two are logically equivalent under relational algebra.

Null-Rejecting Condition

A condition that evaluates to False or Unknown when any of its operands is NULL. Examples include `column = 'value'`, `column > 100`, and `column IN ('A','B')`. Such conditions cause outer join elimination when applied to the nullable side of an outer join.

Strict Function

A function that returns NULL whenever any of its input arguments is NULL. Most SQL built-in functions like UPPER(), LOWER(), and arithmetic operators are strict, meaning wrapping a nullable column in a strict function and filtering on it still triggers outer join elimination.

View Inline / Query Rewrite

An optimization where the database engine expands a view reference into its underlying query definition before planning, allowing outer WHERE clauses to interact directly with the view's internal join logic and potentially trigger outer join elimination across layers.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗