Incorrect Query Result With Substr Partition

[ShaoxunLi]

Steps to reproduce the behavior (Required)

1. CREATE TABLE bill_detail ( bill_code varchar(200) NOT NULL DEFAULT "" COMMENT "" ) ENGINE=OLAP PRIMARY KEY(bill_code) PARTITION BY RANGE(cast(substr(bill_code, 3, 13) as bigint)) (PARTITION p1 VALUES [('0'), ('5000000')), PARTITION p2 VALUES [('5000000'), ('10000000')), PARTITION p3 VALUES [('10000000'), ('15000000')), PARTITION p4 VALUES [('15000000'), ('20000000')), PARTITION p999 VALUES[('2921712368983'), ('2921712368985')) ) DISTRIBUTED BY HASH(bill_code) BUCKETS 10;

2. INSERT INTO bill_detail VALUES(5000000), (9000000), (6000000), (15000001);

3. SELECT * FROM bill_detail WHERE bill_code > 1;

Expected behavior (Required)

show all data;

Real behavior (Required)

The query result is as follows:

StarRocks version (Required)

3.1.10

[ShaoxunLi]

The data is improperly filtered through partitions.

[ShaoxunLi]

The PR is used as a reference:https://github.com/StarRocks/starrocks/pull/23059 Is this the expected result? Thanks! @Astralidea

[chenminghua8]

This feature is not used in this way. It is mainly used to perform range partitioning on string columns with prefixes. The data you insert should have a prefix (such as INSERT INTO bill_detail VALUES('FX5000000'), ('FX9000000'), ('FX6000000'), ('FX15000001');)

[ShaoxunLi]

This feature is not used in this way. It is mainly used to perform range partitioning on string columns with prefixes. The data you insert should have a prefix (such as INSERT INTO bill_detail VALUES('FX5000000'), ('FX9000000'), ('FX6000000'), ('FX15000001');)