Implicit Casts That Defeat an Index

Concepts covered: Type Casting, Basic Types

A B-tree index keeps the column's values sorted under the column's own type and collation, so it can serve a predicate on the bare column and nothing else. Wrap the column in a function and the order of the index stops matching the order of the expression, so the engine scans. An implicit cast is a function the engine inserted on your behalf, and that's why the bug reaches production: the SQL text looks sargable, and the plan says otherwise.

Which side gets converted

Every engine resolves a mixed-type comparison by converting one side, and the side it picks decides whether the index survives. Precedence rules differ by engine, and naming the rule for the engine on the whiteboard is what the interviewer is listening for.

PostgreSQL's own trap runs the other way. WHERE payment_id = 880412.0 compares a BIGINT column with a NUMERIC literal. No index operator pairs those 2 types, so the planner casts the column to NUMERIC and the B-tree on payment_id goes unused. The explicit version, created_at::date = '2024-03-15' against an index on a timestamp, fails the same way, and the rewrite is a half-open range: created_at >= '2024-03-15' AND created_at < '2024-03-16'.

The driver nobody suspects

The most common production case never appears in the SQL. Microsoft's JDBC driver sends Java strings as NVARCHAR by default (sendStringParametersAsUnicode=true). NVARCHAR outranks VARCHAR, so a lookup on a VARCHAR column converts the column on every row. Under a SQL collation the seek becomes a scan; under a Windows collation the optimizer can still seek over a computed range, with a worse row estimate. The fix is a single connection-string flag, and the only way to find it is to read the plan.

Reading it in the plan

-- SQL Server: actual plan for a ledger lookup
|--Index Scan(OBJECT:([ledger_entries].[ix_payment_ref]),
     WHERE:(CONVERT_IMPLICIT(int,[payment_ref],0)=(880412)))
Warning: Type conversion in expression (CONVERT_IMPLICIT(int,[payment_ref],0))
  may affect "SeekPlan" in query plan choice

-- PostgreSQL: EXPLAIN
Seq Scan on payments  (cost=0.00..48210335.00 rows=12000000 width=64)
  Filter: ((payment_id)::numeric = 880412.0)

-- MySQL 8: SHOW WARNINGS after EXPLAIN
Cannot use ref access on index 'ix_payment_ref' due to type or
collation conversion on field 'payment_ref'

The rewrite that changes the answer

The reflexive fix is to match the literal to the column: payment_ref = '880412' in place of = 880412. The seek comes back, and so does a different result set, because the numeric comparison matched every spelling of the number and the text comparison matches exactly one. Watch both comparisons evaluate over 6 ledger rows.

SELECT entry_id,
       payment_ref,
       payment_ref = '880412' AS text_match,
       CAST(payment_ref AS BIGINT) = 880412 AS numeric_match
FROM ledger_entries;

Rows 2 and 5 carry leading zeros from an old export. The numeric comparison counts 4 rows as payment 880412; the text comparison finds 2. In the reconciliation job, that is 2 ledger entries reported as unmatched with no error anywhere. The sentence the interviewer wants to hear: "Before I move the cast, I count the rows where the 2 comparisons disagree." If that count is 0, the rewrite costs nothing. If it is higher, the data needs normalizing before any predicate changes.

MySQL's DOUBLE path has a second edge. A double holds every integer exactly only up to a limit, and past it the conversion rounds, so payment_ref = 18014398509481985 matches a row holding '18014398509481984'. On 17-digit ids, comparing text to a number is a correctness bug as well as a scan.

• 2^53

4 fixes and what each costs

The expression index is the tempting middle answer, so name its failure mode before the interviewer does. The first row holding 'N/A' makes CREATE INDEX fail, and once the index exists, every later insert of such a row fails too, which moves a reporting problem into the ledger service's write path. Changing the type itself is a migration with rules of its own.

About this lesson

This section is part of the Type Casting: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.