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

The Report Was Missing 40% of Its Data: That LEFT JOIN No One Ever Questioned Finally Got Exposed During the KES Migration

Last Friday, around 4 PM, the operations team started pinging me in the group chat. They asked, "Why is the total count in today's user report nearly 40% lower than yesterday's?"

My first thought was that a scheduled task hadn't run successfully. I went through the logs but found no errors. Then I checked the core statistical SQL. The syntax looked fine, and it executed without throwing any exceptions. Even when I ran it in my local test environment, the results were correct. But in production, the data was just missing.

After two hours, I finally found the cause. What was it? A LEFT JOIN written incorrectly. It triggered the optimizer's outer join elimination, which silently filtered out a chunk of the data. What made it more frustrating was that this SQL had been running the same way in the old MySQL system. It's just that no one had ever carefully reconciled the accounts before, so the problem stayed hidden.

This kind of pitfall is actually the most frequent one I've encountered in my recent project migrating from MySQL and PostgreSQL to KES. The situation often goes like this: The SQL syntax runs fine, execution throws no errors, you can't spot it during testing, but the actual business results are just missing something. So, in this article, I'll break down the LEFT JOIN issue from start to finish and provide a checklist of pitfalls to avoid, so you'll have a reference if you run into this situation later.

@[toc]

1. Incident Review: A Reconciliation Problem Caused by One SQL Statement

Let me start with a real case I encountered. The project background was a provincial-level government affairs platform. They previously used MySQL 8.0 and were recently migrating entirely to KingbaseES (KES), version V9R1C10. In the first week after going live, the operations team reported that the total number of records in the "User Order Comprehensive Report" was about 40% less than in the old system.

I took the problematic SQL, desensitized it, and simplified it to something like this:

-- Business requirement: Query all users and display their "completed" order information alongside.
-- If a user has no completed orders, the user should still be shown, with order-related fields as NULL.
SELECT u.user_id,
       u.user_name,
       o.order_id,
       o.order_amount,
       o.order_status
FROM   t_user u
LEFT   JOIN t_order o
       ON  u.user_id = o.user_id
WHERE  o.order_status = 'FINISHED';

Just looking at this code, you could throw this SQL into MySQL, PostgreSQL, or KES, and it would run; the syntax is fine. However, the result set size it produced was exactly the same in MySQL 8.0 and KingbaseES KES. Yes, you read that right. The problem here wasn't that "the behavior differed after migration." The real issue was that this SQL was written incorrectly from the start. It's just that in the old system, the business people had never discovered this problem.

What confirmed the issue for me was finding a companion reconciliation SQL still running in the old system:

-- Another reconciliation logic in the original system
SELECT COUNT(DISTINCT u.user_id)
FROM   t_user u
LEFT   JOIN t_order o ON u.user_id = o.user_id;

This SQL queried the total number of all users. Why? Because it didn't add any conditions on the nullable-side in the WHERE clause. Thus, the original intent of LEFT JOIN was preserved. This is why the business people always thought "the total user count column is correct," but the report was consistently "missing data." Essentially, these two SQL statements followed different logical paths. It's just that no one had ever traced the logic to check before.

Let me state the conclusion first: This really wasn't KES optimizing too aggressively. The original writing itself had a semantic flaw: the "outer join being silently eliminated." In fact, KES, MySQL, and PG handle this path in exactly the same way. It's just that during the migration, everyone's eyes were on the new database, so the business side assumed the new database was at fault.

2. Underlying Mechanism: Why a LEFT JOIN "Becomes" an INNER JOIN

To explain this pitfall clearly, one thing must be stated first. From a relational algebra perspective, writing a LEFT JOIN followed by a WHERE clause that filters for non-null conditions on the right table is logically equivalent to writing an INNER JOIN with the same conditions. As soon as the optimizer recognizes these two are equivalent, it rewrites the outer join into an inner join for execution. This operation is called Outer Join Elimination.

1. The Illusion of SQL Execution Order

Many people think the SQL execution order is:

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

If we're only talking about "logical meaning," this order is indeed correct. That is, LEFT JOIN is executed first, keeping all rows from the left table and filling unmatched rows from the right table with NULLs. Then, WHERE filters this result set.

So here's the problem. What happens when the condition WHERE o.order_status = 'FINISHED' encounters those "NULL rows padded by the outer join"?

The situation is this: NULL = 'FINISHED' in three-valued logic evaluates to Unknown. And WHERE filters out any row where the condition is Unknown. This results in all those NULL rows padded by the outer join being completely wiped out.

So what's left? Only the rows that "actually matched and also have order_status = 'FINISHED'." Think about it—isn't that exactly what an INNER JOIN does?

2. The Optimizer's "Equivalent Rewrite"

