Repository navigation
Unexpected result when comparing IP #16690
Description
CrateDB version
CrateDB 5.9.0-SNAPSHOT built 1064365
CrateDB setup information
Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst
Problem description
Consider the test case below. The third query could be unexpected since if the second query is evaluated to TRUE, which shows that the expression t0.c0 <= '41.119.195.181' is TRUE, thus the third query should return the row in the table. Otherwise the second query is unexpected.
Steps to Reproduce
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 IP);
INSERT INTO t0(c0) VALUES ('192.168.0.1');
REFRESH TABLE t0;
SELECT * FROM t0; -- 192.168.0.1
SELECT (t0.c0 <= '41.119.195.181' ) FROM t0; -- TRUE
SELECT * FROM t0 WHERE (t0.c0 <= '41.119.195.181' );
-- Expected: 192.168.0.1
-- Actual: 0 rows
-- original query:
SELECT * FROM t0 WHERE (NOT (t0.c0 BETWEEN CAST(695714741 AS IP) AND t0.c0)) UNION ALL SELECT * FROM t0 WHERE (NOT (NOT (t0.c0 BETWEEN CAST(695714741 AS IP) AND t0.c0))) UNION ALL SELECT * FROM t0 WHERE (((NOT (t0.c0 BETWEEN CAST(695714741 AS IP) AND t0.c0))) IS NULL); -- empty set
Actual Result
As mentioned above.
Expected Result
As mentioned above.
Activity
Fix comparison of IPs
Fix comparison and ORDER BY of IPs
Fix comparison and ORDER BY of IPs
Fix comparison and ORDER BY of IPs
Fix comparison and ORDER BY of IPs
matriv commentedon Oct 1, 2024
Thanks for reporting this issue @suyZhong ! The fix will be available with the next hotfix release.
Fix comparison and ORDER BY of IPs