malwaregarry opened a new issue, #36070: URL: https://github.com/apache/doris/issues/36070
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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. 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);` ### 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);` ### 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); ``` ### Anything Else? Did not test but might not be limited to CASE statements ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
