The Two Oracle-to-Postgres Migration Traps That Never Throw an Error

When taking on an Oracle migration assessment task, people habitually start by looking at syntax compatibility: can this stored procedure run, does this function have a corresponding implementation, does this view's syntax need to be changed. These issues are easy to check, tools can scan for them, and running through the error logs tells you what to fix. What really gives you a headache isn't these—it's the places where the syntax is completely legal, runs fine after migration, but the results are just different from before. These kinds of problems don't throw errors, won't appear in the 'failed items' of any migration report, and only surface when the business side finds that reports don't match or reconciliations are off by a few rows, often after going live.

This assessment hit two such problems: one is outer join elimination, the other is whether an empty string counts as NULL. The former ranks first because old systems are full of historical SQL using the (+) syntax, and no one can say for sure whether the original intent was to preserve the full set of left-table rows; the latter ranks second because it involves the question 'Is KES really like Oracle?' which has no fixed answer—it depends on the configuration. The environment is KES V009R001C010, connected with a business account:

ksql -h 127.0.0.1 -p 54321 -U app_user -d app_db

First, set up the scenario: customer table and customer grade table

create table app_schema.t_mig_customer (
  cust_id integer primary key,
  cust_name varchar(50) not null,
  grade_code varchar(10)
);

create table app_schema.t_mig_grade (
  grade_code varchar(10) primary key,
  grade_name varchar(30) not null,
  grade_level integer not null
);

insert into app_schema.t_mig_customer(cust_id, cust_name, grade_code) values
  (1, 'customer-a', 'G1'),
  (2, 'customer-b', 'G2'),
  (3, 'customer-c', null),
  (4, 'customer-d', 'G3');

insert into app_schema.t_mig_grade(grade_code, grade_name, grade_level) values
  ('G1', 'silver', 1),
  ('G2', 'gold',   2);

customer-c was never assigned a grade, so grade_code is NULL; customer-d's grade_code is G3, but the grade table doesn't have a G3 level at all—this is the norm in many old systems, where grade table maintenance can't keep up with business expansion, leaving a bunch of foreign key values in historical data that don't match. In a 'query all customers' scenario, both of these customers should theoretically be kept, just with their grade information shown as empty.

Trap One: The (+) syntax itself is fine, but adding a condition changes the flavor

First, confirm KES's compatibility with this old Oracle syntax. Whichever side (+) is marked on is the side that can be null in the outer join. The most basic syntax:

select c.cust_id, c.cust_name, g.grade_name
from app_schema.t_mig_customer c, app_schema.t_mig_grade g
where c.grade_code = g.grade_code(+);

All four customers are returned, with grade_name empty for customer-c and customer-d. Nothing special about this step; the (+) syntax is handled very well, old code can be moved over as-is and work perfectly, which is indeed where KES's claim of Oracle compatibility delivers.

The trouble comes from a more common pattern in old systems: beyond the join condition, the business often needs an extra layer of filtering, like 'only show customers with a formal grade (grade level >= 1)':

select c.cust_id, c.cust_name, g.grade_name
from app_schema.t_mig_customer c, app_schema.t_mig_grade g
where c.grade_code = g.grade_code(+)
  and g.grade_level >= 1;

Only 2 rows returned, customer-a and customer-b. customer-c and customer-d are gone—this SQL clearly has (+) written in it, obviously intending outer join semantics, yet the result has only the row count of an inner join.

Run the execution plan to confirm what happened behind the scenes:

explain analyze
select c.cust_id, c.cust_name, g.grade_name
from app_schema.t_mig_customer c, app_schema.t_mig_grade g
where c.grade_code = g.grade_code(+)
  and g.grade_level >= 1;

Hash Join, no Left or Right label, a pure inner join. The reason isn't hard to understand: the condition g.grade_level >= 1 is applied to the right table. For customer-c and customer-d, the corresponding g.grade_level is NULL, and NULL >= 1 evaluates to 'unknown', which in a WHERE clause is effectively discarded. Since these rows that should have been kept by the outer join are filtered out by this condition anyway, 'outer join + this filter' and 'inner join + this filter' produce exactly the same result, so the optimizer chooses the cheaper inner join to execute—this is outer join elimination. It has nothing to do with whether (+) was written or not; (+) is just one way to write an outer join, and the underlying judgment logic is the same as ANSI LEFT JOIN.

