I don't know the answer, but I would point out that doing exact equality comparisons on floats is fraught with risk.

Maybe it's the comparison between real and intger that causes this.

┌────────────┐
│ typeof(c3) │
├────────────┤
│ real       │
└────────────┘
┌─────────────────────────────┐
│ typeof(9223372036854775807) │
├─────────────────────────────┤
│ integer                     │
└─────────────────────────────┘

Indeed. "Real" is what sqlite calls floating point numbers.

I refer you to my previous comment.

9_223_372_036_854_775_807 happens to be 2^63-1, the largest value that can be represented in a signed 8 byte integer. So the comparison would work with an ordinary table. It works for even larger numbers because SQLite then resorts to store the value as IEEE754 8-byte floats which can handle this particular comparison correctly for even larger values.

The rtree extension stores everything as IEEE754 32-bit floats, where such large numbers are not represented exactly and a comparison with retrieved 4-byte float converted to integer can of course become incorrect. The rtree extension is for example able to handle geographic positions obtained with high precision GPS fine, 4 bytes give enough accuracy for this.

I don't have access to the rtree extension to see if the following will work, but CASTing c3 to INTEGER appears to produce the expected result with a vanilla SQLite table. Might be worth a try with your rtree table to see if it works:

CREATE TABLE v0 ( c1 REAL, c2 REAL, c3 REAL );

INSERT INTO v0 VALUES ( 1, 1, 9223372036854775807 );

SELECT (CAST (c3 AS INTEGER) <= 9223372036854775807) as flag FROM v0;

flag 1

I don't have access to the rtree extension...

Yes you do. Just click on this link: https://sqlite.org/fiddle.

WON'T FIX - WORKS AS DESIGNED

To summarize the points made by others above:

• RTREE stores values as 32-bit IEEE-754 floating point values.

• When you try to store 9223372036854775807 as a 32-bit float, it actually gets stored as 2⁶³ which is 9223372036854775808.

• The expression 9223372036854775808<9223372036854775807 is false.

You can see this as follows:

CREATE VIRTUAL TABLE v0 USING rtree ( c1, c2, c3 );  
INSERT INTO v0 VALUES ( 1, 1, 9223372036854775807 );
SELECT c3,
       c3<9223372036854775807,
       c3=9223372036854775807,
       c3>9223372036854775807 FROM v0;
SELECT c3,
       c3<9223372036854775807.0,
       c3=9223372036854775807.0,
       c3>9223372036854775807.0 FROM v0;

If you will copy/paste the above into Fiddle, you will see that c3>9223372036854775807 but c3=9223372036854775807.0. This seeming paradox is one of the consequences of the way floating point values are stored.

However, the following query return 9.22337203685478e+18. It seems contradictory to your explanation.

SELECT c3 FROM v0 WHERE c3 <= 9223372036854775807;

I'll phrase my reply differently:

SQLite promises to do floating point arithmetic accurately to 15 significant digits. (Less than that in RTREE, but it turns out the reduced accuracy of RTREE does not come into play with this issue.) See here and here.

Your complaint is that SQLite's floating point computations are not accurate to 19 significant digits. But 19 is greater than 15.