starRocks3.1.11版本decimal类型字段存在的问题

[gebilaowangseven]

Steps to reproduce the behavior (Required)

1. CREATE TABLE '...'

CREATE TABLE number_test (
a_int INT COMMENT "int类型",
b_bigint BIGINT(20) COMMENT "bigint类型",
c_decimal DECIMAL(12,4) COMMENT "decimal类型"
) ENGINE=OLAP
PRIMARY KEY(a_int)
DISTRIBUTED BY HASH(a_int) BUCKETS 4;

2. INSERT INTO '....'

insert into number_test values(1,10,100.10);
insert into number_test values(2,20,200);

3. SELECT '....'

默认是true
set cbo_decimal_cast_string_strict = false;

select * from number_test where '50.123456' > c_decimal;
select * from number_test where 50.123456 > c_decimal;

Expected behavior (Required)

预期以下两个sql查询结果应该一致：
select * from number_test where '50.123456' > c_decimal;
select * from number_test where 50.123456 > c_decimal;

Real behavior (Required)

真实结果是：
先看下执行计划中

mysql> EXPLAIN select * from number_test where c_decimal >= '200.123456' and c_decimal <= '200.1234';
+------------------------------------------------------------------------------------------+
| Explain String |
+------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: a_int | 2: b_bigint | 3: c_decimal |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 1:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 01 |
| UNPARTITIONED |
| |
| 0:OlapScanNode |
| TABLE: number_test |
| PREAGGREGATION: ON |
| PREDICATES: CAST(3: c_decimal AS VARCHAR) >= '200.123456', 3: c_decimal <= 200.1234 |
| partitions=1/1 |
| rollup: number_test |
| tabletRatio=4/4 |
| tabletList=402300,402304,402308,402312 |
| cardinality=3 |
| avgRowSize=20.0 |
| numNodes=0 |
+------------------------------------------------------------------------------------------+
27 rows in set (0.00 sec)
其中：
PREDICATES: CAST(3: c_decimal AS VARCHAR) >= '200.123456', 3: c_decimal <= 200.1234

看上面的建表语句：c_decimal 是 DECIMAL(12,4) 类型
starRocks3.1.11版本 执行 SET GLOBAL cbo_decimal_cast_string_strict= false; 这个命令后：
如果传入的字符串精度不超过4，starrocks会把字符串会转成decimal来比较;
如果传入的字符串精度超过了4，starrocks就会把c_decimal转成字符串来比较

再看下实际查询结果：
mysql> select * from number_test;
+-------+----------+-----------+
| a_int | b_bigint | c_decimal |
+-------+----------+-----------+
| 1 | 10 | 100.1000 |
| 2 | 20 | 200.0000 |
+-------+----------+-----------+
2 rows in set (0.00 sec)

很显然这样查询出来的结果是正确的

mysql> select * from number_test where 50.123456 > c_decimal;
Empty set (0.00 sec)

很显然这样查询出来的结果是错误的

mysql> select * from number_test where '50.123456' > c_decimal;
+-------+----------+-----------+
| a_int | b_bigint | c_decimal |
+-------+----------+-----------+
| 1 | 10 | 100.1000 |
| 2 | 20 | 200.0000 |
+-------+----------+-----------+
2 rows in set (0.00 sec)

StarRocks version (Required)

• You can get the StarRocks version by executing SQL select current_version()
  mysql> select current_version();
  +-------------------+
  | current_version() |
  +-------------------+
  | 3.1.11-34f131b |
  +-------------------+
  1 row in set (0.00 sec)