CAST / CONVERT, implicit type conversion, and index defeat

-- Standard SQL: CAST
SELECT CAST('2026-10-25' AS DATE);           -- string to date
SELECT CAST(50000.00 AS VARCHAR);            -- number to string
SELECT CAST('12345' AS INTEGER);             -- string to integer

-- SQL Server: CONVERT with style codes
SELECT CONVERT(DATE, '2026-10-25', 23);      -- ISO format
SELECT CONVERT(VARCHAR, GETDATE(), 120);     -- 'YYYY-MM-DD HH:MI:SS'

-- MySQL: CONVERT or CAST
SELECT CAST('2026-10-25' AS DATE);
SELECT CONVERT('12345', SIGNED INTEGER);

-- Good: cast the literal to match the column type
SELECT * FROM transactions
WHERE created_at >= CAST('2026-10-01' AS TIMESTAMP);

-- Bad: cast the indexed column (defeats the index)
SELECT * FROM transactions
WHERE CAST(created_at AS DATE) = '2026-10-25';
-- This is a function on the column -> Seq Scan

-- account_id is VARCHAR(20)
-- Comparing to a number triggers implicit conversion
SELECT * FROM accounts WHERE account_id = 2001;

-- The database sees: VARCHAR vs INTEGER
-- Type precedence: INTEGER > VARCHAR
-- So the database casts the COLUMN: CAST(account_id AS INTEGER) = 2001
-- This is a function on the column -> index is defeated

-- Index on accounts(account_id) (VARCHAR)
-- Implicit cast on the column defeats it
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = 2001;

Seq Scan on accounts
  Filter: ((account_id)::integer = 2001)
  Rows Removed by Filter: 9,999,999
  Execution Time: 3,200 ms  -- full table scan

-- Fix: compare string to string
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = '2001';

Index Scan using accounts_pkey on accounts
  Index Cond: (account_id = '2001')
  Rows: 1
  Execution Time: 0.05 ms  -- 64,000x faster

-- Runtime error: non-numeric value in a VARCHAR column
-- account_id contains 'SAV2001' (has letters)
SELECT * FROM accounts WHERE account_id = 2001;
-- The database tries CAST('SAV2001' AS INTEGER)
-- ERROR: invalid input syntax for type integer: "SAV2001"

-- Incorrect matches: leading zeros, spaces
-- '0042' as VARCHAR vs 42 as INTEGER
-- CAST('0042' AS INTEGER) = 42 -> matches, but '0042' != '42' as strings
-- This can return unexpected rows

// com.bank.service.AccountLookupService
package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class AccountLookupService {

    /**
     * Wrong: binding an int when the column is VARCHAR.
     * This causes implicit CAST on the column, defeating the index.
     */
    public void lookupWrong(Connection conn, int id)
            throws SQLException {
        String sql = "SELECT * FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);  // int sent for VARCHAR column!
            // Database: CAST(account_id AS INT) = 2001 -> Seq Scan
        }
    }

    /**
     * Correct: binding a String for a VARCHAR column.
     * Types match, index is used.
     */
    public BigDecimal lookupCorrect(Connection conn, String accountId)
            throws SQLException {
        String sql = "SELECT balance FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);  // String for VARCHAR -> Index Scan
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getBigDecimal("balance");
                }
                return null;
            }
        }
    }

    /**
     * Timestamp filter: bind as Timestamp, not String.
     */
    public int countTransactionsAfter(
            Connection conn, String accountId, Timestamp after)
            throws SQLException {
        String sql = """
            SELECT COUNT(*)
            FROM   transactions
            WHERE  account_id = ?
              AND  created_at >= ?
            """;
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, accountId);      // VARCHAR -> setString
            ps.setTimestamp(2, after);        // TIMESTAMP -> setTimestamp
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                return rs.getInt(1);
            }
        }
    }
}

General type precedence (highest to lowest):
  1. DATETIME / TIMESTAMP
  2. NUMERIC / DECIMAL / FLOAT
  3. INTEGER / BIGINT
  4. VARCHAR / CHAR / TEXT

When comparing VARCHAR vs INTEGER:
  - INTEGER outranks VARCHAR
  - So the VARCHAR column is cast to INTEGER
  - This defeats the index on the VARCHAR column

-- Look for a cast in the filter
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = 2001;
-- Filter: ((account_id)::integer = 2001)
-- The ::integer cast is the implicit conversion
-- If you see this -> fix the literal type

-- After fix:
EXPLAIN ANALYZE
SELECT * FROM accounts WHERE account_id = '2001';
-- Index Cond: (account_id = '2001')
-- No cast in the condition -> index is used