Before calculating costs, the database optimizer performs a step called logical equivalence transformation. It scans the WHERE clause conditions to see if any can prove that "the NULL rows padded by the outer join are invalid." Its judgment rules are roughly these two:

Condition Target: The columns used in the condition must come from the Nullable-Side (the side of the outer join that can be NULL);
Condition's Attitude Towards NULL: This condition must evaluate to False or Unknown when encountering NULL. This is what's known as a "null-rejecting" condition.

For our SQL, o.order_status = 'FINISHED' meets both criteria. It uses a column from the right side, and it rejects NULLs.

Thus, the optimizer directly rewrites the LEFT JOIN into an INNER JOIN. After the rewrite, it proceeds with actual execution paths like Hash Join or Nested Loop.

This is the real reason the data disappeared: not that the data was actually lost, but that according to the SQL semantics, that data shouldn't have been in the result set in the first place.

3. The Consistent Stance of Mainstream Databases

It's worth mentioning that this is not some "unique" behavior invented by a domestic database. Essentially any relational database supporting the SQL:2008 standard performs this optimization. MySQL, PostgreSQL, Oracle, SQL Server, and KingbaseES KES all perform outer join elimination because they must follow the rules of a standard SQL engine.

The only difference lies in when the optimizer can "discover" this equivalence. For example, when you use subqueries, nested views, or UDFs, different databases have varying capabilities to recognize it. This leads to the kind of pitfalls we'll discuss later that are particularly hard to detect in production environments.

3. KES's Compatibility Design: Returning "Intent" to the Business

From a purely "SQL standard compliance" perspective, optimizing the above SQL into an INNER JOIN is completely correct. But from a "migration and re-engineering project" perspective, there's a very critical product design choice here—Should the new database replicate the erroneous behavior of the old one?

KingbaseES KES's design choice here is one I personally highly approve of: Strictly adhere to standard SQL semantics, never accidentally "bailing out" incorrect SQL, while providing extremely comprehensive execution plan visualization capabilities, allowing developers to expose semantic traps during the re-engineering phase.

1. Instantly Spot Eliminated Outer Joins with EXPLAIN

In KingbaseES KES, my first move is always to use EXPLAIN or EXPLAIN ANALYZE to view the execution plan:

EXPLAIN ANALYZE
SELECT u.user_id, u.user_name, o.order_id, o.order_amount, o.order_status
FROM   t_user u
LEFT   JOIN t_order o ON u.user_id = o.user_id
WHERE  o.order_status = 'FINISHED';

If the outer join is eliminated, the returned execution plan will show keywords that are not Hash Left Join or Nested Loop Left Join, but rather a plain Hash Join (inner join) or Nested Loop. This single observation point is enough to discover the vast majority of outer join elimination cases during the migration code audit phase.

-- Key lines of the execution plan after elimination (illustrative)
Hash Join  (cost=... rows=...)
  Hash Cond: (u.user_id = o.user_id)
  ->  Seq Scan on t_user u
  ->  Hash
        ->  Seq Scan on t_order o
              Filter: (order_status = 'FINISHED')

Note the Filter: (order_status = 'FINISHED') above has been pushed down to the scan node of t_order, and the JOIN node is directly marked as Hash Join—the outer join has disappeared cleanly.

2. The Fallback Behavior When Explicitly Adding IS NULL Conditions

There's a very common business pattern that intentionally uses LEFT JOIN to find "unmatched" records, such as "which users have never placed an order":

SELECT u.user_id, u.user_name
FROM   t_user u
LEFT   JOIN t_order o ON u.user_id = o.user_id
WHERE  o.order_id IS NULL;

This SQL will not be eliminated in KES. The reason is simple: the IS NULL condition is precisely meant to capture the "NULL rows padded by the outer join." If the optimizer forcibly rewrote it as an inner join, the business semantics would be destroyed. KES's optimizer strictly adheres to the boundary rule here: As long as the WHERE condition is not null-rejecting, the outer join is not eliminated.

What does this detail signify? It shows that KES's optimizer implementation prioritizes semantic safety over a crude "optimize whenever possible" approach. This is very important in domestic migration—it means SQL you wrote correctly in the old system will definitely run correctly in KES; incorrectly written SQL will be treated by KES in a manner consistent with mainstream databases, without sacrificing standards to "appear more compatible."

3. Moving Conditions to the ON Clause: Leaving Filtering to the "Join Rule"

Returning to the report SQL from the beginning of this article, the business's true intent was "all users must appear, only showing order information when there is a matching completed order." The correct way to write it is to push the filter condition down into the ON clause:

-- Correct way: Filter the right table in the ON clause, keeping all rows from the left table.
SELECT u.user_id,
       u.user_name,
       o.order_id,
       o.order_amount,
       o.order_status
FROM   t_user u
LEFT   JOIN t_order o
       ON  u.user_id = o.user_id
       AND o.order_status = 'FINISHED';