To confirm this, run the same logic using ANSI syntax as a common troubleshooting method during migration verification:

explain analyze
select c.cust_id, c.cust_name, g.grade_name
from app_schema.t_mig_customer c
left join app_schema.t_mig_grade g
  on c.grade_code = g.grade_code
where g.grade_level >= 1;

Again a Hash Join, again no Left label, behavior completely consistent with the (+) syntax. This also serves as a reminder for those doing migration verification: whether the old system used (+) or the translated LEFT JOIN, as long as the WHERE clause has this kind of filter on a right-table field that evaluates NULL as false, this trap is unavoidable. Just checking if the syntax translation is correct is useless; you need to verify with execution plans and row counts.

The fix is to move this filter condition into the join condition:

select c.cust_id, c.cust_name, g.grade_name
from app_schema.t_mig_customer c
left join app_schema.t_mig_grade g
  on c.grade_code = g.grade_code
  and g.grade_level >= 1;

All four rows come back, with the grade fields for customer-c and customer-d shown as empty as expected. This isn't just a random guess from moving things around; ON determines 'how to join', filtering the right table first before performing the outer join; WHERE determines 'what to filter out after the join is done', and conditions placed here are applied after the join is complete, so NULL rows can't escape.

There's no shortcut for finding this type of problem during migration verification: for every piece of existing SQL involving an outer join, the row counts before and after migration must be compared one by one. Just checking 'can it run' is not enough.

Trap Two: Whether an empty string counts as NULL depends on how this specific environment is configured

Before writing this section, first confirm two things clearly. You can't guess based on the intuition that 'KES's kernel is derived from PostgreSQL':

show database_mode;
show ora_input_emptystr_isnull;

database_mode is oracle, ora_input_emptystr_isnull is on. This environment defaults to Oracle compatibility mode—many people subconsciously assume 'the kernel is from the PG family, so behavior should match PG', but this assumption doesn't hold here. You still need to confirm the compatibility mode first, unless the database was explicitly created in PG mode. And the parameter ora_input_emptystr_isnull, as the name suggests, is specifically for replicating that classic Oracle behavior: treating a zero-length string as NULL.

Verify with a test:

insert into app_schema.t_mig_customer(cust_id, cust_name, grade_code)
values (5, 'customer-e', '');

select
  cust_id, cust_name, grade_code,
  grade_code is null as is_null_flag,
  length(grade_code) as code_len
from app_schema.t_mig_customer
where cust_id = 5;

The inserted empty string '' shows is_null_flag as t when queried—it was stored as NULL, and code_len is also blank, not 0. This is the opposite of what many people expect from 'the PostgreSQL system wouldn't do this'; KES in Oracle compatibility mode faithfully replicates this Oracle-specific behavior.

For migration readers, this is actually good news: if the old system's PL/SQL or business code relies on the assumption that 'empty string equals NULL' (for example, using col = '' to check for 'not filled in'), as long as the migrated KES is still in Oracle compatibility mode, this batch of code doesn't need changing, and the behavior remains consistent. What really needs caution is the reverse situation—if operations later switches the database to PG compatibility mode, or manually turns off this parameter, this batch of old code will silently fail without any errors. This is also the most memorable point about this trap: it's not a fixed conclusion, but one that varies with the compatibility mode and parameters. During the migration assessment phase, you must personally confirm these two items in the target environment; you can't prejudge based on experience.

Migration Pitfall Checklist

1. Register all existing SQL involving outer joins (LEFT/RIGHT JOIN or (+)) in a ledger, and run them once before migration to get baseline row counts.
2. After migration, rerun the same SQL on KES and compare row counts one by one. Judging correctness solely by 'can it run' is impossible.
3. Review WHERE clauses for filters on right-table (nullable side) fields that lack a corresponding (+) or haven't been pushed down into ON.
4. Before migration, confirm the target environment's database_mode and ora_input_emptystr_isnull, nail down the 'does empty string equal NULL' question, and then decide whether this part of the code needs changing.
5. For judgment logic involving finance, inventory, or approvals, neither of these two types of problems will throw errors; they will only silently change results. No matter how tight the verification timeline, these two checks cannot be skipped.