Float col does not support filtering conditions

[zxf216]

Steps to reproduce the behavior (Required)

CREATE TABLE float_demo ( pk BIGINT(20) NOT NULL COMMENT "", channel FLOAT COMMENT "4 bytes", channel2 decimal(38,10) COMMENT "4 bytes", income DOUBLE COMMENT "8 bytes" ) ENGINE=OLAP DUPLICATE KEY(pk) COMMENT "OLAP" DISTRIBUTED BY HASH(pk) PROPERTIES ( "replication_num" = "1", "in_memory" = "false", "enable_persistent_index" = "false", "replicated_storage" = "true", "compression" = "LZ4" );

insert into zxf.float_demo(pk,channel,channel2,income) values (1,1.1, cast(1.1 as decimal(38,10)),1.1), (2,1.2, cast(1.2 as decimal(38,10)),1.2), (3,1.3, cast(1.3 as decimal(38,10)),1.3);

select * from zxf.float_demo where channel=1.1; Empty set (0.01 sec)

Expected behavior (Required)

select * from zxf.float_demo where channel=1.1; | 1 | 1.1 |

Real behavior (Required)

select * from zxf.float_demo where channel=1.1; no data!!!!!!

StarRocks version (Required)

3.2.5

[kangkaisen]

float is a floating point type, and comparison is inaccurate. You can use the decimal type.

[zxf216]

float is a floating point type, and comparison is inaccurate. You can use the decimal type.

This way, data can be retrieved.

mysql> select * from zxf.float_demo where channel = cast(1.1 as float); +------+---------+--------------+--------+ | pk | channel | channel2 | income | +------+---------+--------------+--------+ | 1 | 1.1 | 1.1000000000 | 1.1 | +------+---------+--------------+--------+ 1 row in set (0.01 sec)

[zxf216]

Default situation： select from zxf.float_demo where channel=1.1; ——>select from zxf.float_demo where cast(channel as double)=1.1;

We expect： select from zxf.float_demo where channel=1.1; ——>select from zxf.float_demo where channel = cast(1.1 as float);

[zxf216]

@kangkaisen income col type is double ，is OK！！！

mysql> select * from zxf.float_demo where income = 1.1; +------+---------+--------------+--------+ | pk | channel | channel2 | income | +------+---------+--------------+--------+ | 1 | 1.1 | 1.1000000000 | 1.1 | +------+---------+--------------+--------+

[zxf216]

Adjust the following code to retrieve the data. mysql> select * from zxf.float_demo where channel = 1.1; +------+---------+--------------+--------+ | pk | channel | channel2 | income | +------+---------+--------------+--------+ | 1 | 1.1 | 1.1000000000 | 1.1 | +------+---------+--------------+--------+ 1 row in set (0.03 sec)

com.starrocks.sql.optimizer.Utils#tryCastConstant

if (!op.isConstantRef() || op.getType().matchesType(descType) || Type.FLOAT.equals(op.getType())
                || descType.equals(Type.FLOAT)) {
            return Optional.empty();
        }

change to:

com.starrocks.sql.optimizer.Utils#tryCastConstant
if (!op.isConstantRef() || op.getType().matchesType(descType)) {
            return Optional.empty();
        }

[zxf216]

@kangkaisen In Clickhouse, columns of type Float64 are acceptable, but columns of type Float32 cannot be used as search conditions. Can StarRocks resolve this issue by using columns of type double?

CREATE TABLE default.test_float64 ( num Float64 COMMENT '项目名', day Date COMMENT '日期' )

select * from default.test_float64;

┌─────────────num─┬────────day─┐ │ 1.2342432432432 │ 2024-09-10 │ └─────────────────┴────────────┘ ┌─num─┬────────day─┐ │ 0.1 │ 2024-09-10 │ └─────┴────────────┘ ┌─num─┬────────day─┐ │ 0.2 │ 2024-09-10 │ └─────┴────────────┘

SELECT * FROM default.test_float64 WHERE num = 1.2342432432432

┌─────────────num─┬────────day─┐ │ 1.2342432432432 │ 2024-09-10 │ └─────────────────┴────────────┘