You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered:
malwaregarry
changed the title
[Bug] Bug with CASE, BETWEEN and floats
[Bug] NULL expression not recognised with CASE, BETWEEN and floats
Jun 8, 2024
malwaregarry
changed the title
[Bug] NULL expression not recognised with CASE, BETWEEN and floats
[Bug] NULL expression from function not recognised with BETWEEN and floats
Jun 8, 2024
Search before asking
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:
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:
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?
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?
Code of Conduct
The text was updated successfully, but these errors were encountered: