Repository navigation
Unexpected result when using bigint in ORDER BY #16615
Description
CrateDB version
CrateDB 5.9.0-SNAPSHOT built 848ca13
CrateDB setup information
Manual build following the instructions here: https://github.com/crate/crate/blob/master/devs/docs/basics.rst
Problem description
Consider the following test case. the third query is unexpected because the second query shows that the result should be 9223372036854775807, and the ORDER BY clause should not affect the result. Besides, the NULL value is not in the original table.
Steps to Reproduce
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c1 LONG );
INSERT INTO t0(c1) VALUES (9223372036854775807);
REFRESH TABLE t0;
SELECT t0.c1 FROM t0; -- 9223372036854775807
SELECT t0.c1 FROM t0 WHERE NOT ((false)AND(NULL)); -- 9223372036854775807
SELECT t0.c1 FROM t0 WHERE NOT ((false)AND(NULL)) ORDER BY t0.c1;
-- Expected: 9223372036854775807
-- Actual: NULLActual Result
NULL
Expected Result
9223372036854775807
Activity
mfussenegger commentedon Sep 16, 2024
This is due to ORDER BY using max/min values as sentintel for
null, so the effective range is-9223372036854775808to9223372036854775806It also reproduces with just
SELECT t0.c1 FROM t0 ORDER BY t0.c1We should probably turn the insert into an error
mfussenegger commentedon Sep 17, 2024
Thanks for the report. We're going to narrow the range of allowed long values, so the insert in this case will already result in an error.
See #16629