ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists t2;
SET @@sql_mode='NO_UNSIGNED_SUBTRACTION';
create table t2 (a bigint unsigned) partition by range (a) (
    partition p1 values less than (0),
    partition p2 values less than (1),
    partition p3 values less than (18446744073709551614),
    partition p4 values less than (18446744073709551615),
    partition p5 values less than maxvalue
);

2. What did you expect to see? (Required)

mysql> SET @@sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a bigint unsigned) partition by range (a) (
    ->     partition p1 values less than (0),
    ->     partition p2 values less than (1),
    ->     partition p3 values less than (18446744073709551614),
    ->     partition p4 values less than (18446744073709551615),
    ->     partition p5 values less than maxvalue
    -> );
Query OK, 0 rows affected (0.05 sec)

3. What did you see instead (Required)

release-5.1 & master

mysql> SET @@sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a bigint unsigned) partition by range (a) (
    ->     partition p1 values less than (0),
    ->     partition p2 values less than (1),
    ->     partition p3 values less than (18446744073709551614),
    ->     partition p4 values less than (18446744073709551615),
    ->     partition p5 values less than maxvalue
    -> );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

4. What is your TiDB version? (Required)

Release Version: v5.1.1
Edition: Community
Git Commit Hash: 797bddd25310ed42f0791c8eccb78be8cce2f502
Git Branch: release-5.1
UTC Build Time: 2021-07-31 02:01:43
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ChenPeng2013]

case2:

use test;
drop table if exists tu;
set @@sql_mode='';
CREATE TABLE tu (c1 BIGINT UNSIGNED) PARTITION BY RANGE(c1 - 10) (
    PARTITION p0 VALUES LESS THAN (-5),
    PARTITION p1 VALUES LESS THAN (0),
    PARTITION p2 VALUES LESS THAN (5),
    PARTITION p3 VALUES LESS THAN (10),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

SET @@sql_mode='NO_UNSIGNED_SUBTRACTION';
CREATE TABLE tu (c1 BIGINT UNSIGNED) PARTITION BY RANGE(c1 - 10) (
    PARTITION p0 VALUES LESS THAN (-5),
    PARTITION p1 VALUES LESS THAN (0),
    PARTITION p2 VALUES LESS THAN (5),
    PARTITION p3 VALUES LESS THAN (10),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
insert into tu values (1), (10);

expect

mysql> insert into tu values (1), (10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

got

mysql> insert into tu values (1), (10);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(tu.c1 - 10)'

[bb7133]

It looks like the bug was introduced by #23428 but actually, it is not, the following case can be reproduced in v4.0, v5.0:

SET @@sql_mode='NO_UNSIGNED_SUBTRACTION';
create table t2 (a int unsigned) partition by range (a) (     partition p1 values less than (0),     partition p2 values less than (1),     partition p3 values less than (18446744073709551614),     partition p4 values less than (18446744073709551615),     partition p5 values less than maxvalue );

Root cause of this bug is that, when ``NO_UNSIGNED_SUBTRACTION`' is set, TiDB tries to evaluate/cast the value as signed and it reports an error when an overflow happens.

PTAL @xiongjiwei, I think we can check the related logic of MySQL source code for a better compatibility of handling 'NO_UNSIGNED_SUBTRACTION' for partition tables. /cc @mjonss , your advise is appreciated.

[mjonss]

@bb7133 your conclusion is correct, it will change the range from UNSIGNED to SIGNED. In #25437 (comment) I did mention the limitation of the range as well as in the next comment, suggesting to reverting #25435.
I advice to revert pr #25435 / issue #24880 and document this corner case as non-MySQL compliant.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions