Why Did MySQL’s BIT Index Miss a Query? A Real‑World Debugging Tale

In a five‑year‑old production service, a new release unexpectedly caused a system crash, triggering frantic alerts in the production issue channel. The failure stemmed from a SQL query that returned no rows for a specific condition, even though the data existed in the database.

Investigation Steps

The problematic query was:

select * from w_location where location_type = 'SORTING_TEMPORARY' AND status = '1'

Directly querying the live database with the same condition showed data, indicating the issue was not data‑absence but query execution.

Reviewing the release notes revealed no code changes related to the affected service, suggesting the bug was introduced elsewhere.

Root Cause Analysis

The w_location table defines status as bit(1). The index index_location_type_status (location_type, status) forces MySQL to perform implicit type conversion when evaluating status = '1'. In an indexed lookup, MySQL converts the BIT value to a string, comparing b'1' with the ASCII character '1' (value 49), which never matches. When the condition is written as status = 1, MySQL converts the BIT to an integer, and the comparison succeeds.

Without the index, MySQL performs a full table scan and applies a uniform conversion, so the query works, but with the index the mismatch prevents index usage, leading to a silent failure.

Solution

The immediate fix was to ask the DBA to drop the newly added index, restoring normal query behavior. Longer‑term recommendations include:

Avoid using BIT columns in indexed predicates that compare against string literals.

Prefer TINYINT(1) or explicit casting to ensure consistent type handling.

Review index definitions after schema changes to prevent hidden implicit conversion bugs.

Key Takeaways

Implicit type conversion in MySQL, especially with BIT fields, can produce subtle bugs when combined with indexes. Understanding how MySQL converts types in indexed versus non‑indexed contexts is essential for reliable query design.