跪拜 Guibai
← Back to the summary

Nine SQL Pitfalls That Break Silently During Database Migration

SQL Syntax Is Fully Compatible, but the Results Are Different — The Nine Most Troublesome Hidden Risks in Domestic Migration

There is one type of migration problem that is actually quite a headache. It doesn't report errors, and there are no warnings. The SQL finishes running, and it might even pop up a prompt saying "Execution successful, X rows affected." But then, the business side compares the data from the new and old systems and finds it's just wrong. A few rows are missing, or a few extra rows appear, or the values in a certain column are off. At this point, you go check the syntax, the character set, the time zone, and the connection pool. After investigating for a long time, you find nothing.

This type of problem is particularly troublesome to investigate. Why? Because you need to understand three things at the same time: the semantic standard of SQL execution, how the optimizer runs, and the differences between different databases. Missing any one of these won't work. After doing several migration projects, I organized the error-free problems I encountered. There are nine categories in total.

This article is what I've organized. For each of these nine pitfalls, I've provided a case study and a cause analysis. I also explain how they manifest in KingbaseES (KES) and how to fix them. I've tried to make this piece something you can use directly as a pitfall-avoidance manual.

image.png

@[toc]

1. Nine SQL Logic Pitfalls That Are Easy to Encounter

First, let's look at the overall situation. I've divided these nine types of pitfalls into three major groups based on "at which level they operate":

Category A: Optimizer Behavior Differences (3 items)

Category B: SQL Semantic Standard Differences (3 items)

Category C: Function and Expression Behavior Differences (3 items)

Let's go through them one by one.

2. Category A: Optimizer Behavior Differences

Optimizer behavior differences are the hardest to prevent in advance during domestic migration. It's not that the syntax is written wrong. It's not that the data has problems. It's simply that the database itself thinks running it this way will be faster and makes a decision on its own. Different databases have different standards for judging how to run faster. This leads to differences in the final results.

A1 Outer Join Elimination

Manifestation: SQL with LEFT JOIN or RIGHT JOIN, after migrating to KES, returns a result set with many fewer rows.

Cause: The WHERE condition in the SQL applies a NULL-rejecting filter to a column on the nullable side. For example, writing = 'A' or > 100. The KES optimizer sees this and realizes the SQL is actually the same as an inner join. So it rewrites the outer join into an inner join to execute. Then, those left-table records that were originally retained by the outer join to fill with NULLs are all filtered out by the WHERE clause.

Pitfall Case:

-- Intent: All users, with completed order info displayed alongside
SELECT u.user_id, o.order_amount
FROM   t_user u
LEFT   JOIN t_order o ON u.user_id = o.user_id
WHERE  o.order_status = 'FINISHED';

The intent of this code is "all users must appear, only FINISHED orders should be displayed." But the actual result becomes "only users who have FINISHED orders appear."

Rewrite Solution: Move the condition into the ON clause:

SELECT u.user_id, o.order_amount
FROM   t_user u
LEFT   JOIN t_order o
       ON u.user_id = o.user_id
      AND o.order_status = 'FINISHED';

KES Compatibility Characteristics: The KES optimizer follows the ANSI/ISO standard. When determining whether a condition is NULL-rejecting, it is even more accurate than some older versions of Oracle. Running EXPLAIN will directly show you whether the JOIN node has been changed to an inner join. This is the most effective way to investigate this type of problem.

A2 Boundary Differences in Predicate Pushdown

Manifestation: For complex SQL with subqueries or views, after migration, the execution plan differs from before. Some filter conditions have been moved to a different location, causing the results to mismatch.

Cause: Different databases have different rules for "which condition can be pushed down to which lower layer for execution." This is especially true if your subquery or view contains LIMIT, ORDER BY, DISTINCT, or aggregate functions. In such cases, determining whether a condition can be pushed down becomes quite complex.

Pitfall Case:

-- Intent: Query the first 100 orders where the status is FINISHED
SELECT * FROM (
    SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100
) sub
WHERE sub.order_status = 'FINISHED';

In the old system, it might not push the condition down. That is, it first takes the first 100 orders regardless of status, then filters for FINISHED ones from these 100. But in KES, if the optimizer pushes WHERE sub.order_status = 'FINISHED' into the subquery, it becomes: first filter out FINISHED orders, then take the first 100. The results produced are completely different.

Rewrite Solution: Use OFFSET 0 or MATERIALIZED (the CTE materialization syntax supported by KES) to prevent pushdown:

WITH sub AS MATERIALIZED (
    SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100
)
SELECT * FROM sub WHERE order_status = 'FINISHED';

