The report is 40% missing: the LEFT JOIN that has never been questioned was finally exposed when migrating to KES
Last Friday, around four o'clock in the afternoon, people from the operations side started looking for me in the group. The question I'm asking is this: "Why is the total number of user reports today 40% less than yesterday?"
What I first thought about was whether one of the scheduled tasks failed to run successfully. I went to read the log. After browsing for a long time, I didn't see any errors. Then I looked at the core statistical SQL. The syntax looks fine, and no exceptions are thrown during execution. And when I run it in my local test environment, the results are correct. But the data produced is less.
It took me two hours to finally find the reason. What is the reason? It is a LEFT JOIN that is written incorrectly. It triggers the optimizer's elimination of outer connections and directly filters out part of the data without prompting. An even more helpless situation is that this SQL actually ran like this in the MySQL I used before. It’s just that no one checked the accounts carefully before, so this problem never came to light.
This kind of pitfall is actually the most frequent one I encountered recently in the project of migrating MySQL and PostgreSQL to KES. The situation often goes like this:The SQL syntax can run smoothly, and no errors will be reported when executed. You can't tell it during testing, but the actual results on the business side are missing something. So in this article, I will dismantle LEFT JOIN from front to back. By the way, make a list of pitfalls to avoid. That is to give everyone a reference when encountering this situation in the future.
@[toc]
1. Accident Review: Reconciliation Issues Caused by a SQL
Let me first talk about something that actually happened to me. The background of that project was a provincial government affairs platform. They were previously using MySQL 8.0. Recently, we have to switch to the KES database as a whole, and the version is V9R1C10. As a result, in the first week after the business was launched, the operations side said that the total number of data items in the "User Order Comprehensive Report" was almost 40% less than the previous old system.
I took the problematic SQL and desensitized it, simplifying it to look like this:
-- 业务需求：查询所有用户，并附带展示其"已完成状态"的订单信息；
-- 如果用户没有已完成订单，也需要显示该用户，订单相关字段为 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';
If you just look at this code, you can throw this SQL into MySQL, PostgreSQL, or KES, and it will come out. There is no problem with the syntax. However, the size of the result set it ran out in MySQL 8.0 and Jincang KES is actuallyexactly the sameof. Yes, you read that right. The problem here is not actually "the two sides behave differently after the migration." The real question is,This SQL was wrong from the beginning when it was written.. It's just that in the old system, business people never discovered this problem.
Later, what convinced me that there was a problem was that I saw a matching reconciliation SQL running in the old system:
-- 原系统另一处对账逻辑
SELECT COUNT(DISTINCT u.user_id)
FROM t_user u
LEFT JOIN t_order o ON u.user_id = o.user_id;
What this SQL finds out is the total number of users. Why? because it's not there WHERE Add any nullable-side conditions there. In this case,LEFT JOIN The original meaning is still there. This is why people who did business in the past always felt that "the total number of users is correct", but the report always had "little data". In fact, these two SQLs have taken different logical paths. It's just that no one has followed this logic before.
**Let me start with the conclusion: this is really not a matter of KES optimizing itself too much. But the original way of writing has a problem in itself, which is a semantic problem such as "outer connections are secretly eliminated". In fact, KES, MySQL and PG handle this path in exactly the same way. **It’s just because when doing migration, everyone’s eyes are fixed on the new database. The business side thought, oh, there is something wrong with the new database.
2. The underlying mechanism: why LEFT JOIN "turns" into INNER JOIN
To explain this pit clearly, one thing must be said first.If you look at it at the level of relational algebra, you write LEFT JOIN Then followed by WHERE To filter the condition that the right table cannot be empty, you can write this directly INNER JOIN With the same conditions, they are equal. As long as the optimizer sees that these two things are the same, it will change the outer connection to an inner connection and run it. There is a name for this operation, it’s calledEliminate outer joins。
1. SQL execution order illusion
Many people think that the execution order of SQL is like this:
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
If you only talk about the "logical meaning" of this sequence, then it is indeed the case. In other words, execute first LEFT JOIN. It will retain all the rows of the table on the left, and fill in the rows on the right that do not match with NULL. then it's turn WHERE , and then filter this result set.
Then there will be a problem.WHERE o.order_status = 'FINISHED' What will happen if this condition encounters those "NULL rows filled out by outer joins"?
The situation is this,NULL = 'FINISHED' In three-valued logic, the result is Unknown. and WHERE Well, it saw Unknown The rows are directly filtered out.This leads to a result that all NULL rows filled in by outer connections are wiped out.
So what's left in the end? All that's left are the rows that actually matched and the order_status was 'FINISHED'. Think about it, isn't this INNER JOIN What do you mean?
2. "Equivalent rewriting" of the optimizer
Before the database optimizer calculates the cost, it will actually do one step first logical equivalent transformation things. Go and scan it WHERE Take that list of conditions and see if there is any condition that can prove that "NULL rows complemented by outer joins are incorrect". There are roughly two rules for its judgment:
- Conditioning object: The columns used in the condition must come from Nullable-Side (that is, the side of the outer connection that can be null);
- Conditional behavior towards NULL: When this condition encounters NULL, it must return False or Unknown. This is what people call "null-rejecting".
Take our SQL as an example,o.order_status = 'FINISHED' This sentence happens to cover both of these two items. It uses the right column, and it excludes NULL.
So, the optimizer directly takes action and LEFT JOIN changed to INNER JOIN. After the modification, it goes down to the actual execution paths of Hash Join or Nested Loop.
This is the real reason why the data is missing: it’s not that the data is really gone, but that according to the semantics of SQL, the data should not be in the result set in the first place.
3. Consistent attitude among mainstream databases
It should be mentioned here that this is really not an "original" gameplay of some domestic database. Basically, as long as it is a relational database that supports the SQL:2008 standard, it will perform this optimization. MySQL, PostgreSQL, Oracle, SQL Server, and KES all do outer join elimination. Because they all have to follow the rules of the standard SQL engine.
The only difference is when the optimizer "discovers" this equivalence. For example, when you use a subquery, or a view, or a UDF, different databases have different recognition capabilities. This leads to the kind of pitfalls we will talk about later that are particularly difficult to find in a production environment.
3. KES compatibility design: returning "intention" to the business
If you look purely from the perspective of "compliance with SQL standards", it is absolutely correct that the above SQL is optimized into INNER JOIN. But from the perspective of "migration and transformation projects", there is actually a very critical product design trade-off -Should the new library reproduce the wrong behavior of the old library?
I personally highly approve of the design choices made by Jinchang KES here:Strictly abides by SQL standard semantics and never covers up erroneous SQL "accidentally". At the same time, it provides extremely complete execution plan visualization capabilities, allowing developers to expose semantic traps during the transformation stage.
1. Use EXPLAIN to immediately find out the eliminated outer connections
In Jincang KES, I will use it immediately EXPLAIN or EXPLAIN ANALYZE Take a look at 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 connection is eliminated, you will see keywords in the returned execution planno Hash Left Join or Nested Loop Left Join, but directly Hash Join(inner join) or Nested Loop. This observation point alone is enough to discover most cases of outer connection elimination during the migration code audit phase.
-- 被消除后的执行计划关键行（示意）
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')
Please note the above Filter: (order_status = 'FINISHED') It has been pushed down to the scan node of t_order, and the JOIN node is directly marked as Hash Join——The outer connection disappears completely.
2. Hidden behavior when explicitly completing the IS NULL condition
There is a very common way of writing business, which isDeliberately use 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 in KES isWon'twas eliminated for a simple reason:IS NULL The condition is exactly to catch "NULL rows of outer join complement". If the optimizer forcibly rewrites it as an inner connection, the business semantics will be directly destroyed. KES's optimizer strictly adheres to the boundaries of the decision rules here:As long as the WHERE condition is not null-rejecting, the outer join is not eliminated.
What does this detail mean? Description The optimizer implementation of KES isSemantic security first, rather than the extensive mode of "optimizing if you can". This is very important in localization migration - it means that the SQL you write correctly in the old system will also run correctly in KES; if you write the wrong SQL, KES will treat it in the same way as mainstream databases, and will not sacrifice standards just because it "want to appear more compatible".
3. Use the ON clause: leave filtering to "join rules"
Going back to the report SQL at the beginning of this article, the real intention of the business side is "all users must appear, and only order information will be displayed when there are matching completed orders." The correct way to write it is to push the filter condition down to ON In the clause:
-- 正确写法：ON 子句里过滤右表，左表所有行保留
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:ON The clause controls "how to connect",WHERE The clause controls "which rows are left in the end". ON Riga filtering is equivalent to "filter t_order to only FINISHED, and then make an outer connection with t_user" - all rows in the user table are completely retained. Even if the corresponding order does not meet the conditions, it will appear in the result set as NULL.
After this article is rewritten, the business reconciliation will immediately return to the correct state.
4. Six high-frequency "outer connection elimination" pitfalls when migrating from MySQL/PG to KES
In addition to the most typical "filtering the right table column in WHERE", there are a large number of variant writing methods in actual projects that will trigger the elimination of outer joins. The following six are high-frequency traps that I have seen repeatedly in migration projects in the past six months.
Pitfall 1: Using non-null comparison operators
-- 反例：>、<、!=、LIKE 等对 NULL 都返回 Unknown
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.amount > 100;
t2.amount > 100 Unknown is also returned for NULL values, triggering the elimination of outer connections. This type of writing is extremely common in report SQL.
Trap 2: Function-wrapped right table column
-- 反例：函数结果同样是 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" - that is, it returns NULL output when encountering NULL input - outer joins will still be eliminated. Most system functions in KES are strict functions.
Trap 3: IN / BETWEEN clause
-- 反例：t2.status 为 NULL 时不会命中任何列表值
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.status IN ('A', 'B', 'C');
IN Essentially multiple = The OR, NULL and any value IN are judged to be Unknown. Elimination is also triggered.
Trap 4: Mixing IS NULL with other right table conditions
-- 反例：AND 一旦包含 null-rejecting 条件，整体也变成 null-rejecting
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.status = 'A' AND t2.other_col IS NULL;
It seems there is IS NULL Is it "safe"? No. entire AND The expression must be at least likely to return True on NULL input in order not to trigger elimination. here t2.status = 'A' All NULLs have been rejected, resulting in the whole being still eliminated.
Pitfall 5: "Transitive elimination" of multi-level nested JOINs
-- 反例：t3 的过滤条件传染到 t2，间接把 t1→t2 的外连接也消除掉
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' Eliminate directly t2 LEFT JOIN t3;once t2 → t3 Become an inner join,t2.id It becomes a not-null join key, which will be further eliminated. t1 → t2 outer join. Eliminating two outer joins with one condition will cause heavy losses.
Trap 6: Outer joins in views/subqueries are eliminated by the outer WHERE reverse
-- 反例：v_user_order 内部是 LEFT JOIN，外层 WHERE 反向消除
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 there is an outer connection inside the view, the outer WHERE has a nullable-side condition. Once the optimizer "sees through" the view (View Inline / Query Rewrite), the outer connection will still be eliminated. This type of "cross-layer trap" is the most difficult to troubleshoot, because the view definition seems to be correct, but the problem lies in the calling method on the business side.
5. A practical pit avoidance list
Based on the previous analysis, I compiled the "outer connection elimination" troubleshooting actions that are performed daily during migration and transformation into a list that can be implemented directly. I have run this list in several projects, and it can intercept more than 90% of similar semantic traps during the transformation stage.
1. Static code scanning: intercept the "suspicious elimination" writing method
In the CI/CD pipeline, you can use scripts to perform regular scans on existing SQL and output a list of suspicious SQL that "contains LEFT/RIGHT JOIN and the WHERE clause refers to a Nullable-Side column (and is not IS NULL)". The simplified version of the regular idea I commonly use in projects is:
# 匹配 LEFT JOIN ... WHERE ... right_alias.col = 之类的模式
LEFT\s+JOIN\s+(\w+)\s+(\w+).*?WHERE.*?\2\.\w+\s*(?!IS\s+NULL)
This step does not pursue 100% accuracy; the purpose isFirst circle the suspicious list, hand it over to the DBA and the business for joint review.
2. EXPLAIN sampling verification
Suspicious SQL sampling of static scan output, executed in KES EXPLAIN, focus on:
- Does the JOIN node keyword contain
Left/Right； - Whether the Filter condition of the Nullable-Side table is pushed down to the scan node;
- If the keyword is normal
Hash Join/Nested Loop, confirm that the outer connection has been eliminated.
For SQL where the confirmation is eliminated, but the business intention really needs to retain the outer connection, proceed to the next step to rewrite.
3. Rewrite the rules: ON clause vs. COALESCE
In most scenarios, it is recommended to push the filter condition down to ON：
-- 改写前
LEFT JOIN t2 ON t1.id = t2.id WHERE t2.status = 'A'
-- 改写后
LEFT JOIN t2 ON t1.id = t2.id AND t2.status = 'A'
If the business logic is complex and the conditions cannot be simply pushed down (for example, the conditions involve CASE expressions of mixed columns of the left and right tables), you can use COALESCE Manually construct a "NULL-safe" comparison:
-- 用 COALESCE 处理 NULL 值场景
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE COALESCE(t2.status, 'A') = 'A';
COALESCE(t2.status, 'A') 'A' is returned when NULL is encountered, and the condition returns True for NULL input - this is the standard "null-preserving" writing method, and the KES optimizer will recognize that it is not null-rejecting, soDo not eliminateouter join.
4. Encapsulation principles of views and subqueries
For outer joins in views/CTEs, maintain one principle:The caller is not allowed to perform null-rejecting filtering on the Nullable-Side columns output by the view in the outer WHERE. If filtering is necessary, use it inside the view COALESCE Surround NULL, or output an extra one in the view definition is_matched Boolean flag column, let the caller filter using this flag column to avoid direct exposure of nullable-side.
5. Production environment regression testing
The key action after the transformation is completed is to put the SQL before and after the transformation inThe same test dataon execution, do EXCEPT Difference set comparison:
-- 差集验证：改写前后结果集是否完全一致
(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 means that the rewriting has introduced semantic changes and needs to be reviewed.
6. Take the "companion" channel of Jincang Community
Frankly speaking, the elimination of outer joins is just one of dozens of semantic traps in localization migration. Every time you encounter a pitfall, if you only rely on your own team to search for documents, the efficiency will be very low. The habit I have developed in the past six months is - when encountering difficult and complicated diseases, I first go to the Jincang community bbs.kingbase.com.cn to see if there are any posts about similar problems. Many times, the original R&D team or seniors have already kept records of how to avoid pitfalls. Not long ago, Jincang Community also launched the "Travelers Program" to encourage front-line engineers to feed back the migration scenarios and optimization cases of their companies back to the community. I personally feel that this model of "community co-construction → collective experience accumulation" is very valuable for the localization implementation stage, especially for pitfalls such as "external connection elimination" that "official documents are written correctly, but are easiest to step on in actual practice". Word of mouth from the front line is much faster than reading the product manual by yourself.
6. Summary
Let’s go back to the situation mentioned at the beginning of the article. That's when the report was missing 40% of the data. After I put the modified SQL online, the business side was very satisfied with the new report. They even came to me and asked me if this new database was smarter and why the old system couldn't calculate this number. After listening to it, I just smiled and didn’t say much. Actually, the old system always ran wrong data. It’s just that no one has carefully figured out the details of the reconciliation before. For Jincang KES, it runs according to SQL standards. As a result, semantic bugs that were previously hidden deeply were exposed. This happened during this localization transformation.
I usually do this kind of migration and transformation myself. So aboutEliminate outer joinsI personally have three thoughts on this matter. Let me summarize it for you.
-
This matter is really not a problem with the database. To put it bluntly, the SQL semantics need to be carefully controlled.. No matter which library you want to move to. When modifying, you have to check the semantics of outer joins. Jinchang KES strictly adheres to SQL standards. The results it runs are basically the same as those of mainstream libraries. In this case, there will be no data problems when you change platforms.
-
The most reliable way is to look at the execution plan. No matter how detailed the document is, it’s useless. It’s useless no matter how standard the code looks. In the end, how SQL runs depends on the keywords in the execution plan. When debugging, develop the habit of typing EXPLAIN first. This habit can save you a lot of trouble. If there is a production problem in the future, troubleshooting will be much faster.
-
Domestic replacement is often just an excuse. In fact, it just makes you read the old code again.. The optimizer of Jincang KES is more rigorous in processing semantics. It is very strict about standards compatibility. And it comes with many diagnostic tools. This actually forces us to re-examine the previous stock code. The semantic bugs that no one noticed in the old system before will be found for you during this migration. This is actually not a bad thing. I think this is a more practical use of this localization transformation.
External connections eliminate this point, which is actually only a small part of the localization migration. I will continue to write later. Post all the various migration pitfalls and optimization methods encountered. That is to say, I hope it can give some reference to brothers who are doing the same thing now. If everyone reads it, you may avoid a few pitfalls.