<p>The error appears when a RANGE or LIST partition definition includes a non-constant expression.</p>
<p>MySQL Error 1487 ER_NO_CONST_EXPR_IN_RANGE_OR_LIST_ERROR signals that RANGE or LIST partition values are not literal constants. Replace functions, columns, or variables with fixed literals or switch to HASH partitioning to resolve the issue quickly.</p>
Expression in RANGE/LIST VALUES must be constant
MySQL raises this error while parsing a CREATE TABLE or ALTER TABLE statement that defines RANGE or LIST partitions. The engine expects every value supplied in the VALUES clause to be a literal constant.
When MySQL detects a column reference, function call, user variable, or any non-constant expression inside that clause, it aborts with SQLSTATE HY000 and error number 1487.
Partition metadata must be deterministic at create time so that the optimizer can map rows to partitions without executing run-time calculations. Constant literals guarantee that mapping stays valid even after data changes.
Using built-in functions such as YEAR, UPPER, NOW, or arithmetic inside VALUES clauses is the most frequent trigger.
Referencing table columns or session variables in partition definitions also breaks the constant requirement.
Dynamic SQL generators that template partition names sometimes introduce expressions accidentally, leading to this error on deployment.
Rewrite every value list so that only literal constants remain. Replace UPPER('us') with 'US' or YEAR('2024-01-01') with 2024.
If deterministic mapping is impossible with constants, convert the table to HASH or KEY partitioning, which permits expressions in the partitioning column definition instead.
Problem: LIST partition with function calls. Solution: Evaluate functions ahead of time and hard-code results.
Problem: RANGE partition on DATE using YEAR(date_col). Solution: Add a generated column storing YEAR(date_col) and partition by that column with constant limits.
Validate partition DDL in a staging database before production rollout to catch non-constant expressions early.
Automate DDL generation scripts to pre-compute literals rather than embedding functions.
Use Galaxy SQL editor linting to highlight non-constant expressions in partition definitions during code review.
Error 1493 partition constant out of range appears when constants themselves violate the column range; fix by adjusting boundary values.
Error 1503 duplicates a partition name; ensure unique names across partitions.
Using YEAR, MONTH, UPPER, or other functions produces a non-constant expression that MySQL cannot store as static metadata.
Including a table column directly inside VALUES violates the requirement for constants because column values differ per row.
Session variables such as @country_code or system variables like @@lc_time_names are evaluated at run time, not creation time.
Raised when constant boundary values fall outside the column domain.
Occurs when two partitions share the same identifier.
Triggers when partitioning function uses unsupported constructs.
Yes. HASH and KEY partitioning allow expressions because mapping occurs at run time, unlike RANGE or LIST.
The constant requirement applies to all supported MySQL versions, including 5.7, 8.0, and later.
Galaxy highlights non-constant expressions in partition DDL during editing and suggests constant replacements via its AI copilot.
Yes, dropping partitions or switching to a non-partitioned table eliminates the constant expression requirement altogether.
.avif)
.avif){kind=logo}