KES Compatibility Characteristics: KES supports the CTE materialization syntax without issues. During domestic transformation, CTEs are a good tool for ensuring semantic correctness. In my projects, I generally set a rule that all complex subqueries must be rewritten as CTEs. This way, these pushdown pitfalls can be avoided.

A3 Subquery Unnesting

Manifestation: SQL with EXISTS / IN / ANY subqueries behaves differently after migration, especially when the subquery contains NULL values.

Cause: The optimizer tries to break apart the subquery and rewrite it as a semi-join or anti-join for execution. This runs much faster. However, if the data returned by the subquery contains NULLs, the meaning of IN and NOT IN becomes slightly different from what you expect.

Pitfall Case:

-- Intent: Query customers with no associated contracts
SELECT * FROM t_customer
WHERE  customer_id NOT IN (SELECT customer_id FROM t_contract);

If t_contract.customer_id contains NULLs, this SQL will return an empty set. Why? Because NOT IN (NULL, ...) always evaluates to Unknown. This is the standard behavior of SQL's three-valued logic. Oracle, MySQL, and KES behave identically on this point. However, if your test environment happens to have no NULL data, you will never discover this problem. Once a NULL appears in the production environment, the entire functionality breaks.

Rewrite Solution: Rewrite using NOT EXISTS or LEFT JOIN + IS NULL:

-- Rewrite as NOT EXISTS
SELECT * FROM t_customer c
WHERE  NOT EXISTS (SELECT 1 FROM t_contract ct WHERE ct.customer_id = c.customer_id);

-- Or rewrite as LEFT JOIN
SELECT c.* FROM t_customer c
LEFT   JOIN t_contract ct ON ct.customer_id = c.customer_id
WHERE  ct.customer_id IS NULL;

KES Compatibility Characteristics: KES supports the semantics and execution optimization of NOT EXISTS. After rewriting to NOT EXISTS, it generally runs faster than NOT IN and won't encounter problems with NULLs. This is also the writing style I usually recommend during migration.

3. Category B: SQL Semantic Standard Differences

Category B pitfalls are unrelated to the optimizer. They are simply different implementations of SQL semantic standards by different databases. These problems are more low-level and very hard to bypass. That is, even if you completely disable the optimizer, these differences still exist.

B1 NULL and Empty String Equivalence

Manifestation: SQL with WHERE col = '' or col <> '' returns different results after migrating to KES.

Cause: In Oracle, the empty string '' is the same as NULL. But KES follows the SQL standard. It considers an empty string to be a value of length 0, distinct from NULL. So col = '' in Oracle is actually equivalent to col IS NULL. But in KES, it genuinely searches for "empty strings of length 0."

Pitfall Case:

-- Oracle original intent: Query records where remark is empty
SELECT * FROM t_log WHERE remark = '';
-- Oracle: Returns records where remark is NULL
-- KES: Returns records where remark is an empty string of length 0 (returns empty set if no such data exists)

Rewrite Solution: To check for emptiness, always use IS NULL:

SELECT * FROM t_log WHERE remark IS NULL;

If you genuinely want to find "empty string or NULL", use COALESCE:

SELECT * FROM t_log WHERE COALESCE(remark, '') = '';

KES Compatibility Characteristics: KES has several configuration options available. You can set it at the session level or database level to mimic Oracle's behavior of treating empty strings as NULL. This allows migrated legacy code to run initially (check the KES official manual's Oracle compatibility chapter for specific parameter names). However, in the long run, I recommend modifying the code to conform to the standard. Don't rely on the compatibility layer indefinitely.

B2 Three-Valued Logic Short-Circuit Differences

Manifestation: SQL uses AND or OR to connect multiple conditions, some of which encounter NULLs. After migration, the returned results differ.

Cause: SQL uses three-valued logic. When NULLs are involved, whether AND and OR short-circuit becomes complex:

Different databases handle the details of this short-circuiting differently. If your SQL contains UDFs with side effects, the results may differ.

Pitfall Case:

-- The first condition in WHERE involves NULL
SELECT * FROM t
WHERE  t.col1 = 'A' AND expensive_udf(t.col2) > 0;

If t.col1 contains NULLs, t.col1 = 'A' evaluates to Unknown. The subsequent AND result depends on what expensive_udf returns. At this point, different databases may make different choices. It might execute expensive_udf, or it might not. If your UDF contains data modification operations, the difference emerges.

