EC
1493
MySQLERRORNotablePartitioningHIGH confidence

Expression in LIST/RANGE VALUES must be constant

Production Risk

Low — DDL error; the table will not be created.

What this means

ER_NO_CONST_EXPR_IN_RANGE_OR_LIST_ERROR (1493, SQLSTATE HY000) is raised when a non-constant expression is used in a RANGE or LIST partition VALUES clause.

Why it happens
  1. 1Using a column reference or variable in VALUES LESS THAN or VALUES IN
  2. 2Using a function call that is not a constant expression
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT, limit_val INT)
PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (limit_val)  -- Not a constant
);

expected output

ERROR 1493 (HY000): VALUES value must be of same type as partition function

Fix

Use only literal constant values in partition VALUES clauses

Use only literal constant values in partition VALUES clauses
CREATE TABLE t (id INT)
PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

Why this works

VALUES LESS THAN and VALUES IN require literal constant values; variables and column references are not allowed.

Sources
Official documentation ↗

MySQL 8.0 — 1493 ER_NO_CONST_EXPR_IN_RANGE_OR_LIST_ERROR

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors