Database

Two SQL Traps That Run Without Error and Return Wrong Results

By 一只牛博 · Jul 22, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Implicit type conversion and NULL propagation are universal SQL behaviors, not KES-specific quirks. A batch reconciliation script that silently merges orders or drops customers can produce financial errors that survive several processing stages before anyone notices.

Summary

Comparing a varchar column to an unquoted integer causes the database to cast every stored value to an integer before comparison. Strings like '00123', '123', and '0123' all become 123, so a query meant for one order returns three. The syntax is legal and no error fires, making the data corruption invisible to logs and monitoring. On large tables the cast also disables the B-tree index on that column, forcing a full scan.

A NOT IN subquery that contains even one NULL poisons the entire outer query. The expression expands to a chain of ANDed inequalities, and any comparison with NULL evaluates to unknown. Because WHERE discards both false and unknown, every row gets filtered out. NOT EXISTS is immune because it checks only for the existence of a matching row, and a NULL value matches nothing.

The common thread is that both bugs pass clean test suites and only surface against real data with small imperfections: leading zeros, a missing foreign key. The fix is to match the literal type to the column type and to default to NOT EXISTS for anti-join queries unless the column has a NOT NULL constraint.

Takeaways

— Writing `WHERE order_no = 123` when order_no is varchar causes the database to cast every stored value to an integer; '00123', '123', and '0123' all become 123 and match.

— The implicit cast wraps the indexed column, so a B-tree index on order_no becomes unusable and the query degrades to a full table scan on large tables.

— A single NULL in a NOT IN subquery makes the entire outer query return zero rows because `x <> NULL` evaluates to unknown, and WHERE discards unknown.

— NOT EXISTS is immune to NULLs in the subquery because it only checks for the existence of a matching row, and a NULL value matches nothing.

— Both bugs pass clean test data and only surface with real data that contains leading zeros or missing foreign-key values.

Conclusions

Implicit type conversion is a silent data-corruption mechanism: it produces no error, no warning, and no log entry, yet it can merge distinct business entities into one.

The NOT IN trap is a direct consequence of SQL's three-valued logic, but most developers treat NULL behavior as an edge case rather than a default failure mode for an entire query class.

Clean test data is the common enabler for both bugs. The real safeguard is testing with deliberately dirty data—leading zeros, null foreign keys—before the query reaches production.

Concepts & terms

Implicit type conversion in SQL

When a query compares values of different types, the database automatically converts one type to the other according to precedence rules. Comparing a varchar column to an unquoted integer typically casts the string to a number, which can collapse distinct string values like '00123' and '123' into the same integer.

Three-valued logic and NULL propagation

SQL uses three-valued logic: true, false, and unknown. Any comparison with NULL yields unknown. In a NOT IN clause, this unknown propagates through the AND chain and causes the WHERE clause to discard every row, returning an empty set.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