Rewrite Solution: Never write UDFs with side effects in the WHERE clause. (I will elaborate on this in C1 below). Even for read-only UDFs, it's best to mark them as STABLE or IMMUTABLE.

KES Compatibility Characteristics: In KES, you can explicitly mark UDFs with four attributes. One is STRICT, meaning if the input is NULL, it directly returns NULL. One is IMMUTABLE, meaning the same input always produces the same output. Another is STABLE, meaning within the same SQL statement, the same input produces the same output. Finally, VOLATILE, meaning each call may differ. The more explicitly you mark them, the better the optimizer knows how to handle them.

B3 NULL Position in Sorting

Manifestation: You write ORDER BY col. The position of NULL values in the query results differs from the previous system. This affects pagination and TopN results.

Cause: The SQL standard does not strictly mandate where NULLs should be placed during sorting:

Pitfall Case:

-- Intent: Take the first 10 records sorted by priority ascending
SELECT * FROM t_task ORDER BY priority ASC LIMIT 10;

If priority contains NULLs, in MySQL, the first 10 records you get might all be NULL data. But in KES, the first 10 records you get are the 10 non-NULL data with the smallest priority values. These two meanings are vastly different.

Rewrite Solution: Manually specify where NULLs should be placed:

SELECT * FROM t_task ORDER BY priority ASC NULLS LAST LIMIT 10;

Or use COALESCE to convert NULLs into a specific number for sorting:

SELECT * FROM t_task ORDER BY COALESCE(priority, 999999) ASC LIMIT 10;

KES Compatibility Characteristics: KES supports the NULLS FIRST / NULLS LAST syntax. This is also part of the SQL:2003 standard. In my projects, I generally require that whenever the column in ORDER BY might contain NULLs, you must manually specify the NULL position. Don't let the database guess.

4. Category C: Function and Expression Behavior Differences

Category C pitfalls are related to how built-in database functions or your UDFs execute. These pitfalls are the easiest to miss. Why? Because people assume that a function, based on its name, should do what it says. But different databases often have vastly different internal implementations for the same function.

C1 Function Side Effects and Execution Order in WHERE

Manifestation: Several UDFs are called together in the WHERE clause. One is for "setting a state," another is for "getting a state." After migrating to KES, the query results are intermittently correct or incorrect.

Cause: SQL is a declarative language. That is, the order in which conditions in the WHERE clause are evaluated is not determined by the order you write them. The optimizer has the right to reorder them for faster execution. If your UDF contains state-modifying operations, it's actually very risky in any database. It's just that the point where the problem manifests differs across databases.

Pitfall Case:

-- Dangerous writing: Relies on set_id executing first, get_id executing second
SELECT * FROM t
WHERE  t.id = get_id()
  AND  set_id(t.value) = 1;

In Oracle, it might accidentally produce results because the Package's session variables still retain previous values. But in KES, because session variables are cleared and the optimizer might swap the evaluation order, the result could be empty.

Rewrite Solution: Move the state-modifying logic outside the SQL:

-- Application layer: Call set first, then execute select separately
CALL pkg_abc.set_id(v_value);
SELECT * FROM t WHERE t.id = pkg_abc.get_id();

KES Compatibility Characteristics: KES offers more options in its UDF attribute system than Oracle (such as IMMUTABLE / STABLE / VOLATILE / STRICT). Additionally, it has a LEAKPROOF attribute. This is used to control whether a function can be pushed down in RLS scenarios. This actually provides a good underlying support for writing code more properly during domestic transformation.

C2 Implicit Type Conversion Between Strings and Numbers

Manifestation: SQL with WHERE varchar_col = 12345 runs very slowly or produces incorrect results after migration.

Cause: When comparing strings and numbers, different databases have different rules for deciding how to convert:

Pitfall Case:

SELECT * FROM t WHERE varchar_col = 12345;

In Oracle, it might still use the index on varchar_col. In KES, it might directly perform a full table scan. If varchar_col contains letters, it directly throws an error.

Rewrite Solution: Manually add CAST:

SELECT * FROM t WHERE varchar_col = CAST(12345 AS VARCHAR);
-- Or
SELECT * FROM t WHERE varchar_col = '12345';

KES Compatibility Characteristics: KES also has configuration options available. You can enable some implicit conversion compatibility at the session level (check the KES manual for specific parameter names). But I actually strongly advise against this. It's best to get the types right when writing business code. This eliminates implicit conversion at the root. Writing SQL more clearly also makes it easier for the optimizer to handle.

C3 Date/Time and Time Zone Parsing Differences

Manifestation: Date/time related SQL, after migration, shows times that are off by 8 hours (or another time zone difference). Or the behavior when converting strings to time differs.

Cause: Different databases have different default strategies for handling time zones:

Pitfall Case:

-- Intent: Get the current time
SELECT SYSDATE FROM DUAL;
-- Oracle: Returns the current time in the OS time zone
-- KES: SYSDATE semantics might be compatible with CURRENT_TIMESTAMP, returning the session time zone time

If the previous system ran on a UTC operating system, and now the KES session time zone is set to CST, the queried time will be off by 8 hours.

Rewrite Solution:

KES Compatibility Characteristics: KES supports the SQL standard TIMESTAMPTZ type and the AT TIME ZONE syntax. It also simultaneously supports Oracle's SYSDATE and SYSTIMESTAMP functions. After migration, I suggest gradually changing to standard syntax. This way, your code will be easier to port to other databases in the future.

5. A Practical Troubleshooting Methodology for Daily Work

All nine pitfalls have been discussed. Now let's return to the practical level of getting work done. In a project, how do you find and eliminate these pitfalls one by one? I usually use a "three-layer investigation" method.

image.png

1. First Layer: Static Code Scanning

Use regular expressions or a SQL parser to scan all existing code. For each of these nine pitfalls, generate a "suspicious list." When writing regex, don't expect 100% accuracy. It's better to include more seemingly fine code than to miss problematic ones. If your codebase is only a few hundred or a thousand lines, a manual review is sufficient. If it's tens of thousands of lines, you generally need to write some scripting tools or purchase commercial SQL analysis software to scan.

2. Second Layer: Execution Plan and Result Comparison

For each SQL on that suspicious list, you need to do two things:

Whenever a difference is found, throw that SQL into the rewrite queue.

3. Third Layer: Production Canary Release and Real-time Monitoring

After rewriting, comes the production canary release step. The most critical thing in this step is continuous monitoring—

The monitoring setup I built in my projects is essentially a set of operations tools for domestic databases. It combines the code scanning rules mentioned earlier, dynamic monitoring probes, and alerting rules. This way, during the canary phase, if any pitfall is triggered, it can be discovered immediately.

Our team is currently organizing this entire set of tools, preparing to participate in the "2026 KingbaseES Database Intelligent Operations Tool Development Competition" recently launched by Kingbase officially. I think this kind of competition is great. The pitfalls stepped on and scripts written by frontline workers, if they can be shared for everyone to use, that's a very practical thing.

Additionally, there's a "Fellow Traveler Program" in the Kingbase community that I often check. When encountering uncertain problems during migration, posting a question in the community for the original vendor's staff to answer is much faster than guessing blindly on your own. Also, the Kingbase community is currently running a call for articles. If you've also done domestic migration and stepped on pitfalls, consider writing about it and submitting. Leave your experience behind so others can avoid those pitfalls. This is a good thing for everyone.

6. Summary

Migrating traditional databases to domestic platforms is really not as simple as "it's fine as long as it runs." On the surface, SQL syntax compatibility might seem to reach over 95%. But what truly determines whether the job is done well is the remaining 5% of subtle SQL logic pitfalls. Whether you can find them, assess them, and fix them—that's the key.

The nine pitfalls discussed in this article are just a part of what's commonly encountered. Behind every pitfall lies the SQL semantic standard, as well as some design trade-offs made by each database. Actually, undertaking a migration transformation is just an opportunity to re-organize previously messy code according to the SQL standard.

Looking at it from the current point in time, KingbaseES KES gives me three very direct impressions when doing domestic migration:

  1. The optimizer is strict, and you can predict how it will run: It won't cover for your incorrectly written code. Nor will it make inferences beyond the standard. When you migrate your code, you can have a clear idea of how it will execute.
  2. Compatibility features are quite comprehensive: For the old writing styles of Oracle, MySQL, and PostgreSQL, it provides compatibility layers. This allows for a smoother migration process.
  3. Troubleshooting tools are sufficient: Things like EXPLAIN, execution statistics, and system views are aligned with mainstream databases. You have the tools available to investigate problems.

With these characteristics, domestic transformation becomes feasible. But what truly prevents project issues is having a working methodology and adhering to discipline—static scanning must be done, intent must be verified, execution plans must be compared, EXCEPT must be run, canary releases must be implemented, and monitoring must be in place. You can't skip any of these steps.

The database migration journey is quite long. I hope the nine pitfalls listed in this article, along with the three-layer investigation method, can offer some help to fellow engineers doing the same work. Let's work together to do a good job on domestic transformation.