[Bug] NULL expression from function not recognised with BETWEEN and floats

[malwaregarry]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.1.3

What's Wrong?

Some expressions which evaluate to NULL passed into a BETWEEN operation with floats does not result in NULL.

CASE

Given this table:

CREATE TABLE t0(c0 int) DISTRIBUTED BY RANDOM PROPERTIES ("replication_num" = "1");
INSERT INTO t0 (c0) VALUES (1);

This returns NULL:
SELECT CASE (t0.c0) WHEN (0) THEN 0 END from t0;

But this returns false:
SELECT CASE (t0.c0) WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5 from t0;

However, this does not retrieve any row:
SELECT * FROM t0 WHERE (NOT (CASE (t0.c0) WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5));

Neither does this:
SELECT * FROM t0 WHERE ((CASE (t0.c0) WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5) IS NULL);

CAST

Given this table:

CREATE TABLE t0(c0 varchar(1)) DISTRIBUTED BY RANDOM PROPERTIES ("replication_num" = "1");
INSERT INTO t0 (c0) VALUES ("a");

This returns NULL:
SELECT CAST(t0.c0 AS INT) from t0;

But this returns false:
SELECT CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5 from t0;

This does not retrieve any row:
SELECT * FROM t0 WHERE (CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5);

Neither does this:
SELECT * FROM t0 WHERE ((CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5) is null);

What You Expected?

this returns null:
SELECT CASE (t0.c0) WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5 from t0;

this retrieves a row:
SELECT * FROM t0 WHERE ((CASE (t0.c0) WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5) IS NULL);

this returns null:
SELECT CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5 from t0;

this retrieves a row:
SELECT * FROM t0 WHERE ((CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5) is null);

How to Reproduce?

DROP DATABASE IF EXISTS doris0;
CREATE DATABASE doris0;
USE doris0;
CREATE TABLE t0(c0 int) DISTRIBUTED BY RANDOM PROPERTIES ("replication_num" = "1");
INSERT INTO t0 (c0) VALUES (1);

SELECT CASE (t0.c0) WHEN (0) THEN 0 END from t0;
SELECT CASE (t0.c0) WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5 from t0;
SELECT * FROM t0 WHERE (NOT (CASE (t0.c0)  WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5));
SELECT * FROM t0 WHERE ((CASE (t0.c0)  WHEN (0) THEN 0 END BETWEEN 1.1 AND 1.5) is null);

DROP DATABASE IF EXISTS doris0;
CREATE DATABASE doris0;
USE doris0;
CREATE TABLE t0(c0 varchar(1)) DISTRIBUTED BY RANDOM PROPERTIES ("replication_num" = "1");
INSERT INTO t0 (c0) VALUES ("a");

SELECT CAST(t0.c0 AS INT) from t0;
SELECT CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5 from t0;
SELECT * FROM t0 WHERE (CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5);
SELECT * FROM t0 WHERE ((CAST(t0.c0 AS INT) BETWEEN 1.1 AND 1.5) is null);

Anything Else?

Works as expected if we use integers, ie BETWEEN 1 AND 2

Did not test but might not be limited to CASE and CAST functions and BETWEEN

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[github-actions]

We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!