← Back to the summary

Two SQL Traps That Run Without Error and Return Wrong Results

A few days ago, I was reviewing a batch reconciliation script and came across a SQL query for orders with the condition where order_no = 123, without quotes. I glanced at it and didn't think much of it; order_no looked like a number, and who bothers to put quotes around a number? It wasn't until later, when the same order number returned three records, that I went back to figure out what was wrong with the SQL.

I'll document this pitfall along with another one. Both are the kind where the syntax is perfectly fine, no errors are thrown at runtime, but the results are wrong. The environment is KES V009R001C010:

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

An unquoted order number pulls in two unrelated records

First, let's reproduce the issue with the original table. The order number is stored as varchar, which is common because it might have leading zeros or an alphabetic prefix—it's not purely numeric:

create table app_schema.t_trap_order (
  id integer primary key,
  order_no varchar(20) not null,
  amount numeric(10,2) not null
);

insert into app_schema.t_trap_order(id, order_no, amount) values
  (1, '00123', 100.00),
  (2, '123',   200.00),
  (3, '0123',  300.00),
  (4, '9999',  400.00);

create index idx_trap_order_no on app_schema.t_trap_order(order_no);

I deliberately chose 00123, 123, and 0123—they all look like "123" but are three completely independent orders, with every character different at the string level.

Let's query with quotes first to see the normal behavior:

explain analyze
select * from app_schema.t_trap_order where order_no = '123';

String comparison uses the index to match exactly one row

Filter: ((order_no)::text = '123'::text), filters out 3 rows, leaving 1 row, which is id=2. The execution plan shows a Seq Scan, not an index scan—this table only has 4 rows, so the optimizer thinks a direct scan is cheaper than an index lookup. That's unrelated to the pitfall itself, so don't confuse the two. Nothing surprising here: string compared to string, 123 is 123, and 00123 is not.

Now, remove the quotes—this is the query I originally stepped on:

explain analyze
select * from app_schema.t_trap_order where order_no = 123;

Implicit conversion in numeric comparison results and execution plan

It runs, no errors, but the Filter has become ((order_no)::integer = 123). KES converts the order_no column to an integer and then compares it to 123. '00123' converted to an integer is 123, '123' is 123, '0123' is also 123—all three order numbers collide after conversion. The execution plan says rows=3, which matches what I actually saw: this SQL pulled out all three orders together.

This kind of problem is more troublesome than a direct error. An error would at least be caught by logs and monitoring, but "pulling in two extra orders that look somewhat related" makes no noise, especially when embedded in a batch script. The extra data might just get processed along with everything else, and by the time you notice, it's already passed through several stages.

In this experiment, the table is small, and both queries do a full table scan, so we can't compare from an index perspective. But one thing is worth noting: once a conversion function wraps the indexed column itself (here, (order_no)::integer), a regular B-tree index becomes unusable. On a large table, this kind of writing not only produces wrong data but also renders the index useless, turning the query into a full table scan.

The fix is simple: write the condition according to the column's actual type. If order_no is a string, just add the quotes. Comparing numerically isn't impossible, but only if you're clear that doing so means '00123' and '123' will be treated as the same thing. That's a business decision, not a side effect of accidentally omitting quotes.

Querying "customers with no orders"—NOT IN gave me an empty set

Another pitfall is querying for "customers who have not placed an order" using NOT IN with a subquery. The syntax is nothing special:

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

create table app_schema.t_trap_order_ref (
  order_id integer primary key,
  cust_id integer,
  order_no varchar(20) not null
);

insert into app_schema.t_trap_customer(cust_id, cust_name) values
  (1, 'customer-a'),
  (2, 'customer-b'),
  (3, 'customer-c');

insert into app_schema.t_trap_order_ref(order_id, cust_id, order_no) values
  (1, 1, 'ORD-001'),
  (2, null, 'ORD-002-abnormal order, customer ID missing');

The second order record has a NULL cust_id, simulating dirty data from a missed mapping during data import or an anonymous order that wasn't linked to a customer ID. Such records aren't rare in real projects. customer-b and customer-c have never placed an order, so logically they should be returned.

Filtering out NULLs in the subquery first—no problem:

select cust_id, cust_name
from app_schema.t_trap_customer
where cust_id not in (
  select cust_id from app_schema.t_trap_order_ref where cust_id is not null
);

NOT IN subquery with NULLs excluded returns correct results

customer-b, customer-c—two rows, correct.

But nobody casually adds an is not null to a subquery, especially when they have no idea the orders table contains records with missing customer IDs. Remove that filter:

select cust_id, cust_name
from app_schema.t_trap_customer
where cust_id not in (
  select cust_id from app_schema.t_trap_order_ref
);

Subquery containing NULL causes NOT IN to return an empty set

0 rows. customer-b and customer-c are all gone, even though they have nothing to do with that abnormal order—they're dragged down along with it.

The first time I saw this, I was baffled: how does one unrelated record in the subquery wipe out two legitimate customers? The root cause is what NOT IN expands to—cust_id not in (1, null) is equivalent to cust_id <> 1 and cust_id <> null. The comparison <> null is neither true nor false; it's "unknown." In an and chain, if any term is "unknown" and no term evaluates to false, the entire expression is "unknown." The WHERE clause treats "unknown" and "false" the same—both are discarded. So as soon as a single NULL sneaks into the subquery, NOT IN corrupts the judgment for every row in the outer query. It's not that a few rows are missed; all judgments fail.

Switching to NOT EXISTS avoids this problem:

select c.cust_id, c.cust_name
from app_schema.t_trap_customer c
where not exists (
  select 1 from app_schema.t_trap_order_ref o
  where o.cust_id = c.cust_id
);

NOT EXISTS is unaffected by NULLs in the subquery

customer-b and customer-c are back. That abnormal order is still in the table, but it doesn't affect the result. NOT EXISTS only cares about "whether there is a matching row." A record with a NULL cust_id naturally matches nothing, so it can't drag down other judgments. I used to think that "using NOT EXISTS instead of NOT IN for exclusion queries" was a stylistic preference. Now I understand it's not a preference—if there's any chance a NULL could appear in the subquery, NOT IN is genuinely unsafe.

Afterthoughts

These two pitfalls seem far apart—one is about types, the other about NULL's three-valued logic—but the process of stepping into them was similar: both ran perfectly on clean test data, then fell apart when fed real data with a few "imperfections"—order numbers with leading zeros, a subquery tainted by one dirty record. From now on, after writing SQL, I plan to ask myself one more question: if this batch of data weren't so clean, would this statement still run the way I think it will? If I'm not sure, I'll run a few boundary-case rows and see for myself. It's a lot less trouble than discovering the problem after it's live.