3351
MySQLERRORNotableDDL / IndexesHIGH confidence
Functional index expression cannot evaluate to NULL for all rows
Production Risk
Low — DDL fails; no index is created.
How to reproduce
trigger — this will error
trigger — this will error
CREATE INDEX idx ON t ((nullable_col + 1)); -- where nullable_col allows NULL
expected output
ERROR 3351 (HY000): The expression of a functional index cannot produce NULL values.
Fix
Use COALESCE
Use COALESCE
CREATE INDEX idx ON t ((COALESCE(nullable_col, 0) + 1));
Why this works
COALESCE substitutes a non-NULL fallback, ensuring the index has useful values.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3351 ER_FUNCTIONAL_INDEX_CANNOT_HAVE_NULL
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev