Informix Error -25810: The fragment key, interval value, and upper limit of the fragments are not compatible.
Cause and resolution
The fragment key, interval value, and upper limit of the fragments are not compatible.
The fragment key (the column expression on which the table or index is fragmented), the interval value, and the upper limit of fragments are not compatible data types. For a numeric fragment key, the interval value must be a numeric type and the upper limit of a fragment must be numeric. For a fragment key of DATE or DATETIME type, the interval value must be an INTERVAL type and the upper limit of a fragment must be a DATE or DATETIME type.
The error can also occur in each of the following scenarios: - For a fragment key of DATETIME type, the first field qualifier of the fragment key is greater than the last field qualifier of the interval value. - For a fragment key of DATE type, the interval value is not an integer multiple of days (i.e the hours, minutes, seconds or fractions are nonzero). - For a fragment key of DATE or DATETIME type, the first field qualifier of the fragment key and the first field qualifier of the upper limit of a fragment are not the same. - The resultant type of the upper limit of a fragment is not a built-in data type.
For example, the following statements generate an error:
Example 1:
CREATE TABLE employee (emp_id INTEGER, emp_name CHAR(64), ssn CHAR(12), basepay FLOAT, varpay FLOAT, dept_id SMALLINT) FRAGMENT BY RANGE(emp_id) INTERVAL(3 UNITS YEAR) STORE IN (dbs1, dbs2, dbs3) PARTITION p1 VALUES < 3000 IN dbs3;
The fragment key and the upper limit of fragment p1 are numeric, but the interval value is of the incompatible INTERVAL type.
Example 2:
CREATE TABLE orders (order_num INT, order_time DATETIME HOUR TO SECOND, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_time) INTERVAL(3 UNITS DAY) PARTITION p1 VALUES < DATETIME(01:00:00) HOUR TO SECOND IN dbs1;
In the above example the first field qualifier (HOUR) of the fragment key order_time is greater than the last field qualifier (DAY) of the interval value.
The correct usage is as follows:
CREATE TABLE orders (order_num INT, order_time DATETIME HOUR TO SECOND, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_time) INTERVAL(3 UNITS HOUR) PARTITION p1 VALUES < DATETIME(01:00:00) HOUR TO SECOND IN dbs1;
Example 3:
CREATE TABLE orders (order_num INT, order_time DATETIME YEAR TO SECOND, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_time) INTERVAL(3 UNITS DAY) PARTITION p1 VALUES < DATETIME(01-01 01:00:00) MONTH TO SECOND IN dbs1;
In the above example, the first field qualifier (YEAR) of the fragment key order_time is not same as the first field qualifier (MONTH) of the upper limit of the initial range fragment.
The correct usage is as follows:
CREATE TABLE orders (order_num INT, order_time DATETIME YEAR TO SECOND, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_time) INTERVAL(3 UNITS DAY) PARTITION p1 VALUES < DATETIME(2010-01-01 01:00:00) YEAR TO SECOND IN dbs1;
Example 4:
CREATE TABLE orders (order_num INT, order_date DATE, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_date) INTERVAL(3 UNITS HOUR) PARTITION p1 VALUES < DATE('01/01/2009') IN dbs1;
In the above example the fragment key order_date is of type DATE but the interval value (3 UNITS HOUR) is not an integer multiple of DAY.
The correct usage is as follows:
CREATE TABLE orders (order_num INT, order_date DATE, customer_num INT, ship_instruct_code CHAR(1)) FRAGMENT BY RANGE(order_date) INTERVAL(24 UNITS HOUR) PARTITION p1 VALUES < DATE('01/01/2009') IN dbs1;
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-25810 fires when the fragment key, interval value, and fragment upper limit have
incompatible types — per the official guidance: a numeric key needs a numeric interval and
numeric upper limits; a DATE/DATETIME key needs an INTERVAL interval value and
DATE/DATETIME upper limits. It also covers several more specific mismatches: a
DATETIME key whose first field qualifier exceeds the interval value's last qualifier; a
DATE key with a non-integer-days interval value; a DATE/DATETIME key whose first
field qualifier doesn't match the upper limit's; or an upper limit that isn't a built-in
type.
- Type incompatibility between the fragment key, interval value, and upper limit, per the official guidance — several specific named sub-cases, listed above.
Solutions / Resolution
- Align the fragment key's type with a compatible interval value and upper-limit
type, per the official guidance — numeric with numeric,
DATE/DATETIMEwithINTERVAL.
Examples
Numeric key with an INTERVAL-type interval value
CREATE TABLE employee (emp_id INTEGER, emp_name CHAR(64), ssn CHAR(12),
basepay FLOAT, varpay FLOAT, dept_id SMALLINT)
FRAGMENT BY RANGE(emp_id) INTERVAL(3 UNITS YEAR) STORE IN (dbs1, dbs2, dbs3)
PARTITION p1 VALUES < 3000 IN dbs3;
-- -25810: emp_id and 3000 are numeric, but the interval value is INTERVAL type
Diagnostic Checks
- Check that the fragment key, interval value, and upper limit all use compatible types, per the rules above.
Related Errors / Related Topics
- -25804 — "The interval value must be a non-zero positive constant." A related interval-value restriction.
- -25805 — "The interval value must be a numeric or INTERVAL data type." A related interval-value type restriction.
- -25806 — "The fragment key for interval fragmentation must be a numeric, DATE, or DATETIME type." A related fragment-key type restriction.
The fragment key, interval value, and upper limit had incompatible types — align them per
the numeric/INTERVAL pairing rules.