The semantic difference here is crucial: The ON clause controls "how to join," while the WHERE clause controls "which rows to ultimately keep." Adding a filter in ON is equivalent to "first filtering t_order to only FINISHED orders, then outer joining it with t_user"—all rows from the user table are fully preserved. Even if the corresponding order doesn't meet the condition, it will appear with NULLs in the result set.

After this rewrite, the business reconciliation immediately returned to the correct state.

4. Six High-Frequency "Outer Join Elimination" Traps in MySQL/PG to KES Migration

Beyond the most typical "filtering right table columns in WHERE," there are numerous variant patterns in real projects that trigger outer join elimination. The following six are high-frequency traps I've repeatedly seen in migration projects over the past six months.

Trap 1: Using Non-Equality Comparison Operators

-- Anti-pattern: >, <, !=, LIKE, etc., all return Unknown for NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE  t2.amount > 100;

t2.amount > 100 also returns Unknown for NULL values, triggering outer join elimination. This kind of pattern is extremely common in report SQL.

Trap 2: Function-Wrapped Right Table Columns

-- Anti-pattern: Function results are also NULL
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE  UPPER(t2.name) = 'A';

As long as the function is a "strict function"—meaning it returns NULL for NULL input—the outer join will still be eliminated. The vast majority of system functions in KES are strict functions.

Trap 3: IN / BETWEEN Clauses

-- Anti-pattern: When t2.status is NULL, it won't match any value in the list
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE  t2.status IN ('A', 'B', 'C');

IN is essentially multiple = ORs. NULL compared with any value using IN results in Unknown. This also triggers elimination.

Trap 4: Mixing IS NULL with Other Right Table Conditions

-- Anti-pattern: Once an AND includes a null-rejecting condition, the whole expression becomes null-rejecting
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE  t2.status = 'A' AND t2.other_col IS NULL;

Looks like having IS NULL makes it "safe"? Not so. The entire AND expression must have at least a possibility of returning True for NULL input to avoid triggering elimination. Here, t2.status = 'A' has already rejected all NULLs, causing the whole expression to still be eliminated.

Trap 5: "Transitive Elimination" in Multi-Level Nested JOINs

-- Anti-pattern: The filter condition on t3 infects t2, indirectly eliminating the outer join from t1 to t2 as well.
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
WHERE t3.status = 'A';

In this SQL, t3.status = 'A' directly eliminates t2 LEFT JOIN t3; once t2 → t3 becomes an inner join, t2.id becomes a not-null join key, which further eliminates the outer join from t1 → t2. One condition eliminates two outer joins—a heavy loss.

Trap 6: Outer Joins Inside Views/Subqueries Reversely Eliminated by Outer WHERE

-- Anti-pattern: v_user_order internally uses LEFT JOIN, but the outer WHERE reversely eliminates it.
CREATE VIEW v_user_order AS
SELECT u.user_id, u.user_name, o.order_id, o.order_amount
FROM   t_user u LEFT JOIN t_order o ON u.user_id = o.user_id;

SELECT * FROM v_user_order WHERE order_amount > 500;

Although the view internally uses an outer join, the outer WHERE clause carries a nullable-side condition. Once the optimizer "sees through" the view (View Inline / Query Rewrite), the outer join will still be eliminated. This kind of "cross-layer trap" is the hardest to troubleshoot because the view definition looks correct; the problem lies in how the business side invokes it.

5. A Practical-Level Pitfall Avoidance Checklist

Combining the analysis above, I've organized the "outer join elimination" troubleshooting steps I routinely perform during migration re-engineering into a directly actionable checklist. I've run this checklist in several projects, and it can intercept over 90% of similar semantic traps during the re-engineering phase.

1. Static Code Scanning: Intercepting "Suspected Elimination" Patterns

In the CI/CD pipeline, scripts can use regex to scan existing SQL and output a list of suspicious SQL statements that "contain LEFT/RIGHT JOIN and have a WHERE clause referencing Nullable-Side columns (that are not IS NULL)." A simplified regex approach I often use in projects is:

# Match patterns like LEFT JOIN ... WHERE ... right_alias.col = ...
LEFT\s+JOIN\s+(\w+)\s+(\w+).*?WHERE.*?\2\.\w+\s*(?!IS\s+NULL)

This step doesn't aim for 100% accuracy; its purpose is to first circle a list of suspects for joint review by DBAs and the business team.

2. EXPLAIN Sampling Verification

Sample the suspicious SQL output from the static scan and execute EXPLAIN in KES, focusing on:

Whether the JOIN node keywords include Left / Right;
Whether Filter conditions on the Nullable-Side table have been pushed down to the scan node;
If the keywords are plain Hash Join / Nested Loop, confirm the outer join has been eliminated.

For SQL confirmed as eliminated but where the business intent genuinely requires preserving the outer join, proceed to the next step for rewriting.

3. Rewriting Rules: ON Clause vs. COALESCE

In most scenarios, pushing filter conditions down to ON is recommended:

-- Before rewrite
LEFT JOIN t2 ON t1.id = t2.id WHERE t2.status = 'A'

-- After rewrite
LEFT JOIN t2 ON t1.id = t2.id AND t2.status = 'A'

If the business logic is complex and conditions cannot be simply pushed down (e.g., conditions involving CASE expressions with mixed columns from left and right tables), you can use COALESCE to manually construct a "NULL-safe" comparison:

-- Use COALESCE to handle NULL value scenarios
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE  COALESCE(t2.status, 'A') = 'A';

COALESCE(t2.status, 'A') returns 'A' when encountering NULL, so the condition returns True for NULL input—this is a standard "null-preserving" pattern. The KES optimizer will recognize it as not null-rejecting and thus will not eliminate the outer join.

4. Encapsulation Principles for Views and Subqueries

For outer joins inside views / CTEs, maintain one principle: Do not allow callers to perform null-rejecting filtering on the Nullable-Side columns output by the view in the outer WHERE clause. If filtering is necessary, the view should internally use COALESCE to handle NULLs, or the view definition should additionally output an is_matched boolean flag column, allowing callers to filter using this flag column, thus avoiding direct exposure of the nullable-side.

5. Production Regression Testing

A key action after completing the rewrite is to execute the SQL before and after the rewrite on the same test data and perform an EXCEPT difference comparison:

-- Difference verification: Are the result sets before and after the rewrite completely consistent?
(SELECT * FROM query_before)
EXCEPT
(SELECT * FROM query_after);

(SELECT * FROM query_after)
EXCEPT
(SELECT * FROM query_before);

If either side returns non-empty, it indicates the rewrite introduced a semantic change and requires review.

6. Leveraging the Kingbase Community's "Fellow Traveler" Channel

Frankly, outer join elimination is just one of dozens of semantic traps in domestic database migration. If your team relies solely on searching documentation internally every time you hit a pitfall, the efficiency is very low. A habit I've developed over the past six months is—when encountering tricky problems, first check the Kingbase community (bbs.kingbase.com.cn) to see if there are posts about similar issues. Often, the original R&D team or senior engineers have already left pitfall avoidance records. Recently, the Kingbase community also launched the "Fellow Traveler Program," encouraging frontline engineers to feed back their enterprise migration scenarios and optimization cases to the community. I personally find this model of "community co-building → collective experience accumulation" extremely valuable for the domestic adoption phase, especially for pitfalls like "outer join elimination" which are "correctly documented officially but easiest to step into in practice." Word-of-mouth from the front lines is much faster than grinding through product manuals alone.

6. Summary

Let's return to the situation mentioned at the beginning of the article—the time the report was missing 40% of its data. After I deployed the corrected SQL, the business side was very satisfied with the new report. They even came to ask me if this new database was smarter, wondering why the old system couldn't calculate this number. I just smiled and didn't say much at the time. Actually, the old system had been running incorrect data all along. It's just that no one had ever meticulously scrutinized the reconciliation criteria before. KingbaseES KES simply ran according to the SQL standard. As a result, it exposed a semantic bug that had been deeply hidden for a long time. This just happened to coincide with this domestic replacement project.

I personally do this kind of migration and re-engineering quite often. So regarding outer join elimination, I have three personal thoughts to summarize.

This really isn't a database problem; bluntly put, SQL semantics must be strictly controlled. No matter which database you're migrating to, during the re-engineering, you must review the semantics of outer joins. KingbaseES KES strictly adheres to the SQL standard. Its results are basically the same as mainstream databases. This way, data won't have issues when you switch platforms.
Checking the execution plan is the most reliable method. No matter how detailed the documentation is, or how standard the code looks, to see how the SQL actually runs, you must look at the keywords in the execution plan. For daily debugging, develop the habit of typing EXPLAIN first. This habit can save you a lot of trouble and makes troubleshooting production issues much faster later on.
Domestic replacement is often just an excuse; it's really about forcing you to re-examine old code. KingbaseES KES's optimizer is quite rigorous in handling semantics. It strictly enforces standard compatibility and comes with many built-in diagnostic tools. This essentially forces us to re-organize our existing legacy code. Semantic bugs in the old system that no one ever noticed are all being found during this migration. This isn't a bad thing. I think this is actually a very practical benefit of this domestic replacement project.

Outer join elimination is actually just a small part of domestic database migration. I'll continue writing afterward, publishing various migration pitfalls and optimization methods I encounter. I hope it can serve as a reference for peers currently doing the same work, so they might step on a few fewer pitfalls.